How to find the total value by category in a table

Answer 1

[Cris LaPierre]

Use findgroups and splitapply.

G = findgroups(Table.Clase);
Result = splitapply(@sum,Table.Monto,G);
Result = table(categories(Table.Clase),Result,'VariableNames',{'Clase','Monto'})

[julios]

Thank you very much, this is very useful for me. I understand Matlab reorder by alphabetical the "Result"; it is possible maintain the original order?. This because I have a 1 million of observations, and it's very useful maintain the original order. Thank in advance.

[Cris LaPierre]

Sure. There is a reordercats function in MATLAB for this. However, with millions of observations, I'm guessing defining the desired order by hand would be tedious. What about something like this?

[cats,ia,~] = unique(string(Table.Clase));
[~,idx] = sort(ia);
Table.Clase = reordercats(Table.Clase,cats(idx))

G = findgroups(Table.Clase);
Result = splitapply(@sum,Table.Monto,G);
Result = table(Table.Clase(ia(idx)),Result,'VariableNames',{'Clase','Monto'})

I may just be being lazy, but the "Table.Clase(ia(idx))" part is just a simple way to keep the names categorical in Result (don't show with single or double quotes).

[julios]

sorry but, I think there is a mistake; here the message of matlab (the name of my table is "borra") ;

can you help me?, (the first code work well) thanks in advance !!

[Cris LaPierre]

In your command window, type

summary(borra)

Paste the result here. I'm most interested in seeing the data type of Clase. For what I implemented, it is a categorical

>> summary(Table)

Variables:

    Clase: 10×1 categorical

        Values:

            White        4   
            Black        3   
            Yellow       3   

    Monto: 10×1 double

        Values:

            Min          1   
            Median       1   
            Max          3   

[julios]

sorry for delay, here the result of summary (and Yes, variable "Clase" is categorical),

wokspace data,

[Cris LaPierre]

Ah, you are in 2017a. It looks like reordercats did not yet support the string data type.

Update the line that uses string to use cellstr instead

[cats,ia,~] = unique(cellstr(Table.Clase));

[julios]

That's right !!!, the final result has a initial order. Thank you very much !!

Answer 2

[Peter Perkins]

The problem with Chris' solution is that by going through findgroups in that way, you lose all the categoricalness of your grouping variable. You can work around that in splitapply in a few ways, one of which Chris shows, but here's something more direct:

>> Clase = categorical({'w'; 'w'; 'w'; 'b'; 'b'; 'y'},{'w' 'b' 'y'});
>> Monto = [1;2;3;4;5;6];
>> t = table(Clase,Monto)
t =
  6×2 table
    Clase    Monto
    _____    _____
      w        1  
      w        2  
      w        3  
      b        4  
      b        5  
      y        6  
>> tsum = varfun(@mean,t,'GroupingVariable','Clase')
tsum =
  3×3 table
    Clase    GroupCount    mean_Monto
    _____    __________    __________
      w          3              2    
      b          2            4.5    
      y          1              6      
>> categories(tsum.Clase)
ans =
  3×1 cell array
    {'w'}
    {'b'}
    {'y'}

In (very) recent versions of MATLAB, there's also groupsummary:

>> groupsummary(t,"Clase","mean")
ans =
  3×3 table
    Clase    GroupCount    mean_Monto
    _____    __________    __________
      w          3              2    
      b          2            4.5    
      y          1              6    

varfun only works for tables, whereas groupsummary is more widel applicable.

[Cris LaPierre]

Not sure what you mean by losing its categoricalness.

>> summary(Result)

Variables:
    Clase: 3×1 categorical
        Values:
            White     1      
            Black     1      
            Yellow    1      

    Monto: 3×1 double
        Values:
            Min       4      
            Median    5      
            Max       6      

Answer 3

[Image Analyst]

You can use grpstats() to get the stats by group, if you have the Statistics and Machine Learning Toolbox. It doesn't have sum but it has count and mean so you can multiply those to get the sum. Attach your table in a .mat file, and your expected results if you need more guidance.

[julios]

Tank you for your time, but I try to use the answer of Cris.

[Image Analyst]

OK, no problem. You accepted it so it looks like it's solved your problem. Or maybe not?

[julios]

Tank you for your interest, and Yes the principal problem has been solved, we only tuning a response.