如何拆分DataFrame多列中列表内的嵌套字典且不产生重复行？

我有如下DataFrame：

column1                                   column2                                 column3                                   column4
0        1  [{'key1': 'value1'}, {'key2': 'value2'}]  {'key1': 'value10', 'key2': 'value11'}  [{'key1': 'value1'}, {'key2': 'value2'}]
1        2  [{'key1': 'value3'}, {'key2': 'value4'}]  {'key1': 'value12', 'key2': 'value13'}  [{'key1': 'value3'}, {'key2': 'value4'}]
2        3  [{'key1': 'value5'}, {'key2': 'value6'}]  {'key1': 'value14', 'key2': 'value15'}  [{'key1': 'value5'}, {'key2': 'value6'}]
...

我希望将column2和column4拆分为单独的列，得到如下格式：

col1     col2_key1        col2_key2     col3                                       col4_key1    col4_key2
1          value1           value2        {'key1': 'value10', 'key2': 'value11'}    value1       value2
2          value3           value4        {'key1': 'value12', 'key2': 'value13'}   value3     value4
...

最初我尝试以下代码，产生了大量重复行：

dfs = {k:pd.DataFrame(x) for k, x in df.pop('column2').items()}
df = df.join(pd.concat(dfs).add_prefix('column2_').reset_index(level=1, drop=True)).reset_index(drop=True)

dfs = {k:pd.DataFrame(x) for k, x in df.pop('column4').items()}
df = df.join(pd.concat(dfs).add_prefix('column4_').reset_index(level=1, drop=True)).reset_index(drop=True)

之后我使用pd.json_normalize，得到了异常的DataFrame：

df['column2'] = df['column2'].apply(lambda x: pd.json_normalize(x[0]) if isinstance(x[0], dict) else x[0])
df['column4'] = df['column4'].apply(lambda x: pd.json_normalize(x[0]) if isinstance(x[0], dict) else x[0])

请问我哪里出错了？以下是我的完整代码：

import pandas as pd

# Sample data
data = {
    'column1': [1, 2, 3],
    'column2': [[{'key1': 'value1'}, {'key2': 'value2'}],
                [{'key1': 'value3'}, {'key2': 'value4'}],
                [{'key1': 'value5'}, {'key2': 'value6'}]],
    'column3': [{'key1': 'value10', 'key2': 'value11'}, 
                {'key1': 'value12', 'key2': 'value13'}, 
                {'key1': 'value14', 'key2': 'value15'}],
    'column4': [[{'key1': 'value1'}, {'key2': 'value2'}],
                [{'key1': 'value3'}, {'key2': 'value4'}],
                [{'key1': 'value5'}, {'key2': 'value6'}]],
}

# Create DataFrame
df = pd.DataFrame(data)
print(df)

'''
dfs = {k:pd.DataFrame(x) for k, x in df.pop('column2').items()}
df = df.join(pd.concat(dfs).add_prefix('column2_').reset_index(level=1, drop=True)).reset_index(drop=True)

dfs = {k:pd.DataFrame(x) for k, x in df.pop('column4').items()}
df = df.join(pd.concat(dfs).add_prefix('column4_').reset_index(level=1, drop=True)).reset_index(drop=True)
'''
# Apply pd.json_normalize on column2 and column4
df['column2'] = df['column2'].apply(lambda x: pd.json_normalize(x[0]) if isinstance(x[0], dict) else x[0])
df['column4'] = df['column4'].apply(lambda x: pd.json_normalize(x[0]) if isinstance(x[0], dict) else x[0])

# Print the modified DataFrame
print(df)

一、第一种方法的错误 #

你用字典推导式循环处理column2时，每个列表里的字典会被转换成单独的DataFrame，pd.concat会把这些小DataFrame堆叠起来，导致每一行原始数据被拆分成多行（列表里有几个字典就拆成几行），最后和原表join时就会产生大量重复行。

二、第二种方法的错误 #

column2的每个元素是包含两个字典的列表，但你只取了列表的第一个元素x[0]做json_normalize，所以只能提取到key1的值；而且json_normalize返回的是DataFrame，直接赋值给原表单元格会导致单元格嵌套DataFrame，最终表结构异常。

三、正确解决方案 #

针对column2和column4的结构（每个元素是多个单键字典组成的列表），先把每个列表里的字典合并成一个完整字典，再用pd.json_normalize展开成列，最后和原表拼接。

代码实现： #

import pandas as pd

# Sample data
data = {
    'column1': [1, 2, 3],
    'column2': [[{'key1': 'value1'}, {'key2': 'value2'}],
                [{'key1': 'value3'}, {'key2': 'value4'}],
                [{'key1': 'value5'}, {'key2': 'value6'}]],
    'column3': [{'key1': 'value10', 'key2': 'value11'}, 
                {'key1': 'value12', 'key2': 'value13'}, 
                {'key1': 'value14', 'key2': 'value15'}],
    'column4': [[{'key1': 'value1'}, {'key2': 'value2'}],
                [{'key1': 'value3'}, {'key2': 'value4'}],
                [{'key1': 'value5'}, {'key2': 'value6'}]],
}

df = pd.DataFrame(data)

# 处理column2：合并列表中的字典，再展开成列
col2_normalized = pd.json_normalize(df['column2'].apply(lambda lst: {k: v for d in lst for k, v in d.items()}))
col2_normalized = col2_normalized.add_prefix('col2_')

# 处理column4：合并列表中的字典，再展开成列
col4_normalized = pd.json_normalize(df['column4'].apply(lambda lst: {k: v for d in lst for k, v in d.items()}))
col4_normalized = col4_normalized.add_prefix('col4_')

# 拼接所有列，并重命名column1为col1
result = pd.concat([df[['column1', 'column3']], col2_normalized, col4_normalized], axis=1)
result = result.rename(columns={'column1': 'col1'})

print(result)

输出结果： #

col1                                   col3 col2_key1 col2_key2 col4_key1 col4_key2
0     1  {'key1': 'value10', 'key2': 'value11'}    value1    value2    value1    value2
1     2  {'key1': 'value12', 'key2': 'value13'}    value3    value4    value3    value4
2     3  {'key1': 'value14', 'key2': 'value15'}    value5    value6    value5    value6

关键逻辑说明： #

1. lambda lst: {k: v for d in lst for k, v in d.items()}：把列表里的多个单键字典合并成一个完整字典，比如[{'key1':'v1'}, {'key2':'v2'}]变成{'key1':'v1', 'key2':'v2'}。
2. pd.json_normalize：将合并后的字典展开成对应列。
3. add_prefix：给展开后的列加前缀，避免列名冲突。
4. pd.concat：将原表保留列和展开后的列横向拼接。

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