1

I have a JSON which is making requests from http://api.worldweatheronline.com/ via their useful API.

I am struggling to convert the JSON into a tabular format such as a pandas data frame. I think the issue is due to the nested structure of the JSON.

I have tried pd.DataFrame(json), however this doesn't format correctly as it struggles with the nested structure as 'weather hourly time' spans over many rows, whereas the initial lines span a single row.

I have also tried exporting as a JSON and reading in as pd.read_json, however this has also run into similar issues.

Would really appreciate some help!

JSON is as follows:

{'data': {'request': [{'type': 'UK Postcode', 'query': 'E4'}],
  'weather': [{'date': '2018-11-28',
    'astronomy': [{'sunrise': '07:39 AM',
      'sunset': '03:57 PM',
      'moonrise': '09:46 PM',
      'moonset': '12:21 PM',
      'moon_phase': 'Last Quarter',
      'moon_illumination': '69'}],
    'maxtempC': '13',
    'maxtempF': '56',
    'mintempC': '10',
    'mintempF': '51',
    'totalSnow_cm': '0.0',
    'sunHour': '3.1',
    'uvIndex': '0',
    'hourly': [{'time': '0',
      'tempC': '9',
      'tempF': '48',
      'windspeedMiles': '4',
      'windspeedKmph': '7',
      'winddirDegree': '212',
      'winddir16Point': 'SSW',
      'weatherCode': '296',
      'weatherIconUrl': [{'value': 'http://cdn.worldweatheronline.net/images/wsymbols01_png_64/wsymbol_0033_cloudy_with_light_rain_night.png'}],
      'weatherDesc': [{'value': 'Light rain'}],
      'precipMM': '1.6',
      'humidity': '93',
      'visibility': '11',
      'pressure': '1010',
      'cloudcover': '100',
      'HeatIndexC': '9',
      'HeatIndexF': '48',
      'DewPointC': '8',
      'DewPointF': '46',
      'WindChillC': '8',
      'WindChillF': '46',
      'WindGustMiles': '8',
      'WindGustKmph': '12',
      'FeelsLikeC': '8',
      'FeelsLikeF': '46'},
     {'time': '300',
      'tempC': '9',
      'tempF': '48',
      'windspeedMiles': '7',
      'windspeedKmph': '11',
      'winddirDegree': '174',
      'winddir16Point': 'S',
      'weatherCode': '266',
      'weatherIconUrl': [{'value': 'http://cdn.worldweatheronline.net/images/wsymbols01_png_64/wsymbol_0033_cloudy_with_light_rain_night.png'}],
      'weatherDesc': [{'value': 'Light drizzle'}],
      'precipMM': '0.5',
      'humidity': '92',
      'visibility': '15',
      'pressure': '1009',
      'cloudcover': '100',
      'HeatIndexC': '9',
      'HeatIndexF': '48',
      'DewPointC': '8',
      'DewPointF': '46',
      'WindChillC': '7',
      'WindChillF': '45',
      'WindGustMiles': '11',
      'WindGustKmph': '17',
      'FeelsLikeC': '7',
      'FeelsLikeF': '45'},
     {'time': '600',
      'tempC': '11',
      'tempF': '51',
      'windspeedMiles': '11',
      'windspeedKmph': '18',
      'winddirDegree': '175',
      'winddir16Point': 'S',
      'weatherCode': '266',
      'weatherIconUrl': [{'value': 'http://cdn.worldweatheronline.net/images/wsymbols01_png_64/wsymbol_0033_cloudy_with_light_rain_night.png'}],
      'weatherDesc': [{'value': 'Light drizzle'}],
      'precipMM': '0.7',
      'humidity': '95',
      'visibility': '14',
      'pressure': '1008',
      'cloudcover': '100',
      'HeatIndexC': '11',
      'HeatIndexF': '51',
      'DewPointC': '10',
      'DewPointF': '50',
      'WindChillC': '8',
      'WindChillF': '47',
      'WindGustMiles': '19',
      'WindGustKmph': '31',
      'FeelsLikeC': '8',
      'FeelsLikeF': '47'},
     {'time': '900',
      'tempC': '12',
      'tempF': '54',
      'windspeedMiles': '12',
      'windspeedKmph': '19',
      'winddirDegree': '208',
      'winddir16Point': 'SSW',
      'weatherCode': '296',
      'weatherIconUrl': [{'value': 'http://cdn.worldweatheronline.net/images/wsymbols01_png_64/wsymbol_0017_cloudy_with_light_rain.png'}],
      'weatherDesc': [{'value': 'Light rain'}],
      'precipMM': '0.8',
      'humidity': '93',
      'visibility': '14',
      'pressure': '1008',
      'cloudcover': '100',
      'HeatIndexC': '12',
      'HeatIndexF': '54',
      'DewPointC': '11',
      'DewPointF': '52',
      'WindChillC': '10',
      'WindChillF': '51',
      'WindGustMiles': '20',
      'WindGustKmph': '32',
      'FeelsLikeC': '10',
      'FeelsLikeF': '51'},
     {'time': '1200',
      'tempC': '13',
      'tempF': '56',
      'windspeedMiles': '16',
      'windspeedKmph': '26',
      'winddirDegree': '209',
      'winddir16Point': 'SSW',
      'weatherCode': '266',
      'weatherIconUrl': [{'value': 'http://cdn.worldweatheronline.net/images/wsymbols01_png_64/wsymbol_0017_cloudy_with_light_rain.png'}],
      'weatherDesc': [{'value': 'Light drizzle'}],
      'precipMM': '0.2',
      'humidity': '87',
      'visibility': '15',
      'pressure': '1008',
      'cloudcover': '100',
      'HeatIndexC': '13',
      'HeatIndexF': '56',
      'DewPointC': '11',
      'DewPointF': '52',
      'WindChillC': '11',
      'WindChillF': '52',
      'WindGustMiles': '25',
      'WindGustKmph': '41',
      'FeelsLikeC': '11',
      'FeelsLikeF': '52'},
     {'time': '1500',
      'tempC': '13',
      'tempF': '56',
      'windspeedMiles': '19',
      'windspeedKmph': '31',
      'winddirDegree': '205',
      'winddir16Point': 'SSW',
      'weatherCode': '266',
      'weatherIconUrl': [{'value': 'http://cdn.worldweatheronline.net/images/wsymbols01_png_64/wsymbol_0017_cloudy_with_light_rain.png'}],
      'weatherDesc': [{'value': 'Light drizzle'}],
      'precipMM': '0.5',
      'humidity': '84',
      'visibility': '15',
      'pressure': '1007',
      'cloudcover': '100',
      'HeatIndexC': '13',
      'HeatIndexF': '56',
      'DewPointC': '11',
      'DewPointF': '51',
      'WindChillC': '11',
      'WindChillF': '52',
      'WindGustMiles': '30',
      'WindGustKmph': '48',
      'FeelsLikeC': '11',
      'FeelsLikeF': '52'},
     {'time': '1800',
      'tempC': '10',
      'tempF': '51',
      'windspeedMiles': '19',
      'windspeedKmph': '31',
      'winddirDegree': '216',
      'winddir16Point': 'SW',
      'weatherCode': '122',
      'weatherIconUrl': [{'value': 'http://cdn.worldweatheronline.net/images/wsymbols01_png_64/wsymbol_0004_black_low_cloud.png'}],
      'weatherDesc': [{'value': 'Overcast'}],
      'precipMM': '0.0',
      'humidity': '82',
      'visibility': '15',
      'pressure': '1007',
      'cloudcover': '100',
      'HeatIndexC': '12',
      'HeatIndexF': '54',
      'DewPointC': '11',
      'DewPointF': '51',
      'WindChillC': '10',
      'WindChillF': '51',
      'WindGustMiles': '30',
      'WindGustKmph': '49',
      'FeelsLikeC': '10',
      'FeelsLikeF': '51'},
     {'time': '2100',
      'tempC': '6',
      'tempF': '44',
      'windspeedMiles': '15',
      'windspeedKmph': '23',
      'winddirDegree': '219',
      'winddir16Point': 'SW',
      'weatherCode': '353',
      'weatherIconUrl': [{'value': 'http://cdn.worldweatheronline.net/images/wsymbols01_png_64/wsymbol_0025_light_rain_showers_night.png'}],
      'weatherDesc': [{'value': 'Light rain shower'}],
      'precipMM': '0.5',
      'humidity': '82',
      'visibility': '15',
      'pressure': '1008',
      'cloudcover': '70',
      'HeatIndexC': '8',
      'HeatIndexF': '47',
      'DewPointC': '10',
      'DewPointF': '50',
      'WindChillC': '6',
      'WindChillF': '44',
      'WindGustMiles': '24',
      'WindGustKmph': '39',
      'FeelsLikeC': '6',
      'FeelsLikeF': '44'}]}]}}

