JSON dictionaries to Pandas DataFrame in Python

Question

I get JSON data from an API service, and I would like to use a DataFrame to then output the data into CSV.

So, I am trying to convert a list of dictionaries, with about 100.000 dictionaries with about 100 key value pairs, nested up to 4 levels deep, into a Pandas DataFrame.

I am using the following code, but it is painfully slow:

try:
    # Convert each JSON data event to a Pandas DataFrame
    df_i = []
    for d in data:
        df_i.append( json_normalize(d) )

    # Concatenate all DataFrames into a single one
    df = concat(df_i, axis=0)

except AttributeError:
    print "Error: Expected a list of dictionaries to parse JSON data"

Does anyone know of a better and faster way to do this?

pandas can read json directly: pandas.pydata.org/pandas-docs/stable/generated/… — K.Chen
– K.Chen, Commented Sep 8, 2014 at 23:07

Andy Hayden · Accepted Answer · 2014-09-09 00:14:10Z

4

There's a whole section in the io docs on reading json (as strings or files) directly using pd.read_json.

You ought to be able to do something like:

pd.concat((pd.read_json(d) for d in data), axis=0)

This will often be much faster than creating a temporary dict.

answered Sep 9, 2014 at 0:14

Andy Hayden

378k110 gold badges640 silver badges546 bronze badges

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

1 Comment

MauricioRoman Over a year ago

Thank you. Given that the json format I have is nested, it was not parsed correctly by read_json(), giving me a TypeError. However, I used your form with json_normalize():df = concat((json_normalize(d) for d in data), axis=0)

Gass · Accepted Answer · 2021-07-06 09:32:13Z

3

The following example founded in pandas.io.json.json_normalize documentation (link: https://pandas.pydata.org/pandas-docs/version/0.22/generated/pandas.io.json.json_normalize.html):

>>> from pandas.io.json import json_normalize
>>> data = [{'id': 1, 'name': {'first': 'Coleen', 'last': 'Volk'}},
        {'name': {'given': 'Mose', 'family': 'Regner'}},
        {'id': 2, 'name': 'Faye Raker'}]
>>> json_normalize(data)
   id        name name.family name.first name.given name.last
0  1.0         NaN         NaN     Coleen        NaN      Volk
1  NaN         NaN      Regner        NaN       Mose       NaN
2  2.0  Faye Raker         NaN        NaN        NaN       NaN

answered Jul 6, 2021 at 9:32

Gass

311 bronze badge

Collectives™ on Stack Overflow

JSON dictionaries to Pandas DataFrame in Python

2 Answers 2

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related