Loop for randomisation assignment in table columns

1 Ansicht (letzte 30 Tage)
Anna
Anna am 15 Jan. 2021
Kommentiert: Anna am 17 Jan. 2021
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.

Akzeptierte Antwort

dpb
dpb am 16 Jan. 2021
Bearbeitet: dpb am 17 Jan. 2021
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.

Weitere Antworten (2)

Image Analyst
Image Analyst am 15 Jan. 2021
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 Kommentare
Image Analyst
Image Analyst am 16 Jan. 2021
I can help more once you've uploaded your data file. Make it easy for us to help you, not hard.
Anna
Anna am 17 Jan. 2021
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

Melden Sie sich an, um zu kommentieren.


dpb
dpb am 15 Jan. 2021
Bearbeitet: dpb am 15 Jan. 2021
% 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 Kommentare
Anna
Anna am 16 Jan. 2021
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 am 16 Jan. 2021
Bearbeitet: dpb am 16 Jan. 2021
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

Melden Sie sich an, um zu kommentieren.

Kategorien

Mehr zu Cell Arrays finden Sie in Help Center und File Exchange

Community Treasure Hunt

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

Start Hunting!

Translated by