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
– anky, Commented May 8, 2021 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
– Henry Ecker ♦, Commented May 8, 2021 at 19:23

anky · Accepted Answer · 2021-05-08 19:09:16Z

1

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(.....)

answered May 8, 2021 at 19:09

anky

75.3k11 gold badges46 silver badges76 bronze badges

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

Comments

GeorgesAA · Accepted Answer · 2021-05-08 18:55:10Z

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 8, 2021 at 18:55

GeorgesAA

1535 bronze badges

2 Comments

Broderick Bonelli Over a year ago

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

GeorgesAA Over a year ago

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.

Collectives™ on Stack Overflow

Style rows of a dataframe based on column value

2 Answers 2

Comments

2 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related