按值分组的数据帧的随机列颜色

Question

我需要为标题列添加分组背景色，根据一个来自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"
},
]

函数添加一些样式。

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

给熊猫的资料：

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()

我想要做的是通过parent_asin给parent_asin和asin组添加随机背景色。

如何添加基于parent_asin的随机背景色？或者让我说，如果我更新数据，它将包含名为颜色的键？

  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"
     },

无色

​

​

这是我需要的输出

​

​

任何帮助都是非常感谢的。

谢谢

Answer 1

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)

定义用于突出显示和居中列标题和随机颜色的功能：

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

创建color_map字典：

color_map = {}

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

将lambda颜色应用于列标题(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()

输出

​

​