A      B     C       D
1     foo    12    California
2     foo    22    California
3     bar    8     Rhode Island
4     bar    32    Rhode Island

Required output
 A      B      C    D
1     foo    12    California
2            22    California
Total        34
3     bar    8     Rhode Island
4            32    Rhode Island
Total        40

want to add a total of the numeric columns at the end of each group

CodePudding user response：

Here are two ways to do it.

using groupy apply:

(df.set_index('A')
   .groupby('B', as_index=False, sort=False)
   .apply(lambda d: pd.concat([d, d['C'].agg({'Total': 'sum'}).to_frame()]))
   .droplevel(0).rename_axis('A')  
   .reset_index()
   .fillna('')
)

output:

       A    B   C             D
0      1  foo  12    California
1      2  foo  22    California
2  Total       34              
3      3  bar   8  Rhode Island
4      4  bar  32  Rhode Island
5  Total       40              

using concat and groupby agg:

(pd.concat([df,
            df.groupby('B', as_index=False).agg({'A': lambda x: 'Total', 'C': 'sum'}) 
           ])
   .sort_values(by=['B', 'A'])
   .assign(B=lambda d: d['B'].mask(d['A'].eq('Total')))
   .fillna('')
)

output:

       A    B   C             D
2      3  bar   8  Rhode Island
3      4  bar  32  Rhode Island
0  Total       40              
0      1  foo  12    California
1      2  foo  22    California
1  Total       34