Plant Park Plant Park - 1 year ago 226
JSON Question

how to read json file with pandas?

I have scraped a website with scrapy and stored the data in a json file.

Link to the json file:

But the json isn't standard json and gives errors:

>>> import json
>>> with open("/root/code/itjuzi/itjuzi/investorinfo.json") as file:
... data = json.load(file)
Traceback (most recent call last):
File "<stdin>", line 2, in <module>
File "/root/anaconda2/lib/python2.7/json/", line 291, in load
File "/root/anaconda2/lib/python2.7/json/", line 339, in loads
return _default_decoder.decode(s)
File "/root/anaconda2/lib/python2.7/json/", line 367, in decode
raise ValueError(errmsg("Extra data", s, end, len(s)))
ValueError: Extra data: line 3 column 2 - line 3697 column 2 (char 45 - 3661517)

Then I tried this:

with open('/root/code/itjuzi/itjuzi/investorinfo.json','rb') as f:
data = f.readlines()
data = map(lambda x: x.decode('unicode_escape'), data)
>>> df = pd.DataFrame(data)
Traceback (most recent call last):
File "<stdin>", line 1, in <module>
NameError: name 'pd' is not defined
>>> import pandas as pd
>>> df = pd.DataFrame(data)
>>> print pd
<module 'pandas' from '/root/anaconda2/lib/python2.7/site-packages/pandas/__init__.pyc'>
>>> print df
[3697 rows x 1 columns]

Why does this only return 1 column?

How can I standardize the json file and read it with pandas correctly?

Answer Source

try this from SO documentation[JSON] :

import json
with open('data.json') as data_file:    
data = json.load(data_file)

This has the advantage of dealing well with large JSON files that do not fit in memory

EDIT: Your data is not valid JSON. Delete the following in the first 3 lines and it will validate:

    "website": ["\u5341\u65b9\u521b\u6295"]

EDIT2[Since you need to access nested values from json]:

You can now also access single values like this:

data["one"][0]["id"]  # will return 'value'
data["two"]["id"]    # will return 'value'
data["three"]      # will return 'value'
Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download