CODEWITHSUNDEEP
pythonpandasdataframemulti-index
user18708380
user18708380

Reputation:

How to color second level columns based on condition

Below is the script I am currently working with. I'd like to color the C column only based on the condition below. So in column C, anything positive should be colored green, anything negative with red, and lastly when 0, it would be yellow. I've attached the expected outcome. Any help would be greatly appreciated.

import pandas as pd
df = pd.DataFrame(data=[[100,200,400,500,222,222], [77,28,110,211,222,222], [11,22,33,11,22,33],[213,124,136,147,54,56]])
df.columns = pd.MultiIndex.from_product([['x', 'y', 'z'], list('ab')])

for c in df.columns.levels[0]:
    df[(c, 'c')] = df[(c, 'a')].sub(df[(c, 'b')])
    df = df.sort_index(axis=1)

def colors(i):
    if i < 0:
        return 'background: red'
    elif i > 0:
        return 'background: green'
    elif i == 0:
        return 'background: yellow'
    else:
        ''

idx = pd.IndexSlice
sliced=df.loc[idx[:],idx[:,['c']]]
df.style.apply(colors, subset=sliced)

enter image description here

Upvotes: 0

Views: 254

Answers (1)

Prakash Edara
Prakash Edara

Reputation: 173

Just added a few more lines, you can try something like below

#your code
df = pd.DataFrame(data=[[100,200,400,500,222,222], [77,28,110,211,222,222], [11,22,33,11,22,33],[213,124,136,147,54,56]])
df.columns = pd.MultiIndex.from_product([['x', 'y', 'z'], list('ab')])

for c in df.columns.levels[0]:
    df[(c, 'c')] = df[(c, 'a')].sub(df[(c, 'b')])
    df = df.sort_index(axis=1)

#made some changes inorder to get the background color
def colors(i):
    if i < 0:
        return 'background-color: red'
    elif i > 0:
        return 'background-color: green'
    elif i == 0:
        return 'background-color: yellow'
    else:
        ''

#styling the specific columns
req_cols = [col for col in df.columns if col[1]=='c']
df.style.applymap(lambda x: colors(x), subset=req_cols)

Output:

enter image description here

Hope this code might helps

Upvotes: 0

Related Questions