Adding a row with subtotal by category to my dataframe

Question

I have created a new aggregated dataframe by using groupby and I am having problems with adding a subtotal row under each category.

I have tried using pd.groupby and pivottable and changing the index but I don't manage to represent the data as I want it.

Creates a subtotal of "USD_Balance" for each "Client", but add it as a column:

df_balance['Subtotal'] = df_balance.groupby('Client')['USD_Balance'].transform('sum')

Creating a groupby and merging with my raw table gives me the same result.

+----------+-------------+------------+
|CLient ID | USD_Balance | Subtotal   |
+----------+---------+------------+----
|       1  |     2       |     6      |      
|       1  |     2       |     6      |     
|       1  |     2       |     6      |    
+----------+-------------+------------+

How I would like to display my data:

|---------------------|------------------|
|      Client ID      |    USD_Balance   |
|---------------------|------------------|
|          1          |         2        |
|---------------------|------------------|
|          1          |         2        |
|---------------------|------------------|
|          1          |         2        |
|---------------------|------------------|
|        SubTotal     |         6        |
|---------------------|------------------|

I would like to add a Subtotal row, with the corresponding agg per Client ID group.

Thanks in advance for any pointers on how to present my data like this!

This is with `groupby` I think, so not exactly the same as dup answer. OP just didn't provide more `client ids`. @cs95 — Erfan, Jun 18 '19 at 18:10
@Erfan Ah that's right. I am sure it is a duplicate of something. Let me know if you find anything. Will reopen for now. — cs95, Jun 18 '19 at 18:13

Erfan · Answer 1 · 2019-06-18T18:41:26.243

5

You can use groupby and access each group and add an Subtotal row:

dfs = []

for _, d in df.groupby('CLient ID', as_index=False):
    d.loc['Total', 'USD_Balance'] = df['USD_Balance'].sum()
    dfs.append(d)

df_final = pd.concat(dfs, ignore_index=True)

   CLient ID  USD_Balance
0        1.0          2.0
1        1.0          2.0
2        1.0          2.0
3        NaN          6.0

edited Jun 18 '19 at 18:41

answered Jun 18 '19 at 18:34

Erfan

40,971
8
66
78

score 1 · Answer 2 · answered Jun 18 '19 at 18:48

sum_res= df.groupby(['CLient ID'],as_index=False)['USD_Balance'].sum()
sum_res['grand_total'] ='Grand Total'
df.sort_values(by=['CLient ID'],ascending=[True],inplace=True)

Separate two columns from original dataframe after sorting
res = df[['CLient ID','USD_Balance']]
final_res = pd.concat([res,sum_res])
final_res = final_res.sort_values(by=['CLient ID','grand_total'],ascending=[True,True],na_position='first')
final_res['CLient ID'] =np.where(final_res['grand_total'].isnull(), final_res['CLient ID'], final_res['grand_total'])

final_res.drop(['grand_total'],axis=1,inplace=True)

You welcome @CJ123 approve the answer so that more people can benefit from the solution — tawab_shakeel, Jun 20 '19 at 15:41

score 0 · Answer 3 · answered Jun 18 '19 at 18:55

Are you okay with doing it a little different way?

dftotal = df.groupby('CLient ID')['USD_Balance'].sum().reset_index()
dftotal['CLient ID'] = 'SubTotal'
pd.concat([df, dftotal])

Output:

  CLient ID  USD_Balance
0         1            2
1         1            2
2         1            2
0  SubTotal            6

Adding a row with subtotal by category to my dataframe

3 Answers3

Linked

Related