I have created a pivot table with multiple index as follows:
I tried the below codes but nothing worked. Appreciate it if someone can help with this.
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 totalMy 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