Pandas dataframe dump to excel with color formatting

Question

I have a large pandas dataframe df as:

Sou ATC   P25   P75 Avg
A   11    9     15  10
B   6.63  15    15  25
C   6.63  5     10  8

I want to print this datamframe to excel file but I want to apply formatting to each row of the excel file such that following rules are applied to cells in ATC and Avg columns:

colored in red if value is less than P25
colored in green if value is greater than P75
colored in yellow if value is between P25 and P75

Sample display in excel is as follows:

I am not sure how to approach this.

jezrael · Accepted Answer · 2020-05-07 04:36:30Z

1

You can use style.Styler.apply with DataFrame of styles with numpy.select for filling by masks created by DataFrame.lt and DataFrame.gt:

def color(x): 
   c1 = 'background-color: red'
   c2 = 'background-color: green'
   c3 = 'background-color: yellow'
   c = ''

   cols = ['ATC','Avg']
   m1 = x[cols].lt(x['P25'], axis=0)
   m2 = x[cols].gt(x['P75'], axis=0)
   arr = np.select([m1, m2], [c1, c2], default=c3)

   df1 = pd.DataFrame(arr, index=x.index, columns=cols)
   return df1.reindex(columns=x.columns, fill_value=c)

df.style.apply(color,axis=None).to_excel('format_file.xlsx', index=False, engine='openpyxl')

edited May 7, 2020 at 4:36

answered May 7, 2020 at 4:29

jezrael

868k103 gold badges1.4k silver badges1.3k bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Pandas dataframe dump to excel with color formatting

1 Answer 1

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related