CODEWITHSUNDEEP
pythonjsonpandas
user2805507
user2805507

Reputation:

Load json to pandas dataframe with openweather API

I am trying to convert my json data from OpenWeatherMap but it keeps giving me error. when i tried other json file on other tutorial site, it was working perfectly fine.

Json format:

{
    "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
    }
}

I have created a function with python to read the JSON with api call and convert it to a dataframe using pandas

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

after reading it as json , i tried to convert it to a dataframe but am receiving error 

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

Any steps did i miss out ?

The expected dataframe is as shown in the picture, I am trying to display 16 row data as i am extracting data for 16day/daily forecast.

Upvotes: 2

Views: 1049

Answers (2)

sammywemmy
sammywemmy

Reputation: 28644

just another variant on the question, I prefer glom or jmespath when dealing with nested data, especially json :

the path to the weather section is : list key -> 'list container' -> weather key -> 'list container'

the lists are identified with [] symbol, while the dictionary keys are prefixed with a dot(.)

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

Upvotes: 0

user2805507
user2805507

Reputation:

Try this and understand more on 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

Upvotes: 1

Related Questions