Python Forum
Python and a pivot table
Thread Rating:
  • 0 Vote(s) - 0 Average
  • 1
  • 2
  • 3
  • 4
  • 5
Python and a pivot table
#1
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()
Reply


Possibly Related Threads…
Thread Author Replies Views Last Post
  pivot table (excel vs python) Bric 1 1,641 Feb-02-2022, 11:04 PM
Last Post: Bric
  Resampling and regrouping using pivot table karlito 4 3,265 Apr-25-2020, 04:03 PM
Last Post: karlito
  Pivot Table hash problem stilwellj 1 2,162 Oct-30-2019, 08:14 AM
Last Post: Larz60+
  Pivot dataframe SriMekala 2 2,277 Jun-22-2019, 03:02 AM
Last Post: SriMekala

Forum Jump:

User Panel Messages

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