Random Column colors for dataframes grouped by value

I need to add grouped background colors for the header columns based by a value from list of dicts,

The data that contains list of dicts

    data = [
{
    "rank": 2,
    "keyword": "classic ultraschallreiniger",
    "volume": null,
    "asin": "B073WWXTRF",
    "parent_asin": "B073WW53SR"
},
{
    "rank": 14,
    "keyword": "classic ultraschallreiniger",
    "volume": null,
    "asin": "B073WWXTRF",
    "parent_asin": "B073WW53SR"
},
{
    "rank": 10,
    "keyword": "classic ultraschallreiniger",
    "volume": null,
    "asin": "B09K7F2YN6",
    "parent_asin": "B09K7BW5KH"
},
{
    "rank": 22,
    "keyword": "classic ultraschallreiniger",
    "volume": null,
    "asin": "B09K7F2YN6",
    "parent_asin": "B09K7BW5KH"
},
{
    "rank": 1,
    "keyword": "classic ultraschallreiniger",
    "volume": null,
    "asin": "B08LCB95V8",
    "parent_asin": "B08LCD86RD"
},
{
    "rank": 13,
    "keyword": "classic ultraschallreiniger",
    "volume": null,
    "asin": "B08LCB95V8",
    "parent_asin": "B08LCD86RD"
},
{
    "rank": 4,
    "keyword": "flüssigkeit für ultraschallreiniger",
    "volume": 500,
    "asin": "B073WWXTRF",
    "parent_asin": "B073WW53SR"
},
{
    "rank": 20,
    "keyword": "flüssigkeit für ultraschallreiniger",
    "volume": 500,
    "asin": "B073WWXTRF",
    "parent_asin": "B073WW53SR"
},
{
    "rank": 6,
    "keyword": "flüssigkeit für ultraschallreiniger",
    "volume": 500,
    "asin": "B09K7F2YN6",
    "parent_asin": "B09K7BW5KH"
},
{
    "rank": 14,
    "keyword": "flüssigkeit für ultraschallreiniger",
    "volume": 500,
    "asin": "B09K7F2YN6",
    "parent_asin": "B09K7BW5KH"
},
{
    "rank": 5,
    "keyword": "flüssigkeit für ultraschallreiniger",
    "volume": 500,
    "asin": "B094XZ4T96",
    "parent_asin": "B09DD2XZBQ"
},
]

function to add some styles

def center(x):
   px = ["text-align: center" for x in x]
   return px

To pandas dataframe:

lists = ["asin", "keyword", "volume", "rank"]
df_sponsored = pd.DataFrame(data)
df_sponsored = df_sponsored.drop_duplicates(
        subset=["asin", "keyword"], keep="first"
    )
    if df_sponsored:
        df_sponsored[lists] = df_sponsored[lists].fillna(0)
        df_sponsored = df_sponsored.assign(
            rank=df_sponsored["rank"].astype(str)
        ).pivot_table(
            index=["keyword", "volume"],
            columns=["parent_asin", "asin"],
            values="rank",
            aggfunc="min",
            fill_value=0,
            # margins=True,
            # margins_name="Best_ranking",
        )


 writer = pd.ExcelWriter(f"{path}/sheet.xlsx", engine="xlsxwriter")
 df_sponsored.style.apply(center, axis=1).to_excel(
        writer, sheet_name="Sponsored", index=True, startrow=0, header=True
    )
 for column in df_sponsored:
        column_length = 40
        col_idx = df_sponsored.columns.get_loc(column)
        writer.sheets["Sponsored"].set_column(col_idx, 100, column_length)
 writer.save()
  

What I am trying to accomplish is to add random background color to parent_asin and asin group by parent_asin

How to add random background colors based on parent_asin? or lets say, if I update the data so it will contain key called color?

  data = [
    {
       "rank": 6,
       "keyword": "flüssigkeit für ultraschallreiniger",
       "volume": 500,
       "asin": "B09K7F2YN6",
       "parent_asin": "B09K7BW5KH",
       "color": "blue",
    },
    {
       "rank": 14,
       "keyword": "flüssigkeit für ultraschallreiniger",
       "volume": 500,
       "asin": "B09K7F2YN6",
       "parent_asin": "B09K7BW5KH",
       "color": "green"
     },

no color

This is the output I need

Any help is appreciated

Thanks

Answer

import random

df_sponsored.style.apply(center, axis=1).to_excel(
    writer, sheet_name="Sponsored", index=True, startrow=0, header=True
)
for column in df_sponsored:
    column_length = 40
    col_idx = df_sponsored.columns.get_loc(column)
    writer.sheets["Sponsored"].set_column(col_idx, 100, column_length)

Defining the functions for highlight and center the column headers and randomized colors:

def highlight_center(st, colors):
    return [f"background-color: {colors[v]}; text-align: center;" for v in st]


def random_color():
    return ["#"+''.join([random.choice('ABCDEF0123456789') for i in range(6)])][0]


s = df_sponsored.style

creating the color_map dictionary:

color_map = {}

for parent, child in df_sponsored.columns:
    color = color_map.setdefault(parent, random_color())
    color_map[child] = color

applying the colors with lambda to the columns header (axis=1):

s.apply_index(lambda x: highlight_center(x, color_map), axis=1, level=[0, 1]).to_excel(
    writer, sheet_name="Sponsored"
)
writer.save()

output