Change column type when writing dataframe to excel-CodePudding

I am writing multiple dfs to an excel and I am trying to change certain index types to percentage, pound and others.

One of the df look like this ( dummy df ) :

            2019            2020            2021        2022
A           40              40              51          58
B           5               40              54          97
C           0.3             0.5             0.5         0.8
D           2000            40              200         300
E           0.02            1               0.25        0.19

So I would like to change the index E to be type percentage when writing to excel, same thing would happen if I would select the whole row in excel and click % button to change the type.

I tried:

with pd.ExcelWriter(f'{name}.xlsx',  engine="openpyxl", mode="a") as writer: 

    for cat in cats: # creating multiple dfs
        df_temp = df_main[df_main['Category'] == cat]
        df_temp.to_excel(writer, sheet_name = cat)

        # Get the xlsxwriter workbook and worksheet objects.
        workbook  = writer.book
        worksheet = writer.sheets[cat]

        # Add some cell formats.
        format1 = workbook.add_format({'num_format': '#,##0.00'})
        format2 = workbook.add_format({'num_format': '0%'})

        worksheet.set_row('10', 18, format2)

The result I am looking for is :

           2019            2020            2021        2022
A           40              40              51          58
B           5               40              54          97
C           0.3             0.5             0.5         0.8
D           2000            40              200         300
E           2%              100%            25%         19%

But I get an error:

AttributeError: 'Workbook' object has no attribute 'add_format'

I don't how I should continue with this problem and if there is an easier way to change the format types when writing dfs to excel. I am following this guide. And is there a way to give a name of an index which row type needs changing rather than noting the row number?

CodePudding user response：

You have your ExcelWriter engine set to openpyxl. The tutorial you're following calls for xlsxwriter.

writer = pd.ExcelWriter("pandas_column_formats.xlsx", engine='xlsxwriter')

CodePudding user response：

You are using openpyxl engine, instead of xlsxwriter (which is used in guide you linked).