Calculations involving indexed variables and creating data tables

I'm running calculations from a spreadsheet and I have some variables indexed. I want to subtract calculated variables from the indexed variables from my spreadsheet. I need to use values that are dependent on samplele type, for example for the DMCQ samples, I need to use a Beta of 0.58, and for the TGA samples a Beta of 0.1. The way I have the indexing currently set up puts the DMCQ and TGA data into their own columns. This has been fine for making plots but I need to create a final data Excel table of my results.

Is there a way to keep the indexing but have the output values maintain the same order (the order of the Sample ID or the Group ID column) so I can more easily create the table.

Example:

Take all the DMCQ samples from column F and subtract BDPlg_w1 @ 250 degrees Beta=0.58, take the TGA samples from column F and subtract BDPlg_w1 @ 250 degrees Beta=0.2, and make the result one new column beside F in the final table.

Thank you!

%Code from spreadsheet
clear variables
T=readtable('ESDMatlabAsk021125.xlsx',VariableNamingRule='preserve')
%MCQ Min Sep Oxygen Fluids in EQ
C=[250:150:400]
K=[C+273.15]
Temp=K
Beta=[0.58,0.1]

%Fractionation Equations

BDplg_w1 = (2.91-0.76.*Beta(:,1)) .* (10^6./Temp.^2) - 3.41 - 0.41.*Beta(:,1); % Fractionation plag-water OT1967
BDpx_w1=-1.27;%Mathewsetal1983a - Three isotope exchange method. -1.27@600C, -1.08@700C, -0.98@800C
BDpx_w2=3.08.* (10^6./Temp.^2)-5.13.*(10^3./Temp) %Vho et al 2019
BDep_w=3.54.* (10^6./Temp.^2)-5.72.*(10^3./Temp) %Vho et al 2019
BDq_w1=2.51 .* (10^6./Temp.^2)-1.96 %Clayton et al 1972
BDbt_w=3.21.* (10^6./Temp.^2)-6.05.*(10^3./Temp) %Vho et al 2019
BDhbl_w=3.21.* (10^6./Temp.^2)-5.76.*(10^3./Temp) %Vho et al 2019
BDch_w=1.56.* (10^6./Temp.^2)-4.70 %Wenner and Taylor 1971
%Set up indexing 
idx_DMCQ = strcmp(T.GroupID,'DMCQ');
TDMCQ = T(idx_DMCQ,:);
idx_TGA = strcmp(T.GroupID,'TGA');
TTGA = T(idx_TGA,:);
%Indexing for each column
%Whole Rock Mill Creek Quarry 
WR1_DMCQ=[TDMCQ.("δ18O WR")];
WR2_DMCQ=[TDMCQ.("δ18O WRr1")];
WR3_DMCQ=[TDMCQ.("δ18O WRr2")];
WR1_TGA=[TTGA.("δ18O WR")];
WR2_TGA=[TTGA.("δ18O WRr1")];
WR3_TGA=[TTGA.("δ18O WRr2")];

FEQ_WR1_DMCQ=[WR1_DMCQ(:,:)-BDplg_w1]

Answer 1

[Stephen23]

Lets first create some fake data (because you did not upload any sample data):

T = array2table(rand(7,4),'VariableNames',{'X','δ18O WR','δ18O WR1','δ18O WR2'});
T = addvars(T,{'DMCQ';'AAA';'TGA';'DMCQ';'BBB';'TGA';'DMCQ'}, 'Before',1, 'NewVariableNames','GroupID')

T = 7x5 table
    GroupID        X        δ18O WR     δ18O WR1    δ18O WR2
    ________    ________    ________    ________    ________

    {'DMCQ'}     0.06404     0.42084    0.75563     0.28976 
    {'AAA' }     0.21165     0.93118    0.40206     0.29311 
    {'TGA' }      0.9315     0.99812    0.16826     0.59712 
    {'DMCQ'}     0.67944     0.65717    0.55271      0.2492 
    {'BBB' }     0.49654     0.88852    0.31354     0.81825 
    {'TGA' }    0.030262     0.46033    0.90109     0.75549 
    {'DMCQ'}     0.17957    0.050664    0.69379     0.18416 

We will try to merge your separate processing. The most important change is to avoid having lots of separate variables with meta-data in their names. That is not an approach conducive to using loops and generalising your data processing.

While we are here, lets also

• get rid of all of those superfluous square brackets,
• use more efficient e-notation rather than powers of ten.

I did not understand all of your requirements, but hopefully this will get you started.

