0

On a Pandas dataframe I would like to apply a style on a specific column, on a multi-criteria taken from different columns values.

table =
product    sales   revenues
fruit      10      6
eggs       15      12
fruit      16      8

If the product is fruit I would like the sales value only (no modification on revenues) to come in red when it is below 17, if the product is eggs another criteria applies (= below 20)

So only the figure 10 should appear in red.

I tried to style.apply and style.applymap but cannot figure out a style function working with the different columns independently.

Thanks in advance for elements you could provide to help.

Henry Ecker
  • 34,399
  • 18
  • 41
  • 57
GK31
  • 11
  • 2

1 Answers1

0

The easiest way to do this would be to create a boolean index before-hand from the DataFrame then apply it to the specific subset:

c1 = df['product'].eq('fruit') & df['sales'].lt(17)
c2 = df['product'].eq('eggs') & df['sales'].lt(20)
df.style.apply(lambda _: np.where(c1 | c2, 'background-color: red', ''),
               subset=['sales'])

output

Different DataFrame was used to show conditions:

df = pd.DataFrame({'product': ['eggs', 'fruit', 'fruit', 'eggs'],
                   'sales': [ 15, 17, 16, 20],
                   'revenues': [12, 8, 12, 19]})

c1 is the rows where fruit is less than 17:

0    False
1    False
2     True
3    False
dtype: bool

c2 indicates the rows where eggs and less than 20:

0     True
1    False
2    False
3    False
dtype: bool

np.where to get the colors for the sales column based on where either c1 or c2 is True:

np.where(c1 | c2, 'background-color: red', '')

['background-color: red' '' 'background-color: red' '']
Henry Ecker
  • 34,399
  • 18
  • 41
  • 57
  • 1
    Hello Henry, this answers perfectly my need. thanks you so much. Being new on that forum, how can I validate your solution ? – GK31 Jul 03 '21 at 17:36
  • Hello again, to apply a different style on column 'revenue' I'd like to apply a complementary style on subset ['revenues'] but it erases the firts style. Any suggestion ? Thanks a lot ! – GK31 Jul 04 '21 at 07:20
  • Styles should be chained together `df.style.apply(..., subset...).apply(..., subset...)`. – Henry Ecker Jul 04 '21 at 14:10
  • It works great, I am impressed by the power of this tool. A last one, any chance to have the style.apply working with multi-index issue from a pivot dataframe ? (not simple -for me - to adress the columns especially when some of them have same sub-index) – GK31 Jul 05 '21 at 11:58
  • It depends on a number of conditions. Without seeing the actual dataframe it would be hard to give a specific answer. Also within the context of StackOverflow, that is likely a different question. I'd suggest you open a _new_ question with the specific multi-index dataframe and conditions for styling. – Henry Ecker Jul 05 '21 at 18:17