0

pivot table in excel

df=pd.DataFrame({'Fruit':['Apple', 'Orange', 'Apple', 'Apple', 'Orange', 'Orange'],
            'Variety':['Fuji', 'Navel', 'Honeycrisp', 'Gala', 'Tangerine', 'Clementine'],
            'Count':[2, 5, 5, 1, 8, 4]})
df_pvt=pd.pivot_table(df, index=['Fruit', 'Variety'], values=['Count'], aggfunc=np.sum)
df_final=pd.concat([
d.append(d.sum().rename((k, 'SubTotal')))
for k, d in df_pvt.groupby(level=0)
]).append(df_pvt.sum().rename(('','GrandTotal')))

subtotal

df_final.to_excel('pvt.xlsx')

yield this exported to excel

1) How can I get the pivot table generated in pandas to look like the excel one? 2) How do I get the subtotals in each of the top row like excel?

Thank you.

user7675621
  • 51
  • 4

1 Answers1

0

IIUC,

df_grand = df[['Count']].sum().T.to_frame(name='Count').assign(Fruit='Grand Total', Variety='').set_index(['Fruit','Variety'])

df_sub = df.groupby('Fruit')[['Count']].sum().assign(Variety='').set_index('Variety', append=True)

df_excel = df.set_index(['Fruit','Variety']).append(df_sub).sort_index().append(df_grand)

Output:

                        Count
Fruit       Variety          
Apple                       8
            Fuji            2
            Gala            1
            Honeycrisp      5
Orange                     17
            Clementine      4
            Navel           5
            Tangerine       8
Grand Total                25
Scott Boston
  • 147,308
  • 15
  • 139
  • 187
  • Thank you. Instead of variety= ' ', if I put Variety='SubTotal' it comes to the bottom again for each group. Is there a way to fix this? – user7675621 Oct 29 '19 at 01:02
  • Use numbers or make it alphabetical or create pd.Categorical data with a forced order. Those are the only ways. – Scott Boston Oct 29 '19 at 01:32