将DataFrame中含字典列表的列展开为新DataFrame并基于ID与原DataFrame合并的问题

Hey there! Let's work through this problem together. The core issue here is that when you expand the list of dictionaries in the ocurrences column into df1, you're not carrying over the original row's ID from your main DataFrame. That's why df1 ends up with more rows than df, and you can't properly align them for merging.

Here are two reliable ways to fix this:

方法1：使用explode + json_normalize（推荐，更简洁） #

This approach first splits the list in ocurrences into individual rows while keeping all original columns (including your ID), then expands the dictionary into separate columns.

import requests
import pandas as pd

# 先获取并加载数据
url = "https://data.maldita.es/ukrainefacts"
resp = requests.get(url)
data = resp.json()
df = pd.DataFrame.from_records(data)

# 确保你的DataFrame有一个唯一ID列，如果没有可以用索引临时替代
# 如果原数据已经有'id'列，这步可以跳过
df['id'] = df.index

# 第一步：将ocurrences列的列表拆成单独行，保留原所有列
df_exploded = df.explode('ocurrences', ignore_index=True)

# 第二步：将字典列展开为多列
df1 = pd.json_normalize(df_exploded['ocurrences'])

# 第三步：合并原展开后的DataFrame和字典展开的列，同时去掉原ocurrences列
df_final = pd.concat([df_exploded.drop('ocurrences', axis=1), df1], axis=1)

方法2：手动遍历绑定ID #

If you prefer a more explicit approach, you can loop through each row, attach the original ID to every dictionary in the ocurrences list, then build df1 from these enriched dictionaries.

import requests
import pandas as pd

url = "https://data.maldita.es/ukrainefacts"
resp = requests.get(url)
data = resp.json()
df = pd.DataFrame.from_records(data)

# 确保有ID列
df['id'] = df.index

# 遍历每行，给每个字典添加对应的ID
expanded_records = []
for _, row in df.iterrows():
    for occurrence in row['ocurrences']:
        # 把原行的ID加入到当前字典中
        occurrence['id'] = row['id']
        expanded_records.append(occurrence)

# 生成带ID的df1
df1 = pd.DataFrame(expanded_records)

# 现在可以用ID列将原df和df1合并，这里用left join保留原df的所有行
df_final = df.merge(df1, on='id', how='left')

关键说明 #

• 两种方法都能保证df1的每一行都和原df的对应行ID绑定，这样后续合并就完全没问题了。
• 如果你的原始数据集本身就有天然的唯一ID列（比如自带的id字段），优先用它代替索引，这样数据关联会更可靠。

备注：内容来源于stack exchange，提问作者Alexandra