Iterating through JSON and appending into dataframe

I’m getting weather forecasting data from weatherstack API.

params = {
    'access_key': 'enter_key_here',
    'query': 'Montreal',
    'forecast_days': '2',               #two days forecast
    'hourly': '1'                       #API to return weather data split hourly
}

api_result = requests.get('https://api.weatherstack.com/forecast', params)

api_response = api_result.json()

api_response

The output looks like this:

{'request': {'type': 'City',
  'query': 'Montreal, Canada',
  'language': 'en',
  'unit': 'm'},
 'location': {'name': 'Montreal',
  'country': 'Canada',
  'region': 'Quebec',
  'lat': '45.500',
  'lon': '-73.583',
  'timezone_id': 'America/Toronto',
  'localtime': '2021-05-11 13:08',
  'localtime_epoch': 1620738480,
  'utc_offset': '-4.0'},
 'current': {'observation_time': '05:08 PM',
  'temperature': 11,
  'weather_code': 122,
  'weather_icons': ['https://assets.weatherstack.com/images/wsymbols01_png_64/wsymbol_0004_black_low_cloud.png'],
  'weather_descriptions': ['Overcast'],
  'wind_speed': 31,
  'wind_degree': 230,
  'wind_dir': 'SW',
  'pressure': 1012,
  'precip': 1,
  'humidity': 58,
  'cloudcover': 100,
  'feelslike': 8,
  'uv_index': 2,
  'visibility': 14,
  'is_day': 'yes'},
 'forecast': {'2021-05-11': {'date': '2021-05-11',
   'date_epoch': 1620691200,
   'astro': {'sunrise': '05:28 AM',
    'sunset': '08:14 PM',
    'moonrise': '05:35 AM',
    'moonset': '08:14 PM',
    'moon_phase': 'Waxing Crescent',
    'moon_illumination': 7},
   'mintemp': 6,
   'maxtemp': 13,
   'avgtemp': 8,
   'totalsnow': 0,
   'sunhour': 12.4,
   'uv_index': 6,
   'hourly': [{'time': '0',
     'temperature': 9,
     'wind_speed': 16,
     'wind_degree': 240,
     'wind_dir': 'WSW',
     'weather_code': 119,
     'weather_icons': ['https://assets.weatherstack.com/images/wsymbols01_png_64/wsymbol_0003_white_cloud.png'],
     'weather_descriptions': ['Cloudy'],
     'precip': 0.1,
     'humidity': 74,
     'visibility': 10,
     'pressure': 1012,
     'cloudcover': 75,
     'heatindex': 9,
     'dewpoint': 5,
     'windchill': 7,
     'windgust': 23,
     'feelslike': 7,
     'chanceofrain': 43,
     'chanceofremdry': 31,
     'chanceofwindy': 0,
     'chanceofovercast': 86,
     'chanceofsunshine': 5,
     'chanceoffrost': 0,
     'chanceofhightemp': 0,
     'chanceoffog': 0,
     'chanceofsnow': 0,
     'chanceofthunder': 0,
     'uv_index': 1},
    {'time': '300',
     'temperature': 8,
     'wind_speed': 15,
     'wind_degree': 247,
     'wind_dir': 'WSW',
     'weather_code': 122,
     'weather_icons': ['https://assets.weatherstack.com/images/wsymbols01_png_64/wsymbol_0004_black_low_cloud.png'],
     'weather_descriptions': ['Overcast'],
     'precip': 0,
     'humidity': 83,
     'visibility': 10,
     'pressure': 1012,
     'cloudcover': 88,
     'heatindex': 8,
     'dewpoint': 6,
     'windchill': 6,
     'windgust': 21,
     'feelslike': 6,
     'chanceofrain': 0,
     'chanceofremdry': 89,
     'chanceofwindy': 0,
     'chanceofovercast': 88,
     'chanceofsunshine': 15,
     'chanceoffrost': 0,
     'chanceofhightemp': 0,
     'chanceoffog': 0,
     'chanceofsnow': 0,
     'chanceofthunder': 0,
     'uv_index': 1},
    {'time': '600',
     'temperature': 7,
     'wind_speed': 17,
     'wind_degree': 239,
     'wind_dir': 'WSW',
     'weather_code': 122,
     'weather_icons': ['https://assets.weatherstack.com/images/wsymbols01_png_64/wsymbol_0004_black_low_cloud.png'],
     'weather_descriptions': ['Overcast'],
     'precip': 0,
     'humidity': 90,
     'visibility': 10,
     'pressure': 1012,
     'cloudcover': 100,
     'heatindex': 7,
     'dewpoint': 6,
     'windchill': 4,
     'windgust': 24,
     'feelslike': 4,
     'chanceofrain': 0,
     'chanceofremdry': 84,
     'chanceofwindy': 0,
     'chanceofovercast': 90,
     'chanceofsunshine': 15,
     'chanceoffrost': 0,
     'chanceofhightemp': 0,
     'chanceoffog': 0,
     'chanceofsnow': 0,
     'chanceofthunder': 0,
     'uv_index': 2},
    {'time': '900',
     'temperature': 8,
     'wind_speed': 21,
     'wind_degree': 248,
     'wind_dir': 'WSW',
     'weather_code': 296,
     'weather_icons': ['https://assets.weatherstack.com/images/wsymbols01_png_64/wsymbol_0017_cloudy_with_light_rain.png'],
     'weather_descriptions': ['Light rain'],
     'precip': 0.5,
     'humidity': 88,
     'visibility': 10,
     'pressure': 1012,
     'cloudcover': 100,
     'heatindex': 8,
     'dewpoint': 6,
     'windchill': 5,
     'windgust': 27,
     'feelslike': 5,
     'chanceofrain': 28,
     'chanceofremdry': 57,
     'chanceofwindy': 0,
     'chanceofovercast': 83,
     'chanceofsunshine': 11,
     'chanceoffrost': 0,
     'chanceofhightemp': 0,
     'chanceoffog': 0,
     'chanceofsnow': 0,
     'chanceofthunder': 0,
     'uv_index': 2},
    {'time': '1200',
     'temperature': 10,
     'wind_speed': 24,
     'wind_degree': 259,
     'wind_dir': 'WSW',
     'weather_code': 293,
     'weather_icons': ['https://assets.weatherstack.com/images/wsymbols01_png_64/wsymbol_0017_cloudy_with_light_rain.png'],
     'weather_descriptions': ['Patchy light rain'],
     'precip': 1.3,
     'humidity': 71,
     'visibility': 9,
     'pressure': 1012,
     'cloudcover': 85,
     'heatindex': 10,
     'dewpoint': 4,
     'windchill': 7,
     'windgust': 28,
     'feelslike': 7,
     'chanceofrain': 82,
     'chanceofremdry': 0,
     'chanceofwindy': 0,
     'chanceofovercast': 87,
     'chanceofsunshine': 0,
     'chanceoffrost': 0,
     'chanceofhightemp': 0,
     'chanceoffog': 0,
     'chanceofsnow': 0,
     'chanceofthunder': 0,
     'uv_index': 2},
    {'time': '1500',
     'temperature': 12,
     'wind_speed': 23,
     'wind_degree': 273,
     'wind_dir': 'W',
     'weather_code': 176,
     'weather_icons': ['https://assets.weatherstack.com/images/wsymbols01_png_64/wsymbol_0009_light_rain_showers.png'],
     'weather_descriptions': ['Patchy rain possible'],
     'precip': 0.7,
     'humidity': 49,
     'visibility': 9,
     'pressure': 1012,
     'cloudcover': 66,
     'heatindex': 12,
     'dewpoint': 2,
     'windchill': 10,
     'windgust': 27,
     'feelslike': 10,
     'chanceofrain': 79,
     'chanceofremdry': 0,
     'chanceofwindy': 0,
     'chanceofovercast': 89,
     'chanceofsunshine': 0,
     'chanceoffrost': 0,
     'chanceofhightemp': 0,
     'chanceoffog': 0,
     'chanceofsnow': 0,
     'chanceofthunder': 0,
     'uv_index': 3},
    {'time': '1800',
     'temperature': 11,
     'wind_speed': 21,
     'wind_degree': 277,
     'wind_dir': 'W',
     'weather_code': 116,
     'weather_icons': ['https://assets.weatherstack.com/images/wsymbols01_png_64/wsymbol_0002_sunny_intervals.png'],
     'weather_descriptions': ['Partly cloudy'],
     'precip': 0.1,
     'humidity': 54,
     'visibility': 9,
     'pressure': 1011,
     'cloudcover': 73,
     'heatindex': 11,
     'dewpoint': 2,
     'windchill': 9,
     'windgust': 26,
     'feelslike': 9,
     'chanceofrain': 56,
     'chanceofremdry': 30,
     'chanceofwindy': 0,
     'chanceofovercast': 68,
     'chanceofsunshine': 27,
     'chanceoffrost': 0,
     'chanceofhightemp': 0,
     'chanceoffog': 0,
     'chanceofsnow': 0,
     'chanceofthunder': 0,
     'uv_index': 4},
    {'time': '2100',
     'temperature': 8,
     'wind_speed': 17,
     'wind_degree': 273,
     'wind_dir': 'W',
     'weather_code': 176,
     'weather_icons': ['https://assets.weatherstack.com/images/wsymbols01_png_64/wsymbol_0025_light_rain_showers_night.png'],
     'weather_descriptions': ['Patchy rain possible'],
     'precip': 0,
     'humidity': 75,
     'visibility': 10,
     'pressure': 1013,
     'cloudcover': 61,
     'heatindex': 8,
     'dewpoint': 3,
     'windchill': 5,
     'windgust': 24,
     'feelslike': 5,
     'chanceofrain': 22,
     'chanceofremdry': 60,
     'chanceofwindy': 0,
     'chanceofovercast': 59,
     'chanceofsunshine': 53,
     'chanceoffrost': 0,
     'chanceofhightemp': 0,
     'chanceoffog': 0,
     'chanceofsnow': 0,
     'chanceofthunder': 0,
     'uv_index': 1}]},
  '2021-05-12': {'date': '2021-05-12',
   'date_epoch': 1620777600,
   'astro': {'sunrise': '05:27 AM',
    'sunset': '08:15 PM',
    'moonrise': '05:59 AM',
    'moonset': '09:19 PM',
    'moon_phase': 'Waxing Crescent',
    'moon_illumination': 14},
   'mintemp': 5,
   'maxtemp': 15,
   'avgtemp': 10,
   'totalsnow': 0,
   'sunhour': 11.4,
   'uv_index': 4,
   'hourly': [{'time': '0',
     'temperature': 6,
     'wind_speed': 16,
     'wind_degree': 266,
     'wind_dir': 'W',
     'weather_code': 122,
     'weather_icons': ['https://assets.weatherstack.com/images/wsymbols01_png_64/wsymbol_0004_black_low_cloud.png'],
     'weather_descriptions': ['Overcast'],
     'precip': 0.1,
     'humidity': 85,
     'visibility': 10,
     'pressure': 1013,
     'cloudcover': 100,
     'heatindex': 6,
     'dewpoint': 4,
     'windchill': 3,
     'windgust': 21,
     'feelslike': 3,
     'chanceofrain': 44,
     'chanceofremdry': 29,
     'chanceofwindy': 0,
     'chanceofovercast': 92,
     'chanceofsunshine': 4,
     'chanceoffrost': 0,
     'chanceofhightemp': 0,
     'chanceoffog': 0,
     'chanceofsnow': 0,
     'chanceofthunder': 0,
     'uv_index': 1},
    {'time': '300',
     'temperature': 6,
     'wind_speed': 13,
     'wind_degree': 270,
     'wind_dir': 'W',
     'weather_code': 122,
     'weather_icons': ['https://assets.weatherstack.com/images/wsymbols01_png_64/wsymbol_0004_black_low_cloud.png'],
     'weather_descriptions': ['Overcast'],
     'precip': 0,
     'humidity': 85,
     'visibility': 10,
     'pressure': 1013,
     'cloudcover': 98,
     'heatindex': 6,
     'dewpoint': 4,
     'windchill': 3,
     'windgust': 18,
     'feelslike': 3,
     'chanceofrain': 0,
     'chanceofremdry': 88,
     'chanceofwindy': 0,
     'chanceofovercast': 90,
     'chanceofsunshine': 13,
     'chanceoffrost': 0,
     'chanceofhightemp': 0,
     'chanceoffog': 0,
     'chanceofsnow': 0,
     'chanceofthunder': 0,
     'uv_index': 1},
    {'time': '600',
     'temperature': 7,
     'wind_speed': 11,
     'wind_degree': 293,
     'wind_dir': 'WNW',
     'weather_code': 122,
     'weather_icons': ['https://assets.weatherstack.com/images/wsymbols01_png_64/wsymbol_0004_black_low_cloud.png'],
     'weather_descriptions': ['Overcast'],
     'precip': 0,
     'humidity': 81,
     'visibility': 10,
     'pressure': 1016,
     'cloudcover': 94,
     'heatindex': 7,
     'dewpoint': 3,
     'windchill': 4,
     'windgust': 16,
     'feelslike': 4,
     'chanceofrain': 0,
     'chanceofremdry': 91,
     'chanceofwindy': 0,
     'chanceofovercast': 88,
     'chanceofsunshine': 15,
     'chanceoffrost': 0,
     'chanceofhightemp': 0,
     'chanceoffog': 0,
     'chanceofsnow': 0,
     'chanceofthunder': 0,
     'uv_index': 2},
    {'time': '900',
     'temperature': 10,
     'wind_speed': 13,
     'wind_degree': 324,
     'wind_dir': 'NW',
     'weather_code': 176,
     'weather_icons': ['https://assets.weatherstack.com/images/wsymbols01_png_64/wsymbol_0009_light_rain_showers.png'],
     'weather_descriptions': ['Patchy rain possible'],
     'precip': 0,
     'humidity': 60,
     'visibility': 10,
     'pressure': 1018,
     'cloudcover': 98,
     'heatindex': 10,
     'dewpoint': 3,
     'windchill': 8,
     'windgust': 15,
     'feelslike': 8,
     'chanceofrain': 23,
     'chanceofremdry': 61,
     'chanceofwindy': 0,
     'chanceofovercast': 93,
     'chanceofsunshine': 7,
     'chanceoffrost': 0,
     'chanceofhightemp': 0,
     'chanceoffog': 0,
     'chanceofsnow': 0,
     'chanceofthunder': 0,
     'uv_index': 3},
    {'time': '1200',
     'temperature': 14,
     'wind_speed': 14,
     'wind_degree': 344,
     'wind_dir': 'NNW',
     'weather_code': 176,
     'weather_icons': ['https://assets.weatherstack.com/images/wsymbols01_png_64/wsymbol_0009_light_rain_showers.png'],
     'weather_descriptions': ['Patchy rain possible'],
     'precip': 0.1,
     'humidity': 42,
     'visibility': 10,
     'pressure': 1018,
     'cloudcover': 95,
     'heatindex': 14,
     'dewpoint': 1,
     'windchill': 13,
     'windgust': 16,
     'feelslike': 13,
     'chanceofrain': 66,
     'chanceofremdry': 0,
     'chanceofwindy': 0,
     'chanceofovercast': 90,
     'chanceofsunshine': 0,
     'chanceoffrost': 0,
     'chanceofhightemp': 0,
     'chanceoffog': 0,
     'chanceofsnow': 0,
     'chanceofthunder': 0,
     'uv_index': 3},
    {'time': '1500',
     'temperature': 15,
     'wind_speed': 10,
     'wind_degree': 332,
     'wind_dir': 'NNW',
     'weather_code': 116,
     'weather_icons': ['https://assets.weatherstack.com/images/wsymbols01_png_64/wsymbol_0002_sunny_intervals.png'],
     'weather_descriptions': ['Partly cloudy'],
     'precip': 0.1,
     'humidity': 39,
     'visibility': 10,
     'pressure': 1018,
     'cloudcover': 72,
     'heatindex': 15,
     'dewpoint': 1,
     'windchill': 14,
     'windgust': 12,
     'feelslike': 14,
     'chanceofrain': 41,
     'chanceofremdry': 28,
     'chanceofwindy': 0,
     'chanceofovercast': 72,
     'chanceofsunshine': 23,
     'chanceoffrost': 0,
     'chanceofhightemp': 0,
     'chanceoffog': 0,
     'chanceofsnow': 0,
     'chanceofthunder': 0,
     'uv_index': 4},
    {'time': '1800',
     'temperature': 14,
     'wind_speed': 6,
     'wind_degree': 271,
     'wind_dir': 'W',
     'weather_code': 116,
     'weather_icons': ['https://assets.weatherstack.com/images/wsymbols01_png_64/wsymbol_0002_sunny_intervals.png'],
     'weather_descriptions': ['Partly cloudy'],
     'precip': 0,
     'humidity': 44,
     'visibility': 10,
     'pressure': 1019,
     'cloudcover': 44,
     'heatindex': 14,
     'dewpoint': 2,
     'windchill': 14,
     'windgust': 9,
     'feelslike': 14,
     'chanceofrain': 0,
     'chanceofremdry': 85,
     'chanceofwindy': 0,
     'chanceofovercast': 37,
     'chanceofsunshine': 75,
     'chanceoffrost': 0,
     'chanceofhightemp': 0,
     'chanceoffog': 0,
     'chanceofsnow': 0,
     'chanceofthunder': 0,
     'uv_index': 4},
    {'time': '2100',
     'temperature': 12,
     'wind_speed': 6,
     'wind_degree': 239,
     'wind_dir': 'WSW',
     'weather_code': 116,
     'weather_icons': ['https://assets.weatherstack.com/images/wsymbols01_png_64/wsymbol_0004_black_low_cloud.png'],
     'weather_descriptions': ['Partly cloudy'],
     'precip': 0,
     'humidity': 56,
     'visibility': 10,
     'pressure': 1020,
     'cloudcover': 41,
     'heatindex': 12,
     'dewpoint': 3,
     'windchill': 12,
     'windgust': 10,
     'feelslike': 12,
     'chanceofrain': 0,
     'chanceofremdry': 84,
     'chanceofwindy': 0,
     'chanceofovercast': 37,
     'chanceofsunshine': 82,
     'chanceoffrost': 0,
     'chanceofhightemp': 0,
     'chanceoffog': 0,
     'chanceofsnow': 0,
     'chanceofthunder': 0,
     'uv_index': 1}]}}}

