Pandas结合XlsxWriter按parent_asin分组设置Excel列背景色

实现逻辑 #

• 完全保留现有数据预处理、透视表生成、居中对齐、列宽设置的逻辑，仅插入颜色映射、批量列上色两段代码即可
• 颜色读取优先级：原始数据自带的color字段 > 自动生成的高区分度浅色系
• 自动识别每个parent_asin对应的连续列范围，覆盖两级表头+所有数据行，同组列背景色完全统一

完整可运行代码 #

import pandas as pd
import random
import colorsys

# ---------------------- 原有预处理逻辑 无需修改 ----------------------
# raw_data替换为你的原始字典列表
df = pd.DataFrame(raw_data)
# 按asin+keyword去重
df = df.drop_duplicates(subset=['asin', 'keyword'])
# 空值填充
df = df.fillna('')
# rank字段转数值类型
df['rank'] = pd.to_numeric(df['rank'], errors='coerce')
# 生成多级列透视表
pivot_df = pd.pivot_table(
    df,
    index=['keyword', 'volume'],
    columns=['parent_asin', 'asin'],
    values='rank',
    aggfunc='min'
)

# ---------------------- 新增：分组颜色映射 ----------------------
color_map = {}
# 优先读取数据中自带的color字段
if 'color' in df.columns:
    for pa, sub_df in df.groupby('parent_asin'):
        valid_color = sub_df['color'].dropna().iloc[0] if not sub_df['color'].dropna().empty else None
        if valid_color:
            color_map[pa] = valid_color

# 为无自定义颜色的分组生成高区分度浅底色
pa_list = pivot_df.columns.get_level_values(0).unique()
# 均匀分布色相，打乱顺序避免相邻分组颜色过近
hue_list = [i/len(pa_list) for i in range(len(pa_list))]
random.shuffle(hue_list)
for idx, pa in enumerate(pa_list):
    if pa not in color_map:
        # hsl转十六进制色：亮度0.9、饱和度0.3，输出柔和浅底不挡黑字
        r, g, b = colorsys.hls_to_rgb(hue_list[idx], 0.9, 0.3)
        color_map[pa] = f'#{int(r*255):02x}{int(g*255):02x}{int(b*255):02x}'

# ---------------------- 原有Excel导出逻辑 新增格式适配 ----------------------
writer = pd.ExcelWriter('asin_rank_output.xlsx', engine='xlsxwriter')
pivot_df.to_excel(writer, sheet_name='排名数据')
workbook = writer.book
worksheet = writer.sheets['排名数据']

# 复用原有居中配置，生成不同分组的带底色格式
base_fmt_config = {'align': 'center', 'valign': 'vcenter'}
index_fmt = workbook.add_format(base_fmt_config) # 行索引列无底色
group_fmt_map = {pa: workbook.add_format({**base_fmt_config, 'bg_color': c}) for pa, c in color_map.items()}

# 原有列宽设置 可按需调整数值
worksheet.set_column(0, 1, 20, index_fmt) # 前两列为行索引（keyword、volume）

# ---------------------- 新增：按分组批量设置背景色 ----------------------
data_col_start = 2 # 前2列被行索引占用，数据列从第3列（索引2）开始
current_pa = None
current_group_col_start = data_col_start
total_data_col = len(pivot_df.columns)

# 遍历所有数据列，按连续的parent_asin分组上色
for col_idx in range(data_col_start, data_col_start + total_data_col):
    pa = pivot_df.columns[col_idx - data_col_start][0]
    if current_pa is None:
        current_pa = pa
    # 遇到新分组时，完成上一个分组的格式设置
    if pa != current_pa:
        col_end = col_idx - 1
        # 设置整列数据格式
        worksheet.set_column(current_group_col_start, col_end, 15, group_fmt_map[current_pa])
        # 单独设置两级表头格式（set_column不覆盖已写入的表头内容，用write_blank仅刷格式不删文字）
        for header_row in [0, 1]:
            for c in range(current_group_col_start, col_end + 1):
                worksheet.write_blank(header_row, c, None, group_fmt_map[current_pa])
        # 重置分组标记
        current_pa = pa
        current_group_col_start = col_idx

# 处理最后一个分组
col_end = data_col_start + total_data_col - 1
worksheet.set_column(current_group_col_start, col_end, 15, group_fmt_map[current_pa])
for header_row in [0, 1]:
    for c in range(current_group_col_start, col_end + 1):
        worksheet.write_blank(header_row, c, None, group_fmt_map[current_pa])

writer.close()

可调参数说明 #

• 自动生成底色深浅：修改colorsys.hls_to_rgb的第二个参数（取值0-1，数值越大颜色越浅，建议保持0.85-0.95区间），第三个参数为饱和度（数值越大颜色越鲜艳）
• 列宽：修改set_column的第三个参数即可，行索引列默认宽度20，数据列默认宽度15
• 表头层级适配：如果后续调整为3级以上列索引，把表头遍历的for header_row in [0, 1]修改为对应层级的行号列表即可（比如3级表头用[0,1,2]）
• 自定义色值兼容：如果color字段存储的是RGB元组，提前转换为#RRGGBB格式的十六进制字符串即可被XlsxWriter直接识别

内容的提问来源于stack exchange，提问作者ira