JSON to pandas DataFrame

What I am trying to do is extract elevation data from a google maps API along a path specified by latitude and longitude coordinates as follows:

    from urllib2 import Request, urlopen
    import json

    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()

This gives me a data that looks like this:

    elevations.splitlines()

    ['{',
     '   "results" : [',
     '      {',
     '         "elevation" : 243.3462677001953,',
     '         "location" : {',
     '            "lat" : 42.974049,',
     '            "lng" : -81.205203',
     '         },',
     '         "resolution" : 19.08790397644043',
     '      },',
     '      {',
     '         "elevation" : 244.1318664550781,',
     '         "location" : {',
     '            "lat" : 42.974298,',
     '            "lng" : -81.19575500000001',
     '         },',
     '         "resolution" : 19.08790397644043',
     '      }',
     '   ],',
     '   "status" : "OK"',
     '}']

when putting into as DataFrame here is what I get:

enter image description here

    pd.read_json(elevations)

and here is what I want:

enter image description here

I'm not sure if this is possible, but mainly what I am looking for is a way to be able to put the elevation, latitude and longitude data together in a pandas dataframe (doesn't have to have fancy mutiline headers).

If any one can help or give some advice on working with this data that would be great! If you can't tell I haven't worked much with json data before...

EDIT:

This method isn't all that attractive but seems to work:

    data = json.loads(elevations)
    lat,lng,el = [],[],[]
    for result in data['results']:
        lat.append(result[u'location'][u'lat'])
        lng.append(result[u'location'][u'lng'])
        el.append(result[u'elevation'])
    df = pd.DataFrame([lat,lng,el]).T

ends up dataframe having columns latitude, longitude, elevation

enter image description here

I found a quick and easy solution to what I wanted using json_normalize function included in the latest release of pandas 0.13.

    from urllib2 import Request, urlopen
    import json
    from pandas.io.json import json_normalize

    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)
    json_normalize(data['results'])

This gives a nice flattened dataframe with the json data that I got from the google maps API.

From: stackoverflow.com/q/21104592

Back to homepage or read more recommendations: