Pandas导出Excel时将原始超链接转为可点击格式及分组聚合问题

问题场景 #

现有如下Pandas DataFrame：

import pandas as pd

data = {'product_id': [11,11,11,11],
        'rec_prod_id':[12,13,14,15],
        'product_URL': ['www.example.com','www.example4.com','www.example3.com','www.example2.com']}
data = pd.DataFrame(data)

需求：

1. 按product_id分组，统计推荐产品的去重数量，并将每个推荐产品的超链接聚合成列表；
2. 导出Excel时，每个rec_prod_id对应可点击的超链接，跳转至对应product_URL。

之前尝试的代码导出后仅显示原始URL格式，无法实现可点击效果：

def create_hyperlink(row):
    return '<a href="{0}">{1}</a>'.format(row['product_id'], row['product_URL'])
data['product_hyperlinks'] = data.apply(create_hyperlink, axis=1)
data.groupby('product_id').agg{('# of recs'=product_id:nunique,product_links:list)}

解决方案 #

1. 修正超链接生成逻辑 #

Excel识别的可点击超链接需使用内置HYPERLINK函数格式，而非HTML标签。同时要对应正确的URL和显示文本（这里显示rec_prod_id，跳转至product_URL）：

def create_excel_hyperlink(row):
    # Excel HYPERLINK函数格式：=HYPERLINK("URL", "显示文本")
    return f'=HYPERLINK("{row["product_URL"]}", "{row["rec_prod_id"]}")'

data['hyperlinks'] = data.apply(create_excel_hyperlink, axis=1)

2. 正确执行分组聚合 #

修正agg的语法错误，指定正确的聚合列和操作：

grouped_data = data.groupby('product_id').agg(
    # 统计推荐产品的去重数量
    num_of_recs=('rec_prod_id', 'nunique'),
    # 聚合超链接为列表
    product_links=('hyperlinks', list)
).reset_index()

3. 导出至Excel #

使用openpyxl引擎确保Excel能识别公式：

# 导出时需指定engine='openpyxl'，支持写入Excel公式
grouped_data.to_excel('product_recs.xlsx', engine='openpyxl', index=False)

关键说明 #

• 错误原因：之前使用HTML的<a>标签无法被Excel解析，必须用Excel原生的HYPERLINK函数；
• 聚合逻辑：统计推荐产品数量应针对rec_prod_id，而非product_id（原数据中product_id无重复）；
• 导出引擎：openpyxl是支持写入Excel公式的必要引擎，默认引擎可能无法正确解析公式。

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