Example csv of what I'd like:

"request__type", "request__query", "weather__date", "weather__astronomy__sunrise", "weather__astronomy__sunset", "weather__astronomy__moonrise", "weather__astronomy__moonset", "weather__astronomy__moon_phase", "weather__astronomy__moon_illumination", "weather__maxtempC", "weather__maxtempF", "weather__mintempC", "weather__mintempF", "weather__totalSnow_cm", "weather__sunHour", "weather__uvIndex", "weather__hourly__time", "weather__hourly__tempC", "weather__hourly__tempF", "weather__hourly__windspeedMiles", "weather__hourly__windspeedKmph", "weather__hourly__winddirDegree", "weather__hourly__winddir16Point", "weather__hourly__weatherCode", "weather__hourly__weatherIconUrl__value", "weather__hourly__weatherDesc__value", "weather__hourly__precipMM", "weather__hourly__humidity", "weather__hourly__visibility", "weather__hourly__pressure", "weather__hourly__cloudcover", "weather__hourly__HeatIndexC", "weather__hourly__HeatIndexF", "weather__hourly__DewPointC", "weather__hourly__DewPointF", "weather__hourly__WindChillC", "weather__hourly__WindChillF", "weather__hourly__WindGustMiles", "weather__hourly__WindGustKmph", "weather__hourly__FeelsLikeC", "weather__hourly__FeelsLikeF"
"UK Postcode", "E4", "2018-11-28", "07:39 AM", "03:57 PM", "09:46 PM", "12:21 PM", "Last Quarter", "69", "13", "56", "10", "51", "0.0", "3.1", "0", "0", "9", "48", "4", "7", "212", "SSW", "296", "http://cdn.worldweatheronline.net/images/wsymbols01_png_64/wsymbol_0033_cloudy_with_light_rain_night.png", "Light rain", "1.6", "93", "11", "1010", "100", "9", "48", "8", "46", "8", "46", "8", "12", "8", "46"
"", "", "", "", "", "", "", "", "", "", "", "", "", "", "", "", "300", "9", "48", "7", "11", "174", "S", "266", "http://cdn.worldweatheronline.net/images/wsymbols01_png_64/wsymbol_0033_cloudy_with_light_rain_night.png", "Light drizzle", "0.5", "92", "15", "1009", "100", "9", "48", "8", "46", "7", "45", "11", "17", "7", "45"
"", "", "", "", "", "", "", "", "", "", "", "", "", "", "", "", "600", "11", "51", "11", "18", "175", "S", "266", "http://cdn.worldweatheronline.net/images/wsymbols01_png_64/wsymbol_0033_cloudy_with_light_rain_night.png", "Light drizzle", "0.7", "95", "14", "1008", "100", "11", "51", "10", "50", "8", "47", "19", "31", "8", "47"
"", "", "", "", "", "", "", "", "", "", "", "", "", "", "", "", "900", "12", "54", "12", "19", "208", "SSW", "296", "http://cdn.worldweatheronline.net/images/wsymbols01_png_64/wsymbol_0017_cloudy_with_light_rain.png", "Light rain", "0.8", "93", "14", "1008", "100", "12", "54", "11", "52", "10", "51", "20", "32", "10", "51"
"", "", "", "", "", "", "", "", "", "", "", "", "", "", "", "", "1200", "13", "56", "16", "26", "209", "SSW", "266", "http://cdn.worldweatheronline.net/images/wsymbols01_png_64/wsymbol_0017_cloudy_with_light_rain.png", "Light drizzle", "0.2", "87", "15", "1008", "100", "13", "56", "11", "52", "11", "52", "25", "41", "11", "52"
"", "", "", "", "", "", "", "", "", "", "", "", "", "", "", "", "1500", "13", "56", "19", "31", "205", "SSW", "266", "http://cdn.worldweatheronline.net/images/wsymbols01_png_64/wsymbol_0017_cloudy_with_light_rain.png", "Light drizzle", "0.5", "84", "15", "1007", "100", "13", "56", "11", "51", "11", "52", "30", "48", "11", "52"
"", "", "", "", "", "", "", "", "", "", "", "", "", "", "", "", "1800", "10", "51", "19", "31", "216", "SW", "122", "http://cdn.worldweatheronline.net/images/wsymbols01_png_64/wsymbol_0004_black_low_cloud.png", "Overcast", "0.0", "82", "15", "1007", "100", "12", "54", "11", "51", "10", "51", "30", "49", "10", "51"
"", "", "", "", "", "", "", "", "", "", "", "", "", "", "", "", "2100", "6", "44", "15", "23", "219", "SW", "353", "http://cdn.worldweatheronline.net/images/wsymbols01_png_64/wsymbol_0025_light_rain_showers_night.png", "Light rain shower", "0.5", "82", "15", "1008", "70", "8", "47", "10", "50", "6", "44", "24", "39", "6", "44"
Trooper Z
  • 1,617
  • 14
  • 31
