使用openweather API将json加载到pandas数据帧中

Question

我试图从OpenWeatherMap转换json数据，但它一直给我错误。当我在其他教程网站上尝试其他json文件时，它运行得非常好。

Json格式：

{
"cod": "200",
"message": 0,
"cnt": 40,
"list": [
{
"dt": 1586250000,
"main": {
"temp": 303.36,
"feels_like": 306.76,
"temp_min": 303.36,
"temp_max": 303.95,
"pressure": 1006,
"sea_level": 1006,
"grnd_level": 1004,
"humidity": 61,
"temp_kf": -0.59
},
"weather": [{
"id": 500,
"main": "Rain",
"description": "light rain",
"icon": "10d"
}],
"clouds": {
"all": 97
},
"wind": {
"speed": 1.74,
"deg": 38
},
"rain": {
"3h": 0.29
},
"sys": {
"pod": "d"
},
"dt_txt": "2020-04-07 09:00:00"
}, {
"dt": 1586260800,
"main": {
"temp": 300.42,
"feels_like": 303.73,
"temp_min": 300.42,
"temp_max": 300.86,
"pressure": 1008,
"sea_level": 1008,
"grnd_level": 1006,
"humidity": 76,
"temp_kf": -0.44
},
"weather": [{
"id": 500,
"main": "Rain",
"description": "light rain",
"icon": "10n"
}],
"clouds": {
"all": 83
},
"wind": {
"speed": 2.5,
"deg": 52
},
"rain": {
"3h": 0.53
},
"sys": {
"pod": "n"
},
"dt_txt": "2020-04-07 12:00:00"
}, {
"dt": 1586271600,
"main": {
"temp": 299.85,
"feels_like": 303.12,
"temp_min": 299.85,
"temp_max": 300.15,
"pressure": 1010,
"sea_level": 1010,
"grnd_level": 1008,
"humidity": 80,
"temp_kf": -0.3
},
"weather": [{
"id": 500,
"main": "Rain",
"description": "light rain",
"icon": "10n"
}],
"clouds": {
"all": 62
},
"wind": {
"speed": 2.78,
"deg": 32
},
"rain": {
"3h": 0.16
},
"sys": {
"pod": "n"
},
"dt_txt": "2020-04-07 15:00:00"
}
],
"city": {
"id": 1880252,
"name": "Singapore",
"coord": {
"lat": 1.2897,
"lon": 103.8501
},
"country": "SG",
"population": 3547809,
"timezone": 28800,
"sunrise": 1586214152,
"sunset": 1586257828
}
}

我用python创建了一个函数，通过api调用读取JSON，并使用pandas 将其转换为数据帧

def _weather():
url = 'http://api.openweathermap.org/data/2.5/forecast?q=Singapore,{API}'
res = requests.get(url)
data = res.json()
return data

在将其作为json读取后，我试图将其转换为数据帧，但收到错误

#df = pd.dataFrame(_weather)
df = pd.read_json(_weather)

我错过了什么步骤？

预期的数据帧如图所示，我正在尝试显示16行数据，因为我正在提取16天/每日预测的数据。

Answer 1

试试这个，了解更多关于json_normalize、的信息

def _weather_pd(url):
df1 = json_normalize(url['list'], 'weather')
df2 = json_normalize(url['list'])
df = df2.drop('weather', axis=1).join(df1)
return df

Answer 2

这只是问题的另一个变体，在处理嵌套数据时，我更喜欢glom或jmespath，尤其是json:

天气部分的路径是：列表键->"列表容器"->天气键->"列出容器">

列表用[]符号标识，而字典键则以句点(.(为前缀

import jmespath
expression = jmespath.compile('list[].weather[]')
res = expression.search(data)
pd.DataFrame(res)
id  main    description icon
0   500 Rain    light rain  10d
1   500 Rain    light rain  10n
2   500 Rain    light rain  10n