0

I have some data which contains columns for the actual value and then the original target value

   dimension | metric_actual | metric_target | metric2_actual | metric2_target
        A          16.41            20.00             68.54%            72.00%
        B          17.39            18.00             63.87%            60.00%

Ideally, I want the cell to have red text if the metric_actual is less than the target, and to have green text if it is greater than the target.

I would like to output a dataframe into an email using the .style options. I can't seem to figure out how to do this on a row by row basis though if the target is potentially different for each row.

http://pandas.pydata.org/pandas-docs/stable/style.html

These functions work but only if I have one static column. My issue is that the targets are different for the same metric for each row in the data.

def color_negative_red(value, target):
    color = 'red' if value < target else 'green'
    return 'color: %s' % color

Edit the issue is that I get 'truth is ambiguous errors':

 df.style.applymap(color_negative_red, target=df['metric_target'], subset=['metric_actual']

I feel like this should be possible but I need to compare each value per row to the corresponding target value in that same row.

Henry Ecker
  • 34,399
  • 18
  • 41
  • 57
trench
  • 5,075
  • 12
  • 50
  • 80

1 Answers1

2

In the reference page you provided it says that Styler.applymap is elementwise and Styler.apply is column-/row-/table-wise. I used the column-wise version with respectively modified color_negative_red function.

import pandas as pd

def color_negative_red(value, target):
    is_smaller = value < target  # a pandas.Series of dtype bool
    colors = is_smaller.apply(lambda b: 'color: red' if b else 'color: green')  # a pandas.Series of dtype object (i.e. str)
    return colors

df = pd.DataFrame(data={'metric_actual': [16.41, 17.39, 28.23],
                        'metric_target': [20.00, 18.00, 19.00]})

df.style.apply(color_negative_red, target=df['metric_target'], subset=['metric_actual'], )

Gave me this result:

enter image description here

Uğur Güney
  • 184
  • 8
  • Great. I tried .apply with the original function (which I got from the documentation) but I get the 'truth is ambiguous error'. It appears that your modification for that function fixed the issue though – trench Oct 21 '16 at 13:09