MATLAB Answers

Loop for randomisation assignment in table columns

37 ビュー (過去 30 日間)
Anna
Anna 2021 年 1 月 15 日
コメント済み: Anna 2021 年 1 月 17 日
I have imported a spreadshet and 'extracted' some certain columns from and made a table with them.
T=readtable(fullfile(datapath,filename), 'Sheet', sheet_name)
T(:, [1,27,28,31,49,59:76])
Now, I need to add 6 columns at the back of the new table named A to F. One of the existing columns contains a randomisation conditions. Depending on which condition (1 or 2) is assigned to each subject, I now need the the A to F lines be filled out with two different orders, e.g. for 1 the lines should be filled out with WE, BC, CCC, LB, RO, NN (see picture).
I thought about making a for or if loop for the two conditions but I don't undertsand how to access the randomisation column and fill it out in the right way.

  0 件のコメント

サインインしてコメントする。

採用された回答

dpb
dpb 2021 年 1 月 16 日
編集済み: dpb 2021 年 1 月 17 日
From above modifications of IA's original using the idea of a lookup table instead of loop...
% set path to where the session 1 protocol spreadsheet is
datapath = ...;
filename = ...; %specified in original code
T=readtable(fullfile(datapath,filename), 'Sheet', 'file_name')
% now select only certain columns
T(:, [1,27,28,31,49,59:76]);
% Create A - F columns
snacks="Snack"+['A':'F'].';
T=[T, array2table(categorical(strings(height(T),numel(snacks)),'VariableNames',snacks)];
ROWS=categorical(["WE","BC","CCC","LB","RO","NN";
"LB","RO","WE","BC","NN","CCC"]);
ix=isfinite(T.TasteTest_Randomization);
T(ix,(snacks))=ROWS(T.TasteTest_Randomization(ix),:);
The above assumes the values in the original table in the retained columns include the variable TasteTest_Randomization shown in the example; therefore do not need the initialization of it shown in the code below:
% Get random numbers
T.TasteTest_Randomization=randi(2,height(T),1);
Perhaps it's this being there that creates a new random order that is what led to the symptom that you thought the assignment seemed to be "random" and not what you expected; the above uses what's already there.
This also generalizes the reference columns by using the target column variable names.

  0 件のコメント

サインインしてコメントする。

その他の回答 (2 件)

Image Analyst
Image Analyst 2021 年 1 月 15 日
Try this:
t = table(randi(9, 5, 1), randi(9, 5, 1)) % Original table
% Create A - F columns
rows = height(t);
% Append columns
t = [t, table('Size', [rows, 6], 'VariableType', ["string", "string", "string", "string", "string", "string"], 'VariableNames', {'A', 'B', 'C', 'D', 'E', 'F'})]
% Get random numbers
randomNumbers = randi(2, rows, 1);
for row = 1 : rows
if randomNumbers(row) == 1
% Assign the first order.
t.A(row) = "WE";
t.B(row) = "BC";
t.C(row) = "CCC";
t.D(row) = "LB";
t.E(row) = "RO";
t.F(row) = "NN";
else
% Assign the second order.
t.A(row) = "LB";
t.B(row) = "RO";
t.C(row) = "WE";
t.D(row) = "BC";
t.E(row) = "NN";
t.F(row) = "CCC";
end
end
t
You'll see
t =
5×8 table
Var1 Var2 A B C D E F
____ ____ ____ ____ _____ ____ ____ _____
2 1 "LB" "RO" "WE" "BC" "NN" "CCC"
3 3 "WE" "BC" "CCC" "LB" "RO" "NN"
3 8 "LB" "RO" "WE" "BC" "NN" "CCC"
4 1 "WE" "BC" "CCC" "LB" "RO" "NN"
5 9 "WE" "BC" "CCC" "LB" "RO" "NN"
Notice the two different orderings of the columns.

  3 件のコメント

Anna
Anna 2021 年 1 月 16 日
Thanks so much for your answer!
However, there are still some problems, maybe I did not describe my question well enough either.
I tried to modify your suggestion as follows:
% set path to where the session 1 protocol spreadsheet is
datapath = ...;
filename = ...; %specified in original code
T=readtable(fullfile(datapath,filename), 'Sheet', 'file_name')
% now select only certain columns
T(:, [1,27,28,31,49,59:76])
% Create A - F columns
rows = height(T);
% Append columns
N = [T(:, [1,27,28,31,49,59:76]), table('Size', [rows, 6], 'VariableType', ["string", "string", "string", "string", "string", "string"], 'VariableNames', {'SnackA', 'SnackB', 'SnackC', 'SnackD', 'SnackE', 'SnackF'})]
% Get random numbers
randomValue = randi(2, rows, 1);
for row = 1 : rows
if randomValue(row) == 1
% Assign the first order.
N.SnackA(row) = "WE";
N.SnackB(row) = "BC";
N.SnackC(row) = "CCC";
N.SnackD(row) = "LB";
N.SnackE(row) = "RO";
N.SnackF(row) = "NN";
elseif randomValue(row) == 2
% Assign the second order.
N.SnackA(row) = "LB";
N.SnackB(row) = "RO";
N.SnackC(row) = "WE";
N.SnackD(row) = "BC";
N.SnackE(row) = "NN";
N.SnackF(row) = "CCC";
else randomValue (row) == 'NaN'
% if NaN:
N.SnackA(row) = "";
N.SnackB(row) = "";
N.SnackC(row) = "";
N.SnackD(row) = "";
N.SnackE(row) = "";
N.SnackF(row) = "";
end
end
N
If I try it like that, however, it seems to fill it out randomly. I actually don't understand how it would get the information from the randomizaion column.
(I ommitted the irrelevant columns for clarity. The randomization row would be the 5th from the selected table T.)
SubjectID TasteTest_Randomization SnackA SnackB SnackC SnackD SnackE SnackF
_________________ _______________________ ______ ______ ______ ______ ______ ______
{'TUE002_000001'} 1 "WE" "BC" "CCC" "LB" "RO" "NN"
{'TUE002_000002'} 1 "WE" "BC" "CCC" "LB" "RO" "NN"
{'TUE002_000003'} 2 "WE" "BC" "CCC" "LB" "RO" "NN"
{'TUE002_000004'} NaN "LB" "RO" "WE" "BC" "NN" "CCC"
{'TUE002_000005'} 1 "WE" "BC" "CCC" "LB" "RO" "NN"
{'TUE002_000006'} 2 "WE" "BC" "CCC" "LB" "RO" "NN"
{'TUE002_000007'} 2 "LB" "RO" "WE" "BC" "NN" "CCC"
Image Analyst
Image Analyst 2021 年 1 月 16 日
I can help more once you've uploaded your data file. Make it easy for us to help you, not hard.
Anna
Anna 2021 年 1 月 17 日
I am sorry, of course I did not mean to make it hard for you to help me. Thank you for pointing it out, this was only my second post/question on mathworks. I hope I will do better if have more questions in the future. Thank you a lot for your help. I will try out your suggestions

サインインしてコメントする。


dpb
dpb 2021 年 1 月 15 日
編集済み: dpb 2021 年 1 月 15 日
% prepare lookup table first...
ROWS=cellstr(char('WE', 'BC', 'CCC', 'LB', 'RO', 'NN')).';
ROWS=categorical([ROWS;ROWS([4 5 1 6 2 3])]);
% populate the randomization column of table
t=array2table(randi(2,5,1),'VariableNames',{'Rand'});
% the engine to augment table with additional columns based on randomized values
t=[t array2table(ROWS(t.Rand,:),'VariableNames',string(['A':'F'].'))];
For a sample test vector here, the result of above was
>> t =
5×7 table
Rand A B C D E F
____ __ __ ___ __ __ ___
1 WE BC CCC LB RO NN
2 LB RO WE NN BC CCC
2 LB RO WE NN BC CCC
2 LB RO WE NN BC CCC
1 WE BC CCC LB RO NN
>>
If the table once exists, then to update it is trivial...
t.Rand=randi(2,5,1); % generate new randomized order
t{:,2:end}=ROWS(t.Rand,:); % reset the dependent columns
giving
>> t
t =
5×7 table
Rand A B C D E F
____ __ __ ___ __ __ ___
2 LB RO WE NN BC CCC
1 WE BC CCC LB RO NN
2 LB RO WE NN BC CCC
1 WE BC CCC LB RO NN
2 LB RO WE NN BC CCC
>>
Your indices will be different depending on the size of your overall table, of course...

  2 件のコメント

Anna
Anna 2021 年 1 月 16 日
Thank you! That looks good but what if in my existing table there aready exists the randomization column I want to make use of for populating the added columns from the new table? (see my answer to another solution above)
I am sorry, I think the phrasing of my initial question was weak.
dpb
dpb 2021 年 1 月 16 日
Just use it. Since you didn't give us anything to use (see IA's plaint there as well), I called the variable "Rand", t.Rand is the reference to that column in the table. Substitute whatever are your variable and table names instead.
That's what the "engine" does; I just had to have something to use, first. It needs, of course, the lookup table to refer to.
What's not addressed above is the "NaN' -- to have a clean solution you need to deal with it; what's to go in those rows? Whatever is the value, then fill the whole area with that first, then use the above except instead of the unqualified reference to t.Rand use
ix=isfinite(t.Rand); % the rows that aren't NaN
t{ix,2:end}=ROWS(t.Rand(ix),:); % only assign those rows

サインインしてコメントする。

Community Treasure Hunt

Find the treasures in MATLAB Central and discover how the community can help you!

Start Hunting!

Translated by