如何高效将字典的字典列表转换为Pandas DataFrame？

问题背景 #

我有如下结构的字典列表：

lis = [{'Health and Welfare Plan + Change Notification': {'evidence_capture': 'null',
   'test_result_justification': 'null',
   'latest_test_result_date': 'null',
   'last_updated_by': 'null',
   'test_execution_status': 'Not Started',
   'test_result': 'null'}},
 {'Health and Welfare Plan + Computations': {'evidence_capture': 'null',
   'test_result_justification': 'null',
   'latest_test_result_date': 'null',
   'last_updated_by': 'null',
   'test_execution_status': 'Not Started',
   'test_result': 'null'}},
 {'Health and Welfare Plan + Data Agreements': {'evidence_capture': 'null',
   'test_result_justification': 'Due to the Policy',
   'latest_test_result_date': '2019-10-02',
   'last_updated_by': 'null',
   'test_execution_status': 'In Progress',
   'test_result': 'null'}},
 {'Health and Welfare Plan + Data Elements': {'evidence_capture': 'null',
   'test_result_justification': 'xxx',
   'latest_test_result_date': '2019-10-02',
   'last_updated_by': 'null',
   'test_execution_status': 'In Progress',
   'test_result': 'null'}},
 {'Health and Welfare Plan + Data Quality Monitoring': {'evidence_capture': 'null',
   'test_result_justification': 'xxx',
   'latest_test_result_date': '2019-08-09',
   'last_updated_by': 'null',
   'test_execution_status': 'Completed',
   'test_result': 'xxx'}},
 {'Health and Welfare Plan + HPU Source Reliability': {'evidence_capture': 'null',
   'test_result_justification': 'xxx.',
   'latest_test_result_date': '2019-10-02',
   'last_updated_by': 'null',
   'test_execution_status': 'In Progress',
   'test_result': 'null'}},
 {'Health and Welfare Plan + Lineage': {'evidence_capture': 'null',
   'test_result_justification': 'null',
   'latest_test_result_date': 'null',
   'last_updated_by': 'null',
   'test_execution_status': 'Not Started',
   'test_result': 'null'}},
 {'Health and Welfare Plan + Metadata': {'evidence_capture': 'null',
   'test_result_justification': 'Valid',
   'latest_test_result_date': '2020-07-02',
   'last_updated_by': 'null',
   'test_execution_status': 'Completed',
   'test_result': 'xxx'}},
 {'Health and Welfare Plan + Usage Reconciliation': {'evidence_capture': 'null',
   'test_result_justification': 'Test out of scope',
   'latest_test_result_date': '2019-10-02',
   'last_updated_by': 'null',
   'test_execution_status': 'In Progress',
   'test_result': 'null'}}]

需要将其转换为如下格式的Pandas DataFrame：

evidence_capture last_updated_by latest_test_result_date test_execution_status test_result test_result_justification            test_category
Change Notification                 null            null                    null           Not Started        null                      null  Health and Welfare Plan
Computations                        null            null                    null           Not Started        null                      null  Health and Welfare Plan
Data Agreements                     null            null              2019-10-02           In Progress        null         Due to the Policy  Health and Welfare Plan
Data Elements                       null            null              2019-10-02           In Progress        null                       xxx  Health and Welfare Plan
Data Quality Monitoring             null            null              2019-08-09             Completed         xxx                       xxx  Health and Welfare Plan
HPU Source Reliability              null            null              2019-10-02           In Progress        null                      xxx.  Health and Welfare Plan
Lineage                             null            null                    null           Not Started        null                      null  Health and Welfare Plan
Metadata                            null            null              2020-07-02             Completed         xxx                     Valid  Health and Welfare Plan
Usage Reconciliation                null            null              2019-10-02           In Progress        null         Test out of scope  Health and Welfare Plan

当前使用循环结合concat的方法可以实现，但效率较低：

df3 = pd.DataFrame(lis[0])
for i in range(1, len(lis)):
    df3 = pd.concat([df3, pd.DataFrame(lis[i])], axis=1)
df3.columns = [col.split(' + ')[1] for col in df3.columns]
df3 = df3.T
df3['test_category'] = 'Health and Welfare Plan'
print(df3)

更优实现方案 #

方案1：合并字典后直接构造DataFrame #

先将列表中的所有字典合并为一个大字典，再通过from_dict一次性构造DataFrame，避免循环拼接的内存开销：

import pandas as pd

# 合并列表中所有字典为一个大字典
combined_dict = {key: value for item in lis for key, value in item.items()}

# 从字典构造DataFrame，指定orient='index'将键作为索引
df = pd.DataFrame.from_dict(combined_dict, orient='index')

# 拆分索引，提取test_category和子类别
df[['test_category', 'sub_category']] = df.index.str.split(' + ', expand=True)

# 设置子类别为新索引，并调整列顺序匹配目标格式
df = df.set_index('sub_category').reindex(columns=[
    'evidence_capture', 'last_updated_by', 'latest_test_result_date',
    'test_execution_status', 'test_result', 'test_result_justification',
    'test_category'
])

print(df)

方案2：预处理数据为列表后构造DataFrame #

先将每个条目拆分成包含类别信息的字典，再一次性构造DataFrame，逻辑更直观：

import pandas as pd

# 预处理数据，将每个条目转换为包含完整字段的字典
processed_data = []
for item in lis:
    for full_name, attrs in item.items():
        # 拆分完整名称为类别和子类别
        test_category, sub_category = full_name.split(' + ')
        # 复制属性并添加类别字段
        row = attrs.copy()
        row['test_category'] = test_category
        row['sub_category'] = sub_category
        processed_data.append(row)

# 构造DataFrame并设置子类别为索引，调整列顺序
df = pd.DataFrame(processed_data).set_index('sub_category').reindex(columns=[
    'evidence_capture', 'last_updated_by', 'latest_test_result_date',
    'test_execution_status', 'test_result', 'test_result_justification',
    'test_category'
])

print(df)

方案优势 #

这两种方案都避免了循环中多次调用concat带来的内存重复分配问题，直接一次性构造DataFrame，时间复杂度从O(n²)降低到O(n)，在数据量较大时性能提升明显。

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