'Sort JSON File and output as Excel in Python

I want to get the Weather Forecast for my city. I got an API from Openweathermap, which gives me a json file. Now i want to sort this json and output it as an excel.
So far I got this code (I edited out the 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)

This generates the following 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
            }
        },

I just used an example of 2 List entries for the weather forecast. The json contains 40 (8 a day for 5 days).

I just need the temperature, relative humidity, and the pressure. The rest is useless for my application.

Solution 1:^[1]

One way is to store them as CSV like:

records.append("{},{},{}\n".format(temperature1, humidity1, pressure1))
records.append("{},{},{}\n".format(temperature2, humidity2, pressure2))
records.append("{},{},{}\n".format(temperature3, humidity3, pressure3))

Then open this CSV from Excel and save as Excel format.

Another way (more complicated) to use 3rd party libs for it, such as openpyxl

Sources

This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.

Source: Stack Overflow

Solution	Source
Solution 1	Raymond Reddington

'Sort JSON File and output as Excel in Python

Solution 1:[1]

Sources

Related Questions

Solution 1:^[1]