Format excel file with colors pd.ExcelWriter

Question

I have a dataframe df

df
Out[15]: 
           Subject ID  
StartDate                                                                    
2017-11-06        ID1  
2017-11-07        ID1
2017-11-08        ID2  
2017-11-10        ID3

and I save it in an excel file

writer = pd.ExcelWriter(os.path.join(folders_path,'summary.xls'))
df.to_excel(writer,'Sheet1')
writer.save()

However I would like to format the color of the rows according to the Subject ID , i.e. I would like a different colour for each ID

Simpom · Accepted Answer · 2017-10-05 09:00:05Z

7

You have to use the 'xlsxwriter' engine and define your formats.

Below is a snippet of what is possible:

#!/usr/bin/env python3

from datetime import date
import pandas as pd

data = [['2017-11-06', 'ID1'],
        ['2017-11-07', 'ID1'],
        ['2017-11-08', 'ID2'],
        ['2017-11-10', 'ID3']]

df = pd.DataFrame(data, columns=['StartDate', 'Subject ID'])
print(df)

writer = pd.ExcelWriter('/home/spomared/tmp/summary.xls', engine='xlsxwriter')
df.to_excel(writer, sheet_name='Sheet1', index=False)

workbook = writer.book
worksheet = writer.sheets['Sheet1']

my_formats = {'"ID1"': '#FF0000',
              '"ID2"': '#00FF00',
              '"ID3"': '#0000FF'}

for val, color in my_formats.items():
    fmt = workbook.add_format({'font_color': color})
    worksheet.conditional_format('B2:B5', {'type': 'cell',
                                           'criteria': '=',
                                           'value': val,
                                           'format': fmt})

writer.save()

Here is the output Excel file:

You can find some references of conditional formatting on http://xlsxwriter.readthedocs.io/working_with_conditional_formats.html

edited Oct 5, 2017 at 9:00

answered Oct 5, 2017 at 8:52

Simpom

1,0071 gold badge7 silver badges25 bronze badges

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

3 Comments

gabboshow Over a year ago

Thank you! I wanted to color all the rows (background).. not only the "IDs"...is that possible?

Simpom Over a year ago

Of course: just change the font_color keyword by bg_color in workbook.add_format. Consider looking at the given xlsxwriter link: it shows lots of use cases.

Simpom Over a year ago

See the format help page to have a list of available options: xlsxwriter.readthedocs.io/format.html

Collectives™ on Stack Overflow

Format excel file with colors pd.ExcelWriter

1 Answer 1

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related