Pandas, getting mean and sum with groupby

Question

I have a data frame, df, which looks like this:

index  New  Old  Map      Limit  count
1       93   35   54       > 18      1
2      163   93  116       > 18      1
3      134   78   96       > 18      1
4      117   81   93       > 18      1
5      194  108  136       > 18      1
6      125   57   79      <= 18      1
7       66   39   48       > 18      1
8      120   83   95       > 18      1
9      150   98  115       > 18      1
10     149   99  115       > 18      1
11     148   85  106       > 18      1
12      92   55   67      <= 18      1
13      64   24   37       > 18      1
14      84   53   63       > 18      1
15      99   70   79       > 18      1

I need to produce a data frame that looks like this:

Limit        <=18             >18
         total  mean     total  mean
New       xx1    yy1      aa1    bb1
Old       xx2    yy2      aa2    bb2
MAP       xx3    yy3      aa3    bb3

I tried this without success:

df.groupby('Limit')['New', 'Old', 'MAP'].[sum(), mean()].T without success.

How can I achieve this in pandas?

Possible duplicate of How to use groupby to apply multiple functions to multiple columns in Pandas — IanS
– IanS, Commented May 20, 2016 at 8:46
As explained in the documentation: df.groupby('Limit')['New', 'Old', 'MAP'].agg(['sum', 'mean']). — IanS
– IanS, Commented May 20, 2016 at 8:47

Community · Accepted Answer · 2017-05-23 12:08:01Z

1

You can use groupby with agg, then transpose by T and unstack:

print (df[['New', 'Old', 'Map', 'Limit']].groupby('Limit').agg([sum, 'mean']).T.unstack())

Limit  <= 18           > 18            
         sum   mean     sum        mean
New    217.0  108.5  1581.0  121.615385
Old    112.0   56.0   946.0   72.769231
Map    146.0   73.0  1153.0   88.692308

I edit by comment, it looks nicer:

print (df.groupby('Limit')['New', 'Old', 'Map', 'Limit'].agg([sum, 'mean']).T.unstack())

And if need total columns:

print (df.groupby('Limit')['New', 'Old', 'Map', 'Limit']
         .agg({'total':sum, 'mean': 'mean'})
         .T
         .unstack(0))

Limit  <= 18           > 18            
       total   mean   total        mean
New    217.0  108.5  1581.0  121.615385
Old    112.0   56.0   946.0   72.769231
Map    146.0   73.0  1153.0   88.692308

edited May 23, 2017 at 12:08

CommunityBot

11 silver badge

answered May 20, 2016 at 8:33

jezrael

868k103 gold badges1.4k silver badges1.3k bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Pandas, getting mean and sum with groupby

1 Answer 1

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related