如何将Pandas DataFrame中JSON数组列转换为HTML链接格式？

现有一个包含metadata.reference列的Pandas DataFrame，该列存储JSON数组数据，每个数组元素包含@source、@ref_id、@ref_url字段。需要去除该列的JSON数组结构，将每个数组元素转换为HTML <a> 链接（链接地址为@ref_url，显示文本为@ref_id），并以分号分隔多个链接，生成符合要求的新DataFrame。

原始代码 #

import pandas as pd

df = pd.DataFrame([
    {
      "serverid": "admin@admin.com",
      "ipaddress": "10.10.10.10",
      "metadata.reference": [
        {
          "@source": "elsa",
          "@ref_id": "ELSA-2022-9781",
          "@ref_url": "https://linux.oracle.com/errata/ELSA-2022-9781.html"
        },
        {
          "@source": "CVE",
          "@ref_id": "CVE-2020-0466",
          "@ref_url": "https://linux.oracle.com/cve/CVE-2020-0466.html"
        }
      ]
    },
    {
        "serverid": "admin2@admin.com",
        "ipaddress": "10.10.10.10",
        "metadata.reference": [
          {
            "@source": "elsa",
            "@ref_id": "ELSA-2022-9781",
            "@ref_url": "https://linux.oracle.com/errata/ELSA-2022-9781.html"
          },
          {
            "@source": "CVE",
            "@ref_id": "CVE-2020-0466",
            "@ref_url": "https://linux.oracle.com/cve/CVE-2020-0466.html"
          }
        ]
      }
])

当前输出 #

serverid          ipaddress     metadata.reference
0   admin@admin.com   10.10.10.10   [{'@source': 'elsa', '@ref_id': 'ELSA-2022-978...
1   admin2@admin.com  10.10.10.10   [{'@source': 'elsa', '@ref_id': 'ELSA-2022-978...

期望输出 #

serverid          ipaddress     metadata.reference
0   admin@admin.com   10.10.10.10   <a href="https://linux.oracle.com/errata/ELSA-2022-9781.html">ELSA-2022-9781</a>;<a href="https://linux.oracle.com/cve/CVE-2020-0466.html">CVE-2020-0466</a>;
1   admin2@admin.com  10.10.10.10   <a href="https://linux.oracle.com/errata/ELSA-2022-9781.html">ELSA-2022-9781</a>;<a href="https://linux.oracle.com/cve/CVE-2020-0466.html">CVE-2020-0466</a>;

通过Pandas的apply方法结合自定义函数，实现批量转换：

完整代码 #

import pandas as pd

df = pd.DataFrame([
    {
      "serverid": "admin@admin.com",
      "ipaddress": "10.10.10.10",
      "metadata.reference": [
        {
          "@source": "elsa",
          "@ref_id": "ELSA-2022-9781",
          "@ref_url": "https://linux.oracle.com/errata/ELSA-2022-9781.html"
        },
        {
          "@source": "CVE",
          "@ref_id": "CVE-2020-0466",
          "@ref_url": "https://linux.oracle.com/cve/CVE-2020-0466.html"
        }
      ]
    },
    {
        "serverid": "admin2@admin.com",
        "ipaddress": "10.10.10.10",
        "metadata.reference": [
          {
            "@source": "elsa",
            "@ref_id": "ELSA-2022-9781",
            "@ref_url": "https://linux.oracle.com/errata/ELSA-2022-9781.html"
          },
          {
            "@source": "CVE",
            "@ref_id": "CVE-2020-0466",
            "@ref_url": "https://linux.oracle.com/cve/CVE-2020-0466.html"
          }
        ]
      }
])

def convert_to_links(ref_list):
    links = []
    for item in ref_list:
        links.append(f'<a href="{item["@ref_url"]}">{item["@ref_id"]}</a>')
    return ';'.join(links) + ';'

df['metadata.reference'] = df['metadata.reference'].apply(convert_to_links)

print(df)

实现说明 #

• 自定义convert_to_links函数：遍历每个JSON数组元素，生成对应的HTML <a>标签字符串；将所有标签用分号拼接，末尾额外添加分号以匹配期望输出格式。
• 使用apply方法将函数作用于metadata.reference整列，完成批量转换。

内容的提问来源于stack exchange，提问作者Bandhala Raja Selvam