CODEWITHSUNDEEP
pythonjsonpandas
scrollex
scrollex

Reputation: 2693

How to read JSON file that contains list of dictionaries into pandas data frame?

I've got a list of dictionaries in a JSON file that looks like this:

[{"url": "http://www.URL1.com", "date": "2001-01-01"}, 
 {"url": "http://www.URL2.com", "date": "2001-01-02"}, ...]

but I'm struggling to import it into a pandas data frame — this should be pretty easy, but I'm blanking on it. Anyone able to set me straight here?

Likewise, what's the best way to simply read it into a list of dictionaries to use w/in python?

Upvotes: 11

Views: 22516

Answers (2)

Brad Solomon
Brad Solomon

Reputation: 40878

While from_dict will work here, the prescribed way would be to use pd.read_json with orient='records'. This parses an input that is

list-like [{column -> value}, ... , {column -> value}]

Example: say this is the text of lis.json:

[{"url": "http://www.URL1.com", "date": "2001-01-01"}, 
 {"url": "http://www.URL2.com", "date": "2001-01-02"}]

To pass the file path itself as input rather than a list as in @jezrael's answer:

print(pd.read_json('lis.json', orient='records'))
        date                  url
0 2001-01-01  http://www.URL1.com
1 2001-01-02  http://www.URL2.com

Upvotes: 7

jezrael
jezrael

Reputation: 862521

You can use from_dict:

import pandas as pd

lis = [{"url": "http://www.URL1.com", "date": "2001-01-01"}, 
       {"url": "http://www.URL2.com", "date": "2001-01-02"}]

print pd.DataFrame.from_dict(lis)

         date                  url
0  2001-01-01  http://www.URL1.com
1  2001-01-02  http://www.URL2.com

Or you can use DataFrame constructor:

import pandas as pd

lis = [{"url": "http://www.URL1.com", "date": "2001-01-01"}, {"url": "http://www.URL2.com", "date": "2001-01-02"}]

print pd.DataFrame(lis)

         date                  url
0  2001-01-01  http://www.URL1.com
1  2001-01-02  http://www.URL2.com

Upvotes: 14

Related Questions