ID   = ["DMCQ","TGA"];
Beta = [  0.58,  0.1];
C    = [   250,  400];
K = C+273.15;
% Fractionation Equations
tmp = (2.91-0.76.*Beta).*(1e6./K.^2) - 3.41 - 0.41.*Beta; % Fractionation plag-water OT1967
% Subtable:
idx = matches(T.GroupID,ID);
out = T(idx,["GroupID","δ18O WR"]);
for ii = 1:numel(ID)
    ix = matches(out.GroupID,ID(ii));
    wr = out{ix,"δ18O WR"} - tmp(ii);
    out{ix,"Diff"} = wr;
    out{ix,"Beta"} = Beta(ii);
    out{ix,"Temp"} = C(ii);
end
% Show the resulting table:
display(out)

out = 5x5 table
    GroupID     δ18O WR      Diff      Beta    Temp
    ________    ________    _______    ____    ____

    {'DMCQ'}     0.42084    -4.9534    0.58    250 
    {'TGA' }     0.99812    -1.8051     0.1    400 
    {'DMCQ'}     0.65717    -4.7171    0.58    250 
    {'TGA' }     0.46033    -2.3429     0.1    400 
    {'DMCQ'}    0.050664    -5.3236    0.58    250 

[Erin Summerlin-Donofrio]

Yes that would have helped if I had uploaded the data like I intended to! Thank you for answering anyway! I think this will get me where I want to go but I will report back. Thanks again!

[Erin Summerlin-Donofrio]

T=readtable('ESDMatlabAsk021125.xlsx',VariableNamingRule='preserve');


ID   = ["DMCQ","TGA"];  
Beta = [  0.58,  0.1];
C    = [   250,  400];
K = C+273.15;
% Fractionation Equations
BD_plg1 = (2.91-0.76.*Beta(:,1)).*(1e6./K.^2) - 3.41 - 0.41.*Beta(:,1); % Fractionation plag-water OT1967


% Subtable:
idx = matches(T.GroupID,ID);
out = T(idx,["GroupID","δ18O WR","δ18O WRr1","δ18O WRr2"]);
for ii = 1:numel(ID)
    ix = matches(out.GroupID,ID(ii));
    wr1 = out{ix,"δ18O WR"} - BD_plg1(ii);
    out{ix,"δ18O EQ fluid WR"} = wr1;
    wr2 = out{ix,"δ18O WRr1"} - BD_plg1(ii);
    out{ix,"δ18O EQ fluid WR1"} = wr2;
    wr3 = out{ix,"δ18O WRr2"}- BD_plg1(ii);
    out{ix,"δ18O EQ fluid WR2"} = wr3;
    
    %out{ix,"Beta"} = Beta(ii);
    %out{ix,"Temp"} = C(ii);
end
% Show the resulting table:
display(out)

out = 19x7 table
    GroupID     δ18O WR    δ18O WRr1    δ18O WRr2    δ18O EQ fluid WR    δ18O EQ fluid WR1    δ18O EQ fluid WR2
    ________    _______    _________    _________    ________________    _________________    _________________

    {'TGA' }      7.6         6.7          NaN             5.7986             4.8986                  NaN      
    {'DMCQ'}      4.3         NaN          NaN            -1.0742                NaN                  NaN      
    {'DMCQ'}      4.3         NaN          NaN            -1.0742                NaN                  NaN      
    {'DMCQ'}      4.7         NaN          NaN           -0.67422                NaN                  NaN      
    {'DMCQ'}      6.4         NaN          NaN             1.0258                NaN                  NaN      
    {'TGA' }      8.4         NaN          NaN             6.5986                NaN                  NaN      
    {'TGA' }      7.6         6.6            7             5.7986             4.7986               5.1986      
    {'TGA' }      7.6         7.4          NaN             5.7986             5.5986                  NaN      
    {'TGA' }      8.7         NaN          NaN             6.8986                NaN                  NaN      
    {'TGA' }      7.5         7.8          NaN             5.6986             5.9986                  NaN      
    {'DMCQ'}        5         NaN          NaN           -0.37422                NaN                  NaN      
    {'TGA' }      4.7         NaN          NaN             2.8986                NaN                  NaN      
    {'TGA' }      3.4         2.6          3.2             1.5986             0.7986               1.3986      
    {'TGA' }      3.2         NaN          NaN             1.3986                NaN                  NaN      
    {'DMCQ'}      2.4         NaN          NaN            -2.9742                NaN                  NaN      
    {'TGA' }      7.6         NaN          NaN             5.7986                NaN                  NaN      

The modifications I've made are working with two caveats. 1) I would like two columns, one using 250 and one using 400 to be put after each variable in the first indexing line before the for loop, and (2) is there a way to specify which column you'd like the "out" to to before or after? I will be calculating the 6 resulting columns using the BDplg_1 equation, but I will have more equations for the rest of the variables.

