Conditionally color pandas column

Question

My dataframe looks like this:

Week    Item    Buyer   11  12  13  
0   clothes buyerID1    2   3   4   
1   food    buyerID2    2   1   1   
2   water   buyerID     7   5   1

11, 12, 13 are the weeks. I am trying to conditionally color the rows of the last column to depict a change in items purchased week over week. I am using an adaptation of another answer here to do so, but I am running into an IndexingError: Too many indexers error.

Below is my modified code:

def highlight3(x):
#if increase
c1 = 'background-color: green'

#if decrease
c2 = 'background-color: red'

c3 = ''

#last row greater than value in second to last row
m1 = x.iloc[:, -1] > x.iloc[:, -2]

#last row lesser than value in second to last row
m2 = x.iloc[:, -1] < x.iloc[:, -2]

out = np.select([m1, m2], [c1, c2], default=c3)
return pd.DataFrame(out, index=x.index, columns=x.columns)

And then I apply it to my df using: df.apply(highlight3, axis=None)

Roy2012 · Accepted Answer · 2020-06-07T18:04:57.993

1

Here's a solution:

data = """Week    Item    Buyer   11  12  13  
0   clothes buyerID1    2   3   4   
1   food    buyerID2    2   1   1   
2   water   buyerID     7   5   1   """
df = pd.read_csv(StringIO(data), sep="\s+")

green = 'background-color: green'
red = 'background-color: red'

def style_last_week(x):
    s = pd.Series([""] * x.size)
    if x[-1] > x[-2]:
        s[x.size -1 ] = green

    if x[-2] > x[-1]:
        s[x.size -1 ] = red

    return s

df.style.apply(style_last_week, axis=1)

The result is:

edited Jun 07 '20 at 18:04

answered Jun 07 '20 at 16:04

Roy2012

11,755
2
22
35

Does this answer your question? – Roy2012 Jun 07 '20 at 17:22
This gives me a `KeyError: 1`. Not sure why - I am copy-pasting your code – Mega_Noob Jun 07 '20 at 17:45
I changed x[-1] to x.iloc[-1] and it works but it loses the default spacing and coloring of alternate rows that a df has - any idea how I can keep that? @Roy2012 – Mega_Noob Jun 07 '20 at 17:48
I added the code that creates the dataframe. Could you please give it a try? – Roy2012 Jun 07 '20 at 18:05
https://imgur.com/uRQU6ye this is the image. I am using Google Colab - does that make a difference? @Roy2012 – Mega_Noob Jun 07 '20 at 18:16
seems identical to what I'm seeing. Where's the difference? – Roy2012 Jun 07 '20 at 18:18
Compared to the picture in your answer, the columns are more bunched up, making it harder to read longer item names, and also making it harder to demarcate between numbers of different columns. The alternative light grey spacing is also gone. Is there any way to get that back? @Roy2012 – Mega_Noob Jun 07 '20 at 18:23
what does it look like in Google colab when you're not using my code? – Roy2012 Jun 07 '20 at 18:25
https://imgur.com/GS0iKdn this is the image. More spacing and alternative grey coloring @Roy2012 – Mega_Noob Jun 07 '20 at 18:26
I played a bit with formatting in Google collab. As you noticed, there's something funny about it. Not sure what's the issue. I'll continue looking into it later or tomorrow. – Roy2012 Jun 07 '20 at 19:02
It would be great if you could circle back! accepted the answer for now – Mega_Noob Jun 07 '20 at 19:03
Spent some time looking into it. Can't figure it out - seems like a bug. Posted a question about it: https://stackoverflow.com/questions/62255818/pandas-style-doesnt-work-with-google-colab – Roy2012 Jun 08 '20 at 05:53

Conditionally color pandas column

1 Answers1