优化已接受答案:

公认的答案有一些功能问题，所以我想分享我的代码，不依赖于urllib2:

import requests
from pandas import json_normalize
url = '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)

输出:

        _id                    HourUTC               HourDK  ... ElbasAveragePriceEUR  ElbasMaxPriceEUR  ElbasMinPriceEUR
0    264028  2019-01-01T00:00:00+00:00  2019-01-01T01:00:00  ...                  NaN               NaN               NaN
1    138428  2017-09-03T15:00:00+00:00  2017-09-03T17:00:00  ...                33.28              33.4              32.0
2    138429  2017-09-03T16:00:00+00:00  2017-09-03T18:00:00  ...                35.20              35.7              34.9
3    138430  2017-09-03T17:00:00+00:00  2017-09-03T19:00:00  ...                37.50              37.8              37.3
4    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.4
996  139291  2017-10-09T14:00:00+00:00  2017-10-09T16:00:00  ...                41.90              44.3              33.9
997  139292  2017-10-09T15:00:00+00:00  2017-10-09T17:00:00  ...                46.26              49.5              41.4
998  139293  2017-10-09T16:00:00+00:00  2017-10-09T18:00:00  ...                56.22              58.5              49.1
999  139294  2017-10-09T17:00:00+00:00  2017-10-09T19:00:00  ...                56.71              65.4              42.2 

PS: API是指丹麦的电价

一旦你有了被接受的答案所获得的扁平数据帧，你可以让列成为一个MultiIndex(“花式多行标题”)，就像这样:

df.columns = pd.MultiIndex.from_tuples([tuple(c.split('.')) for c in df.columns])

我使用pandas 1.01中包含的json_normalize()找到了一个快速而简单的解决方案。

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

这给了一个很好的扁平数据框架与json数据，我从谷歌地图API。

下面是一个小实用程序类，它可以将JSON转换为DataFrame和DataFrame:希望这对您有帮助。

# -*- coding: utf-8 -*-
from pandas.io.json import json_normalize

class DFConverter:

    #Converts the input JSON to a DataFrame
    def convertToDF(self,dfJSON):
        return(json_normalize(dfJSON))

    #Converts the input DataFrame to JSON 
    def convertToJSON(self, df):
        resultJSON = df.to_json(orient='records')
        return(resultJSON)

Rumble通过JSONiq原生支持JSON，运行在Spark上，内部管理DataFrames，所以你不需要这样做——即使数据不是完全结构化的:

let $coords := "42.974049,-81.205203%7C42.974298,-81.195755"
let $request := json-doc("http://maps.googleapis.com/maps/api/elevation/json?locations="||$coords||"&sensor=false")
for $obj in $request.results[]
return {
  "latitude" : $obj.location.lat,
  "longitude" : $obj.location.lng,
  "elevation" : $obj.elevation
}

结果可以导出为CSV，然后以任何其他主机语言作为DataFrame重新打开。

问题是数据帧中有几列包含字典，其中包含更小的字典。有用的Json通常有大量嵌套。我一直在写一些小函数，把我想要的信息拉到一个新的列中。这样我就有了我想要的格式。

for row in range(len(data)):
    #First I load the dict (one at a time)
    n = data.loc[row,'dict_column']
    #Now I make a new column that pulls out the data that I want.
    data.loc[row,'new_column'] = n.get('key')