I am using the MetOffice Datapoint API to download UK Weather data as a JSON. I would then like to read that JSON file into a pandas DataFrame. The format of the JSON file is as shown
{"SiteRep":{"Wx":{"Param":[{"name":"FDm","units":"C","$":"Feels Like Day Maximum Temperature"},{"name":"FNm","units":"C","$":"Feels Like Night Minimum Temperature"},{"name":"Dm","units":"C","$":"Day Maximum Temperature"},{"name":"Nm","units":"C","$":"Night Minimum Temperature"},{"name":"Gn","units":"mph","$":"Wind Gust Noon"},{"name":"Gm","units":"mph","$":"Wind Gust Midnight"},{"name":"Hn","units":"%","$":"Screen Relative Humidity Noon"},{"name":"Hm","units":"%","$":"Screen Relative Humidity Midnight"},{"name":"V","units":"","$":"Visibility"},{"name":"D","units":"compass","$":"Wind Direction"},{"name":"S","units":"mph","$":"Wind Speed"},{"name":"U","units":"","$":"Max UV Index"},{"name":"W","units":"","$":"Weather Type"},{"name":"PPd","units":"%","$":"Precipitation Probability Day"},{"name":"PPn","units":"%","$":"Precipitation Probability Night"}]},"DV":{"dataDate":"2024-07-20T09:00:00Z","type":"Forecast","Location":{"i":"3210","lat":"54.5181","lon":"-3.615","name":"ST. BEES HEAD","country":"ENGLAND","continent":"EUROPE","elevation":"124.0","Period":[{"type":"Day","value":"2024-07-20Z","Rep":[{"D":"SSE","Gn":"9","Hn":"93","PPd":"91","S":"7","V":"GO","Dm":"19","FDm":"18","W":"15","U":"3","$":"Day"},{"D":"NW","Gm":"22","Hm":"93","PPn":"80","S":"16","V":"VG","Nm":"11","FNm":"9","W":"12","$":"Night"}]},{"type":"Day","value":"2024-07-21Z","Rep":[{"D":"WSW","Gn":"16","Hn":"78","PPd":"50","S":"11","V":"VG","Dm":"16","FDm":"13","W":"7","U":"5","$":"Day"},{"D":"S","Gm":"29","Hm":"94","PPn":"85","S":"18","V":"MO","Nm":"13","FNm":"11","W":"15","$":"Night"}]},{"type":"Day","value":"2024-07-22Z","Rep":[{"D":"WSW","Gn":"29","Hn":"90","PPd":"55","S":"18","V":"VG","Dm":"17","FDm":"13","W":"12","U":"5","$":"Day"},{"D":"NW","Gm":"20","Hm":"89","PPn":"9","S":"13","V":"VG","Nm":"12","FNm":"11","W":"0","$":"Night"}]},{"type":"Day","value":"2024-07-23Z","Rep":[{"D":"WNW","Gn":"20","Hn":"69","PPd":"5","S":"11","V":"VG","Dm":"18","FDm":"15","W":"3","U":"7","$":"Day"},{"D":"WSW","Gm":"9","Hm":"91","PPn":"4","S":"7","V":"VG","Nm":"11","FNm":"12","W":"7","$":"Night"}]},{"type":"Day","value":"2024-07-24Z","Rep":[{"D":"SSW","Gn":"29","Hn":"74","PPd":"60","S":"16","V":"VG","Dm":"18","FDm":"14","W":"7","U":"6","$":"Day"},{"D":"SSW","Gm":"25","Hm":"95","PPn":"81","S":"16","V":"MO","Nm":"14","FNm":"12","W":"15","$":"Night"}]}]}}}}
I have fomatted this to make it easer to see here:
{"SiteRep":{
"Wx":
{"Param":[
{"name":"FDm","units":"C","$":"Feels Like Day Maximum Temperature"},
{"name":"FNm","units":"C","$":"Feels Like Night Minimum Temperature"},
{"name":"Dm","units":"C","$":"Day Maximum Temperature"},
{"name":"Nm","units":"C","$":"Night Minimum Temperature"},
{"name":"Gn","units":"mph","$":"Wind Gust Noon"},
{"name":"Gm","units":"mph","$":"Wind Gust Midnight"},
{"name":"Hn","units":"%","$":"Screen Relative Humidity Noon"},
{"name":"Hm","units":"%","$":"Screen Relative Humidity Midnight"},
{"name":"V","units":"","$":"Visibility"},
{"name":"D","units":"compass","$":"Wind Direction"},
{"name":"S","units":"mph","$":"Wind Speed"},
{"name":"U","units":"","$":"Max UV Index"},
{"name":"W","units":"","$":"Weather Type"},
{"name":"PPd","units":"%","$":"Precipitation Probability Day"},
{"name":"PPn","units":"%","$":"Precipitation Probability Night"}]},
"DV"
{"dataDate":"2024-07-20T09:00:00Z","type":"Forecast","Location":
{"i":"3210","lat":"54.5181","lon":"-3.615","name":"ST. BEES HEAD","country":"ENGLAND","continent":"EUROPE","elevation":"124.0","Period":[
{"type":"Day","value":"2024-07-20Z","Rep":[
{"D":"SSE","Gn":"9","Hn":"93","PPd":"91","S":"7","V":"GO","Dm":"19","FDm":"18","W":"15","U":"3","$":"Day"},
{"D":"NW","Gm":"22","Hm":"93","PPn":"80","S":"16","V":"VG","Nm":"11","FNm":"9","W":"12","$":"Night"}]},
{"type":"Day","value":"2024-07-21Z","Rep":[
{"D":"WSW","Gn":"16","Hn":"78","PPd":"50","S":"11","V":"VG","Dm":"16","FDm":"13","W":"7","U":"5","$":"Day"},
{"D":"S","Gm":"29","Hm":"94","PPn":"85","S":"18","V":"MO","Nm":"13","FNm":"11","W":"15","$":"Night"}]},
{"type":"Day","value":"2024-07-22Z","Rep":[
{"D":"WSW","Gn":"29","Hn":"90","PPd":"55","S":"18","V":"VG","Dm":"17","FDm":"13","W":"12","U":"5","$":"Day"},
{"D":"NW","Gm":"20","Hm":"89","PPn":"9","S":"13","V":"VG","Nm":"12","FNm":"11","W":"0","$":"Night"}]},
{"type":"Day","value":"2024-07-23Z","Rep":[
{"D":"WNW","Gn":"20","Hn":"69","PPd":"5","S":"11","V":"VG","Dm":"18","FDm":"15","W":"3","U":"7","$":"Day"},
{"D":"WSW","Gm":"9","Hm":"91","PPn":"4","S":"7","V":"VG","Nm":"11","FNm":"12","W":"7","$":"Night"}]},
{"type":"Day","value":"2024-07-24Z","Rep":[
{"D":"SSW","Gn":"29","Hn":"74","PPd":"60","S":"16","V":"VG","Dm":"18","FDm":"14","W":"7","U":"6","$":"Day"},
{"D":"SSW","Gm":"25","Hm":"95","PPn":"81","S":"16","V":"MO","Nm":"14","FNm":"12","W":"15","$":"Night"}]}]}}}}
When I read this is as a panda DataFrame, the frame only contains two rows, Wx and DF. How can I read this JSON into a pandas dataframe (so that "Wx" are the indexes and "DV" are the data values?). The following code does not do what I want it to...
import pandas as pd
df = pd.read_json(<data>)
The result is like this Screenshot of DataFrame
If data contains the dictionary from the question you can try:
columns = {}
for c in data["SiteRep"]["Wx"]["Param"]:
columns[c["name"]] = f'{c["$"]} [{c["units"]}]'
values = []
for row in data["SiteRep"]["DV"]["Location"]["Period"]:
for rep in row["Rep"]:
values.append(
{
"Value": row["value"],
**{columns[k] if k != "$" else k: v for k, v in rep.items()},
}
)
df = pd.DataFrame(values)
print(df.set_index(["Value", "$"]).unstack(1))
Prints:
Wind Direction [compass] Wind Gust Noon [mph] Screen Relative Humidity Noon [%] Precipitation Probability Day [%] Wind Speed [mph] Visibility [] Day Maximum Temperature [C] Feels Like Day Maximum Temperature [C] Weather Type [] Max UV Index [] Wind Gust Midnight [mph] Screen Relative Humidity Midnight [%] Precipitation Probability Night [%] Night Minimum Temperature [C] Feels Like Night Minimum Temperature [C]
$ Day Night Day Night Day Night Day Night Day Night Day Night Day Night Day Night Day Night Day Night Day Night Day Night Day Night Day Night Day Night
Value
2024-07-20Z SSE NW 9 NaN 93 NaN 91 NaN 7 16 GO VG 19 NaN 18 NaN 15 12 3 NaN NaN 22 NaN 93 NaN 80 NaN 11 NaN 9
2024-07-21Z WSW S 16 NaN 78 NaN 50 NaN 11 18 VG MO 16 NaN 13 NaN 7 15 5 NaN NaN 29 NaN 94 NaN 85 NaN 13 NaN 11
2024-07-22Z WSW NW 29 NaN 90 NaN 55 NaN 18 13 VG VG 17 NaN 13 NaN 12 0 5 NaN NaN 20 NaN 89 NaN 9 NaN 12 NaN 11
2024-07-23Z WNW WSW 20 NaN 69 NaN 5 NaN 11 7 VG VG 18 NaN 15 NaN 3 7 7 NaN NaN 9 NaN 91 NaN 4 NaN 11 NaN 12
2024-07-24Z SSW SSW 29 NaN 74 NaN 60 NaN 16 16 VG MO 18 NaN 14 NaN 7 15 6 NaN NaN 25 NaN 95 NaN 81 NaN 14 NaN 12