δ18O pyx1, δ18O plg1, δ18O pyx2, etc. The 1 or 2 after the same abbreviations will use the same equation.

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

There is an example in the data file I attached.

Thanks so much!

[Stephen23]

(2) https://www.mathworks.com/help/matlab/ref/table.addvars.html

[Erin Summerlin-Donofrio]

%%My edits are @ double comments

% T = array2table(rand(7,4),'VariableNames',{'X','δ18O WR','δ18O WR1','δ18O WR2'});
% T = addvars(T,{'DMCQ';'AAA';'TGA';'DMCQ';'BBB';'TGA';'DMCQ'}, 'Before',1, 'NewVariableNames','GroupID')


%%T=readtable('ESDWichitasData.xlsx',Sheet='Whole Rock & Mineral Separates',VariableNamingRule='preserve');

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

%%You added the variables in line 2 of the code. I have the variables listed
%%in my table (not what I attached but what I'm working with). List is on
%%the line after defining ID. However when I try to add them and run the
%%code, ii stops at 3 no matter how many variables I put into the definition
%%

of ID and I get an error about the number of rows not matching.

ID   = ["DMCQ","TGA"];
%%ID   = ["DMCQ","TGA","GMLC","WGG","RSVG","MQ","DMS","CRG","CS","GMLCV"];

Beta = [  0.58,  0.1];
C    = [   250,  400];
K = C+273.15;
% Fractionation Equations
tmp = (2.91-0.76.*Beta).*(1e6./K.^2) - 3.41 - 0.41.*Beta; % Fractionation plag-water OT1967
% Subtable:
idx = matches(T.GroupID,ID);
out = T(idx,["GroupID","δ18O WR"]);
for ii = 1:numel(ID)
    ix = matches(out.GroupID,ID(ii));
    wr = out{ix,"δ18O WR"} - tmp(ii);
    out{ix,"Diff"} = wr;
    out{ix,"Beta"} = Beta(ii);
    out{ix,"Temp"} = C(ii);
end
% Show the resulting table:
display(out)

[Stephen23]

You will get an error because the code I wrote requires variables ID, Beta, and C to all have the same number of elements. That is why I wrote those three variables together and aligned their content: if you increase the number of elements in ID then you must increase the number of elements in Beta and C correspondingly.

This is because you wrote that "for example for the DMCQ samples, I need to use a Beta of 0.58, and for the TGA samples a Beta of 0.1", so clearly every sample has its own Beta value. I cannot guess the Beta values that you used for GMLC, WGG, RSVG, etc. and nor can MATLAB. You need to define these.

"I have the variables listed in my table (not what I attached but what I'm working with)"

Then use those variables. Of course for each variable you will need to specify Beta and C, but I cannot guess what that relationship is: perhaps it is also defined in the table that you did not attach, or perhaps there is some rule that you have for deriving that relationship. You need to implement that in your code.

[Erin Summerlin-Donofrio]

I used linspace to make the number of elements the same for the three variables and tried choosing a combnation of each three for each calculation. I was hoping there was a more elegant/efficient way and I didn't upoad my full excel file because its large and potentially confusing to what I was asking. But I will upload it now. Here's what I'm trying to develop the correct syntax for now.

Thank you for your continued help.

T=readtable('ESDWichitasData.xlsx',Sheet='Whole Rock & Mineral Separates',VariableNamingRule='preserve');


ID   = ["DMCQ","TGA","GMLC","WGG","RSVG","MQ","DMS","CRG","CS","GMLCV"];


NT = 10;
NB = 10;
Temp = linspace(573.15,573.15,NT); 
Beta = linspace(0.58,0.58,NB);
Beta = permute(Beta,[1 3 2]);


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




%Other fractionation equations I want the option to choose based on what ID
%I pick or what T.Variable I choose 

BDpx_w1=-1.27;%Mathewsetal1983a - Three isotope exchange method. -1.27@600C, -1.08@700C, -0.98@800C

BDpx_w2=3.08.* (10^6./Temp.^2)-5.13.*(10^3./Temp) %Vho et al 2019

BDpx_w2 = 1×10

0.4254 0.4254 0.4254 0.4254 0.4254 0.4254 0.4254 0.4254 0.4254 0.4254

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

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

BDep_w=3.54.* (10^6./Temp.^2)-5.72.*(10^3./Temp) %Vho et al 2019

BDep_w = 1×10

0.7963 0.7963 0.7963 0.7963 0.7963 0.7963 0.7963 0.7963 0.7963 0.7963

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

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

BDq_w1=2.51 .* (10^6./Temp.^2)-1.96 %Clayton et al 1972

BDq_w1 = 1×10

5.6808 5.6808 5.6808 5.6808 5.6808 5.6808 5.6808 5.6808 5.6808 5.6808

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

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

BDbt_w=3.21.* (10^6./Temp.^2)-6.05.*(10^3./Temp) %Vho et al 2019

BDbt_w = 1×10

-0.7840 -0.7840 -0.7840 -0.7840 -0.7840 -0.7840 -0.7840 -0.7840 -0.7840 -0.7840

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

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

BDhbl_w=3.21.* (10^6./Temp.^2)-5.76.*(10^3./Temp) %Vho et al 2019

BDhbl_w = 1×10

-0.2781 -0.2781 -0.2781 -0.2781 -0.2781 -0.2781 -0.2781 -0.2781 -0.2781 -0.2781

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

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

BDch_w=1.56.* (10^6./Temp.^2)-4.70 %Wenner and Taylor 1971

BDch_w = 1×10

0.0488 0.0488 0.0488 0.0488 0.0488 0.0488 0.0488 0.0488 0.0488 0.0488

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

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

% Subtable:
idx = matches(T.GroupID,ID);
out = T(idx,["GroupID","δ18O WR","δ18O WRr1","δ18O WRr2"]);
for ii = 1:numel(ID)
    ix = matches(out.GroupID,ID(ii));
    if T.GroupID == "DMCQ"
        Temp=573.15 
        Beta= 1
        Calculate BDPlg_w1 using T=573.15 and Beta = 1
        
        
        wr=out{ix,"δ18O WR"} - BD_plg1(1,1,1);
        
        
    % wr = out{ix,"δ18O WR"} - BD_plg1(1,1,1);
    % %wr = out{ix,"δ18O WR"} - tmp(ii);
    % 
    % out{ix,"Diff"} = wr;
    % out{ix,"Beta"} = Beta(ii);
    % out{ix,"Temp"} = Temp(ii);
    end
end
% Show the resulting table:
display(out)

out = 74x4 table
    GroupID     δ18O WR    δ18O WRr1    δ18O WRr2
    ________    _______    _________    _________

    {'TGA' }      7.6         6.7          NaN   
    {'DMCQ'}      4.3         NaN          NaN   
    {'DMCQ'}      4.3         NaN          NaN   
    {'DMCQ'}      4.7         NaN          NaN   
    {'DMCQ'}      6.4         NaN          NaN   
    {'TGA' }      8.4         NaN          NaN   
    {'TGA' }      7.6         6.6            7   
    {'TGA' }      7.6         7.4          NaN   
    {'TGA' }      NaN         NaN          NaN   
    {'TGA' }      8.7         NaN          NaN   
    {'TGA' }      7.5         7.8          NaN   
    {'DMCQ'}        5         NaN          NaN   
    {'TGA' }      4.7         NaN          NaN   
    {'TGA' }      3.4         2.6          3.2   
    {'TGA' }      3.2         NaN          NaN   
    {'DMCQ'}      2.4         NaN          NaN   

%Example if T.GroupID = 'DMCQ', then subtract BD_plg1 (using a chosen value of Beta and T) from the values of ["δ18O WR","δ18O
%WRr1","δ18O WRr2"] that correspond with the rows labeled 'DMCQ', and then display the new value after each column
%its been subtracted from

%Display example column names for table out 
%Group ID, d18O WR, d18O WR - BDplg_1 @ 573.15 and Beta =1,d18O WR - BDplg_1 @ 773.15 and Beta =1, d18O WRr1 - BDplg_1 @ 573.15 Beta =1,d18O WRr1 - BDplg_1 @ 773.15 Beta =1, d18O WRr2 - BDplg_1 @ 573.15 Beta =1, d18O WRr2 - BDplg_1 @ 773.15 Beta =1, 

[Stephen23]

Do not write IF T.GroupID == "DMCQ". T.GroupID is a vector with many values, so what do you expect IF to do when you provide it with a logical vector of values e.g. [true;false;false;true;...] ?

You already wrote the IDs in one string vector. Now do exactly the same for the temperature and beta values: do not write them out as lots of individual scalar values in lots of separate IF/ELSE or SWITCH statements (unless you really love writing lots and lots of very verbose code). Use vectors and matrices!

Add a nested loop, iterate over the Beta etc. vectors (of the same lengths!), perform your operations, allocate the data back into the table using ADDVARS.

[Erin Summerlin-Donofrio]

I will keep trying. Thanks.

I guess I was attempting to couple if statments with the indexing by incorrectly assuming the command was similar to general if then statements. Currently having trouble using addvars but I'll figure it out. Sorry for taking up your time.