JSON to pandas DataFrame
I found a quick and easy solution to what I wanted using json_normalize()
included in pandas 1.01
.
from urllib2 import Request, urlopenimport jsonimport pandas as pd path1 = '42.974049,-81.205203|42.974298,-81.195755'request=Request('http://maps.googleapis.com/maps/api/elevation/json?locations='+path1+'&sensor=false')response = urlopen(request)elevations = response.read()data = json.loads(elevations)df = pd.json_normalize(data['results'])
This gives a nice flattened dataframe with the json data that I got from the Google Maps API.
Check this snip out.
# reading the JSON data using json.load()file = 'data.json'with open(file) as train_file: dict_train = json.load(train_file)# converting json dataset from dictionary to dataframetrain = pd.DataFrame.from_dict(dict_train, orient='index')train.reset_index(level=0, inplace=True)
Hope it helps :)
Optimization of the accepted answer:
The accepted answer has some functioning problems, so I want to share my code that does not rely on urllib2:
import requestsfrom pandas import json_normalizeurl = 'https://www.energidataservice.dk/proxy/api/datastore_search?resource_id=nordpoolmarket&limit=5'response = requests.get(url)dictr = response.json()recs = dictr['result']['records']df = json_normalize(recs)print(df)
Output:
_id HourUTC HourDK ... ElbasAveragePriceEUR ElbasMaxPriceEUR ElbasMinPriceEUR0 264028 2019-01-01T00:00:00+00:00 2019-01-01T01:00:00 ... NaN NaN NaN1 138428 2017-09-03T15:00:00+00:00 2017-09-03T17:00:00 ... 33.28 33.4 32.02 138429 2017-09-03T16:00:00+00:00 2017-09-03T18:00:00 ... 35.20 35.7 34.93 138430 2017-09-03T17:00:00+00:00 2017-09-03T19:00:00 ... 37.50 37.8 37.34 138431 2017-09-03T18:00:00+00:00 2017-09-03T20:00:00 ... 39.65 42.9 35.3.. ... ... ... ... ... ... ...995 139290 2017-10-09T13:00:00+00:00 2017-10-09T15:00:00 ... 38.40 38.4 38.4996 139291 2017-10-09T14:00:00+00:00 2017-10-09T16:00:00 ... 41.90 44.3 33.9997 139292 2017-10-09T15:00:00+00:00 2017-10-09T17:00:00 ... 46.26 49.5 41.4998 139293 2017-10-09T16:00:00+00:00 2017-10-09T18:00:00 ... 56.22 58.5 49.1999 139294 2017-10-09T17:00:00+00:00 2017-10-09T19:00:00 ... 56.71 65.4 42.2
PS: API is for Danish electricity prices