With 2 group by columns, how can I do a subtotal by each of the group by columns?-CodePudding

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