I have a pandas table like below with groupby applied to get Groups 0, 1 and 2 as follows:
| Group 0 | Group 1 | Group 2 | Count |
|---|---|---|---|
| A | X1 | 577.5000 | 6 |
| 894.8700 | 2 | ||
| X2 | 2697.3100 | 48 | |
| 2697.3100 | 1 | ||
| B3 | 2697.3100 | 30 | |
| B | C12 | 34.2700 | 9 |
| 39.2700 | 3 |
I would like to get group wise total in pandas like below:
| Group 0 | Group 1 | Group 2 | Count | Group 1 Total by Count |
|---|---|---|---|---|
| A | X1 | 577.5000 | 6 | 8 |
| 894.8700 | 2 | 8 | ||
| X2 | 2697.3100 | 48 | 49 | |
| 2697.3100 | 1 | 49 | ||
| B3 | 2697.3100 | 30 | 30 | |
| B | C12 | 34.2700 | 9 | 12 |
| 39.2700 | 3 | 12 |
I am able to calculate cumulative sum using df.groupby(level=[0,1]).cumsum() but now sure if there is a way to achieve this.
>Solution :
You can use groupby.transform to transform a sum call over the groups.
df['Group 1 Total by Count'] = df.groupby(['Group 0', 'Group 1'])['Count'].transform('sum')
