Python Forum

I have created a pivot table with multiple index as follows:

df1 = pd.DataFrame(data = {'Customer':['Tom Ford','Susan Mock','Tom Ford','Tom Ford','Donale Fucci','Jo Watthanaram'],
                           'Merchant':['Merchant_A','Merchant_A','Merchant_B','Merchant_A' ,'Merchant_B','Merchant_C'],
                           'Type' : ["Type A","Type A", "Type A", "Type A", "Type A", "Type B"],
                           'Date':['20/09/2021  17:45:27','21/09/2021  18:59:24','22/10/2021  20:42:03','25/10/2021  20:42:03','23/10/2021  20:42:03','20/11/2021  20:42:03'],
                           'Sales':[1000,2000,3000,8000,4000,5000],
                            })

df1['Date'] = pd.to_datetime(df1['Date'])
df1['Date_onlyMonth'] = df1['Date'].dt.strftime('%Y-%b')

pivot_1 = pd.pivot_table(df1,index=['Customer','Merchant'],columns=['Type','Date_onlyMonth'],values=["Sales"],aggfunc=[np.sum])
pivot_1 = pivot_1.reindex(level='Date_onlyMonth',columns=['2021-Sep','2021-Oct','2021-Nov']).reindex()

My pivot table is like this

Output:pivot_1
Out[73]: 
                               sum                  
                             Sales                  
Type                        Type A            Type B
Date_onlyMonth            2021-Sep 2021-Oct 2021-Nov
Customer       Merchant                             
Donale Fucci   Merchant_B      nan 4,000.00      nan
Jo Watthanaram Merchant_C      nan      nan 5,000.00
Susan Mock     Merchant_A 2,000.00      nan      nan
Tom Ford       Merchant_A 1,000.00 8,000.00      nan
               Merchant_B      nan 3,000.00      nan

I want to create subtotals columnar-wise for "Type A" & "Type B" separately.

I tried the below codes but nothing worked. Appreciate it if someone can help with this.

pivot_1[:,'Type A Total'] = pivot_1.loc[:,['2021-Sep','2021-Oct']].sum(axis=1) # This gives an error "TypeError: unhashable type: 'slice'"
pivot_1[:,'Type A Total'] = pivot_1.loc[:,['Type']].sum(axis=1)  # This gives an error "TypeError: unhashable type: 'slice'"

pivot_1['Type A Total'] = pivot_1[['2021-Sep','2021-Oct']].sum(axis=1) # This gives an error "KeyError: "['2021-Sep' '2021-Oct'] not in index"'"
pivot_1['Type A Total'] = pivot_1[['Type A']].sum(axis=1) # This gives an error "KeyError: "['Type A'] not in index""
pivot_1['Type A Total'] = pivot_1[['Type']].sum(axis=1)  # This gives an error "KeyError: "['Type'] not in index""

pivot_1['Type A Total'] = pivot_1[:2].sum(axis=1)  # This gives wrong total

My expected pivot table is like this

Output:pivot_1
Out[73]: 
                               sum                  
                             Sales                  
Type                        Type A            Type B
Date_onlyMonth            2021-Sep 2021-Oct 2021-Nov Type A Total
Customer       Merchant                             
Donale Fucci   Merchant_B      nan 4,000.00      nan 4,000.00
Jo Watthanaram Merchant_C      nan      nan 5,000.00      nan
Susan Mock     Merchant_A 2,000.00      nan      nan 2,000.00
Tom Ford       Merchant_A 1,000.00 8,000.00      nan 9,000.00
               Merchant_B      nan 3,000.00      nan 3,000.00

Managed to sort this

pivot_1['Type A Total'] = pivot_1[("sum", "Sales", "Type A")].sum(axis=1)

klllmmm

klllmmm