How do I iterate through this output and create a new data frame to look like this: (And get only the date time hour, and rain values)

Date	precip at time 0	precip at time 300	…
2021-05-11	0.1	0	…
2021-05-12	0.1	0	…
…	…	…	…

Also, I want to convert the date column to Month Day ,Year. ie. 2021-05-11 -> May 05, 2021. I tried using

df['Date'] = datetime.datetime.strftime('%b %d,%Y')

then

df['Date'] = pandas.to_datetime(df['Date'])

But I get this error:

AttributeError: type object 'datetime.datetime' has no attribute 'datetime'

Answer

Create dataframe

This will create a dataframe with the required data and column names.

import pandas as pd

### code to get data

forecast={}

for date in api_response['forecast']:
  precip = {f"precip at time {api_response['forecast'][date]['hourly'][hour]['time']}":
              api_response['forecast'][date]['hourly'][hour]['precip']  for hour in range(0, 8)}
  forecast[date] = precip

df  = pd.DataFrame.from_dict(forecast, orient='index', dtype=float).reset_index()
df.rename(columns={'index':'Date'}, inplace=True)

print(df)

Create dataframe and format headers

If you wanted the times in HH:MM format make these changes/additions to the code.

import json
import pandas as pd

### code to get data

forecast={}

for date in api_response['forecast']:
  precip = {api_response['forecast'][date]['hourly'][hour]['time']:
              api_response['forecast'][date]['hourly'][hour]['precip']  for hour in range(0, 8)}
  forecast[date] = precip

df  = pd.DataFrame.from_dict(forecast, orient='index', dtype=float).reset_index()

cols = ['Date' if col=='index' else f'precip at time {col.zfill(4)[:2]+":"+col.zfill(4)[2:]}'  for col in df.columns]

df.columns = cols

print(df)

Change Date column format

To change the format of the Date column we first need to convert it to a datetime, which we can do using pd.to_datetime.

We can then chain strftime to get the required format, May 05, 2021.

For a full list of the directives you can use with strftime see here.

df['Date'] = pd.to_datetime(df['Date']).dt.strftime('%B %d, %Y')