Set number of rows in output variable using indexing within for loop, add variable to new table

I have a table with 74 rows and 5 columns/variables. I'm attempting to use indexing and a for loop to subtract a calculated value from rows in a column that match an identifier code which I have put in an adjacent column. I'm running into a problem where the code+variable matches are not the same number of rows as my original table, which I want to add the newly calcualted columns to. I tried the if - size - end loop but all that did was add zeros to the bottom of the rows to make 74, but I need the values to remain in the same order as the input variable. I would like to put the two calculated variable columns directly after the variable column in the original table. Please see attached. Thank you!

Tab=readtable('D021524.xlsx',VariableNamingRule='preserve');
ID   = ["DMCQ","TGA","GMLC","WGG","RSVG","MQ","DMS","CRG","CS","GMLCV"];



Beta=[0.58;1]

Beta = 2×1

0.5800 1.0000

<mw-icon class=""></mw-icon>

<mw-icon class=""></mw-icon>

T=[573.15;1173.15]

T = 2×1

1.0e+03 * 0.5731 1.1732

<mw-icon class=""></mw-icon>

<mw-icon class=""></mw-icon>

Temp=T

Temp = 2×1

1.0e+03 * 0.5731 1.1732

<mw-icon class=""></mw-icon>

<mw-icon class=""></mw-icon>

BDplg_w1 = (2.91-0.76.*Beta(:,1)) .* (1e6./T.^2) - 3.41 - 0.41.*Beta(:,1); % Fractionation plag-water OT1967

% %Subtable:
idx = matches(Tab.GroupID,ID)

idx = 71x1 logical array
   1
   1
   1
   1
   1
   1
   1
   1
   1
   1
   1
   1
   1
   1
   1
   1
   1
   1
   1
   1
   1
   1
   1
   1
   1
   1
   1
   1
   1
   1
   1
   1
   1
   1
   1
   1
   1
   1
   1
   1
   1
   1
   1
   1
   1
   1
   1
   1
   1
   1
   1
   1
   1
   1
   1
   1
   1
   1
   1
   1
   1
   1
   1
   1
   1
   1
   1
   1
   1
   1
   1

out = Tab(idx,["GroupID","δ18O WR","δ18O WRr1","δ18O WRr2"]);


for ii = 1:numel(ID(:,:))
    ix = (matches(out.GroupID,'DMCQ'))
    wr1 = out{ix,"δ18O WR"} - BDplg_w1(1,1);
   
    wr2 = out{ix,"δ18O WRr1"} - BDplg_w1(1,1);
  % 
  % if size(wr1,1) < 74 % this didn't work 
  %   wr1(74,1) = 0;
  % end

    out{ix,"H2O in EQ"} = wr1;
    out = addvars(out,wr1, 'After','δ18O WR')
    
end

ix = 71x1 logical array
   0
   1
   1
   1
   1
   0
   0
   0
   0
   0
   1
   0
   0
   0
   1
   0
   0
   0
   1
   0
   0
   0
   0
   0
   0
   0
   0
   0
   0
   0
   0
   0
   0
   0
   0
   0
   0
   0
   0
   0
   0
   0
   0
   0
   0
   0
   0
   0
   0
   0
   0
   0
   0
   0
   0
   0
   0
   0
   0
   0
   0
   0
   0
   0
   0
   0
   0
   0
   0
   0
   0

Warning: The new variables being added to the table have fewer rows than the table. They have been extended with rows containing default values.

Error using . (line 507)
To assign to or create a variable in a table, the number of rows must match the height of the table.

Error in tabular/addvars (line 160)
b = move(b).dotAssign(newvarnames{ii},varargin{ii}); % b.(newvarnames{ii}) = varargin{ii}

[Stephen23]

Preallocate the entire column, add data to it using indexing (optionally on each loop iteration), then assign it to the table.

[Erin Summerlin-Donofrio]

Tab=readtable('D021524.xlsx',VariableNamingRule='preserve');

Error using readtable (line 517)
Unable to find or open 'D021524.xlsx'. Check the path and filename or file permissions.

ID   = ["DMCQ","TGA","GMLC","WGG","RSVG","MQ","DMS","CRG","CS","GMLCV"];
Beta=[0.58;1]
T=[573.15;1173.15]

BDplg_w1 = (2.91-0.76.*Beta(:,1)) .* (1e6./T.^2) - 3.41 - 0.41.*Beta(:,1); % Fractionation plag-water OT1967
% %Subtable:
idx = matches(Tab.GroupID,ID)

