How can I turn this nested JSON into a DataFrame?

Question

So I have a piece of JSON code and I want to turn it into a DataFrame, however I am quite new to DataFrames so I am a bit stuck. Any help would be appreciated :)

So this is my code:

data = response.json()

data_pretty = json.dumps(data, sort_keys=True, indent=4)
data_frame = pd.DataFrame(data)

# Pretty print
print(data_pretty)
print(data_frame)

This is the output:

{
    "status": "OK",
    "users": [
        {
            "email": "[email protected]",
            "first_name": "Raf",
            "id": "24959",
            "last_name": "Rasenberg"
        },
        {
            "email": "[email protected]",
            "first_name": "Raf",
            "id": "25795",
            "last_name": "Rasenberg"
        }
    ]
}
  status                                              users
0     OK  {'id': '24959', 'email': '[email protected]', ...
1     OK  {'id': '25795', 'email': 'raf.rasenberg@gmail....

As you can see it needs some additional tweaking, I only want to show the columns 'email', 'first_name', 'id' and 'last_name'. Can someone help me out?

do you need something more complex than pd.DataFrame(data.get('users'))? — gold_cy
– gold_cy, Commented Mar 24, 2019 at 19:48

koPytok · Accepted Answer · 2019-03-24 19:49:45Z

1

You only need to select list of rows:

pd.DataFrame(data.get('users'))

answered Mar 24, 2019 at 19:49

koPytok

3,7731 gold badge16 silver badges29 bronze badges

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

1 Comment

gold_cy Over a year ago

alternatively pd.io.json.json_normalize(data, record_path='users')

Collectives™ on Stack Overflow

How can I turn this nested JSON into a DataFrame?

1 Answer 1

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related