Style rows of a dataframe based on column value

Question

I have a csv file that I'm trying to read into a dataframe and style in jupyter notebook. The csv file data is:

[[' ', 'Name', 'Title', 'Date', 'Transaction', 'Price', 'Shares', '$ Value'],
[0, 'Sneed Michael E', 'EVP, Global Corp Aff & COO', 'Dec 09', 'Sale', 152.93, 54662, 8359460],
[1, 'Wengel Kathryn E', 'EVP, Chief GSC Officer', 'Sep 02', 'Sale', 153.52, 16115, 2473938],
[2, 'McEvoy Ashley', 'EVP, WW Chair, Medical Devices', 'Jul 28', 'Sale', 147.47, 29000, 4276630],
[3, 'JOHNSON & JOHNSON', '10% Owner', 'Jun 30', 'Buy', 17.00, 725000, 12325000]]

My goal is to style the background color of the rows so that the row is colored green if the Transaction column value is 'Buy', and red if the Transaction column value is 'Sale'.

The code I've tried is:

import pandas as pd

data = pd.read_csv('/Users/broderickbonelli/Desktop/insider.csv', index_col='Unnamed: 0')  

def red_or_green():
    if data.Transaction == 'Sale':
        return ['background-color: red']
    else:
        return ['background-color: green']

data.style.apply(red_or_green, axis=1)

display(data)

When I run the code it outputs an unstyled spreadsheet without giving me an error code:

Dataframe

I'm not really sure what I'm doing wrong, I've tried it a # of different ways but can't seem to make it work. Any help would be appreciated!

@HenryEcker I think the proposed dupe is different than the one we have here, the OP already knew about axis=1 here. — anky, May 08 '21 at 19:18
Okay. The accepted answer to the duped question works if they apply it to this function by adding a row parameter and returning a series with row.index instead of a list in the `red_or_green`. — Henry Ecker, May 08 '21 at 19:23

score 1 · Accepted Answer · answered May 08 '21 at 19:09

If you want to compare the entire row when the condition matches, the following is faster than apply on axis=1, where we use style on the entire dataframe:

def red_or_green(dataframe):
    c = dataframe['Transaction'] == 'Sale'
    a = np.where(np.repeat(c.to_numpy()[:,None],dataframe.shape[1],axis=1),
                 'background-color: red','background-color: green')
    return pd.DataFrame(a,columns=dataframe.columns,index=dataframe.index)

df.style.apply(red_or_green, axis=None)#.to_excel(.....)

score 0 · Answer 2 · answered May 08 '21 at 18:55

0

Try

data['background-color'] = data.apply(lambda x: red_or_green(x.Transaction), axis=1)


def red_or_green(transaction):
    if transaction == 'Sale':
        return 'red'
    else:
        return 'green'

or you can use map:

data['background-color'] = data.Transaction.map(red_or_green)

answered May 08 '21 at 18:55

GeorgesAA

153
5

Thanks, when I tried both of these it added a column to the df titled 'background-color' and each row's value was 'red' or 'green', however the background colors of the rows themselves remained unstyled – Broderick Bonelli May 08 '21 at 19:04
Sorry, misread your question. Not sure about styling dataframes. Try returning ['background-color: yellow'] instead and see if that works. Hopefully someone else can chime in. – GeorgesAA May 08 '21 at 19:16

Style rows of a dataframe based on column value

2 Answers2