CODEWITHSUNDEEP
pythonexcelpandasdataframe
user14073111
user14073111

Reputation: 621

Highlight row in a data frame based on a column value?

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?

Desirable output

Upvotes: 2

Views: 803

Answers (2)

Arkadiusz
Arkadiusz

Reputation: 1875

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:

enter image description here

Upvotes: 1

jezrael
jezrael

Reputation: 863511

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)

Upvotes: 2

Related Questions