Loop for randomisation assignment in table columns

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.

Answer 1

[dpb]

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.

Answer 2

[Image Analyst]

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.

[Anna]

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]

I can help more once you've uploaded your data file. Make it easy for us to help you, not hard.

[Anna]

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

Answer 3

[dpb]

% 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...

[Anna]

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]

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