I have this data set:
+===+=======+======+=======+=======+
| | Group | Cost | Name1 | Name2 |
+===+=======+======+=======+=======+
| 0 | G1 | 1574 | N1A | N2A |
+---+-------+------+-------+-------+
| 1 | G2 | 1322 | N1B | N2B |
+---+-------+------+-------+-------+
| 2 | G3 | 1188 | N1C | N2C |
+---+-------+------+-------+-------+
| 3 | G3 | 942 | N1D | N2D |
+---+-------+------+-------+-------+
| 4 | G4 | 838 | N1E | N2E |
+---+-------+------+-------+-------+
| 5 | G5 | 5 | N1F | N2F |
+---+-------+------+-------+-------+
| 6 | G5 | 4 | N1F | N2G |
+---+-------+------+-------+-------+
| 7 | G5 | 3 | N1G | N2H |
+---+-------+------+-------+-------+
Now i want to group by "Group" and add a grouped column with the sum of column "Cost" for each group. Dont know how to explain, so here is the expected result:
+===+=======+======+======+=======+=======+
| | Group | Sum | Cost | Name1 | Name2 |
+===+=======+======+======+=======+=======+
| 0 | G1 | 1574 | 1574 | N1A | N2A |
+---+-------+------+------+-------+-------+
| 1 | G2 | 1322 | 1322 | N1B | N2B |
+---+-------+------+------+-------+-------+
| 2 | G3 | 2130 | 1188 | N1C | N2C |
| | | +------+-------+-------+
| | | | 942 | N1D | N2D |
+---+-------+------+------+-------+-------+
| 3 | G4 | 838 | 838 | N1E | N2E |
+---+-------+------+------+-------+-------+
| 4 | G5 | 12 | 5 | N1F | N2F |
| | | +------+-------+-------+
| | | | 4 | N1F | N2G |
| | | +------+-------+-------+
| | | | 3 | N1G | N2H |
+---+-------+------+------+-------+-------+
How can i achieve this with pandas? Is that even possible? Sorry i am new to this stuff
Use GroupBy.transform
with sum
and then for display your way create MultiIndex
by DataFrame.set_index
, but 'missing'
values in MulitIndex
are only not displaing:
df['Sum'] = df.groupby('Group')['Cost'].transform('sum')
df = df.set_index(['Group','Sum','Cost'])
Or:
df1 = (df.assign(Sum = df.groupby('Group')['Cost'].transform('sum'))
.set_index(['Group','Sum','Cost']))
Collected from the Internet
Please contact [email protected] to delete if infringement.
Comments