Export To excel from 2 dataframes

Question

I have Two data frames Both have Months column in them
I want to export to excel (create excel files) by df1 months

dint_month = df1['month'].unique()
outputfile = pd.ExcelWriter(str(value)+'.xlsx', engine='xlsxwriter')
    for value in dint_month:
        month1 = df1[df1['month']== value]
        month1.to_excel(outputfile, sheet_name='Course', index=False)
        outputfile.save()

This creates the files and exports the data to the files as I want it to do Now I have a DF2 where I have month column and weekly updates So I want to add that weekly information in separate worksheets in the same month excel file I exported from DF1

I tried one more for loop but that exports all weeks to all the files. Please if you can suggest, how can I achieve this.

Answer 1

Given a dataframe df2 which would have both a reference to your monthly data and to your weekly data. One option would be to proceed as follows:

dint_month = df1['month'].unique()
for value in dint_month:
    outputfile = pd.ExcelWriter(str(value) + '.xlsx', engine='xlsxwriter')
    month = df1[df1['month'] == value]
    week_data = df2[df2['month'] == value]
    month.to_excel(outputfile, sheet_name='course', index=False)
    week_data.to_excel(outputfile, sheet_name='course_weeks', index=False)
    outputfile.save()

---

Note that there are a couple of things going on:

First, the ouput file creation line was moved within the loop as it was dependent on your value variable.

outputfile = pd.ExcelWriter(str(value) + '.xlsx', engine='xlsxwriter')

Then we filter the week data on a month column identical to your first dataframe. (given that its there).

week_data = df2[df2['month'] == value]

Finally, we output that data to another sheet by changing the sheet name on the same output file.

week_data.to_excel(outputfile, sheet_name='course_weeks', index=False)

This set up works in the scenario where DF2 also has a month column. If that is not the case you would also have to add a month column to your second dataframe or to rename the month in df2[df2['month'] == value] to your actual column name.