How do I subtract values from one table to another based on conditions, without a loop ?

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.

Answer 1

[dpb]

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);

[Mathieu Ratynski]

Thanks for the quick answer !

Unfortunately, I am met with an error when trying out this function :

Error using dsgrpstats (line 328)

The function '@(x)x-mean(x)' returned a result of size [1 1] when evaluated on data for group [-90, -85)_[-180, -160)_[25-Aug-2019, 01-Sep-2019) in dataset variable '5km', expected size [3 1].

Error in grpstats (line 144)

[varargout{1:nargout}] = dsgrpstats(x,group,whichstats,varargin{:});

I am not certain as to why this error is happening.

[dpb]

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]

Here's the first 100,000 rows of the data, so you can manipulate the data more easily.

I am very grateful for your feedback, but I think I haven't been clear on what I wanted.

Your command gives me a smaller, binned, table with a summary of all the possible combinations of bins.

What I would like to have is a new table, similar in size and content to the first one, with each row having its' binned counterpart subtracted.

The result would therefore remain the same size as the initial table (100,000x56).

Let me know if I made my point clear.

Thank you once again for you insights

[Mathieu Ratynski]

Here's the data, following the same instructions as you proposed.

I don't know why the previous file didn't work.

I simply did test_table=SpaceTimeTemp(1:1000,:); and then right clicked to save as mat file.

[dpb]

load test_table
tG=grpstats(test_table,{'LatBin','LonBin','TimeBin'},@(x){x-mean(x)},'DataVars',vars)

tG = 29×54 table
                                                          LatBin        LonBin               TimeBin              GroupCount    Fun1_5km    Fun1_5.5km    Fun1_6km    Fun1_6.5km    Fun1_7km    Fun1_7.5km    Fun1_8km    Fun1_8.5km    Fun1_9km    Fun1_9.5km    Fun1_10km    Fun1_10.5km    Fun1_11km    Fun1_11.5km    Fun1_12km    Fun1_12.5km    Fun1_13km    Fun1_13.5km    Fun1_14km    Fun1_14.5km    Fun1_15km    Fun1_15.5km    Fun1_16km    Fun1_16.5km    Fun1_17km    Fun1_17.5km    Fun1_18km    Fun1_18.5km    Fun1_19km    Fun1_19.5km    Fun1_20km    Fun1_20.5km    Fun1_21km    Fun1_21.5km    Fun1_22km    Fun1_22.5km    Fun1_23km    Fun1_23.5km    Fun1_24km    Fun1_24.5km    Fun1_25km    Fun1_25.5km    Fun1_26km    Fun1_26.5km    Fun1_27km    Fun1_27.5km    Fun1_28km    Fun1_28.5km    Fun1_29km    Fun1_29.5km
                                                        __________    __________    __________________________    __________    ________    __________    ________    __________    ________    __________    ________    __________    ________    __________    _________    ___________    _________    ___________    _________    ___________    _________    ___________    _________    ___________    _________    ___________    _________    ___________    _________    ___________    _________    ___________    _________    ___________    _________    ___________    _________    ___________    _________    ___________    _________    ___________    _________    ___________    _________    ___________    _________    ___________    _________    ___________    _________    ___________    _________    ___________

    [-80, -75)_[-40, -20)_[28-Jul-2019, 04-Aug-2019)    [-80, -75)    [-40, -20)    [28-Jul-2019, 04-Aug-2019)        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]}   
    [-65, -60)_[-80, -60)_[28-Jul-2019, 04-Aug-2019)    [-65, -60)    [-80, -60)    [28-Jul-2019, 04-Aug-2019)        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]}   
    [-50, -45)_[-40, -20)_[28-Jul-2019, 04-Aug-2019)    [-50, -45)    [-40, -20)    [28-Jul-2019, 04-Aug-2019)        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]}   
    [-30, -25)_[-60, -40)_[28-Jul-2019, 04-Aug-2019)    [-30, -25)    [-60, -40)    [28-Jul-2019, 04-Aug-2019)        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]}   
    [-30, -25)_[-20, 0)_[28-Jul-2019, 04-Aug-2019)      [-30, -25)    [-20, 0)      [28-Jul-2019, 04-Aug-2019)        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]}   
    [-25, -20)_[-40, -20)_[28-Jul-2019, 04-Aug-2019)    [-25, -20)    [-40, -20)    [28-Jul-2019, 04-Aug-2019)        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]}   
    [-10, -5)_[-80, -60)_[28-Jul-2019, 04-Aug-2019)     [-10, -5)     [-80, -60)    [28-Jul-2019, 04-Aug-2019)        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]}   
    [-10, -5)_[-40, -20)_[28-Jul-2019, 04-Aug-2019)     [-10, -5)     [-40, -20)    [28-Jul-2019, 04-Aug-2019)        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]}   
    [-5, 0)_[-60, -40)_[28-Jul-2019, 04-Aug-2019)       [-5, 0)       [-60, -40)    [28-Jul-2019, 04-Aug-2019)        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]}   
    [-5, 0)_[120, 140)_[28-Jul-2019, 04-Aug-2019)       [-5, 0)       [120, 140)    [28-Jul-2019, 04-Aug-2019)        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]}   
    [-5, 0)_[140, 160)_[28-Jul-2019, 04-Aug-2019)       [-5, 0)       [140, 160)    [28-Jul-2019, 04-Aug-2019)        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]}   
    [15, 20)_[-60, -40)_[28-Jul-2019, 04-Aug-2019)      [15, 20)      [-60, -40)    [28-Jul-2019, 04-Aug-2019)        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]}   
    [20, 25)_[120, 140)_[28-Jul-2019, 04-Aug-2019)      [20, 25)      [120, 140)    [28-Jul-2019, 04-Aug-2019)        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]}   
    [25, 30)_[-80, -60)_[28-Jul-2019, 04-Aug-2019)      [25, 30)      [-80, -60)    [28-Jul-2019, 04-Aug-2019)        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]}   
    [25, 30)_[-60, -40)_[28-Jul-2019, 04-Aug-2019)      [25, 30)      [-60, -40)    [28-Jul-2019, 04-Aug-2019)        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]}   
    [25, 30)_[140, 160)_[28-Jul-2019, 04-Aug-2019)      [25, 30)      [140, 160)    [28-Jul-2019, 04-Aug-2019)        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]}   

NOTA BENE:

1. 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;
2. 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]

Thank you very much for your answer, that works completely fine.

However, after struggling with the unpacking of this cell table, I came up with an easier solution :

BackgroundTemp=grpstats(SpaceTimeTemp,["LatBin","LonBin","TimeBin"],"mean","DataVars",KmNames);

SpaceTimePert=outerjoin(SpaceTimeTemp,BackgroundTemp,'Keys',{'LatBin' 'LonBin' 'TimeBin'});
SpaceTimePert=[SpaceTimePert(:,1:56),SpaceTimePert(:,60:end)]; % Remove Bin duplicates

SpaceTimePert=array2table(SpaceTimePert{:,4:53}-SpaceTimePert{:,58:end},'VariableNames',KmNames);
SpaceTimePert=[SpaceTimeTemp(:,1:3),SpaceTimePert,SpaceTimeTemp(:,54:56)]; % Add previous columns

Using an outerjoin allows for a simple subtraction between adjacent rows, which resolves the issues of having to go through a unpacking of the data process (which also perfectly works but confused me quite a lot).

Hope this helps whoever might come across this post.

Best Regards