Pandas: Cant Insert Pivot Table information into a different Sheet

Question

I have a code where I convert a txt to xlsx, then add a column with formulas and then I want to create a Pivot Table with that information in a different Sheet. The code works without errors but it creates and empty Sheet instead of a Sheet with information.

So the code looks like this:

import numpy as np
import openpyxl

#Transforming our txt to xlsx
path = r"C:\Users\roslber\Desktop\Codes\Python\Projects\Automated routes.xlsx"
rssdata= pd.read_csv("dwp.txt", sep="\t")
rssdata.to_excel(path, index= None , header= True)                        

#Writing the formula column
wb = openpyxl.load_workbook(filename=path)
ws1 = wb["Sheet1"]  
ws1["AC1"] = "CF Weight"                                                      
row_count= ws1.max_row
actual_row= 2
while actual_row <= row_count:                                        #writting the formula in every row
    r= str(actual_row)
    ws1["AC"+r] = "=(O"+r + "*P"+r +"*Q"+r +")/28316.8"
    actual_row= actual_row + 1

#Creating a new sheet with the pivot tables  
df = pd.read_excel(path, 0, header= 0)                                      #defining pivot table dataframe
wb.create_sheet("Sheet2")
 
pv_pack = pd.pivot_table(df, values=["actual_service_time"],\
index=["delivery_station_code"], columns=["cluster_prefix"], aggfunc=np.sum)  #constructing the pivot table

print(pv_pack)

with pd.ExcelWriter(path, mode="a") as writer:
    pv_pack.to_excel(writer, sheet_name="Sheet2") 
    writer.save()                                                      #inserting pivot table in sheet2

wb.save(path)

For data protection reasons I can´t show you the information inside the pivot table but when I print it I can see exactly what I want. The problem is that, although a Sheet2 is created correctly, The information that I can see printed doesn't appear in Sheet2. Why is this happening?

I have checked these questions:

Regarding to the first one, apparently openpyxl can't create a Pivot Table, but I actually don't need a Pivot Table format, I just need the pv_pack information in Sheet2 as its shown when I print it.

I tried to change my code to imitate what they did in the second question but it didn't work.

Thank you in advance

Edit answering to RJ Adriaansen: The information in Sheet1 would look like this:

id  order   mtd delivery_station_code   cluster_prefix   actual_service_time
xh  aabb1   one     1                    One_            231    
xr  aabb2   two     2                    Two_            135    
xd  aabb3   three   3                    One_            80 
xh  aabb8   two     1                    Two_            205    
xp  aabb9   three   2                    One_            1  
xl  aabb10  one     3                    Two_            115

And the code printed in my editor looks like this:

delivery_station_code One_  Two_
1                     231   205
2                     1     135
3                     80    115

Can you post some fictitious dummy data that mimics your text file so we can reproduce the issue? — RJ Adriaansen, Mar 04 '21 at 12:16

RJ Adriaansen · Accepted Answer · 2021-03-04T13:55:34.253

with automatically closes the file, so there is no need to try to save it manually. It is also not needed to create the second sheet prior to writing it. Removing writer.save() and moving wb.save(path) up will make the code work.

#Writing the formula column
wb = openpyxl.load_workbook(filename=path)
ws1 = wb["Sheet1"]  
ws1["AC1"] = "CF Weight"                                                      
row_count= ws1.max_row
actual_row= 2

while actual_row <= row_count:                                        #writting the formula in every row
    r= str(actual_row)
    ws1["AC"+r] = "=(O"+r + "*P"+r +"*Q"+r +")/28316.8"
    actual_row= actual_row + 1
wb.save(path)

#Creating a new sheet with the pivot tables  
df = pd.read_excel(path, 0, header= 0)                                      #defining pivot table dataframe
 
pv_pack = pd.pivot_table(df, values=["actual_service_time"],\
index=["delivery_station_code"], columns=["cluster_prefix"], aggfunc=np.sum)  #constructing the pivot table

with pd.ExcelWriter(path, mode="a") as writer:
    pv_pack.to_excel(writer, sheet_name="Sheet2")

Awesome Adriaansen. That's exactly what I needed. Thank you so much — R23, Mar 04 '21 at 14:17

Pandas: Cant Insert Pivot Table information into a different Sheet

1 Answers1