Format the color of a cell in a pandas dataframe according to multiple conditions

Question

I am trying to format the color of a cell of an specific column in a data frame, but I can't manage to do it according to multiple conditions.

This is my dataframe (df):

    Name     ID         Cel     Date
0   Diego   b000000005  7878    2565-05-31 20:53:00
1   Luis    b000000015  6464    2017-05-11 20:53:00
2   Vidal   b000000002  1100    2017-05-08 20:53:00
3   John    b000000011  4545    2017-06-06 20:53:00
4   Yusef   b000000013  1717    2017-06-06 20:53:00

I want the values in the "Date" column to change color according to the following conditions:

 if date < datetime.now():
        color = 'green'
    elif date > datetime.now():
        date = 'yellow'
    elif date > (datetime.now() + timedelta(days=60)):
        color = 'red'

This is my current code:

def color(val):
    if val < datetime.now():
        color = 'green'
    elif val > datetime.now():
        color = 'yellow'
    elif val > (datetime.now() + timedelta(days=60)):
        color = 'red'
    return 'background-color: %s' % color

df.style.apply(color, subset = ['Fecha'])

I am getting the following error:

ValueError: ('The truth value of a Series is ambiguous. Use a.empty, a.bool(), a.item(), a.any() or a.all().', 'occurred at index Fecha')

The output is:

Out[65]: <pandas.formats.style.Styler at 0x1e3ab8dec50>

Any help will be appreciated.

EDIT: the error is: ValueError: ('The truth value of a Series is ambiguous. Use a.empty, a.bool(), a.item(), a.any() or a.all().', 'occurred at index Date') — Yusef Jacobs
– Yusef Jacobs, Commented May 8, 2017 at 3:00
It should read: "df.style.apply(color, subset = ['Date'])" Missed that translation... — Yusef Jacobs
– Yusef Jacobs, Commented May 8, 2017 at 3:03
One of your Date entries is in the year 2565. The rest are set in 2017. Is this correct? — andrew_reece
– andrew_reece, Commented May 8, 2017 at 3:30

andrew_reece · Accepted Answer · 2017-05-08 03:27:02Z

17

Use applymap:

from datetime import datetime, timedelta
import pandas as pd

name = ['Diego', 'Luis', 'Vidal', 'John', 'Yusef']
id = ['b000000005', 'b000000015', 'b000000002', 'b000000011', 'b000000013']
cel = [7878, 6464, 1100, 4545, 1717]
date = pd.to_datetime(['2017-05-31 20:53:00', '2017-05-11 20:53:00', '2017-05-08 20:53:00', 
                       '2017-06-06 20:53:00', '2017-06-06 20:53:00'])

df = pd.DataFrame({'Name':name,'ID':id,'Cel':cel,'Date':date})

def color(val):
    if val < datetime.now():
        color = 'green'
    elif val > datetime.now():
        color = 'yellow'
    elif val > (datetime.now() + timedelta(days=60)):
        color = 'red'
    return 'background-color: %s' % color

df.style.applymap(color, subset=['Date'])

Screenshot from Jupyter notebook. If you print the output instead, you'll just get a reference to the Styler object:

print(df.style.applymap(color, subset=['Date']))
<pandas.formats.style.Styler object at 0x116db43d0>

answered May 8, 2017 at 3:27

andrew_reece

21.4k3 gold badges40 silver badges64 bronze badges

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

3 Comments

Yusef Jacobs Over a year ago

Thank you! Great answer. It threw me an error while entering the last elif. I changed every condition to "if" and it worked perfectly. Thanks again!

andrew_reece Over a year ago

Glad I could help. Please accept this answer by clicking the check mark if has solved your problem.

Neeraja Bandreddi Over a year ago

yes this code working well in jupyter but in spyder it is not working for me.can you help me.

julia · Accepted Answer · 2018-10-27 13:01:29Z

0

The way it is written, no cells would be painted red. You should change the order, letting the condition to paint red before the one to paint yellow.

answered Oct 27, 2018 at 13:01

julia

1

Collectives™ on Stack Overflow

Format the color of a cell in a pandas dataframe according to multiple conditions

2 Answers 2

3 Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

3 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related