for ii = 1:numel(idx) 
    ix = (matches(out.GroupID,'DMCQ'))
    
    wr1=zeros(1,ii)
    wr1 = out{ix,"δ18O WR"} - BDplg_w1(1,1);

    wr2 = out{ix,"δ18O WRr1"} - BDplg_w1(1,1);
  % 
  % if size(wr1,1) < 74
  %   wr1(74,1) = 0;
  % end

    out{ix,"H2O in EQ"} = wr1;
   % out = addvars(out,wr1, 'After','δ18O WR')
end

So I changed ii to 1:numel(idx) to get 74 rows. Wr1 and Wr2 are still only the number of "DMCQ" in ix, which are the matches definied by ix. I don't know how to preallocate the columns. I tried writing wr1=zeroes(1,ii) following the examples I saw on the preallocation Mathworks page where they use the same variable name but this is incorrect somehow but I can't figure out how.

[Stephen23]

tbl = readtable('D021524.xlsx', VariableNamingRule='preserve');
tbl(:,"Var"+digitsPattern) = []

tbl = 71x5 table
    Sample ID    GroupID     δ18O WR    δ18O WRr1    δ18O WRr2
    _________    ________    _______    _________    _________

        1        {'TGA' }      7.6         6.7          NaN   
        2        {'DMCQ'}      4.3         NaN          NaN   
        3        {'DMCQ'}      4.3         NaN          NaN   
        4        {'DMCQ'}      4.7         NaN          NaN   
        5        {'DMCQ'}      6.4         NaN          NaN   
        6        {'TGA' }      8.4         NaN          NaN   
        7        {'TGA' }      7.6         6.6            7   
        8        {'TGA' }      7.6         7.4          NaN   
        9        {'TGA' }      8.7         NaN          NaN   
       10        {'TGA' }      7.5         7.8          NaN   
       11        {'DMCQ'}        5         NaN          NaN   
       12        {'TGA' }      4.7         NaN          NaN   
       13        {'TGA' }      3.4         2.6          3.2   
       14        {'TGA' }      3.2         NaN          NaN   
       15        {'DMCQ'}      2.4         NaN          NaN   
       16        {'TGA' }      7.6         NaN          NaN   

% The following three vectors must have the same number of elements:
ID   = ["DMCQ";  "TGA"];
Beta = [  0.58;      1];
T    = [573.15;1173.15];

BDplg_w1 = (2.91-0.76.*Beta(:,1)) .* (1e6./T.^2) - 3.41 - 0.41.*Beta(:,1); % Fractionation plag-water OT1967

wr0 = nan(height(tbl),1); % preallocate
wr1 = nan(height(tbl),1); % preallocate

for ii = 1:numel(ID)
    idx = matches(tbl.GroupID,ID(ii));
    
    wr0(idx) = tbl{idx,"δ18O WR"}   - BDplg_w1(ii);
    wr1(idx) = tbl{idx,"δ18O WRr1"} - BDplg_w1(ii);
end

tbl = addvars(tbl,wr0, 'After','δ18O WR');
tbl = addvars(tbl,wr1, 'After','δ18O WRr1');

display(tbl)

tbl = 71x7 table
    Sample ID    GroupID     δ18O WR      wr0      δ18O WRr1     wr1      δ18O WRr2
    _________    ________    _______    _______    _________    ______    _________

        1        {'TGA' }      7.6       9.8578       6.7       8.9578       NaN   
        2        {'DMCQ'}      4.3      0.43123       NaN          NaN       NaN   
        3        {'DMCQ'}      4.3      0.43123       NaN          NaN       NaN   
        4        {'DMCQ'}      4.7      0.83123       NaN          NaN       NaN   
        5        {'DMCQ'}      6.4       2.5312       NaN          NaN       NaN   
        6        {'TGA' }      8.4       10.658       NaN          NaN       NaN   
        7        {'TGA' }      7.6       9.8578       6.6       8.8578         7   
        8        {'TGA' }      7.6       9.8578       7.4       9.6578       NaN   
        9        {'TGA' }      8.7       10.958       NaN          NaN       NaN   
       10        {'TGA' }      7.5       9.7578       7.8       10.058       NaN   
       11        {'DMCQ'}        5       1.1312       NaN          NaN       NaN   
       12        {'TGA' }      4.7       6.9578       NaN          NaN       NaN   
       13        {'TGA' }      3.4       5.6578       2.6       4.8578       3.2   
       14        {'TGA' }      3.2       5.4578       NaN          NaN       NaN   
       15        {'DMCQ'}      2.4      -1.4688       NaN          NaN       NaN   
       16        {'TGA' }      7.6       9.8578       NaN          NaN       NaN   

If you have more than two columns/variables that you need to process in this way then use a loop for those too.

[Erin Summerlin-Donofrio]