shbfy
  • 2,075
  • 3
  • 16
  • 37
  • What would you like your output to look like ? Can you give us an example – WayToDoor Nov 28 '18 at 20:38
  • Sure, the best example I can find is from the following website: https://json-csv.com just paste the JSON into the box and it structures it exactly as I'd like. Thanks for your help! – shbfy Nov 28 '18 at 20:39
  • 1
    You probably want to "flatten" the json before passing it to whatever algorithm you want. If you don't get an answer by tomorrow, ping me and I'll try to write one, but I can't for now since I'm on mobile – WayToDoor Nov 28 '18 at 20:43
  • See also https://stackoverflow.com/a/28246154/3738545 – WayToDoor Nov 28 '18 at 20:43
  • Yep I agree - I've tried that but struggled. You can get into each element i.e. json['data'] but I couldn't get structure from there. Thanks for offering to help kind sir. Will keep in touch! – shbfy Nov 28 '18 at 20:44
  • Also, take a look at : https://stackoverflow.com/a/40589031/3738545 – WayToDoor Nov 28 '18 at 20:48
  • Looks promising.. Trying now! – shbfy Nov 28 '18 at 20:54
  • @WayToDoor Good suggestions, again it's not working but feels close. I think it's difficult to extract given it's quite deeply nested... – shbfy Nov 28 '18 at 20:59

