Adding subtotals to pivot_table using groupby

Question

I'm trying to include subtotals in my pivot_table.

Pivot table code

# Specify the aggregation functions
aggfunc = {'amt': 'sum', 'llp': 'first'}

Pivot and calculate aggregated values using aggfunc

Pivoted_data = pd.pivot_table(pivot_data,index=['Expense', 'Name', 'Currency', 'Facility'],values= ["amt", "llp"], aggfunc= aggfunc, dropna=True)

I have this code which works but my output is creating a long string rather than separate columns for each of the indexes.

sub = pd.concat([d.append(d.sum().rename((k,'Total')))
for k, d in pivoted_data.groupby(level=0)])
.append(pivoted_data.sum().rename(('Grand','Total'))) ```

How can I have it so the indexes aren't merged together as a list in one cell when exporting to excel?

So I want this:

Expense	Name	Currency	Name	Amt	LLP
A	Xy	Eur	Xyz	7059	0
A Total				7059	0
B	Zz	GBP	Xyzs	5803	50
B	Z2z	GBP	Xyzs123	9586	6000
B Total				15389	6050

But my code is giving me this.

	Amt	LLP
A, Xy, Eur,Xyz	7059	0
A Total			7059
B, Zz, GBP, Xyzs	5803	50
B, Z2z, GBP, Xyzs123	9586	6000
B Total			15389

jezrael · Accepted Answer · 2023-08-18 08:10:53Z

1

There is MultiIndex with 4 levels, so added tuples with all values, because append is deprecated solution use concat:

sub = pd.concat([pd.concat([d, d.sum().rename((f'{k} Total','','','')).to_frame().T]) 
                 for k, d in pivoted_data.groupby(level=0)] + 
                [pivoted_data.sum().rename((f'Grand Total','','','')).to_frame().T])
print (sub)
                                     Amt   LLP
Expense     Name Currency Name.1              
A           Xy   Eur      Xyz       7059     0
A Total                             7059     0
B           Zz   GBP      Xyzs      5803    50
            Z2z  GBP      Xyzs123   9586  6000
B Total                            15389  6050
Grand Total                        22448  6050

answered Aug 18, 2023 at 8:10

jezrael

868k103 gold badges1.4k silver badges1.3k bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Adding subtotals to pivot_table using groupby

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related