How do I subtract values from one table to another based on conditions, without a loop ?
Show older comments
Hello Everyone,
I have recently learned about tables and I'm struggling to understand some intricacies about table manipulation.
I have a first table array, that containts several columns, from 5km to 29.km and three columns dedicated to their lattitude, longitude and time bin : 
This table is extremely long and I have used the bins in order to create a second table, representing the "binned means" , using :
BackgroundTemp=grpstats(SpaceTimeTemp,["LatBin","LonBin","TimeBin"],"mean","DataVars",KmNames);
Right now, I'd like to have for each row of my first table, the corresponding row of the second table subtracted, column by column.
The most straightforward way I found of doing it is through a for loop :
for i=1:height(SpaceTimeTemp)
Mean_Temp=BackgroundTemp{string(SpaceTimeTemp(i,:).('LatBin'))+"_"+string(SpaceTimeTemp(i,:).('LonBin'))+"_"+string(SpaceTimeTemp(i,:).('TimeBin')),5:end};
SpaceTimeTemp(i,4:53)=array2table(SpaceTimeTemp{i,4:53}-Mean_Temp,'VariableNames',KmNames);
end
( SpaceTimeTemp is my first Table and BackgroundTemp is the second, binned, one )
I haven't found any specific mention to this type of operation in the table documentation, but I'm guessing that it could work with a join ?
Thank you very much in advance for your help.
Accepted Answer
dpb
on 19 Sep 2022
Just get creative -- the function doesn't have to be one of the named ones...
tLessBgkd=grpstats(SpaceTimeTemp,["LatBin","LonBin","TimeBin"], ...
@(x)x-mean(x),"DataVars",KmNames);
6 Comments
Mathieu Ratynski
on 19 Sep 2022
Edited: Mathieu Ratynski
on 19 Sep 2022
dpb
on 19 Sep 2022
I was thinking grpstats was more forgivng than that; only had to have the same number of columns every return, not the same number or rows as well in every group...and, I see that rowfun does the same unless use 'OutputFormat','cell'
So, either
tLessBgkd=grpstats(SpaceTimeTemp,["LatBin","LonBin","TimeBin"], ...
@(x){x-mean(x)},"DataVars",KmNames);
or
tLessBgkd=rowfun(@(x)x-mean(x),SpaceTimeTemp,'GroupingVariables',["LatBin","LonBin","TimeBin"], ...
,'InputVariables',KmNames,'OutputFormat','cell');
The first will return a table with each set of data in a cell; those can then be turned back into a table by applying cell2mat on each.
The second wiill return the whole thing as a cell array which you can operate and also turn back into a table.
It would be a lot simpler to provide test code if had a short sample of the data file to work with...
Mathieu Ratynski
on 19 Sep 2022
Mathieu Ratynski
on 20 Sep 2022
load test_table
tG=grpstats(test_table,{'LatBin','LonBin','TimeBin'},@(x){x-mean(x)},'DataVars',vars)
NOTA BENE:
- the section of the overall table only has more than a single observation for one grouping which makes the results look funny since the mean of a single observation is the observation -- hence the answer is zero....still the correct answer for the data in the example;
- You did the grouping by Lat,Lon,Time in that order; you'll need to sort the initial table in that order for the result to be in the same order in the grouped table -- or change the grouping variables order to be in the order of the ordering in the original table for the straight expansion to work correctly.
Failing sorting, one can still iterate over the groups and expand the other variables to match using findgroups, but it's not as convenient and doesn't meet the input request of no explicit looping constructs (of course, there's still looping inside the grpstats call, but it's hidden away from top-level user code.
Mathieu Ratynski
on 20 Sep 2022
More Answers (0)
Categories
Find more on Logical in Help Center and File Exchange
Community Treasure Hunt
Find the treasures in MATLAB Central and discover how the community can help you!
Start Hunting!