Got it. Thank you! So the indexing the way its set up will only handle two columns/variables. Is this because of how the code is written (and its too complicated or can't be done for more than two columns/variables) or for some other reason? Just trying to understand why there isn't a way to index each different match in ID at once like I was trying to do in the original post.

[Erin Summerlin-Donofrio]

I think I can use all of the ID matches I require and just have Beta be defined as each value I need for each match and then do them all at the same temperature step so the arrays are all the same length.

tbl = readtable('D021524.xlsx', VariableNamingRule='preserve');
tbl(:,"Var"+digitsPattern) = []
% The following three vectors must have the same number of elements:
ID   = ["DMCQ","TGA","GMLC","WGG"]
    %"RSVG","MQ","DMS","CRG","CS","GMLCV"];
Beta = [  0.58; 1; 0.58; 1];
T    = [573.15;573.15;573.15;573.15;];

BDplg_w1 = (2.91-0.76.*Beta(:,:)) .* (1e6./T.^2) - 3.41 - 0.41.*Beta(:,:); % Fractionation plag-water OT1967

wr0 = nan(height(tbl),1); % preallocate
wr1 = nan(height(tbl),1); % preallocate
j   = nan(height(tbl),1); % preallocate
k   = nan(height(tbl),1); % preallocate

for ii = 1:numel(ID)
    idx = matches(tbl.GroupID,ID(ii));
    
    wr0(idx) = tbl{idx,"δ18O WR"}   - BDplg_w1(ii);
    wr1(idx) = tbl{idx,"δ18O WRr1"} - BDplg_w1(ii);
    j(idx)=Beta(ii);
    k(idx)= T(ii);


 
end

tbl = addvars(tbl,wr0, 'After','δ18O WR');
tbl = addvars(tbl,j, 'After', 'wr0');
tbl = addvars(tbl,wr1, 'After','δ18O WRr1');

display(tbl)

[Stephen23]

"Is this because of how the code is written (and its too complicated or can't be done for more than two columns/variables) or for some other reason?"

Of course it can be done. The reason I would not is that I am lazy (and you should be too). Computers are really only good at one thing: repeating simple tasks in loops. So when you copy-and-paste (and then tweak) lines of almost-identical code then you are just doing the computer's job for it.

If you can easily identify the IDs (either by writing a short list or automatically using e.g. PATTERN matching on the table variable names) then you can easily loop over them using indexing. It probably requires an outer nested loop, possibly vectors of the corresponding parameters, and some indexing (i.e. very basic MATLAB stuff).

Of course there is nothing stopping you from doing the computer's work for it by copy-and-pasting lines of code for every ID that you want to process. Note however that:

• more code increases the chances of bugs
• more code means bugfixing/maintanenance in more locations (i.e. it is simpler to modify one line in a nested loop than fifty almost-identical lines of code that you have laboriously copy-and-pasted).

It depends on what you are doing: writing generalised code relies on identifying patterns in the data that you are processing and making use of them. For example, look at the lines of code that define/process WR0 and WR1, they are really all the same, the only differences are the IDs themselves. So rather than copy-and-pasting more of those lines add an outer loop and iterate over those IDs!

This also makes your code more generalised: later when your run your code on a different file with slightly different IDs you only need to change one list of IDs (or, if you can identify them from the file data, change nothing at all), i.e. you would not need to go through and change all of your code.

Answer 1

[Siddharth Bhutiya]

Based on the description it looks like you have one value of BDplg_w1 for each unique GroupID. If that is the case then instead of using indexing to find matching rows in the table, you can instead use a dictionary that maps a GroupID to the corresponding BDplg_w1 value. So something like the following

