如何从Pandas DataFrame的Series类型列中提取code字段值

问题背景 #

你的DataFrame中col1列的内容是字节类型的JSON数组字符串，原始数据如下：

id   col1
1    b'[{"code":"P16_HCAQNB","onto":"finngen","syns":["hydrocephalus acquired newborn","of newborn 
    acquired hydrocephalus","hydrocephalus, acquired, newborn","newborn acquired 
    hydrocephalus","hydrocephalus, acquired, of newborn","hydrocephalus acquired of 
    newborn","p16_hcaqnb"],"term":"Hydrocephalus, acquired, of newborn"}]'
2    b'[{"code":"OVERPROD_THYROID__HORMONE","onto":"finngen","syns":["drug induced overproduction 
   of thyroid stimulating hormone","overproduction thyroid stimulating hormone, drug 
  induced","overproduction thyroid-stimulating hormone, drug-induced","overproduction thyroid- 
 stimulating hormone drug-induced","drug-induced overproduction of thyroid-stimulating 
  hormone","drug-induced overproduction thyroid-stimulating 
  hormone","overprod_thyroid__hormone","overproduction of thyroid-stimulating hormone drug- 
 induced","overproduction of thyroid stimulating hormone, drug induced","drug induced 
 overproduction thyroid stimulating hormone","overproduction thyroid stimulating hormone drug 
 induced","overproduction of thyroid-stimulating hormone, drug-induced","overproduction of 
 thyroid stimulating hormone drug induced"],"term":"Overproduction of thyroid-stimulating 
 hormone, drug-induced"}]'

你需要提取每个条目中的code字段值，期望输出：

id   col1
1    P16_HCAQNB
2    OVERPROD_THYROID__HORMONE

你之前尝试的df['col1'][0][0]和df['col1'][0][code]无法生效，原因是：

• df['col1'][0]是字节字符串，直接按索引取[0]只会拿到第一个字节（比如b'['），不是解析后的JSON内容
• df['col1'][0][code]存在语法错误，且未解析JSON，无法直接访问键

解决方案 #

需要分两步处理：将字节字符串解码为普通字符串，再解析为JSON对象，最后提取code值，以下是三种可行方法：

方法1：apply结合json模块（通用型） #

import pandas as pd
import json

def extract_code(byte_str):
    # 解码字节字符串为普通字符串
    json_str = byte_str.decode('utf-8')
    # 解析JSON数组，取第一个元素（数据是数组包裹的单个对象）
    data = json.loads(json_str)[0]
    # 返回code字段值
    return data['code']

df['col1'] = df['col1'].apply(extract_code)

方法2：json_normalize（高效批量处理） #

import pandas as pd
import json

# 先解析每个字节字符串为JSON对象
df['col1'] = df['col1'].apply(lambda x: json.loads(x.decode('utf-8'))[0])
# 展开JSON对象并提取code列
df = df.join(pd.json_normalize(df['col1'])[['code']]).drop('col1', axis=1).rename(columns={'code':'col1'})

方法3：正则表达式（格式固定场景适用） #

如果JSON格式完全固定，也可以跳过JSON解析，直接用正则匹配code值：

df['col1'] = df['col1'].str.decode('utf-8').str.extract(r'"code":"(.*?)"')

执行任意一种方法后，即可得到你期望的输出结果。

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