how to export the tables into a csv file pandas

Question

The following is a piece of code I wrote to create a pivot table for categorical vs continuous variable.

for row in categorical:
    for col in numeric:
        ptable = pd.pivot_table(df, values = col, index = row, aggfunc = ['min','max','median','mean','std',lambda x: 100*x.count()/df.shape[0]])
        print(ptable)
        writer = pd.ExcelWriter('report.xlsx')
        ptable.to_excel(writer, 'Sheet1')
        writer.save()

It displays the output as in the image:

but this is not a data frame and when writing into an excel file it displays only the last iteration values.

how do I get all the iterated tables into the excel file or separate excel files?

Answer 1

Your current code overwrites the previous sheet, which is why only the last iteration is present. Setting each sheet to the same name (Sheet1) will overwrite the sheet. This name will need to be changed for each interation.

Try this:

for row_index, row in enumerate(categorical):
    for col_index, col in enumerate(numeric):
        ptable = pd.pivot_table(df, values = col, index = row, aggfunc = ['min','max','median','mean','std',lambda x: 100*x.count()/df.shape[0]])
        print(ptable)
        writer = pd.ExcelWriter('report.xlsx')
        ptable.to_excel(writer, 'Sheet1_{}_{}'.format(row_index, col_index))
        writer.save()

This should save each iteration as an individual sheet