tbl = readtable("https://www.mathworks.com/matlabcentral/answers/uploaded_files/1826322/D021524.xlsx", VariableNamingRule='preserve');
tbl(:,"Var"+digitsPattern) = [];
ID   = ["DMCQ","TGA","GMLC","WGG"];
Beta = [  0.58; 1; 0.58; 1];
T    = [573.15;573.15;573.15;573.15;];
val = (2.91-0.76.*Beta(:,:)) .* (1e6./T.^2) - 3.41 - 0.41.*Beta(:,:); % Fractionation plag-water OT1967
BDplg_w1 = dictionary(ID',val)

BDplg_w1 =

  dictionary (string --> double) with 4 entries:

    "DMCQ" --> 3.8688
    "TGA"  --> 2.7249
    "GMLC" --> 3.8688
    "WGG"  --> 2.7249

Now you can use the group id vector from your table to lookup BDplg_w1 values for each group id. You can also specify NaN as your fallback value, so if a group is missing from your dictionary it will be populated with a NaN.

WR0 = tbl.("δ18O WR") - lookup(BDplg_w1, tbl.GroupID, FallBackValue=NaN);
tbl1 = addvars(tbl,WR0,After="δ18O WR")

tbl1 = 71x6 table
    Sample ID    GroupID     δ18O WR      WR0      δ18O WRr1    δ18O WRr2
    _________    ________    _______    _______    _________    _________

        1        {'TGA' }      7.6       4.8751       6.7          NaN   
        2        {'DMCQ'}      4.3      0.43123       NaN          NaN   
        3        {'DMCQ'}      4.3      0.43123       NaN          NaN   
        4        {'DMCQ'}      4.7      0.83123       NaN          NaN   
        5        {'DMCQ'}      6.4       2.5312       NaN          NaN   
        6        {'TGA' }      8.4       5.6751       NaN          NaN   
        7        {'TGA' }      7.6       4.8751       6.6            7   
        8        {'TGA' }      7.6       4.8751       7.4          NaN   
        9        {'TGA' }      8.7       5.9751       NaN          NaN   
       10        {'TGA' }      7.5       4.7751       7.8          NaN   
       11        {'DMCQ'}        5       1.1312       NaN          NaN   
       12        {'TGA' }      4.7       1.9751       NaN          NaN   
       13        {'TGA' }      3.4      0.67512       2.6          3.2   
       14        {'TGA' }      3.2      0.47512       NaN          NaN   
       15        {'DMCQ'}      2.4      -1.4688       NaN          NaN   
       16        {'TGA' }      7.6       4.8751       NaN          NaN   

If you can somehow programmatically get all your desired variable names, then you can do something like this to calculate and add these values to your table in a loop.

vars = tbl.Properties.VariableNames;
vars = vars(startsWith(vars,"δ"))

vars = 1x3 cell array
    {'δ18O WR'}    {'δ18O WRr1'}    {'δ18O WRr2'}

for i = 1:numel(vars)
    var = vars{i};
    wr = tbl.(var) - lookup(BDplg_w1, tbl.GroupID, FallBackValue=NaN);
    tbl = addvars(tbl, wr, After=var, NewVariableNames="WR"+i);
end
tbl

tbl = 71x8 table
    Sample ID    GroupID     δ18O WR      WR1      δ18O WRr1      WR2       δ18O WRr2      WR3  
    _________    ________    _______    _______    _________    ________    _________    _______

        1        {'TGA' }      7.6       4.8751       6.7         3.9751       NaN           NaN
        2        {'DMCQ'}      4.3      0.43123       NaN            NaN       NaN           NaN
        3        {'DMCQ'}      4.3      0.43123       NaN            NaN       NaN           NaN
        4        {'DMCQ'}      4.7      0.83123       NaN            NaN       NaN           NaN
        5        {'DMCQ'}      6.4       2.5312       NaN            NaN       NaN           NaN
        6        {'TGA' }      8.4       5.6751       NaN            NaN       NaN           NaN
        7        {'TGA' }      7.6       4.8751       6.6         3.8751         7        4.2751
        8        {'TGA' }      7.6       4.8751       7.4         4.6751       NaN           NaN
        9        {'TGA' }      8.7       5.9751       NaN            NaN       NaN           NaN
       10        {'TGA' }      7.5       4.7751       7.8         5.0751       NaN           NaN
       11        {'DMCQ'}        5       1.1312       NaN            NaN       NaN           NaN
       12        {'TGA' }      4.7       1.9751       NaN            NaN       NaN           NaN
       13        {'TGA' }      3.4      0.67512       2.6       -0.12488       3.2       0.47512
       14        {'TGA' }      3.2      0.47512       NaN            NaN       NaN           NaN
       15        {'DMCQ'}      2.4      -1.4688       NaN            NaN       NaN           NaN
       16        {'TGA' }      7.6       4.8751       NaN            NaN       NaN           NaN

Hope this helps!

Answer 2

[Matt J]

I would like to put the two calculated variable columns directly after the variable column in the original table.

And you want new columns added with every pass through the loop? That sounds like a strange thing to do, but if you insist it's what you want, then...

miss=nan(height(out),1);

for ii = 1:numel(ID(:,:))
    
    ix = (matches(out.GroupID,'DMCQ'));
    
    wr1 = out{ix,"δ18O WR"} - BDplg_w1(1,1);
    wr2 = out{ix,"δ18O WRr1"} - BDplg_w1(1,1);
 
    out{ix,"H2O in EQ"} = wr1;
    
    out = addvars(out,miss,miss, 'After',3);
    out{ix,4:5}=[wr1,wr2];
    
end

[Erin Summerlin-Donofrio]

Close! That get's me the 74 rows but I would like to subtract the calculated from each following column of data. I've attached an example.

[Matt J]

I still don't really understand what the loop is supposed to be doing. You never use the loop variable ii anywhere.