如何将DataFrame中字典列表按kind键转为列并赋值name值

假设你的DataFrame某列（比如命名为attributes）存储的是JSON格式的字典列表字符串，示例数据如下：

'[{"id": 342658, "kind": "expectancy", "name": "custom_mais_de_5_anos", "category": "response"}, {"id": 709379, "kind": "diretoria", "name": "financeiro", "category": "filter"}, {"id": 727580, "kind": "categoria_do_cargo", "name": "empregado(a)", "category": "filter"}, {"id": 341788, "kind": "gerência", "name": "compras", "category": "filter"}, {"id": 342237, "kind": "cargo", "name": "comprador_sr", "category": "filter"}, {"id": 342554, "kind": "geração", "name": "geração_y", "category": "filter"}, {"id": 700055, "kind": "idade", "name": "37", "category": "filter"}, {"id": 1023221, "kind": "tempo_de_empresa", "name": "4-_entre_3_e_5_anos", "category": "filter"}, {"id": 699749, "kind": "n3", "name": "cind", "category": "filter"}]'

需求是：将该列的字典列表展开，以每个字典的kind值作为新列名，对应name值作为列值，最终得到如下结构的DataFrame：

import pandas as pd
pd.DataFrame({
    'expectancy': ['custom_entre_1_e_3_anos','custom_mais_de_5_anos'],
    'categoria_do_cargo': ['empregado(a)','empregado(a)'],
    'diretoria': ['juridico','financeiro'],
    'gerência': ['relações_institucionais','compras'],
    'cargo': ['analista_de_relações_institucionais_sr','comprador_sr'],
    'geração': ['geração_x','geração_y'],
    'idade': [53,37],
    'tempo_de_empresa': ['8-_mais_de_20_anos','4-_entre_3_e_5_anos'],
    'n3': ['reli','cind']
})

可以通过三步实现目标转换：

1. 解析JSON字符串为字典列表 #

先把存储的JSON字符串转换成Python可操作的字典列表，用json.loads()配合pandas.Series.apply()完成：

import json
import pandas as pd

# 假设原DataFrame名为df，目标列是attributes
df['attributes_parsed'] = df['attributes'].apply(json.loads)

2. 将字典列表转换为kind: name映射字典 #

对每个解析后的字典列表，生成以kind为键、name为值的字典，简化后续展开操作：

df['kind_name_map'] = df['attributes_parsed'].apply(
    lambda item_list: {item['kind']: item['name'] for item in item_list}
)

3. 展开映射字典为新列并合并到原DataFrame #

把kind_name_map列的字典展开成独立列，再和原DataFrame合并，最后清理中间列：

# 展开字典为新的DataFrame
expanded_df = df['kind_name_map'].apply(pd.Series)

# 合并到原DataFrame并删除中间临时列
final_df = pd.concat([df, expanded_df], axis=1)
final_df = final_df.drop(['attributes', 'attributes_parsed', 'kind_name_map'], axis=1)

完整可运行代码示例 #

import json
import pandas as pd

# 构造示例原DataFrame
data = {
    'attributes': [
        '[{"id": 342658, "kind": "expectancy", "name": "custom_mais_de_5_anos", "category": "response"}, {"id": 709379, "kind": "diretoria", "name": "financeiro", "category": "filter"}, {"id": 727580, "kind": "categoria_do_cargo", "name": "empregado(a)", "category": "filter"}, {"id": 341788, "kind": "gerência", "name": "compras", "category": "filter"}, {"id": 342237, "kind": "cargo", "name": "comprador_sr", "category": "filter"}, {"id": 342554, "kind": "geração", "name": "geração_y", "category": "filter"}, {"id": 700055, "kind": "idade", "name": "37", "category": "filter"}, {"id": 1023221, "kind": "tempo_de_empresa", "name": "4-_entre_3_e_5_anos", "category": "filter"}, {"id": 699749, "kind": "n3", "name": "cind", "category": "filter"}]',
        '[{"id": 123456, "kind": "expectancy", "name": "custom_entre_1_e_3_anos", "category": "response"}, {"id": 789012, "kind": "diretoria", "name": "juridico", "category": "filter"}, {"id": 345678, "kind": "categoria_do_cargo", "name": "empregado(a)", "category": "filter"}, {"id": 901234, "kind": "gerência", "name": "relações_institucionais", "category": "filter"}, {"id": 567890, "kind": "cargo", "name": "analista_de_relações_institucionais_sr", "category": "filter"}, {"id": 234567, "kind": "geração", "name": "geração_x", "category": "filter"}, {"id": 890123, "kind": "idade", "name": "53", "category": "filter"}, {"id": 456789, "kind": "tempo_de_empresa", "name": "8-_mais_de_20_anos", "category": "filter"}, {"id": 678901, "kind": "n3", "name": "reli", "category": "filter"}]'
    ]
}
df = pd.DataFrame(data)

# 执行转换流程
df['attributes_parsed'] = df['attributes'].apply(json.loads)
df['kind_name_map'] = df['attributes_parsed'].apply(lambda x: {item['kind']: item['name'] for item in x})
expanded_df = df['kind_name_map'].apply(pd.Series)
final_df = pd.concat([df, expanded_df], axis=1).drop(['attributes', 'attributes_parsed', 'kind_name_map'], axis=1)

print(final_df)

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