CODEWITHSUNDEEP
pythonpython-3.xpandaspivot-tableopenpyxl
user5438246
user5438246

Reputation: 31

Writing a Python Created Pivot Table using Pandas to a Excel Document

I have been working on automating a series of reports in python. I have been trying to create a series of pivot tables from an imported csv (binlift.csv). I have found the Pandas library very useful for this however, I cant seem to find anything that helps me write the Panda created pivot tables to my excel document (Template.xlsx) and was wondering if anyone can help. So far I have the written the following code

import openpyxl
import csv
from datetime import datetime
import datetime
import pandas as pd
import numpy as np


file1 = "Template.xlsx"   # template file
file2 = "binlift.csv"     # raw data csv


wb1 = openpyxl.load_workbook(file1) # opens template
ws1 = wb1.create_sheet("Raw Data") # create a new sheet in template called Raw Data


summary = wb1.worksheets[0]    # variables given to sheets for manipulation
rawdata = wb1.worksheets[1]



headings = ["READER","BEATID","LIFTYEAR","LIFTMONTH","LIFTWEEK","LIFTDAY","TAGGED","UNTAGGEDLIFT","LIFT"]
df = pd.read_csv(file2, names=headings)

pivot_1 = pd.pivot_table(df, index=["LIFTYEAR", "LIFTMONTH","LIFTWEEK"], values=["TAGGED","UNTAGGEDLIFT","LIFT"],aggfunc=np.sum)
pivot_2 = pd.pivot_table(df, index=["LIFTYEAR", "LIFTMONTH"], values=["TAGGED","UNTAGGEDLIFT"],aggfunc=np.sum)
pivot_3 = pd.pivot_table(df, index=["READER"], values=["TAGGED","UNTAGGEDLIFT","LIFT"],aggfunc=np.sum)

print(pivot_1)
print(pivot_2)
print(pivot_3)

wb1.save('test.xlsx')enter code here

Upvotes: 0

Views: 1056

Answers (1)

blutab
blutab

Reputation: 181

There is an option in pandas to write the 'xlsx' files. Here basically we get all the indices (at level 0) of the pivot table, and then one by one we go over these indices to subset the table and write that part of the table.

writer = pd.ExcelWriter('output.xlsx')

for manager in pivot_1.index.get_level_values(0).unique():
    temp_df = pivot_1.xs(manager, level=0)
    temp_df.to_excel(writer, manager)

writer.save()

Upvotes: 1

Related Questions