Pandas conditional formatting based on comparison from different columns

Question

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.

mozway · Accepted Answer · 2022-08-22 02:18:47Z

2

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 Aug 22, 2022 at 2:18

mozway

267k13 gold badges56 silver badges106 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Pandas conditional formatting based on comparison from different columns

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related