Reading Json File As Pandas Dataframe Error

I have a Json file as follows. It's a list of dicts. [{'city': 'ab', 'trips': 4, 'date': '2014-01-25', 'value': 4.7, 'price': 1.1, 'request_date': '2014-06-17', 'medium': 'iPhone'

Solution 1:

I think you can use modul json for reading file.json and then DataFrame constructor:

import pandas as pd
import json

withopen('file.json') as f:
   data = json.load(f)
print data
[{u'city': u'ab', u'medium': u'iPhone', u'request_date': u'2014-06-17', u'price': 1.1, u'Weekly_pct': 46.2, u'value': 4.7, u'%price': 15.4, u'avg_price': 5.0, u'date': u'2014-01-25', u'avg_dist': 3.67, u'type': True, u'trips': 4}, {u'city': u'bc', u'medium': u'Android', u'request_date': u'2014-05-05', u'price': 1.0, u'weekly_pct': 50.0, u'value': 5.0, u'%price': 0.0, u'avg_price': 5.0, u'date': u'2014-01-29', u'avg_dist': 8.26, u'type': False, u'trips': 0}]

print pd.DataFrame(data)

   %price  Weekly_pct  avg_dist  avg_price city        date   medium  price  \
015.446.23.675.0   ab  2014-01-25   iPhone    1.110.0         NaN      8.265.0   bc  2014-01-29  Android    1.0   

  request_date  trips   type  value  weekly_pct  
02014-06-174True4.7         NaN  
12014-05-05      0False5.050.0

Solution 2:

I had the same error. Turns out it couldn't find the file. I modified the path and pd.read_json worked fine. As for json.loads, this might be helpful.

Solution 3:

You need to indicate to Pandas that "records" formatting (where the JSON appears like a list of dictionaries) is used in datasets.json.

res = pd.read_json('input/dataset.json', orient='records')

print(res.iloc[:, :5])
   %price  Weekly_pct  avg_dist  avg_price city
015.446.23.675   ab
10.0         NaN      8.265   bc

Solution 4:

The following worked for me when pd.read_json failed: open file, load with normal json.load, then load into a pandas dataframe.

import pandas as pd
    import json

    openfile=open('file.json')
    jsondata=json.load(openfile)
    df=pd.DataFrame(jsondata)

    openfile.close()
    print(df)

Solution 5:

For me it was a problem with the path. The path I had to use depended on the directory from where I run the python file. Maybe try to 'cd' into the directory of your python file and then data=pd.read_json('dataset.json') should work.