Loop for randomisation assignment in table columns

2 次查看(过去 30 天)
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.

采纳的回答

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.

更多回答(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 个评论
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
编辑: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

请先登录,再进行评论。

类别

Help CenterFile Exchange 中查找有关 Cell Arrays 的更多信息

Community Treasure Hunt

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

Start Hunting!

Translated by