我正在使用 MetOffice Datapoint API 下载 JSON 格式的英国天气数据。然后我想将该 JSON 文件读入 pandas DataFrame。 JSON文件的格式如图所示
{"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"}]}]}}}}
当我读到这是一个panda DataFrame时，该框架仅包含两行，Wx和DF。如何将此 JSON 读入 pandas 数据帧（以便“Wx”是索引，“DV”是数据值？）。下面的代码没有做我想要的事情...
import pandas as pd
df = pd.read_json(<data>)
结果是这样的 DataFrame的截图
JSON 数据的结构被嵌套，这使得直接将其读入 Pandas DataFrame 变得有点棘手。你需要先对数据进行一些预处理。
以下是使用
json
库加载数据，对其进行重构以适应 DataFrame，然后创建 DataFrame 的方法：
import json
import pandas as pd
data = """
{"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"}]}]}}}}
"""
data_dict = json.loads(data)
# 从 'Wx' 部分提取参数信息
params = {param['name']: param['$'] for param in data_dict['SiteRep']['Wx']['Param']}
# 从 'DV' 部分提取数据
records = []
for period in data_dict['SiteRep']['DV']['Location']['Period']:
for rep in period['Rep']:
record = {'date': period['value'], 'day_night': rep.pop('$'), **rep}
records.append(record)
df = pd.DataFrame(records)
# 使用从 'Wx' 部分提取的参数信息重命名列
df = df.rename(columns=params)
print(df)
这将创建一个 DataFrame，其中包含以下列：
- date：日期
- day_night：是白天还是夜晚的数据
- Feels Like Day Maximum Temperature：白天的最高体感温度
- Feels Like Night Minimum Temperature：夜间的最低体感温度
- Day Maximum Temperature：白天的最高温度
- Night Minimum Temperature：夜间的最低温度
- Wind Gust Noon：中午的风力
- Wind Gust Midnight：午夜的风力
- Screen Relative Humidity Noon：中午的相对湿度
- Screen Relative Humidity Midnight：午夜的相对湿度
- Visibility：能见度
- Wind Direction：风向
- Wind Speed：风速
- Max UV Index：紫外线指数
- Weather Type：天气类型
- Precipitation Probability Day：白天下雨的概率
- Precipitation Probability Night：晚上下雨的概率
这个 DataFrame 的结构更易于分析和使用。
标签：python,json,pandas,datapoint From： 78772540