python - 在 Python 中对 JSON 文件进行排序并输出为 Excel

Question

我想获取我所在城市的天气预报。我从 Openweathermap 获得了一个 API，它为我提供了一个 json 文件。现在我想对这个 json 进行排序并将其输出为 Excel。
到目前为止，我得到了这段代码(我编辑了 API):

import urllib.request
import json
import pandas
urllib.request.urlretrieve ("http://api.openweathermap.org/data/2.5/forecast?id=2867714&APPID=XXXXXXXXX","WeatherData.json")
with open('WeatherData.json') as f:
    data = (line.strip() for line in f)
    data_json = "[{0}]".format(','.join(data))
data=json.loads(data_json)

这会生成以下 JSON:

{
    "city": {
        "coord": {
            "lat": 48.1374,
            "lon": 11.5755
        },
        "country": "DE",
        "id": 2867714,
        "name": "Muenchen",
        "sunrise": 1574231244,
        "sunset": 1574263882,
        "timezone": 3600
    },
    "cnt": 40,
    "cod": "200",
    "list": [
        {
            "clouds": {
                "all": 86
            },
            "dt": 1574251200,
            "dt_txt": "2019-11-20 12:00:00",
            "main": {
                "grnd_level": 941,
                "humidity": 71,
                "pressure": 1014,
                "sea_level": 1014,
                "temp": 278.95,
                "temp_kf": -0.36,
                "temp_max": 279.31,
                "temp_min": 278.95
            },
            "sys": {
                "pod": "d"
            },
            "weather": [
                {
                    "description": "overcast clouds",
                    "icon": "04d",
                    "id": 804,
                    "main": "Clouds"
                }
            ],
            "wind": {
                "deg": 63,
                "speed": 2.86
            }
        },
        {
            "clouds": {
                "all": 96
            },
            "dt": 1574262000,
            "dt_txt": "2019-11-20 15:00:00",
            "main": {
                "grnd_level": 940,
                "humidity": 85,
                "pressure": 1013,
                "sea_level": 1013,
                "temp": 276.96,
                "temp_kf": -0.27,
                "temp_max": 277.23,
                "temp_min": 276.96
            },
            "sys": {
                "pod": "d"
            },
            "weather": [
                {
                    "description": "overcast clouds",
                    "icon": "04d",
                    "id": 804,
                    "main": "Clouds"
                }
            ],
            "wind": {
                "deg": 61,
                "speed": 1.98
            }
        },

我刚刚使用了 2 个天气预报列表条目的示例。 json 包含 40 个(每天 8 个，持续 5 天)。

我只需要温度、相对湿度和压力。其余的对我的应用程序来说毫无用处。

Answer 1

import urllib.request
import json
import pandas as pd
from pandas.io.json import json_normalize
data = urllib.request.urlopen("https://samples.openweathermap.org/data/2.5/forecast?id=2867714&appid=b1b15e88fa797225412429c1c50c122a1")
data = json.loads(data.read())
df=json_normalize(data['list'])
df['city']=data['city']['name']
#with all data
df.to_excel("sample.xls")
#with selected data
df = df[['main.temp','main.pressure','main.humidity','city']]
df.to_excel("moscow_temp.xls")