[FIXED] Pandas conditional formatting based on comparison from different columns

Issue

I have a large dataframe that comes from calculation with varying number of columns and rows:

+------+------+------+-----+-------+----+----+----+----+
| col1 | col2 | col3 | ... | col50 | A1 | B1 | C1 | D1 |
+------+------+------+-----+-------+----+----+----+----+
| 2    | 1    | 7    |     | 0     |  2 |  1 |  0 |  1 |
| 3    | 2    | 5    |     | 1     |  1 |  0 |  1 |  5 |
| 2    | 1    | 3    |     | 5     |  1 |  2 |  3 |  7 |
| 1    | 3    | 2    | ... | 4     |  3 |  1 |  1 |  1 |
| ...  | ...  | ...  | ... | ....  |    |    |    |    |
| A1   | A1   | B1   | ... | C1    |    |    |    |    |
+------+------+------+-----+-------+----+----+----+----+

I need to conditionally format each column from col1 to col50 based on the content of the last row. For example for col1 the last row is A1. Therefore this col should be compared with column A1 and coloured, ie: if col1>A1 highlight the content of col1 with green elseif: col1<A1 highlight with red else highlight with yellow.

I tried manually with:

highlighted_rows = np.where(data['col1']==data['A1'],
                            'background-color: yellow',
                            'background-color: red')

# Apply calculated styles to each column:
styler = data.style.apply(lambda _: highlighted_rows)

styler

What I dont know is to apply three such conditions for each column from col1 to col50 where the reference column is as mentioned at the bottom of every column.

Solution

You can use:

# get last row and keep non NA
s = df.iloc[-1].dropna()

# get reference columns
ref = df[s]

# get sign of difference
m = np.sign(df[s.index]
            .where(ref.notna().values)
            .astype(float)
            .sub(ref.values, axis=0)
           )

# define colors from sign of difference
colors = {-1: 'red', 0: 'yellow', 1: 'green'}

# reindex (optional) and compute style
style = (m.reindex_like(df)
         .applymap(lambda x: f'background-color: {colors.get(x)}'
                   if x in colors else None)
         )

# apply style
df.style.apply(lambda x: style, axis=None)

Output:

Answered By - mozway