how to convert nested dict to dataframe using python?-CodePudding

I need to convert this dict to dataframe/csv

data= {
  "message": {
    "id": 474735,
    "token": "GI797jMv8FuG",
    "direction": "outgoing",
    "message_id": "t",
    "to": "[email protected]",
    "from": "[email protected]",
    "subject": "Test Message at July 05, 2022 16:10",
    "timestamp": 1657017652.063772,
    "spam_status": "NotChecked",
    "tag": null
  },
  "status": "Sent",
  "details": "Message for  accepted by aspmx.l.google.com (142.251.12.26) (from 49.248.200.108)",
  "output": "250 2.0.0 OK  1657017654 f1-20020a63de01000000b0040d4ea0274fsi11524255pgg.697 - gsmtp\n",
  "sent_with_ssl": false,
  "timestamp": 1657017654.6172404,
  "time": 1.26
}

I used this website

CodePudding user response：

You need to create a dictionary with the default value of list and add each element in this dictionary.

import pandas as pd

tmp = {}
for k,v in data.items():
    if isinstance(v, dict):
        for a,b in v.items():
            tmp.setdefault(f'{k}/{a}', []).append(b)
    else:
        tmp.setdefault(k, []).append(v)
        
        
        
pd.DataFrame(tmp)

Output:

   message/id   message/token   message/direction   message/message_id  message/to  message/from    message/subject message/timestamp   message/spam_status message/tag status  details output  sent_with_ssl   timestamp   time
0   474735      GI797jMv8FuG    outgoing                     t   [email protected] [email protected]  Test Message at July 05, 2022 16:10 1.657018e 09    NotChecked  null    Sent    Message for accepted by aspmx.l.google.com (1...    250 2.0.0 OK 1657017654 f1-20020a63de01000000...    false   1.657018e 09    1.26

CodePudding user response：

or you can use this build in function from pandas

pd.json_normalize(data)

output:

status  details output  sent_with_ssl   timestamp   time    message.id  message.token   message.direction   message.message_id  message.to  message.from    message.subject message.timestamp   message.spam_status message.tag
Sent    Message for  accepted by aspmx.l.google.com (142.251.12.26) (from 49.248.200.108)   "250 2.0.0 OK  1657017654 f1-20020a63de01000000b0040d4ea0274fsi11524255pgg.697 - gsmtp "    False   1657017654.6172404  1.26    474735  GI797jMv8FuG    outgoing    t   [email protected]  [email protected]  Test Message at July 05, 2022 16:10 1657017652.063772   NotChecked