Filter | Groupby | Aggregate

user_11622508 0
Viewed 90

I am doing some task in pandas python.

I have a data like this:

col1  |col2  |col3 |col4 |col5 | col6
delhi |assam |"f"  |78.3 |87.1 | B2C
delhi |goa   |"f"  |78.3 |87.1 | B2C
delhi |goa   |"f"  |78.3 |87.1 | B2C
delhi |assam |"f"  |78.3 |87.1 | B2C
up    |assam |"f"  |78.3 |87.1 | B2B
delhi |assam |"f"  |78.3 |87.1 | B2B

Now I want to filter those rows where col6 is B2C. After filtering , I want to groupby col1 and col2 and sum the col4 and col5.

So Output should be like:

col1  |col2  |col3 |col4 |col5 | col6
delhi |assam |"f"  |156.6|174.2| B2C
delhi |goa   |"f"  |156.6|174.2| B2C
up    |assam |"f"  |78.3 |87.1 | B2B
delhi |assam |"f"  |78.3 |87.1 | B2B

The approach I have tried:

df.loc[df['col6'] == 'B2C'].groupby(['col1', 'col2']).agg({'col4':'sum', 'col5':'sum'})

But I don't know how to append this result with original data frame. Also guide me if I can do something better than this.

pandas-groupby pandas aggregate python
1 Answers

IIUC, here's one way:

df = df.groupby(['col1', 'col2', 'col3','col6'], sort=False).sum().reset_index()

NOTE: If you just wanna perform aggregation where value in col6 is eq ('B2C') :

df = pd.concat([df[df.col6.eq('B2C')].groupby(['col1', 'col2', 'col3'],sort=False).sum().reset_index().assign(col6 = 'B2C'), df[df.col6.ne('B2C')]])

OUTPUT:

     col1    col2 col3  col6   col4   col5
0  delhi   assam   f     B2C  156.6  174.2
1  delhi   goa     f     B2C  156.6  174.2
2  up      assam   f     B2B   78.3   87.1
3  delhi   assam   f     B2B   78.3   87.1
user_154380330
Related