convert list of value in nested dictionary to dataframe-CodePudding

i have a dictionary in the form

req_rep = {
  "enrichments": [
    {
      "data": {
        "Company_name": "tester",
        "Company_CXO_Count__c": null,  
        "fbm__Company_Employee_Size__c": "11-50", 
        "fbm__Person_Title__c": "instructor", 
        "fbm__Professional_Email__c": "[email protected]", 
        "fbm__Status__c": "Completed"
      }, 
      "id": "t1", 
      "status": "COMPLETED"
    }, 
    {
      "data": {
        "Company_name": "test3",
        "Company_CXO_Count__c": null, 
        "fbm__Company_Employee_Size__c": "11-50", 
        "fbm__Person_Title__c": "driver", 
        "fbm__Professional_Email__c": "[email protected]", 
        "fbm__Status__c": "Completed"
      }, 
      "id": "t2", 
      "status": "COMPLETED"
    }, 
    {
      "data": {
        "Company_name": "tryiu",
        "Company_CXO_Count__c": null, 
        "fbm__Company_Employee_Size__c": null, 
        "fbm__Person_Title__c": null, 
        "fbm__Professional_Email__c": "[email protected]", 
        "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
}

i wish to create a new dataframe off enrichments key value pairs to look like the table below.

i have tried a few results off my search here on stack overflow but i'm yet to get the expected result i am looking for.

df_2 = pd.DataFrame([{
  'Company_name': "tester",
  'Company_CXO_Count__c': null,
  'fbm__Company_Employee_Size__c': "11-50",
  'fbm__Person_Title__c': "instructor",
  'fbm__Professional_Email__c': "[email protected]",
  'fbm__Status__c': "Completed"},
 {'Company_name': "tester",
  'Company_CXO_Count__c': null,
  'fbm__Company_Employee_Size__c': "11-50",
  'fbm__Person_Title__c': "instructor",
  'fbm__Professional_Email__c': "[email protected]",
  'fbm__Status__c': "Completed"},
 { 'Company_name': "tryiu",
  'Company_CXO_Count__c': null,
  'fbm__Company_Employee_Size__c': null,
  'fbm__Person_Title__c': null,
  'fbm__Professional_Email__c': "[email protected]",
  'fbm__Status__c': "Completed"}])

any help would be greatly appreciated

CodePudding user response：

You need to apply pd.Series to enrichments and data:

df = pd.DataFrame(req_rep)
final_df = df['enrichments'].agg(pd.Series)['data'].agg(pd.Series)

CodePudding user response：

use:

df=pd.DataFrame(req_rep['enrichments'])
final = pd.json_normalize(df["data"])

CodePudding user response：

try json_normalize

df2 = pd.json_normalize(data=req_rep["enrichments"])
df2.columns = df2.columns.str.split(".").str[-1]
df2 = df2.drop(columns=["id", "status"])