How to apply conditional color styling to a column in a Multi-index pandas dataframe

Question

I already had a question answered for conditionally applying color with a normal Dataframe. Link

The question I have is how do I do that with a multi-index data-frame? The dataframe in the previous question was

Value       Limit    Actual
Issues      < 33     0
Rating      > 4      4.2
Complaints  < 15     18
Time        30 - 45  41
Tip         --       -

The new dataframe is the same dataframe but with header like below

df.columns =pd.MultiIndex.from_product([['summary'], df.columns])
df

So the new dataframe is like so

Summary
Value       Limit    Actual
Issues      < 33     0
Rating      > 4      4.2
Complaints  < 15     18
Time        30 - 45  41
Tip         --       -

The out put expected is like the previous question itself but with a additional header row at the top.

I tried replacing row['Limit'] with x.loc[:,idx[:,'Limit']] where idx is pd.IndexSlice but it did not work

import re

def highlight(row):
    numbers = re.findall('[0-9]+', row['Limit'])
    if row['Value'] in ('Issues','Rating','Complaints'):
        if '>' in row['Limit'] and row['Actual'] > numbers[0]:
            color = 'green'
        elif row['Actual'] < numbers[0]:
            color = 'green'
        else:
            color = 'red'
    else:
        if len(numbers) == 0:
            color = 'yellow'
        elif row['Actual'] > numbers[0] and row['Actual'] < numbers[1]:
            color = 'green'
        else:
            color = 'red'
    return f"background-color: {color}"

jezrael · Accepted Answer · 2022-06-03 11:28:05Z

1

You need change:

row['Limit']

to:

row[('summary', 'Limit')]

for select by MultiIndex in columns.

EDIT:

If use:

row[(slice(None),'Limit')]
row[idx[:,'Actual']]

output is one element Series.

So need select first value in this methods for scalar:

row[(slice(None),'Limit')].iat[0]
row[idx[:,'Actual']].iat[0]

edited Jun 3, 2022 at 11:28

answered Jun 3, 2022 at 11:06

jezrael

868k103 gold badges1.4k silver badges1.3k bronze badges

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

4 Comments

moys Over a year ago

The text Summary is dynamic & can change based on a variable. Is there another way (by getting the column name of the first row of the header)?

jezrael Over a year ago

@moys - I think simpliest is use row[('summary', 'Limit')] , is not possible create summary form variable?

moys Over a year ago

I will do that itself. It is easier to read also. One last follow up. How do I change the background color of "Summary"? Only the header, not any columns. I know this is different question but thought the answer may be short as well. If I put something like df.style.set_properties(subset=[('Summary')],**{'background-color': 'red'}), the whole df color is changing, I just want the header to change. I will put a new question if that is better.

jezrael Over a year ago

@moys - I found this - part [11], [12]

Collectives™ on Stack Overflow

How to apply conditional color styling to a column in a Multi-index pandas dataframe

1 Answer 1

4 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

4 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related