CODEWITHSUNDEEP
pythonpython-3.xpandas
Serdia
Serdia

Reputation: 4428

How to write a header into multiple worksheet using python pandas

I am breaking down dataframe by column OfficeLocation, and putting excel file in defined folder. 

import pandas as pd


df = pd.DataFrame({
    'PolicyNumber':['Pol1','Pol2','Pol3','Pol4','Pol5'],
    'OfficeLocation':['San Diego', 'San Diego','San Diego','Vista','Vista'],
    'Premium':[800,300,650,200,700]
})


folder_list = ['San Diego', 'Vista']

for location, d in df.groupby('OfficeLocation'):
    for folder in folder_list:
        if folder == location:            
            d.to_excel(f'\\\\server\user\name\{folder}\{location}.xlsx',sheet_name=location, startrow=4, index=False)

But how can I also write a header "Location - San Diego", "Location - Vista" in each excel spreadsheet?

enter image description here

Upvotes: 1

Views: 83

Answers (1)

jtweeder
jtweeder

Reputation: 759

I believe this should get what you want.

import pandas as pd


df = pd.DataFrame({
    'PolicyNumber':['Pol1','Pol2','Pol3','Pol4','Pol5'],
    'OfficeLocation':['San Diego', 'San Diego','San Diego','Vista','Vista'],
    'Premium':[800,300,650,200,700]
})


folder_list = ['San Diego', 'Vista']

for location, d in df.groupby('OfficeLocation'):
    for folder in folder_list:
        if folder == location:            
            writer = pd.ExcelWriter(f'\\\\server\user\name\{folder}\{location}.xlsx', 
                                    engine='xlsxwriter')
            d.to_excel(writer, sheet_name=location, startrow=4, index=False)
            writer.sheets[location].write('A1', 'Office - {}'.format(location))
            writer.close()

Upvotes: 1

Related Questions