Search code examples
pandaspython-3.8pandas.excelwriter

Pandas dataframe to excel with color formatting using ExcelWriter and left alignment

There is an answer to similar problem at another thread here.But I am looking to learn this using Pandas ExcelWriter

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:

enter image description here

Please note the left aligned display of column A. I am also interested in learning how I can use ExcelWriter to left align the output to excel.

A started code that I am using right now is:

writer = pd.ExcelWriter(r'C:\outfile.xlx', engine='xlsxwriter')
df.to_excel(writer, sheet_name='mysheet', index=False)
worksheet = writer.sheets['mysheet']

I am not sure how to approach this beyond the code above.

Solution

  • I would use style:

    def hightlighter(x):
    return np.select((x<df['P25'], x>df['P75']), 
              ('background-color: red', 'background-color: green'),
              'background-color: yellow')
def align(x):
    return ['text-align:left']*len(x)

(df.style
   .apply(hightlighter, subset=['ATC','Avg'])
   .apply(align, subset=['Source'])
   .to_excel(writer, sheet_name='mysheet', index=False, freeze_panes=(1,0))
)