Python and a pivot table - Printable Version +- Python Forum (https://python-forum.io) +-- Forum: Python Coding (https://python-forum.io/forum-7.html) +--- Forum: Data Science (https://python-forum.io/forum-44.html) +--- Thread: Python and a pivot table (/thread-22210.html) |
Python and a pivot table - stilwellj - Nov-04-2019 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. 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() |