Adding background color to panda dataframe

Question

I have a pivot table created using Pandas looks like below:

How can I achieve this?

Answer 1

You can create DataFrame of styles with Styler.apply and set rows by index value with loc:

df = df.reset_index()

def color(x):
    c1 = 'background-color: yellow'
    c2 = 'background-color: orange'
    c3 = 'background-color: green'
    c4 = 'background-color: blue'
    c = '' 
    #compare columns
    mask1 = x['Row Lbl'] == 'cashback'
    mask2 = x['Row Lbl'].isin(['GrandTot', 'with cashbak'])
    both = mask1 | mask2
    #DataFrame with same index and columns names as original filled empty strings
    df1 =  pd.DataFrame(c, index=x.index, columns=x.columns)
    #modify values of df1 column by boolean mask
    df1.loc[~both, 'price'] = c1
    df1.loc[~both, 'GrandTot'] = c2
    df1.loc[mask1, :] = c3
    df1.loc[mask2, :] = c4
    return df1

df.style.apply(color, axis=None).to_excel('styled.xlsx', engine='openpyxl', index=False)

Answer 2

If you want to style based on numpy.Array mask to your pandas.DataFrame object, you can use:

import pandas as pd
import numpy as np

# Set the random seed for reproducibility (optional)
np.random.seed(42)

# Create a 3x5 random mask
mask = np.random.choice([True, False], size=(3, 5))
mask = pd.DataFrame(mask)

# Create a 3x5 DataFrame with random values from 1 to 5
data = np.random.randint(1, 6, size=(3, 5))
df = pd.DataFrame(data)

print(df)
print(mask)

def apply_background_color(val, color):
    return f'background-color: {color}' if val else None

# Apply the background color based on the mask
def apply_background_color(val, color):
    return f'background-color: {color}' if val else None

# Apply the background color based on the mask
df_styled = df.style.apply(lambda x: mask.applymap(apply_background_color, color='yellow'), axis=None)
df_styled