How to apply a defined function of format to a dataframe-CodePudding

I have multiple data frames (df, df1, df2,...) I want to apply my defined format and then export them to Excel (Excel-file, Excel-file1, Excel-file2,...)

I think of creating a defined function of formatting and applying it to my data frames but I do not know how to do about this. # Create a Pandas Excel writer using XlsxWriter writer = pd.ExcelWriter(r'N:\Excel-file.xlsx', engine='xlsxwriter')

# Skip one row to insert a defined header, turn off the default header, and remove index
df.to_excel(writer, sheet_name='Sheet1', startrow=1, header=False, index=False)

# The xlsxwriter workbook and worksheet objects
workbook = writer.book
worksheet = writer.sheets['Sheet1']

# The default format of the workbook
workbook.formats[0].set_font_size(12)
workbook.formats[0].set_align('right')

# Header format
header_format = workbook.add_format({
    'bold': True,
    'border': 0})
header_format.set_font_size(14)
header_format.set_align('center')

# Write the column headers with the defined format
for col_num, value in enumerate(df.columns.values):
    worksheet.write(0, col_num, value, header_format)

# Export the Excel file
writer.close()

The defined function looks like below

    def format(df, file_name):
        with pd.ExcelWriter(r'file_name.xlsx', engine='xlsxwriter') as writer:
            df.to_excel(writer, sheet_name='Sheet1', startrow=1, header=False, index=False)
            workbook = writer.book
            worksheet = writer.sheets['Sheet1']
            workbook.formats[0].set_font_size(12)
            workbook.formats[0].set_align('right')
            header_format = workbook.add_format({
            'bold': True,
            'border': 0})
            header_format.set_font_size(14)
            header_format.set_align('center')
            for col_num, value in enumerate(df.columns.values):
                worksheet.write(0, col_num, value, header_format)


df_list = [df1, df2, df3, df4, df5, df6]

for i, df in enumerate(df_list):
  format(df, f"Excel-file{i 1}.xlsx")

The error is the format function only is applied to the df6 and I got 1 Excel file named "file_name". Any way to fix this issue?

CodePudding user response：

pandas.DataFrame.apply() iterates the dataframe, on which it is called, by rows (or by columns) and applies provided transformation and returns one result per row (or column). The transformation logic should consider this fact and should process it as if it is individual row (or column). Per your source code above, you seem to be applying the logic to entire dataframe (df1) on which apply() is called.

I assume your problem statement is that you have multiple data frames (df, df1, df2,...) and you want to export them to individual Excel files by applying some common transformation logic.

You can collect them into a list and process them individually in a loop. Since format() does not return any results (i.e. not transforms the list element), the old fashioned iteration using for loop should be the way to go about it. Also, consider using with syntax for auto file resource handling to avoid memory leak or orphan file handlers.

def format(df, file_name):
  with pd.ExcelWriter(r'file_name.xlsx', engine='xlsxwriter') as writer:
    df.to_excel(writer, sheet_name='Sheet1', startrow=1, header=False, index=False)
    workbook = writer.book
    worksheet = writer.sheets['Sheet1']
    workbook.formats[0].set_font_size(12)
    workbook.formats[0].set_align('right')
    header_format = workbook.add_format({
    'bold': True,
    'border': 0})
    header_format.set_font_size(14)
    header_format.set_align('center')
    for col_num, value in enumerate(df.columns.values):
        worksheet.write(0, col_num, value, header_format)


df_list = [df1, df2, df3, ...]

for i, df in enumerate(df_list):
  format(df, f"Excel-file{i 1}.xlsx")