1 Answers1

1

I saved your JSON to a file ('test.json', in wich, by the way, the apostrophes need to be swapped with inverted commas, so that the json module can parse it) and read it with the json module. You want to end up with a pandas DataFrame and we will need defaultdicts for making the flattening easy:

import json
import pandas as pd
from collections import defaultdict

Since you will be working with a nested structure that in every new context is going to look different, you first need a function to flatten a general nested object composed of multiple dict and list until you get a shallow dict: My suggestion:

def flatten_json(json):

    flattened_dict = defaultdict(list)

    def flatten(structure):

        global key

        if type(structure) is dict:
            for key in structure:
                flatten(structure[key])
        elif type(structure) is list:
            for item in structure:
                flatten(item)
        else:
            flattened_dict[key].append(structure)

    flatten(json)
    return flattened_dict

Almost there. The only thing that we need to fix is the fact that pandas is only going to be able to build a Dataframe out of a dictionary if all dict values are of the same length. So we need a function to fill the shorter values up to the length of the longest:

def fill_up_dict(dicti):

    max_length = max([len(liist) for liist in dicti.values()])

    for value in dicti.values():
        remaining_length = max_length - len(value)
        value.extend([""]*remaining_length)

    return dicti

Done. Just read your JSON, flatten it, fill it up and dump it into a Dataframe:

