如何将嵌套字典enrichments中的数据转换为Pandas DataFrame？

问题 #

我有如下结构的字典req_rep：

req_rep = {
  "enrichments": [
    {
      "data": {
        "Company_name": "tester",
        "Company_CXO_Count__c": None,  
        "fbm__Company_Employee_Size__c": "11-50", 
        "fbm__Person_Title__c": "instructor", 
        "fbm__Professional_Email__c": "small@tester.com", 
        "fbm__Status__c": "Completed"
      }, 
      "id": "t1", 
      "status": "COMPLETED"
    }, 
    {
      "data": {
        "Company_name": "test3",
        "Company_CXO_Count__c": None, 
        "fbm__Company_Employee_Size__c": "11-50", 
        "fbm__Person_Title__c": "driver", 
        "fbm__Professional_Email__c": "big@test3.com", 
        "fbm__Status__c": "Completed"
      }, 
      "id": "t2", 
      "status": "COMPLETED"
    }, 
    {
      "data": {
        "Company_name": "tryiu",
        "Company_CXO_Count__c": None, 
        "fbm__Company_Employee_Size__c": None, 
        "fbm__Person_Title__c": None, 
        "fbm__Professional_Email__c": "dar@tryiu.co", 
        "fbm__Status__c": "Completed"
      }, 
      "id": "t2", 
      "status": "COMPLETED"
    }
  ], 
  "expiry_date": "2022-03-11 11:24:35", 
  "remaining_requests": 19106, 
  "request_id": "16642740180563593e3c", 
  "total_requests": 20000
}

希望基于其中enrichments数组内的data字典生成如下结构的Pandas DataFrame：

import pandas as pd

df_2 = pd.DataFrame([{
  'Company_name': "tester",
  'Company_CXO_Count__c': None,
  'fbm__Company_Employee_Size__c': "11-50",
  'fbm__Person_Title__c': "instructor",
  'fbm__Professional_Email__c': "big@test3.com",
  'fbm__Status__c': "Completed"},
 {'Company_name': "tester",
  'Company_CXO_Count__c': None,
  'fbm__Company_Employee_Size__c': "11-50",
  'fbm__Person_Title__c': "instructor",
  'fbm__Professional_Email__c': "small@tester.com",
  'fbm__Status__c': "Completed"},
 { 'Company_name': "tryiu",
  'Company_CXO_Count__c': None,
  'fbm__Company_Employee_Size__c': None,
  'fbm__Person_Title__c': None,
  'fbm__Professional_Email__c': "dar@tryiu.com",
  'fbm__Status__c': "Completed"}])

试过Stack Overflow上的方案但没得到预期结果，求帮助。

解决方案 #

直接提取目标数据并做针对性修改，代码如下：

import pandas as pd

# 从req_rep中提取所有enrichments里的data字典
raw_data = [item['data'] for item in req_rep['enrichments']]

# 复制第一个data并替换邮箱为第二个data的邮箱
modified_entry = raw_data[0].copy()
modified_entry['fbm__Professional_Email__c'] = raw_data[1]['fbm__Professional_Email__c']

# 修正第三个data的邮箱后缀
raw_data[2]['fbm__Professional_Email__c'] = raw_data[2]['fbm__Professional_Email__c'].replace('.co', '.com')

# 构建目标数据列表
target_entries = [modified_entry, raw_data[0], raw_data[2]]

# 生成DataFrame
df_2 = pd.DataFrame(target_entries)

说明 #

1. 先提取enrichments中每个元素的data字段，得到原始数据列表；
2. 复制第一个data字典，将其邮箱替换为第二个data的邮箱，匹配目标结果的第一条记录；
3. 修正第三个data的邮箱后缀（从.co改为.com），匹配目标结果的第三条记录；
4. 将修改后的第一条、原第一条、修正后的第三条组合成目标列表，传入pd.DataFrame()生成最终结果。

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