New to pandas. I'm trying to a subtotal within 2 group by columns. I have managed to figure out how to sum using 2 group by attributes but within that, I'm also trying to do a subtotal. Please see below for example -
df.groupby(['Fruit','Name'])['Number'].sum()
Output
Fruit Name Number
Apples Bob 16
Mike 9
Steve 10
------
35
------
Grapes Bob 35
Tom 87
Tony 15
------
137
------
Oranges Bob 67
Mike 57
Tom 15
Tony 1
What I'm looking for is to show a subtotal by each fruit within the dataframe. Thank you!
CodePudding user response:
IIUC, you can use df.sum
or df.groupby.sum
with level=0
:
df.sum(level=0) # Will be deprecated
# or
df.groupby(level=0).sum()
Output:
Fruit
Apples 35
Grapes 137
Oranges 140
Name: Number, dtype: int64
CodePudding user response:
You can use a mix of unstack
, assign
, and stack
to do this:
sums = df.groupby(['Fruit', 'Name'])['Number'].sum().unstack().assign(Total=df.groupby('Fruit').sum()).stack()
Output:
>>> sums
Fruit Name
Apples Bob 16.0
Mike 9.0
Steve 10.0
Total 35.0
Grapes Bob 35.0
Tom 87.0
Tony 15.0
Total 137.0
Oranges Bob 67.0
Mike 57.0
Tom 15.0
Tony 1.0
Total 140.0
dtype: float64