how to convert nested dict to dataframe using python?

Question

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 https://www.convertcsv.com/json-to-csv.htm and I'm getting the output as I want but need the code to convert dict to dataframe so i can put this in my script please file the output table in the attachment

Hi, Thank you for sharing your question with us. It'd be best if you could provide what you have tried so far, as well as another row for your dataframe (since the data structure is somehow vague). In that case, I'm sure you'll reach the desired answer much faster. Sincerely — TheFaultInOurStars
– TheFaultInOurStars, Commented Jul 6, 2022 at 7:06
I tried to convert dict to dataframe without modifying anything in dict variable but the structure of json not perfect format(nested dict)to get in the form of table and row but when i use single dict it is giving me output — shubham
– shubham, Commented Jul 6, 2022 at 7:33
this dict / JSON data is API output which I want to store in CSV format, for this I'm using flask — shubham
– shubham, Commented Jul 6, 2022 at 7:38

MAFiA303 · Accepted Answer · 2022-07-06 07:56:11Z

3

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

edited Jul 6, 2022 at 7:56

answered Jul 6, 2022 at 7:46

MAFiA303

1,34713 silver badges11 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Mahdi F. · Accepted Answer · 2022-07-06 07:34:34Z

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

Collectives™ on Stack Overflow

how to convert nested dict to dataframe using python?

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related