Pandas add cell background color using list and save to excel

Question

I have dataframe as below.

I want to change background color by condition and finally I want to save to excel. There are some cases as follow:

condition 1: exact match & case sensitive=False --> red
condition 2: contain & case sensitive=False --> yellow
condition 3: repeat same character 3 more --> blue
list_for_condition1 = [us, ca]
list_for_condition1 = [apple, sam]
list_for_condition3 = [xxx, xxxx, xxxxxxxxxxx, bbb, aaa]

This is expected output.

I found below code using stype.applymap but couldn't apply this case.

styled = (df.style.applymap(lambda v: 'background-color: %s' % 'green' if v=='col' else ''))
styled.to_excel('d:/temp/styled.xlsx', engine='openpyxl')

How can I solve this?

Quang Hoang · Accepted Answer · 2020-04-06 04:35:21Z

2

I would define a separate function reflecting your conditions and applymap:

def colorize(x):
    try:
        lower = x.lower()
        # condition 1, pass the corresponding list
        if lower in ['us','ca']: return 'background-color: red'

        # condition 2
        for s in ['apple', 'sam']: 
            if s in lower: return 'background-color: yellow'

        # condition 3
        for s in ['xxx','bbb', 'aaa']:
            if s in lower: return 'background-color:  blue'

        return ''
   except:
        return ''

# toy data
df = pd.DataFrame({'Country':['USA','us','usa','ca', 'CA'],
                   'V1':["Apple", "Applex", 'APPLE', 'SAMSUNG','xxx'],
                   'V2':['Samsung', 'Semsung', 'SamSung1', 'apple', 'bbbb'],
                   'V3':['SAMSUNG', 'SS','APPLEXX', 'APPLEs', 'aaa']
                   })

df.style.applymap(colorize)

Output:

edited Apr 6, 2020 at 4:35

answered Apr 6, 2020 at 3:54

Quang Hoang

151k11 gold badges64 silver badges86 bronze badges

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

5 Comments

purplecollar Over a year ago

AttributeError: 'int' object has no attribute 'lower'

Quang Hoang Over a year ago

I saw that coming. See update with try:.. except:.

purplecollar Over a year ago

error occurred ( 'int' object has no attribute 'lower') and 'CA' in Country Column not change background color.

Quang Hoang Over a year ago

For reference, you did say case sensitive, which means 'ca' != 'CA'. Anyway, see update.

purplecollar Over a year ago

sorry, my mistake. I means case sensitive=False. Thank you for your inform.

Collectives™ on Stack Overflow

Pandas add cell background color using list and save to excel

1 Answer 1

5 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

5 Comments

Your Answer

Sign up or log in

Post as a guest

Related