Let say I have dataframe like this:
col1 col2 col3 col4
0 A A_1 pass 2
1 A A_2 pass 4
2 A A_1 fail 4
3 A A_1 fail 5
4 A A_1 pass 3
5 A A_2 fail 2
I want to save it as an excel file, and then when I open it I want the columns to be highlighted like below, how can I do that?
You can create DataFrame of styles with Styler.apply and set rows by masks with DataFrame.mask:
def color(x):
c1 = 'background-color: green'
c2 = 'background-color: red'
c = ''
m1 = x.col3.eq('pass')
m2 = x.col3.eq('fail')
df1 = pd.DataFrame(c, index=x.index, columns=x.columns)
df1 = df1.mask(m1, c1).mask(m2, c2)
return df1
df.style.apply(color,axis=None).to_excel('styled.xlsx', engine='openpyxl', index=False)
You can also do like that:
from openpyxl import load_workbook
from openpyxl.styles import PatternFill
df.to_excel('my dataframe.xlsx', index=False)
wb = load_workbook('my dataframe.xlsx')
ws = wb.active
for row in ws.iter_rows(min_col=ws.min_column, max_col=ws.max_column,
min_row=ws.min_row+1, max_row=ws.max_row):
for value in row:
if row[2].value == 'pass':
value.fill = PatternFill(fill_type='solid', start_color='00b300', end_color='00b300')
else:
value.fill = PatternFill(fill_type='solid', start_color='ff0000', end_color='ff0000')
wb.save('my dataframe.xlsx')
Result:
