How do I pull out from a table, the total mean of one item based on a particular condition (for several subjects), and then compute the difference in mean values?

I have a large table of data (example below).

For each "Subject", I need to determine 1) the total mean value of all "Item As" that are 'High' in "Condition Z", and 2) the total mean value of all "Item As" that are 'Low' in "Condition Z". For example, Subject 1 the answers would be: 25 (i.e. The mean for Item A for 'High' "Condition Z" items); and 50 (i.e. The mean for Item A for 'Low' "Condition Z" items)

I then need to generate a table that, for each "Subject", lists the difference in these two values as follows: ("total mean value of all "Item As" that are 'High' in "Condition Z"") - ("the total mean value of all "Item As" that are 'Low' in "Condition Z"). Thank you very much!

Subject	Item A	     Condition Z	    Restudied	      Class
1       30              High	            Y	            A
1       20              High                N               B
1       45              Low                 N               A
1       55              Low                 Y               B
2       17              High                Y               A   
2       45              Low	                N	            B
2       13              High                Y               A  
3       56              High                Y	            A
3       12              Low	                N	            B
3       34              Low	                N	            B
3       15              High	            N	            A

Answer 1

[Dave B]

You can do the first part with groupsummary

Subject = [1 1 1 1 2 2 2 3 3 3 3]';
ItemA=[30 20 45 55 17 45 13 56 12 34 15]';
ConditionZ=["High" "High" "Low" "Low" "High" "Low" "High" "High" "Low" "Low" "High"]';

t=table(Subject,ItemA,ConditionZ)

t = 11×3 table
    Subject    ItemA    ConditionZ
    _______    _____    __________

       1        30        "High"  
       1        20        "High"  
       1        45        "Low"   
       1        55        "Low"   
       2        17        "High"  
       2        45        "Low"   
       2        13        "High"  
       3        56        "High"  
       3        12        "Low"   
       3        34        "Low"   
       3        15        "High"  

tsummary=groupsummary(t,["Subject" "ConditionZ"],"mean","ItemA")

tsummary = 6×4 table
    Subject    ConditionZ    GroupCount    mean_ItemA
    _______    __________    __________    __________

       1         "High"          2              25   
       1         "Low"           2              50   
       2         "High"          2              15   
       2         "Low"           1              45   
       3         "High"          2            35.5   
       3         "Low"           2              23   

You might be tempted to do the second part by just subtracting tsummary.mean_ItemA(tsummary.ConditionZ=="High") - tsummary.mean_ItemA(tsummary.ConditionZ=="low")...but i think a nicer solution is to make two separate tables and join them which will make sure that all of the indices line up.

thigh = tsummary(tsummary.ConditionZ=="High",:)

thigh = 3×4 table
    Subject    ConditionZ    GroupCount    mean_ItemA
    _______    __________    __________    __________

       1         "High"          2              25   
       2         "High"          2              15   
       3         "High"          2            35.5   

tlow = tsummary(tsummary.ConditionZ=="Low",:)

tlow = 3×4 table
    Subject    ConditionZ    GroupCount    mean_ItemA
    _______    __________    __________    __________

       1         "Low"           2             50    
       2         "Low"           1             45    
       3         "Low"           2             23    

tdiff=join(tlow,thigh,'Keys','Subject')

tdiff = 3×7 table
    Subject    ConditionZ_tlow    GroupCount_tlow    mean_ItemA_tlow    ConditionZ_thigh    GroupCount_thigh    mean_ItemA_thigh
    _______    _______________    _______________    _______________    ________________    ________________    ________________

       1            "Low"                2                 50                "High"                2                    25      
       2            "Low"                1                 45                "High"                2                    15      
       3            "Low"                2                 23                "High"                2                  35.5      

tdiff.difference = tdiff.mean_ItemA_thigh - tdiff.mean_ItemA_tlow

tdiff = 3×8 table
    Subject    ConditionZ_tlow    GroupCount_tlow    mean_ItemA_tlow    ConditionZ_thigh    GroupCount_thigh    mean_ItemA_thigh    difference
    _______    _______________    _______________    _______________    ________________    ________________    ________________    __________

       1            "Low"                2                 50                "High"                2                    25              -25   
       2            "Low"                1                 45                "High"                2                    15              -30   
       3            "Low"                2                 23                "High"                2                  35.5             12.5   

tdiff=tdiff(:,[1 end]) % just for display, no reason to eliminate the intermediate columns

tdiff = 3×2 table
    Subject    difference
    _______    __________

       1           -25   
       2           -30   
       3          12.5   

[Image Analyst]

Dave, of the similar functions grpstats(), groupsummary(), and splitapply() which would you use when? It seems they all can get the stats of groups.

[Dave B]

@Image Analyst - that's a great question, there's a lot of overlapping domain here! (and your list this didn't include rowfun with GroupingVariables as used by @dpb in the other answer to this question, or accumarray...which is one of the more difficult ones to use IMO).

When I started writing it out below, I landed on groupsummary is my go-to, but I use splitapply when I have multiple argument functions or functions where I don't care about the output argument (like plot). I don't use or recommend grpstats often because it lives in a toolbox, I only know of a few ways that grpstats and groupsummary are different.

Disclaimer - it's far from my area of expertise (as I'm in graphics). I learned about splitapply / groupsummary / grouptransform shortly after I started at mathworks and was blown away, I had always wanted these things in my previous life when I did research (but never really used tables). I've often fantasized about an analogous set of functions for graphics where grouping and summarizing is such a big part of the workflow, but we have so many other projects we need to work on.

groupsummary is my go-to, especially with tables.

• I like that it takes a table as the first argument. Doing groupsummary(t,'<tab> brings me joy (side note, starting in 2021b you should be able to do scatter(t,'<tab too!)
• I really like the all option. If you're doing some data exploration sometimes it's nice to just grab a bunch of stats and worry later about whether you want a boxchart or an errorbar.
• The binning options are probably my favorite feature. I like them with datetime data:

t=readtable('BicycleCounts.csv');
t=t(~isnat(t.Timestamp),:);
a=groupsummary(t, 'Timestamp', 'month', 'mean', 'Westbound')

a = 14×3 table
    month_Timestamp    GroupCount    mean_Westbound
    _______________    __________    ______________

       Jun-2015           168             34.44    
       Jul-2015           744            35.282    
       Aug-2015           744            33.712    
       Sep-2015           719            36.912    
       Oct-2015           744            31.065    
       Nov-2015           721            24.179    
       Dec-2015           744            15.222    
       Jan-2016           744            11.453    
       Feb-2016           696            12.397    
       Mar-2016           744            16.761    
       Apr-2016           720            19.294    
       May-2016           744            23.624    
       Jun-2016           720            27.635    
       Jul-2016           433            24.591    

% although I often wish that the output was a datetime and have to do
% something like datetime(string(a.month_Timestamp)) for my plot...
bar(datetime(string(a.month_Timestamp)), a.mean_Westbound)

groupsummary probably has more than one limitation, but one that often gets me is that it only works with one-arg functions. splitapply let's you work with multiple arguments:

load patients
splitapply(@(x,y)mean(x./y), Systolic, Diastolic, findgroups(Smoker))

ans = 2×1

1.5093 1.4432

% or even:
[grps,smkr,gndr]=findgroups(Smoker,Gender);
rat=splitapply(@(x,y)mean(x./y),Systolic,Diastolic,grps)

rat = 4×1

1.5159 1.4991 1.4496 1.4392

table(smkr,gndr,rat)

ans = 4×3 table
    smkr        gndr        rat  
    _____    __________    ______

    false    {'Female'}    1.5159
    false    {'Male'  }    1.4991
    true     {'Female'}    1.4496
    true     {'Male'  }    1.4392

groupsummary is also (I think) always about an output, but splitapply doesn't need one. That makes for some neat graphics one liners:

figure
splitapply(@(x)histogram(nexttile,x), t.Total, findgroups(t.Timestamp.Month))

A cool trick which is useful from time to time, is to use either of these functions to turn data into a cell array by group:

groupsummary(t,'Timestamp','month',@(x){x})

ans = 14×6 table
    month_Timestamp    GroupCount      fun1_Day        fun1_Total      fun1_Westbound    fun1_Eastbound
    _______________    __________    ____________    ______________    ______________    ______________

       Jun-2015           168        {168×1 cell}    {168×1 double}    {168×1 double}    {168×1 double}
       Jul-2015           744        {744×1 cell}    {744×1 double}    {744×1 double}    {744×1 double}
       Aug-2015           744        {744×1 cell}    {744×1 double}    {744×1 double}    {744×1 double}
       Sep-2015           719        {719×1 cell}    {719×1 double}    {719×1 double}    {719×1 double}
       Oct-2015           744        {744×1 cell}    {744×1 double}    {744×1 double}    {744×1 double}
       Nov-2015           721        {721×1 cell}    {721×1 double}    {721×1 double}    {721×1 double}
       Dec-2015           744        {744×1 cell}    {744×1 double}    {744×1 double}    {744×1 double}
       Jan-2016           744        {744×1 cell}    {744×1 double}    {744×1 double}    {744×1 double}
       Feb-2016           696        {696×1 cell}    {696×1 double}    {696×1 double}    {696×1 double}
       Mar-2016           744        {744×1 cell}    {744×1 double}    {744×1 double}    {744×1 double}
       Apr-2016           720        {720×1 cell}    {720×1 double}    {720×1 double}    {720×1 double}
       May-2016           744        {744×1 cell}    {744×1 double}    {744×1 double}    {744×1 double}
       Jun-2016           720        {720×1 cell}    {720×1 double}    {720×1 double}    {720×1 double}
       Jul-2016           433        {433×1 cell}    {433×1 double}    {433×1 double}    {433×1 double}

grpstats doesn't normally cross my radar because it lives in the Statistics and Machine Learning Toolbox. Of course that doesn't mean it's bad, I just try to be careful to remember that not everyone has it (although I suspect it's one of the more popular toolboxes and most people do have it). It has some built-in stats that groupsummary doesn't, and let's you set a confidence interval for some. I actually often want 'sem' which I see now is built in to grpstats, so maybe I'll start using it more!

[dpb]

Yeah, this (relatively) recent penchant to introduce so many (almost) overlapping functionalities in common toolboxes or even in base product is maddening in causing so much overhead and bloat in the dictionary space; much of which then ends up being deprecated but never going away. The early incarnation of the table as the Statistics TB dataset and its implementation of a different categorical class is a prime example.

While such were indeed needed, it would have been far better for the TB and the base product to have been integrated together even if it meant delaying some TB functionality for a while.

The seeming lack of an overall comprehensive design and direction is becoming a real issue in my mind; the disparate user interfaces and inconsistencies continue to multiply.

$0.02, imo, ymmv, etc., etc., ...

[Monique Chatterton]

This worked beautifully! Thank you so much for this answer and the additional information and explanations!

Answer 2

[dpb]

tMEANS=rowfun(@mean,tData,'GroupingVariables'{'ConditionZ'},'InputVariables','ItemA','OutputVariableNames','GroupedMeans');

See the doc for all the skinny on grouping variables and rowfun and friends...you might also find groupsummary of interest.