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

Question

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:

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.

Quang Hoang · Accepted Answer · 2020-05-07 19:22:05Z

2

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))
)

edited May 7, 2020 at 19:22

answered May 7, 2020 at 18:58

Quang Hoang

151k11 gold badges64 silver badges86 bronze badges

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

6 Comments

Zanam Over a year ago

Thanks but this doesn't use ExcelWriter though?

Quang Hoang Over a year ago

I generally found ExcelWriter a bit redundant. I would simplify thing and let pandas handle the writing in the background. Anyway, see updated answer.

Zanam Over a year ago

I also don't see left alignment for column A output

Zanam Over a year ago

Thanks. I forgot to ask or maybe I can start another thread. Is there a way to freeze the first row and first column using style? I know how to do it using ExcelWriter.

Quang Hoang Over a year ago

Actually, you can try adding with option freeze_panes=(1, 0) to to_excel(...).

|

Collectives™ on Stack Overflow

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

1 Answer 1

6 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

6 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related