Pandas: concat a sum row on groupby dataframe-CodePudding

I have a dataframe of the following layout:

                   Quantity
category  species   
mammal    cat      1.75
mammal    human    0.25
fish      cod      1.5

Where category and species being indexes of this dataframe. I want to get the sum for each category, and concat it into the dataframe.

As a result, I would like to get this:

                   Quantity
category  species   
mammal    cat      1.75
mammal    human    0.25
mammal    total    2
fish      cod      1.5
fish      total    1.5

I'm quite new to pandas, and I've tried playing around sum, concat, and apply without much success. Some help would be very helpful.

Thanks !

CodePudding user response：

Use:

In [1472]: x = df.groupby(level=0)['Quantity'].sum().to_frame().reset_index()
In [1474]: x['species'] = 'total'

In [1478]: res = pd.concat([df.reset_index(), x])

In [1479]: res
Out[1479]: 
  category species  Quantity
0   mammal     cat      1.75
1   mammal   human      0.25
2     fish     cod      1.50
0     fish   total      1.50
1   mammal   total      2.00

CodePudding user response：

I was able to get it using this

df2 = df1.groupby('category')['Quantity'].sum().reset_index()
df_con = pd.concat([df1, df2]).fillna('Total').sort_values('category', ascending = False)
df_con.set_index(['category', 'species'])