Create new column in table

Hello, this seems like a stupidly easy problem but I cannot find any way to do it

Currently I have a table with three columns. I want to add three additional columns to the table, each of which contain certain values from the second column. In this case, column 4 should contain all the elements in column 2 that start with "E", column 5 should contain all the elements in column 2 that start with "I", co;umn 6 should contain all the elements in column 2 that start with "P"

And then these new columns need to align with their respective values in columns 1 and 3 of the table

How would I do something like this? I've attached a screenshot of the table that I want to transform

Thank you

[Steven Lord]

What do you want to fill in the "holes" in columns 4 through 6 where the corresponding elements in column 2 don't start with the correct letter?

[Adam Danz]

Why not use indexing rather than adding duplicate data to the table?

[Walter Roberson]

So if column 2 starts with 'E' then the content of column 2 should be moved to column 4, and if it starts with 'I' then the contents of column 2 should be moved to column 5, and so on?

E8   __   __
E9   __   __
__   I1   __
__   I2   __

[Guillaume]

I agree with Adam, the addition of redundant columns seem unnecessary and would probably make whatever processing you want to do afterward more complicated rather than easier.

I would think that rather than adding 3 variables, a single categorical variable with value of 'E', 'I', or 'P' would be a lot more useful.

More details of the reason behind this request needed.

[Adam Fitchett]

Thank you all for your answers

I want to run a multi-level mixed regression on the data The letters represent different groups, and so they need to be in separate columns so they can be specified as different groups for the regression. But for that they need to be matched with their corresponding time and log impedance values

[Adam Danz]

Depending on what function you're using, there's probably a grouping variable input in which case Guillaume's suggestion to make a group column would be the best solution.

His suggestion and your original idea are both demonstrated in my answer.

[Guillaume]

You will probably want to apply the same code to each group. Splitting the groups into different variables will only make that more difficult. Particularly as you now have to differentiate between empty and non-empty elements in each group.

[Adam Fitchett]

I want to use fitlme https://uk.mathworks.com/help/stats/fitlme.html

I'm essentially trying to find a way in MATLAB to replicate the Stata code in the attached image

I dont have easy access to Stata so i need to perform the regression in Matlab

How would I specify the groups in the syntax here?

[Adam Danz]

If you read about the first input to fitlme(), the table is "input data, which includes the response variable, predictor variables, and grouping variables, specified as a table or dataset array."

Grouping variables (<-- see link for more info) can be a character array or a cell array of characters which is exactly what my answer demonstrates. Have you tried that yet?

Answer 1

[Adam Danz]

Here's a demo that creates a similar table to the table you shared in the png file. It then shows how create indices that find rows of the table where the "Type" begins with "E" and "I" (you can add the "P" index).

% Create table
TIME = zeros(20,1); 
Type = cellstr([("E"+(1:10))';("I"+(1:10))']);
LogImpedance = rand(20,1)*4; 
T = table(TIME,Type,LogImpedance); 

% Create indices for each Type
firstChar = cellfun(@(x)x(1),T.Type); 
isE = firstChar == 'E'; 
isI = firstChar == 'I'; 

Use the indices to select rows of the table

For example, if you want to take the sum of "LogImpedance" for all "E" types,

sum(T.LogImpedance(isE))

Append the table with duplicate data if you must

This section shows how to append the table with duplicate data as you described (probably unnecessary).

% (Assuming the first block of code above has been executed)
% Create new columns
newColumns = cell(size(T,1),2); % Change '2' to add more or less columns
newColumns(isE,1) = T.Type(isE); 
newColumns(isI,2) = T.Type(isI); 
Tappend = array2table(newColumns,'VariableNames',{'isE','isI'}); 

% Append table
Tnew = [T,Tappend]; 

Appending the table with a categorical column (**GROUPING VARIABLE**)

(as suggested by Guillaume)

% (Assuming the first block of code above has been executed)
Tnew = [T, table(firstChar,'VariableNames',{'category'})]