JSON 到 Pandas DataFrame [英] JSON to pandas DataFrame

问题描述

我想要做的是沿着经纬度坐标指定的路径从谷歌地图 API 中提取高程数据，如下所示:

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"',
 '}']

当作为 DataFrame 放入这里时，我得到了:

when putting into as DataFrame here is what I get:

pd.read_json(elevations)

这是我想要的:

我不确定这是否可行，但主要是我正在寻找一种能够将高程、纬度和经度数据放在 Pandas 数据框中的方法(不必有花哨的多行标题).

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

如果有人可以帮助或提供有关处理这些数据的建议，那就太好了！如果你不知道我以前很少使用 json 数据...

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...

这种方法不是很吸引人，但似乎有效:

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

推荐答案

我使用 pandas 1.01 中包含的 json_normalize() 找到了一个快速简单的解决方案来满足我的需求.

I found a quick and easy solution to what I wanted using json_normalize() included in pandas 1.01.

from urllib2 import Request, urlopen
import json

import 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'])

这提供了一个很好的扁平化数据框，其中包含我从 Google Maps API 获得的 json 数据.

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

这篇关于JSON 到 Pandas DataFrame的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！