Python Forum
Sum the values in a pandas pivot table specific columns
Thread Rating:
  • 0 Vote(s) - 0 Average
  • 1
  • 2
  • 3
  • 4
  • 5
Sum the values in a pandas pivot table specific columns
#1
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
Reply
#2
Managed to sort this

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


Possibly Related Threads…
Thread Author Replies Views Last Post
  Converting column of values into muliple columns of counts highland44 0 253 Feb-01-2024, 12:48 AM
Last Post: highland44
  How to create a table with different sizes of columns in MS word pepe 8 1,562 Dec-08-2023, 07:31 PM
Last Post: Pedroski55
  Trying to get counts/sum/percentages from pandas similar to pivot table cubangt 6 1,406 Oct-06-2023, 04:32 PM
Last Post: cubangt
  Using pyodbc&pandas to load a Table data to df tester_V 3 811 Sep-09-2023, 08:55 PM
Last Post: tester_V
  Color a table cell based on specific text Creepy 11 1,985 Jul-27-2023, 02:48 PM
Last Post: deanhystad
  Printing specific values out from a dictionary mcoliver88 6 1,390 Apr-12-2023, 08:10 PM
Last Post: deanhystad
Photo How to select NULL and blank values from MySQL table into csv python300 9 2,442 Dec-27-2022, 09:43 PM
Last Post: deanhystad
  python pandas sql table with header mg24 3 1,951 Dec-08-2022, 08:31 PM
Last Post: Larz60+
  Need Help! Pandas EXCEL PIVOT psb3958 1 947 Nov-13-2022, 10:37 PM
Last Post: deanhystad
  store all variable values into list and insert to sql_summary table mg24 3 1,143 Sep-28-2022, 09:13 AM
Last Post: Larz60+

Forum Jump:

User Panel Messages

Announcements
Announcement #1 8/1/2020
Announcement #2 8/2/2020
Announcement #3 8/6/2020