使用OpenWeatherAPI将json加载到数据帧

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时，我更喜欢glom或jmespath：

“天气”部分的路径是：列表键->；'列出容器'->；天气键->；'列出容器“

列表以[]符号标识，而字典键以点（.）作为前缀

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

Answer 2

试试这个，了解更多关于json_规范化的信息

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