9

I have a dataframe

df = pd.DataFrame(columns = ['id', 'name', 'age'])
df.loc[len(df), :] = [1, 'John', 21]
df.loc[len(df), :] = [2, 'Mary', 19]
df.loc[len(df), :] = [3, 'Ann', 27]
df.loc[len(df), :] = [4, 'Ben', 18]

I want to save it to excel file using xlsxwriter.

However, I want the age larger than 20 to be in red.

In other words, 21 and 27 should appear red in the excel file.

How to do it?

Chan
  • 3,605
  • 9
  • 29
  • 60
  • 1
    Possible duplicate of [how to color selected columns in python dataframe?](https://stackoverflow.com/questions/42408246/how-to-color-selected-columns-in-python-dataframe) – Rahul Agarwal Jan 09 '19 at 12:10
  • Openpyxl 2.5 includes native support for Pandas Dataframes and named styles. check the link below: https://openpyxl.readthedocs.io/en/latest/formatting.html – Harishangaran Krishnamoorty Jan 09 '19 at 12:14

2 Answers2

14

You could use a conditional format with xlsxwriter like this:

import pandas as pd

# Create a Pandas dataframe from some data.
df = pd.DataFrame(columns = ['id', 'name', 'age'])
df.loc[len(df), :] = [1, 'John', 21]
df.loc[len(df), :] = [2, 'Mary', 19]
df.loc[len(df), :] = [3, 'Ann', 27]
df.loc[len(df), :] = [4, 'Ben', 18]

# Create a Pandas Excel writer using XlsxWriter as the engine.
writer = pd.ExcelWriter('pandas_conditional.xlsx', engine='xlsxwriter')

# Convert the dataframe to an XlsxWriter Excel object.
df.to_excel(writer, sheet_name='Sheet1')

# Get the xlsxwriter workbook and worksheet objects.
workbook  = writer.book
worksheet = writer.sheets['Sheet1']


# Add a format. Light red fill with dark red text.
format1 = workbook.add_format({'bg_color': '#FFC7CE',
                               'font_color': '#9C0006'})

# Set the conditional format range.
start_row = 1
start_col = 3
end_row = len(df)
end_cold = start_col

# Apply a conditional format to the cell range.
worksheet.conditional_format(start_row, start_col, end_row, end_cold,
                             {'type':     'cell',
                              'criteria': '>',
                              'value':    20,
                              'format':   format1})

# Close the Pandas Excel writer and output the Excel file.
writer.save()

Output:

output image

jmcnamara
  • 38,196
  • 6
  • 90
  • 108
0

This did not work for strings for me.

writer = pd.ExcelWriter('ProjectSummaryStatusScriptOutput.xlsx', engine='xlsxwriter')
df_to_csv.to_excel(writer, sheet_name='Sheet1')

# Get the xlsxwriter workbook and worksheet objects.
workbook  = writer.book
worksheet = writer.sheets['Sheet1']


# Add a format. Light red fill with dark red text.
format1 = workbook.add_format({'bg_color': '#ffff00'})
format2 = workbook.add_format({'bg_color': '##ff0000'})
format3 = workbook.add_format({'bg_color': '#13bd00'})

# Set the conditional format range.
start_row = 2
start_col = 2
end_row = len(df)
end_cold = len(df.columns)

# Apply a conditional format to the cell range.
worksheet.conditional_format('E2:K56',
                             {'type':     'cell',
                              'criteria': '=',
                              'value':    'Red',
                              'format':   format1})


worksheet.conditional_format('E2:K56',
                             {'type':     'cell',
                              'criteria': '=',
                              'value':    'Yellow',
                              'format':   format2})

worksheet.conditional_format('E2:K56',
                             {'type':     'cell',
                              'criteria': '=',
                              'value':    'Green',
                              'format':   format3})


# Close the Pandas Excel writer and output the Excel file.
writer.save()

My output did not have any formatting

Nagesh Rao
  • 1
  • 2
    as per xlsxwriter documentation you need to do it differently for text cells: worksheet.conditional_format('A1:A4', {'type': 'text', 'criteria': 'containing', 'value': 'foo', 'format': format1}) – Atanas Atanasov Oct 27 '21 at 16:42