Pandas set element style dependent on another dataframe mith multi index

Question

I have previously asked the question Pandas set element style dependent on another dataframe, which I have a working solution to, but now I am trying to apply it to a data frame with a multi index and I am getting an error, which I do not understand.

Problem

I have a pandas df and accompanying boolean matrix. I want to highlight the df depending on the boolean matrix.

Data

import pandas as pd
import numpy as np
from datetime import datetime

date = pd.date_range(start = datetime(2016,1,1), end = datetime(2016,2,1), freq = "D")
i = len(date)
dic = {'X':pd.DataFrame(np.random.randn(i, 2),index = date, columns = ['A','B']),
       'Y':pd.DataFrame(np.random.randn(i, 2),index = date, columns = ['A','B']),
       'Z':pd.DataFrame(np.random.randn(i, 2),index = date, columns = ['A','B'])}
df = pd.concat(dic.values(),axis=1,keys=dic.keys())


boo =  [True, False]
bool_matrix = {'X':pd.DataFrame(np.random.choice(boo, (i,2), p=[0.3,.7]), index = date, columns = ['A','B']),
               'Y':pd.DataFrame(np.random.choice(boo, (i,2), p=[0.3,.7]), index = date, columns = ['A','B']),
           'Z':pd.DataFrame(np.random.choice(boo, (i,2), p=[0.3,.7]), index = date, columns = ['A','B'])}

bool_matrix =pd.concat(bool_matrix.values(),axis=1,keys=bool_matrix.keys())

My attempted solution

def highlight(value):
    return 'background-color: green' 
my_style = df.style
for column in df.columns:
    for i in df[column].index:
        data = bool_matrix.loc[i, column]
        if data: 
            my_style = df.style.use(my_style.export()).applymap(highlight, subset = pd.IndexSlice[i, column])

my_style

Results

The above throws an AttributeError: 'Series' object has no attribute 'applymap'

I do not understand what is returning as a Series. This is a single value I am subsetting and this solution worked for non multi-indexed df's as shown below.

Without Multi-index

import pandas as pd
import numpy as np
from datetime import datetime
np.random.seed(24)
date = pd.date_range(start = datetime(2016,1,1), end = datetime(2016,2,1), freq = "D")
df = pd.DataFrame({'A': np.linspace(1, 100, len(date))})
df = pd.concat([df, pd.DataFrame(np.random.randn(len(date), 4), columns=list('BCDE'))],
               axis=1)

df['date'] = date
df.set_index("date", inplace = True)

boo =  [True, False]
bool_matrix = pd.DataFrame(np.random.choice(boo, (len(date), 5),p=[0.3,.7]), index = date,columns=list('ABCDE'))

def highlight(value):
    return 'background-color: green' 
my_style = df.style
for column in df.columns:
    for i in bool_matrix.index:
        data = bool_matrix.loc[i, column]
        if data: 
            my_style = df.style.use(my_style.export()).applymap(highlight, subset = pd.IndexSlice[i,column])
my_style

Documentation

The docs make reference to CSS Classes and say that "Index label cells include level where k is the level in a MultiIndex." I am obviouly indexing this wrong, but am stumped on how to proceed.

gepcel · Accepted Answer · 2018-01-04 05:00:11Z

2

It's very nice that there is a runable example.

You can use df.style.apply(..., axis=None) to apply a highlight method to the whole dataframe.

With your df and bool_matrix, try this:

def highlight(value):
    d = value.copy()
    for c in d.columns:
        for r in df.index:
            if bool_matrix.loc[r, c]:
                d.loc[r, c] = 'background-color: green'
            else:
                d.loc[r, c] = ''
    return d

df.style.apply(highlight, axis=None)

Or to make codes simple, you can try:

def highlight(value):
    return bool_matrix.applymap(lambda x: 'background-color: green' if x else '')

df.style.apply(highlight, axis=None)

Hope this is what you need.

answered Jan 4, 2018 at 5:00

gepcel

1,35612 silver badges22 bronze badges

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

1 Comment

Jeff Tilton Over a year ago

The second solution is great, makes everything much simpler, and is very extensible to all the other highlights I have to do.

Collectives™ on Stack Overflow

Pandas set element style dependent on another dataframe mith multi index

Problem

Data

My attempted solution

Results

Without Multi-index

Documentation

1 Answer 1

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

Problem

Data

My attempted solution

Results

Without Multi-index

Documentation

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related