So I have a dataframe, df1, that looks like the following:

 A B C
1 foo 12 California
2 foo 22 California
3 bar 8 Rhode Island
4 bar 32 Rhode Island
5 baz 15 Ohio
6 baz 26 Ohio

I want to group by column A and then sum column B while keeping the value in column C. Something like this:

 A B C
1 foo 34 California
2 bar 40 Rhode Island
3 baz 41 Ohio

The issue is, when I say

df.groupby('A').sum()

column C gets removed, returning

 B
A
bar 40
baz 41
foo 34

How can I get around this and keep column C when I group and sum?

2

3 Answers

The only way to do this would be to include C in your groupby (the groupby function can accept a list).

Give this a try:

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

One other thing to note, if you need to work with df after the aggregation you can also use the as_index=False option to return a dataframe object. This one gave me problems when I was first working with Pandas. Example:

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

3

If you don't care what's in your column C and just want the nth value, you could just do this:

df.groupby('A').agg({'B' : 'sum', 'C' : lambda x: x.iloc[n]})

2

Another option is to use groupby.agg and use the first method on column "C".

out = df.groupby('A', as_index=False, sort=False).agg({'B':'sum', 'C':'first'})

Output:

 A B C
0 foo 34 California
1 bar 40 Rhode Island
2 baz 41 Ohio

Your Answer

Sign up or log in

Sign up using Google Sign up using Facebook Sign up using Email and Password Submit

Post as a guest

Post Your Answer Discard

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy