如何展开Pandas中存储字典列表的performance列？

我从第三方NoSQL数据库获取数据得到如下DataFrame，希望展开其中的performance列，请问是否可行？

原始数据 #

import pandas as pd
cols = ['name', 'performance']
data = [
    ['bob',   [{'dates': '15-12-2021', 'gdp': 19},
 {'dates': '16-12-2021', 'gdp': 36},
 {'dates': '12-12-2022', 'gdp': 39},
 {'dates': '13-12-2022', 'gdp': 35},
 {'dates': '14-12-2022', 'gdp': 35}]]]

df = pd.DataFrame(data, columns=cols)

期望输出 #

cols = ['name', 'dates', 'gdp']

data = [
    ['bob',   '15-12-2021', 19],
    ['bob',   '16-12-2021', 36],
    ['bob',   '12-12-2022', 39],
    ['bob',   '13-12-2022', 35],
    ['bob',  '14-12-2022', 35]]

df = pd.DataFrame(data, columns=cols)

解决方案 #

完全可行，结合pandas的explode()和json_normalize()方法就能实现：

import pandas as pd

# 原始数据构造
cols = ['name', 'performance']
data = [
    ['bob',   [{'dates': '15-12-2021', 'gdp': 19},
 {'dates': '16-12-2021', 'gdp': 36},
 {'dates': '12-12-2022', 'gdp': 39},
 {'dates': '13-12-2022', 'gdp': 35},
 {'dates': '14-12-2022', 'gdp': 35}]]]

df = pd.DataFrame(data, columns=cols)

# 1. 将performance列的列表拆分为每行一个字典
df_exploded = df.explode('performance').reset_index(drop=True)

# 2. 将字典列展开为单独的列
df_normalized = pd.json_normalize(df_exploded['performance'])

# 3. 合并name列和展开后的列
result = pd.concat([df_exploded['name'], df_normalized], axis=1)

print(result)

如果想要更简洁的写法，可以把步骤合并：

result = df.explode('performance').pipe(lambda x: pd.concat([x['name'], pd.json_normalize(x['performance'])], axis=1))

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