31

I am trying to color, highlight, or change fond of Python pandas DataFrame based on the value of the cell. e.g. if the cells on each rows are bigger than the cell in the first column of that row, then highlight the cell as red (or any other color), otherwise leave it as it is.

I wrote a for loop here:

for index in range(0, df.shape[0]):
    for column in range(1, df.shape[1]): # from 1 not from 0 because I only need # to compare the 2nd to the last cell of each row with the 1st cell in the row 

        if df.iloc[index][column] - df_BDE_n_months_avg_std_pct.iloc[index][0] > 0:
            then "PLEASE PUT YOUR HELP HERE, I NEED A PIECE OF CODE THAT CAN HIGHLIGHT THE CELL"
        else:
            "DO NOTHING"

So far I haven't found a way to do it. Any help will be great.

Henry Ecker
  • 34,399
  • 18
  • 41
  • 57
DanZimmerman
  • 1,626
  • 6
  • 23
  • 45

2 Answers2

45

From the style docs:

You can apply conditional formatting, the visual styling of a DataFrame depending on the data within, by using the DataFrame.style property.

import pandas as pd
df = pd.DataFrame([[2,3,1], [3,2,2], [2,4,4]], columns=list("ABC"))

df.style.apply(lambda x: ["background: red" if v > x.iloc[0] else "" for v in x], axis = 1)

enter image description here

Edit: to format specific cells, you can add condition checkers to check the name of element with Series.iteritems() or check the index with enumerate(), e.g. if you want to format starting from column 3, you can use enumerate and check the index:

df = pd.DataFrame([[2,3,-3], [3,2,7], [2,4,4]], columns=list("ABC"))

df.style.apply(lambda x: ["background-color: #ff33aa" 
                          if (i >= 2 and (v > x.iloc[0] + x.iloc[1] 
                                          or v < x.iloc[0] - x.iloc[1])) 
                          else "" for i, v in enumerate(x)], axis = 1)

enter image description here

Psidom
  • 209,562
  • 33
  • 339
  • 356
  • Thanks Psidom! Your solution is great! An add-on question, so what if I wanna set the rule as for each row, from the 3rd cell x, if x > 1st cell + 2nd cell, or x < 1st cell - 2nd cell, then highlight, otherwise do nothing? I think the point here is how do I select the range of cells that I want to compare? Your method automatically excluded the 1st cell itself since it won't be bigger than itself, but it gets a bit more complex when other rules apply. – DanZimmerman Dec 18 '16 at 02:46
  • 1
    You can `enumerate` each row and check the index, only modify the cell if the index is *>=2* in your case. See the update. – Psidom Dec 18 '16 at 03:21
  • Thanks again Psidom. I haven't played much with pandas yet and your suggestions are invaluable. I appreciate your time :) – DanZimmerman Dec 18 '16 at 04:08
  • Hi Psidom, a pretty similar question. Do you happen to know how to conditionally color the above situation in matplotlib bar chart? Let's say I want to plot the dataframe in bar chart, but I only want to color "-3" and "7" in red, the rest will be in white or black. – DanZimmerman Dec 18 '16 at 17:20
  • how can I apply to whole row for example I have a row named total multiple times in my dataframe ,how can I achieve that – Priya Chauhan Mar 21 '21 at 06:42
18
import numpy as np
import pandas as pd
df = pd.DataFrame(np.random.rand(4,3))
df.style.applymap(lambda x: 'background-color : yellow' if x>df.iloc[0,0] else '')

df

Zoe L
  • 1,150
  • 14
  • 22