I am trying to create a output pivot table from raw data to a table in a new excel file. below is my error message and my code. I have uploaded my full code as it has what I would like the pivot table to look like.
Error:Traceback (most recent call last):
File "C:\User\python_pivot.py", line 86, in <module>
mtd = pd.pivot_table(df_data, index=[df_sup, df_name], values=[df_pass, df_fail], columns=df_status, aggfunc=np.sum)
File "C:\User\python-3.7.4.amd64\lib\site-packages\pandas\core\reshape\pivot.py", line 71, in pivot_table
if i not in data:
File "C:\User\python-3.7.4.amd64\lib\site-packages\pandas\core\generic.py", line 1936, in __contains__
return key in self._info_axis
File "C:\User\python-3.7.4.amd64\lib\site-packages\pandas\core\indexes\base.py", line 4230, in __contains__
hash(key)
File "C:\User\python-3.7.4.amd64\lib\site-packages\pandas\core\generic.py", line 1886, in __hash__
" hashed".format(self.__class__.__name__)
TypeError: 'DataFrame' objects are mutable, thus they cannot be hashed
import datetime from datetime import timedelta import openpyxl as op from openpyxl import load_workbook import xlsxwriter import time import numpy as np import pandas as pd import PySimpleGUI as sg layout = [ [sg.Text('Step 1 - Download Low RepSate Data.')], [sg.Text('Step 2 - Click Browse and select file that was just downloaded.')], [sg.Text('Step 3 - Click Ok')], [sg.Text('Step 4 - Wait until All Set screen pops up')], [sg.In() ,sg.FileBrowse(file_types=(("Excel Files", "*.xlsx"),))], [sg.Button('Ok'), sg.Button('Exit')] ] layout2 = [ [sg.Text('All Set.')], [sg.Button('Ok')] ] x = datetime.datetime.now() window = sg.Window('File Location', layout) while True: event, values = window.read() if event in (None, 'Ok'): break if event in (None, 'Exit'): quit() window.close() excel_workbook = values[0] sheet1 = pd.read_excel(excel_workbook) sheet1['Survey Date'] = pd.to_datetime(sheet1['Survey Date']) sheet1['Survey Date'] = pd.to_datetime(sheet1['Survey Date']).dt.strftime('%m-%d-%y') agent = sheet1['Agent'] name = [x[:-7] for x in agent] df_name = pd.DataFrame(name,columns=['Agent']) supervisor = sheet1['Supervisor'] name = [x[:-7] for x in supervisor] df_sup = pd.DataFrame(name,columns=['Supervisor']) temp = sheet1['Rep Sat'].astype(int) passrs = pd.np.where(temp == 1, 0, pd.np.where(temp == 2, 0, pd.np.where(temp == 3, 0, pd.np.where(temp == 4, 1, pd.np.where(temp == 5, 1, "N/A"))))) df_pass = pd.DataFrame(passrs,columns=['Pass']) df_pass = df_pass.apply(pd.to_numeric) failrs = pd.np.where(temp == 1, 1, pd.np.where(temp == 2, 1, pd.np.where(temp == 3, 1, pd.np.where(temp == 4, 0, pd.np.where(temp == 5, 0, "N/A"))))) df_fail = pd.DataFrame(failrs,columns=['Fail']) df_fail = df_fail.apply(pd.to_numeric) df_data = pd.concat([sheet1['Survey Date'],sheet1['DataLink ID'],sheet1['UCID'],sheet1['PERNR'],df_name,sheet1['Rep Sat'],df_pass,df_fail,df_sup,sheet1['Low Score Alert'],sheet1['Low Score Coaching Status'],sheet1['Low Score Coaching Disposition'],sheet1['Agent Low Score Coaching Comments'],sheet1['Time to Closed Disposition']],axis=1) #today example for Survey Date = today date. #mtd example for all data in file #Supervisor | Agent | Pass | Fail | Total | Pass % #from df_sup |from df_name | from df_pass | from df_fail | Pass + Fail | Pass / total #Unable to get these two pivot table working. #today = pd.pivot_table(df_data, index=[df_sup, df_name], columns=[df_pass, df_fail], aggfunc=sum) #mtd = pd.pivot_table(df_data, index=[df_sup, df_name], values=[df_pass, df_fail], aggfunc=sum) alert = pd.pivot_table(df_data, index=df_sup, values=df_name, columns=sheet1['Low Score Coaching Status'], fill_value=0, aggfunc=lambda x: x.value_counts().count()) def set_column_width(df_data): length_list = [len(x) for x in df_data.columns] for i, width in enumerate(length_list): df_data.worksheet.set_column(i, i, width) #print(mtd) #quit() today = pd.DataFrame({'Data':[11,12,13,14]}) mtd = pd.DataFrame({'Data':[21,22,23,24]}) TodaysDate = time.strftime("%m-%d %H.%M") excelfile = "RepSat Information " + TodaysDate + ".xlsx" writer = pd.ExcelWriter(excelfile, engine='xlsxwriter') today.to_excel(writer, sheet_name='Today RepSat', index = False) mtd.to_excel(writer, sheet_name='MTD RepSat', index = False) alert.to_excel(writer, sheet_name='Alerts') df_data.to_excel(writer, sheet_name='Raw Data', index = False) writer.save() window = sg.Window('Message', layout2) while True: event, values = window.read() if event in (None, 'Ok'): break window.close()