Skip to content
Advertisement

Exporting many excel files based on column value in pandas dataframe while maintaining formatting?

I have a dataframe that I would like to parse to individual files based on unique strings in a column “names.” I can do this with a simple function like:

f= lambda x: x.to_excel(os.getcwd()+'\{}.xlsx'.format(x.name), index= False)
df.groupby('names').apply(f) 

The caveat being that I would like write out these files with conditional formatting. I’ve been able to achieve the desired formatting using ExcelWriter in the following block:

writer= pd.ExcelWriter('Test.xlsx', engine='xlsxwriter' )
df.to_excel(writer, sheet_name='Sheet1', index=False)
workbook= writer.book
worksheet= writer.sheets['Sheet1']
format1= workbook.add_format({'font_color':'#ec5a24'})
format2= workbook.add_format({'bg_color':'#4b4b4b','font_color':'#ffde00'})
format3= workbook.add_format({'font_color':'#86b05b'})
worksheet.conditional_format('A2:AM10000',{'type':'formula','criteria':'=INDIRECT("E"&ROW())= "High"', 'format':format1})
worksheet.conditional_format('A2:AM10000',{'type':'formula','criteria':'=INDIRECT("E"&ROW())= "Med"', 'format':format2})
worksheet.conditional_format('A2:AM10000',{'type':'formula','criteria':'=INDIRECT("E"&ROW())= "Normal"', 'format':format3})
workbook.close()
writer.save()

Is there a method that would allow me to combine these two so that my output is many files (one for each unique name in the column names) which has the desired conditional formatting in the excel files?

New Code per request:

def new_func():
    f= lambda x: x.to_excel(os.getcwd()+'\{}.xlsx'.format(x.name), index= False)
    writer= pd.ExcelWriter(x.name, engine='xlsxwriter' )
    df.to_excel(writer, sheet_name='Sheet1', index=False)
    workbook= writer.book
    worksheet= writer.sheets['Sheet1']
    format1= workbook.add_format({'font_color':'#ec5a24'})
    format2= workbook.add_format({'bg_color':'#4b4b4b','font_color':'#ffde00'})
    format3= workbook.add_format({'font_color':'#86b05b'})
    worksheet.conditional_format('A2:AM10000',{'type':'formula','criteria':'=INDIRECT("E"&ROW())= "High"', 'format':format1})
    worksheet.conditional_format('A2:AM10000',{'type':'formula','criteria':'=INDIRECT("E"&ROW())= "Med"', 'format':format2})
    worksheet.conditional_format('A2:AM10000',{'type':'formula','criteria':'=INDIRECT("E"&ROW())= "Low"', 'format':format3})
    workbook.close()
    writer.save()
df.groupby('names').apply(new_func())

Advertisement

Answer

Here is the solution that worked for me (I added a header format too), using a lot of help from johathan:

def new_func(x):
        writer= pd.ExcelWriter(os.getcwd()+'\{}.xlsx'.format(x.name), engine='xlsxwriter' )
        x.to_excel(writer, sheet_name='Sheet1', index=False)
        workbook= writer.book
        worksheet= writer.sheets['Sheet1']
        format1= workbook.add_format({'font_color':'#ec5a24'})
        format2= workbook.add_format({'bg_color':'#4b4b4b','font_color':'#ffde00'})
        format3= workbook.add_format({'font_color':'#86b05b'})
        header= workbook.add_format({'font_size':14, 'bold': True, 'font_color':'#3582c0'})
        worksheet.conditional_format('A2:AM10000',{'type':'formula','criteria':'=INDIRECT("E"&ROW())= "High"', 'format':format1})
        worksheet.conditional_format('A2:AM10000',{'type':'formula','criteria':'=INDIRECT("E"&ROW())= "Med"', 'format':format2})
        worksheet.conditional_format('A2:AM10000',{'type':'formula','criteria':'=INDIRECT("E"&ROW())= "Low"', 'format':format3})
        pandas.io.formats.excel.ExcelFormatter.header_style = None
        worksheet.set_row(0, None, header)
        workbook.close()
        writer.save()
    
       
df.groupby('name').apply(new_func)
User contributions licensed under: CC BY-SA
5 People found this is helpful
Advertisement