Loop for randomisation assignment in table columns

2 次查看（过去 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.

请先登录，再回答此问题。

采纳的回答

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 个评论
显示 1更早的评论
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 Center 和 File Exchange 中查找有关 Cell Arrays 的更多信息

标签

Community Treasure Hunt

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

Start Hunting!

Translated by