Pandas添加HYPERLINK时空列自动填充0的问题排查

为DataFrame的「a」列添加HYPERLINK超链接，当「a」列值为空时返回空字符串（''），但使用to_excel导出至Excel时，空值却被自动填充为0，导致Excel表格异常。已为「a」列设置文本格式，「link」是DataFrame中的一列。

用户代码如下：

def hyperlink(row):
  return f'=HYPERLINK("{row.link}", "{row.a}")' if row.a else ''

df["a"] = df.apply(hyperlink, axis=1)

df.drop(columns=["link"], inplace=True)

with pd.ExcelWriter(engine="xlsxwriter") as writer:
   df.to_excel(writer)

   wb = writer.book
   ws = wb.sheets["sheet1"]

   text_format = wb.add_format({
            "font_name": "Calibri",
            "font_size": "11",
            "bold": False,
            "valign": "vcenter",
            "font_color": "blue",
    })
   text_format.set_num_format("@")
   ws.set_column("A:A", 24, text_format)

• 核心原因：Pandas会自动推断列的数据类型，当「a」列同时包含公式字符串和空字符串时，会被推断为数值型，空字符串因此被转换为0。后续设置的文本格式仅作用于单元格显示，无法修正底层数据类型转换导致的0值问题。

• 解决步骤：

  1. 用缺失值替代空字符串：修改hyperlink函数，空值场景返回pd.NA（Pandas的标准缺失值标记），避免被识别为数值类型：

     def hyperlink(row):
         return f'=HYPERLINK("{row.link}", "{row.a}")' if row.a else pd.NA
     

  2. 导出时指定缺失值显示为空白：在to_excel中添加na_rep=''参数，确保缺失值导出为空白单元格：

     df.to_excel(writer, na_rep='')
     

  3. 强制列类型为字符串（可选）：如果仍存在类型推断问题，显式将「a」列转为字符串类型，同时处理pd.NA转字符串后的'nan'值：

     df["a"] = df.apply(hyperlink, axis=1).astype(str).replace('nan', '')
     

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