Clash Royale CLAN TAG#URR8PPP

Aggregate data frame rows based on conditions

I have this table

A B C E
1 2 1 3
1 2 4 4
2 7 1 1
3 4 0 2
3 4 8 3


Now, I want to remove duplicates based on column A and B and at the same time sum up column C. For E, it should take the value where C shows the max value. The desirable result table should look like this:

A B C E
1 2 5 4
2 7 1 1
3 4 8 3


I tried this: df.groupby(['A', 'B']).sum()['C'] but my data frame does not change at all as I am thinking that I didn't incorporate the E column part properly...Can somebody advise?

df.groupby(['A', 'B']).sum()['C']

Thanks so much!

1 Answer
1

If the first and second rows are duplicates, we can group by them.

In [20]: df
Out[20]:
A B C E
0 1 1 5 4
1 1 1 1 1
2 3 3 8 3

In [21]: df.groupby(['A', 'B'])['C'].sum()
Out[21]:
A B
1 1 6
3 3 8
Name: C, dtype: int64


I tried this: df.groupby(['A', 'B']).sum()['C'] but my data frame does not change at all

yes, it's because pandas didn't overwrite initial DataFrame

DataFrame

In [22]: df
Out[22]:
A B C E
0 1 1 5 4
1 1 1 1 1
2 3 3 8 3


You have to overwrite it explicitly.

In [23]: df = df.groupby(['A', 'B'])['C'].sum()

In [24]: df
Out[24]:
A B
1 1 6
3 3 8
Name: C, dtype: int64


By clicking "Post Your Answer", you acknowledge that you have read our updated terms of service, privacy policy and cookie policy, and that your continued use of the website is subject to these policies.