0
$\begingroup$

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:click fo image


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



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

$\endgroup$
0
$\begingroup$

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

$\endgroup$
  • $\begingroup$ i ran into the following error: Exception: Excel worksheet name 'Sheet1_Runners-Up_QualifiedTeams' must be <= 31 chars. $\endgroup$ – Abraham Wilson Jun 19 '18 at 14:00
  • $\begingroup$ Instead of writing it into different CSV, is it possible to convert the ptable into dataframes?? $\endgroup$ – Abraham Wilson Jun 20 '18 at 11:52

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.