Nested json into dataframe with python3

Question

I'm trying to put a JSON result into a DataFrame using python3.

Here is the Json result I'm working with:

{
    "jsonrpc": "2.0",
    "result": {
        "2": {
            "status": "0",
            "problems": [],
            "sla": [
                {
                    "from": 1582066800,
                    "to": 1582116906,
                    "sla": 100,
                    "okTime": 50106,
                    "problemTime": 0,
                    "downtimeTime": 0
                }
            ]
        },
        "3": {
            "status": "0",
            "problems": [],
            "sla": [
                {
                    "from": 1582066800,
                    "to": 1582116906,
                    "sla": 100,
                    "okTime": 50106,
                    "problemTime": 0,
                    "downtimeTime": 0
                }
            ]
        }
   },
    "id": 1
}

So far, I 've been able to get the table for one entry: df = pd.DataFrame(mydata['result']['2']['sla'])

Now I would like to have all entries in my table. Here is what i would like to get:

 hostid   from           to            sla     okTime    problemTime     downtimeTime
 2        1582066800     1582116906    100     50106     0               0
 3        1582066800     1582116906    100     50106     0               0

How can I proceed ?

Try pandas.pydata.org/docs/reference/api/…

Asclepius
– Asclepius

2020-02-20 22:52:47 +00:00
Commented Feb 20, 2020 at 22:52 — Asclepius
– Asclepius, Commented Feb 20, 2020 at 22:52

cwalvoort · Accepted Answer · 2020-02-21 01:39:59Z

1

Try this to grab elements from the different levels you have above:

data = [{'hostid': key, **val['sla'][0]} for key, val in mydata['result'].items()]

df = pd.DataFrame(data)

Doing this before pandas will help if you dataset is large. Otherwise you can use pandas to flatten everything as suggested in the comments.

edited Feb 21, 2020 at 1:39

answered Feb 21, 2020 at 0:35

cwalvoort

1,9551 gold badge19 silver badges21 bronze badges

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

Collectives™ on Stack Overflow

Nested json into dataframe with python3

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related