Python Forum

I am trying to turn my data frame into a pivot table. I am getting this error "TypeError: 'DataFrame' objects are mutable, thus they cannot be hashed". Below is my code.

import datetime
from datetime import timedelta

import numpy as np
from openpyxl import load_workbook
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, errors='coerce')

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, errors='coerce')

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)

table = pd.pivot_table(df_data, values=[df_pass, df_fail], index=[df_sup, df_name], aggfunc=np.sum)

table.to_excel("RepSat Information.xlsx", index = False)

window = sg.Window('Message', layout2)

while True:
    event, values = window.read()
    if event in (None, 'Ok'):
        break
window.close()

Please, always post the error traceback complete and unaltered (in error tags)
It contains valuable debugging information.
without this, hard to say which line is causing error.

stilwellj

Larz60+