shbfy
Reputation: 2135
Tabular formatting for JSON file
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"
Upvotes: 1
Views: 61
Answers (1)
mosegui
Reputation: 721
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.
Upvotes: 1
Related Questions
- How to handle .json fine in tabular form in python?
- Pandas and JSON Format
- Format Json using Pandas DataFrame
- Formatting of JSON file
- How to format a JSON object as Pandas Dataframe?
- Need help formatting pandas data frame from json file
- How to make dataframe table from json file
- Formatting JSON for Pandas Dataframe
- Formatting JSON data in Python
- Formatting json from pandas dataframe