Conditional format cell in column based on it corresponding value in another column

Question

I have created an excel file from a dataframe that looks like this:

In [215]: import pandas as pd

In [216]: df = pd.DataFrame({"Name": ["A", "B", "C"], "Status": ['y', 'n', 'yy']})

In [217]: df
Out[217]:
  Name  Status
0    A       y
1    B       n
2    C      yy

How can I set the bg_color for "Name" based on the value of Status? I have tried a couple of options without success:

format1 = workbook.add_format({"bg_color": "#669731"})
format2 = workbook.add_format({"bg_color": "#FFFA22"})
format3 = workbook.add_format({"bg_color": "#A43829"})

Option 1

worksheet.conditional_format("A2",
                             {"type": "formula",
                              "criteria": "=ISNUMBER(SEARCH('y', B2))",
                              "format": format1
                             }
)

Option 2

worksheet.conditional_format("A2",
                             {"type": "formula",
                              "criteria": "=$B$2='y'",
                              "format": format1
                             }
)

None of this has given the expected result and when I open the file, I get an error with the following message: unreadable content in the .xlsx
Also it will be good if I could somehow set do this without iterating the dataframe's value.

patrickjlong1 · Accepted Answer · 2017-10-25 15:20:38Z

4

Excel doesn't seem to like the single quotes for conditional formatting on a string. It works if you have the double quotes on the inside, i.e.

"criteria": '=($B$2="y")'

versus

"criteria": "=($B$2='y')"

I've put a full reproducible example below with a screenshot of the solution.

import pandas as pd

df = pd.DataFrame({"Name": ["A", "B", "C"], "Status": ['y', 'n', 'yy']})

writer = pd.ExcelWriter('test.xlsx', engine='xlsxwriter')

df.to_excel(writer, sheet_name='Sheet1', index=False)


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

format1 = workbook.add_format({"bg_color": "#669731"})

worksheet.conditional_format("A2",
                             {"type": "formula",
                              "criteria": '=($B$2="y")',
                              "format": format1
                             }
)

workbook.close()

If you want to set this conditional format for a range of say 1000 cells in the column this is possible using the code for the conditional format.

worksheet.conditional_format("A2:A1001",
                             {"type": "formula",
                              "criteria": '=(B2:B1001="y")',
                              "format": format1
                             }
)

If on the other hand you want to set multiple conditions over a range, the only way I think this would be possible is to use a for loop, writing each cell with the format that matches a condition. I've provided the example below and it's expected output. Notice that this is a bit of a cheat since it overwrites what has already been put in the cell, if it meets any of the three conditions.

import pandas as pd

df = pd.DataFrame({"Name": ["A", "B", "C"], "Status": ['y', 'n', 'yy']}) 
writer = pd.ExcelWriter('test.xlsx', engine='xlsxwriter') 
df.to_excel(writer, sheet_name='Sheet1', index=False) 

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

format1 = workbook.add_format({"bg_color": "#669731"})
format2 = workbook.add_format({"bg_color": "#FFFA22"})
format3 = workbook.add_format({"bg_color": "#A43829"})

for i in range (0, len(df)):
    if df['Status'].ix[i] == "y":
        worksheet.write(i+1, 0, df['Name'].ix[i], format1)
    elif df['Status'].ix[i] == "n":
        worksheet.write(i+1, 0, df['Name'].ix[i], format2)
    elif df['Status'].ix[i] == "yy":
        worksheet.write(i+1, 0, df['Name'].ix[i], format3)


workbook.close()

edited Oct 25, 2017 at 15:20

answered Oct 24, 2017 at 20:03

patrickjlong1

3,8231 gold badge21 silver badges34 bronze badges

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

2 Comments

Sede Over a year ago

Well, that works. Thank you. Do you know how I can apply that to all cell in A column with their corresponding value without doing the for loop? I have three different possible formats for each cell and a large dataset. thus manual iteration might cause a drop of performance.

patrickjlong1 Over a year ago

I think the only way to do it is to use the for loop. One condition over multiple cells is fine though. I've expanded on the answer to show how to do it with for loop.

Collectives™ on Stack Overflow

Conditional format cell in column based on it corresponding value in another column

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related