how to read json file with pandas?
Question: I have scraped a website with scrapy and stored the data in a json file.  
Link to the json file:
<https://drive.google.com/file/d/0B6JCr_BzSFMHLURsTGdORmlPX0E/view?usp=sharing>

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/__init__.py", line 291, in load
    **kw)
      File "/root/anaconda2/lib/python2.7/json/__init__.py", line 339, in loads
        return _default_decoder.decode(s)
      File "/root/anaconda2/lib/python2.7/json/decoder.py", 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: try this [from SO documentation[JSON]
](http://stackoverflow.com/documentation/pandas/4752/json/16714/read-
json#t=201608191329212576968):

    
    
    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'
    

