How do I aggregate multiple columns with one function in pandas when using groupby?

Question

I have a data frame with a "group" variable, a "count" variable, and a "total" variable. For each group I want to sum the count column and divide that by the sum of the total column. How do I do this, ideally in one line of code?

Here is an example to work with:

test_dc = {1:{'group':'A','cnt':3,'total':5},
           2:{'group':'B','cnt':1,'total':8},
           3:{'group':'A','cnt':2,'total':4},
           4:{'group':'B','cnt':6,'total':13}
          }
test_df = pd.DataFrame.from_dict(test_dc, orient='index')

Expected output (roughly):

group | average
    A | 0.55555
    B | 0.33333

Edit: changed column name from "count" to "cnt" because there seems to be an existing count() method on groupby objects.

Aleksey Bilogur · Accepted Answer · 2017-03-20 03:11:48Z

4

You can use DataFrame.groupby to group by a column, and then call sum on that to get the sums.

>>> df = test_df
    .groupby('group')
    .sum()
>>> df      
       count  total
group              
A          5      9
B          7     21

Then you can grab the column and divide them through to get your answer.

>>> df['count'] / df['total']
    group
A    0.555556
B    0.333333
dtype: float64

You can do this in one line by taking advantage of the DataFrame.pipe operator:

test_df
    .groupby('group')
    .sum()
    .pipe(lambda df: df['count'] / df['total'])

answered Mar 20, 2017 at 3:11

Aleksey Bilogur

3,9064 gold badges36 silver badges61 bronze badges

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

Comments

piRSquared · Accepted Answer · 2017-03-20 06:49:38Z

2

I'd use a combination of agg and eval

test_df.groupby('group').agg('sum').eval('cnt / total')

group
A    0.555556
B    0.333333
dtype: float64

answered Mar 20, 2017 at 6:49

piRSquared

296k68 gold badges509 silver badges654 bronze badges

Collectives™ on Stack Overflow

How do I aggregate multiple columns with one function in pandas when using groupby?

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related