Pandas: Group by two columns to get sum of another column

Question

I look most of the previously asked questions but was not able to find answer for my question:

I have following dataframe

           id   year month score num_attempts
0      483625  2010    01   50      1
1      967799  2009    03   50      1
2      213473  2005    09  100      1
3      498110  2010    12   60      1
5      187243  2010    01  100      1
6      508311  2005    10   15      1
7      486688  2005    10   50      1
8      212550  2005    10  500      1
10     136701  2005    09   25      1
11     471651  2010    01   50      1

I want to get the following dataframe

year month sum_score sum_num_attempts
2009    03   50           1
2005    09  125           2
2010    12   60           1
2010    01  200           2
2005    10  565           3

Here is what I tried:

sum_df = df.groupby(by=['year','month'])['score'].sum()

But this doesn't look efficient and correct. If I have more than one column need to be aggregate this seems like a very expensive call. for example if I have another column num_attempts and just want to sum by year month as score.

Can you elaborate why you think it's an expansive call, and what you would do with `num_attempts` if you want to keep it in the group operation? — Zeugma, Nov 11 '16 at 17:27
@MaxU not sure if i understand the use of `as_index=False, what does that do? — add-semi-colons, Nov 11 '16 at 17:31
sorry for my first comment - i've misunderstood you. What is wrong with: `df.groupby(['year','month'])[['score','num_attempts']].sum()` or `df.groupby(['year','month']).agg({'score':'sum', 'num_attempts':'max'})`? — MaxU - stand with Ukraine, Nov 11 '16 at 17:37
@MaxU so will this create new columns in the resulting data frame if i want in a different column to reflect the results can I do that. Also my data frame is really large. is this the best way to carry out this task. — add-semi-colons, Nov 11 '16 at 17:40
@Boud I have close to 9million records in this data frame and seems like its taking forever to get the results and I still haven't seen the final frame. — add-semi-colons, Nov 11 '16 at 17:51

score 24 · Accepted Answer · answered Nov 11 '16 at 18:28

24

This should be an efficient way:

sum_df = df.groupby(['year','month']).agg({'score': 'sum', 'num_attempts': 'sum'})

answered Nov 11 '16 at 18:28

Dennis Golomazov

16,269
5
73
81

Does the `score` column has to be explicitly integer? – add-semi-colons Nov 12 '16 at 16:21
@Null-Hypothesis I don't think so. If it doesn't work, will you elaborate? – Dennis Golomazov Nov 16 '16 at 04:00
1

The resulted sum_df only have the summed columns. – Xue Nov 17 '20 at 13:20
2

if anyone also wants to rename the column that gets created, so that the new(score) column doesn't take the same name as input column. append `.rename(columns={'score': 'score_sum_by_year_month'})` at the end. – Naveen Reddy Marthala Sep 28 '21 at 09:34

score 0 · Answer 2 · answered Mar 19 '23 at 02:14

An alternative way is to call groupby.sum on multiple columns and add prefix afterwards. This produces the desired column labels in the OP.

sum_df = df.groupby(['year', 'month'])[['score', 'num_attempts']].sum().add_prefix('sum_').reset_index()

or use named aggregation from the beginning

sum_df = df.groupby(['year', 'month'], as_index=False).agg(sum_score=('score', 'sum'), sum_num_attempts=('num_attempts', 'sum'))

Both produce the following frame:

Pandas: Group by two columns to get sum of another column

2 Answers2

Linked