python color entire pandas dataframe rows based on column values

Question

I have a script that downloads a .csv and does some manipulation and then emails panda dataframes in a nice html format by using df.to_html.

I would like to enhance these tables by highlighting, or coloring, different rows based on their text value in a specific column.

I tried using pandas styler which appears to work however I can not convert that to html using to_html. I get a "AttributeError: 'str' object has no attribute 'to_html"

Is there a another way to do this?

As an example lets say my DF looks like the following and I want to highlight all rows for each manufacturer. i.e Use three different colors for Ford, Chevy, and Dodge:

Year      Color       Manufacturer
2011      Red         Ford
2010      Yellow      Ford
2000      Blue        Chevy
1983      Orange      Dodge

I noticed I can pass formatters into to_html but it appears that it cannot do what I am trying to accomplish by coloring? I would like to be able to do something like:

def colorred():
    return ['background-color: red']

def color_row(value):
    if value is "Ford":
        result = colorred()
        return result

df1.to_html("test.html", escape=False, formatters={"Manufacturer": color_row})

Micmizer · Accepted Answer · 2017-10-21 00:08:07Z

1

Surprised this has never been answered as looking back at it I do not believe this is even possible with to_html formatters. After revisiting this several times I have found a very nice solution I am happy with. I have not seen anything close to this online so I hope this helps someone else.

d = {'Year' : [2011, 2010, 2000, 1983],
    'Color' : ['Red', 'Yellow', 'Blue', 'Orange'],
    'Manufacturer' : ['Ford', 'Ford', 'Chevy', 'Dodge']}
df =pd.DataFrame(d)
print (df)

def color_rows(s):
    df = s.copy()

    #Key:Value dictionary of Column Name:Color
    color_map = {}

    #Unqiue Column values
    manufacturers = df['Manufacturer'].unique()
    colors_to_use = ['background-color: #ABB2B9', 'background-color: #EDBB99', 'background-color: #ABEBC6', 
              'background-color: #AED6F1']

    #Loop over our column values and associate one color to each
    for manufacturer in manufacturers:
        color_map[manufacturer] = colors_to_use[0]
        colors_to_use.pop(0)

    for index, row in df.iterrows():
        if row['Manufacturer'] in manufacturers:
            manufacturer = row['Manufacturer']
            #Get the color to use based on this rows Manufacturers value
            my_color = color_map[manufacturer]
            #Update the row using loc
            df.loc[index,:] = my_color
        else:
            df.loc[index,:] = 'background-color: '        
    return df

df.style.apply(color_rows, axis=None)

Output:

Pandas row coloring

Since I do not have the cred to embed images here is how I email it. I convert it to html with the following.

styled = df.style.apply(color_rows, axis=None).set_table_styles(
                        [{'selector': '.row_heading',
                          'props': [('display', 'none')]},
                         {'selector': '.blank.level0',
                          'props': [('display', 'none')]}])
html = (styled.render())

edited Oct 21, 2017 at 0:08

answered Oct 20, 2017 at 23:52

Micmizer

331 silver badge10 bronze badges

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

2 Comments

Micmizer Over a year ago

Apparently I do not have the cred to embed images so you'll have to follow the link to see the Jupyter rendered image

yishairasowsky Over a year ago

how do i color columns?

Collectives™ on Stack Overflow

python color entire pandas dataframe rows based on column values

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