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)