CODEWITHSUNDEEP
python-3.xexportexport-to-excel
M ob
M ob

Reputation: 97

2 dataframes save to a file with multiple worksheets

I have a dataset of country and city data in the same file

I read the file, group and create a country level table and city level table.

Now I am trying to save my results into excel file with 2 worksheets and here is the code i am using but city data table is overwriting my country level data. What am i missing

split_country = country['country'].unique()
for value in split_country:
    country1 = country[country['country']== value]
    outputfile = str(value)+'.xlsx'
    country1.to_excel(outputfile, sheet_name='Country', index=False)
    city1 = city[city['country']==value]
    city1.to_excel(outputfile, sheet_name = 'City', index=False)

Upvotes: 1

Views: 28

Answers (1)

M ob
M ob

Reputation: 97

this code solved the problem

import xlsxwriter
split_country = country['Country'].unique()
for value in split_country:
    country1 = country[country['Country']== value]
    outputfile = pd.ExcelWriter(str(value)+'.xlsx', engine='xlsxwriter')
    country1.to_excel(outputfile, sheet_name='Country', index=False)
    city1 = city[city['Country']==value]
    city1.to_excel(outputfile, sheet_name = 'City', index=False)
    outputfile.save()

Upvotes: 1

Related Questions