Ignore specified column while highlighting

Question

Here is my DataFrame...

from termcolor import colored
import pandas as pd

data = {'Col1': [0, 15, 10, 5, 20, 17], 'Col2': [11, 6, 3, 16, 21, 8], 'Col3': [2, 17, 12, 7, 22, 9],

         'Col4': [3, 8, 13, 18, 3, 23], 'Col5': [4, 19, 8, 11, 6, 20]}

df = pd.DataFrame(data)

Now I'm trying to highlight 3 lagest values in each column with red(1st largest), blue(2nd largest) & magenta(3rd largest) color excluding Col2 & Col4.

I tried as ...

columns_to_exclude = ['Col2', 'Col4']

def highlighted(column):

    filtered_df = df.drop(columns=columns_to_exclude)

    values = column.nlargest(3).tolist()

    colors = []

    for x in column:

        if x in values:

            if x == values[0]:

                colors.append(colored(str(x), None, 'on_red'))

            elif x == values[1]:

                colors.append(colored(str(x), None, 'on_blue'))

            elif x == values[2]:

                colors.append(colored(str(x), None, 'on_magenta'))

        else:

            colors.append(colored(str(x), 'white'))

    return colors

for col in df.columns:

    df[col] = highlighted(df[col])

df.columns = [colored(col, None, 'on_black') for col in df.columns]

print(df.to_string(index=False))

But getting all the 5 columns to be highlighted as par my defined coloured. Where I'm wrong ???? Is there any more shorter and simpler way too ??

Answer 1

You can check for the name when use apply:

def hightlight(series, excluded, special_col, special_val):
    # handle special case
    if series.name == special_col:
        special_val = str(special_val)
        return series.astype(str).apply(lambda x: colored(x, None, 'on_yellow') if x == special_val else
                                            colored(x, 'white') )

    # general case
    if series.name in excluded:
        return series.astype(str).apply(lambda x: colored(x, 'white'))
    colors = pd.Series([(None,'on_red'),(None,'on_blue'), (None,'on_magenta')]
                            + [('white',)]*(len(series)-3),
                        index=series.sort_values(ascending=False).index
                    ).reindex(series.index)

    return [colored(str(x), *c) for x,c in zip(series, colors)]

    
print(df.apply(hightlight, excluded=columns_to_exclude, special_col='Col4', special_val=18)
        .rename(columns=lambda x: colored(x, 'white', None))
)

Output

Answer 2

Try:

import pandas as pd
from termcolor import colored

data = {
    "Col1": [0, 15, 10, 5, 20, 17],
    "Col2": [11, 6, 3, 16, 21, 8],
    "Col3": [2, 17, 12, 7, 22, 9],
    "Col4": [3, 8, 13, 18, 3, 23],
    "Col5": [4, 19, 8, 11, 6, 20],
}

df = pd.DataFrame(data)

df.columns = [colored(str(c), "white", None) for c in df]

for c in df:
    if any(x in c for x in ("Col2", "Col4")):
        df[c] = df[c].apply(lambda x: colored(str(x), "white", None))
        continue

    largest = df[c].nlargest(3)
    colormap = dict(zip(map(str, largest), ("on_red", "on_blue", "on_magenta")))
    mask = df[c].isin(largest)

    df[c] = df[c].astype(str)

    df.loc[mask, c] = df.loc[mask, c].apply(
        lambda x: colored(str(x), None, colormap.get(x, "on_black"))
    )
    df.loc[~mask, c] = df.loc[~mask, c].apply(
        lambda x: colored(str(x), None, "on_black")
    )

print(df)

Prints (screenshot):

Answer 3

Using rank/map/difference :

top3 = {1: "red", 2: "blue", 3: "magenta"}

def fn(ser):
    return (
        ser.rank(ascending=False, method="dense").map(
            lambda x: f"background-color: {top3.get(x)}").fillna("")
    )

s = df.style.apply(fn, subset=df.columns.difference(columns_to_exclude))

Output :