with open('test.json', 'r') as f:
    json_data = json.load(f)

flat_dict = flatten_json(json_data)

homogeneous_dict = fill_up_dict(flat_dict)

df = pd.DataFrame(homogeneous_dict)

where the outbound df looks as we want:

   DewPointC DewPointF FeelsLikeC FeelsLikeF HeatIndexC HeatIndexF WindChillC
0          8        46          8         46          9         48          8   
1          8        46          7         45          9         48          7   
2         10        50          8         47         11         51          8   
3         11        52         10         51         12         54         10   
4         11        52         11         52         13         56         11   
5         11        51         11         52         13         56         11   
6         11        51         10         51         12         54         10   
7         10        50          6         44          8         47          6   
8                                                                               
9                                                                               
10                                                                              
11                                                                              
12                                                                              
13                                                                              
14                                                                              
15                                                                              

   WindChillF WindGustKmph WindGustMiles      ...       totalSnow_cm  \
0          46           12             8      ...                0.0   
1          45           17            11      ...                      
2          47           31            19      ...                      
3          51           32            20      ...                      
4          52           41            25      ...                      
5          52           48            30      ...                      
6          51           49            30      ...                      
7          44           39            24      ...                      
8                                             ...                      
9                                             ...                      
10                                            ...                      
11                                            ...                      
12                                            ...                      
13                                            ...                      
14                                            ...                      
15                                            ...                      

           type uvIndex                                              value  \
0   UK Postcode       0  http://cdn.worldweatheronline.net/images/wsymb...   
1                                                               Light rain   
2                        http://cdn.worldweatheronline.net/images/wsymb...   
3                                                            Light drizzle   
4                        http://cdn.worldweatheronline.net/images/wsymb...   
5                                                            Light drizzle   
6                        http://cdn.worldweatheronline.net/images/wsymb...   
7                                                               Light rain   
8                        http://cdn.worldweatheronline.net/images/wsymb...   
9                                                            Light drizzle   
10                       http://cdn.worldweatheronline.net/images/wsymb...   
11                                                           Light drizzle   
12                       http://cdn.worldweatheronline.net/images/wsymb...   
13                                                                Overcast   
14                       http://cdn.worldweatheronline.net/images/wsymb...   
15                                                       Light rain shower   

   visibility weatherCode winddir16Point winddirDegree windspeedKmph  \
0          11         296            SSW           212             7   
1          15         266              S           174            11   
2          14         266              S           175            18   
3          14         296            SSW           208            19   
4          15         266            SSW           209            26   
5          15         266            SSW           205            31   
6          15         122             SW           216            31   
7          15         353             SW           219            23   
8                                                                      
9                                                                      
10                                                                     
11                                                                     
12                                                                     
13                                                                     
14                                                                     
15                                                                     

   windspeedMiles  
0               4  
1               7  
2              11  
3              12  
4              16  
5              19  
6              19  
7              15  
8                  
9                  
10                 
11                 
12                 
13                 
14                 
15                 

[16 rows x 40 columns]

Good luck with your project!

D.

mosegui
  • 664
  • 6
  • 15