python - JSON 到 Python 数据框

Question

我有一个天气 API JSON 数据。我从一个网站得到然后转换了 python 字典

markit_dict = json.loads(response.content)
markit_dict

然后我转了一个Dataframe

但正如您所见，天气栏需要分隔 3 个不同的栏

当我选择每一列来转换数据框时我可以

wh = pd.DataFrame(openwet.iloc[1,6])
wh

    description     icon id  main
0   broken clouds   04d 803 Clouds

上次我尝试放入一个 for 循环来制作数据框，但我做不到

编辑:

openwet = pd.DataFrame(markit_dict)
openwet['weather'].values

输出:

array([ [{u'main': u'Clouds', u'id': 803, u'icon': u'04d', u'description': u'broken clouds'}],
       [{u'main': u'Clouds', u'id': 803, u'icon': u'04d', u'description': u'broken clouds'}],
       [{u'main': u'Clouds', u'id': 804, u'icon': u'04d', u'description': u'overcast clouds'}],
       [{u'main': u'Clouds', u'id': 804, u'icon': u'04d', u'description': u'overcast clouds'}],

我需要将 Dataframe 作为天气列。此外，我将我的 json 数据放入可能有人会找到不同的方式。

url = "http://history.openweathermap.org//storage/debd7a72617dd61b0fc871a2c83fcabf.json"
response = requests.get(url)

response.content

Answer 1

我想你需要json_normalize创建 DataFrame 和列 weather 是首先使用 str[0] 选择列表，然后转换为 numpy array values然后到 DataFrame。 (如果需要重命名列名，可以添加 add_prefix )最后 concat转为原文:

import urllib.request, json

url = "http://history.openweathermap.org//storage/debd7a72617dd61b0fc871a2c83fcabf.json"
#http://stackoverflow.com/a/12965254/2901002
with urllib.request.urlopen(url) as url:
    data = json.loads(url.read().decode())

from pandas.io.json import json_normalize    
df = json_normalize(data)
df1 = pd.DataFrame(df['weather'].str[0].values.tolist()).add_prefix('weather.')
print (df1.head())
  weather.description weather.icon  weather.id weather.main
0       broken clouds          04d         803       Clouds
1       broken clouds          04d         803       Clouds
2     overcast clouds          04d         804       Clouds
3     overcast clouds          04d         804       Clouds
4     overcast clouds          04n         804       Clouds

df = pd.concat([df.drop('weather', 1), df1], axis=1)

print (df.head(10))
   city_id  clouds.all          dt                         dt_iso  \
0  2193733          76  1447462800  2015-11-14 01:00:00 +0000 UTC   
1  2193733          76  1447470000  2015-11-14 03:00:00 +0000 UTC   
2  2193733          88  1447477200  2015-11-14 05:00:00 +0000 UTC   
3  2193733          88  1447480800  2015-11-14 06:00:00 +0000 UTC   
4  2193733          88  1447488000  2015-11-14 08:00:00 +0000 UTC   
5  2193733          88  1447491600  2015-11-14 09:00:00 +0000 UTC   
6  2193733          36  1447495200  2015-11-14 10:00:00 +0000 UTC   
7  2193733          36  1447498800  2015-11-14 11:00:00 +0000 UTC   
8  2193733          88  1447506000  2015-11-14 13:00:00 +0000 UTC   
9  2193733          88  1447513200  2015-11-14 15:00:00 +0000 UTC   

   main.humidity  main.pressure  main.temp  main.temp_max  main.temp_min  \
0             52           1020     291.15         291.15         291.15   
1             45           1018     291.15         291.15         291.15   
2             48           1017     290.15         290.15         290.15   
3             55           1017     289.15         289.15         289.15   
4             58           1017     287.15         287.15         287.15   
5             62           1017     286.15         286.15         286.15   
6             71           1017     286.15         286.15         286.15   
7             71           1016     286.15         286.15         286.15   
8             76           1015     286.15         286.15         286.15   
9             87           1014     287.15         287.15         287.15   

   rain.3h  wind.deg  wind.speed weather.description weather.icon  weather.id  \
0      NaN       250           6       broken clouds          04d         803   
1      NaN       240           7       broken clouds          04d         803   
2      NaN       270           6     overcast clouds          04d         804   
3      NaN       250           4     overcast clouds          04d         804   
4      NaN       310           2     overcast clouds          04n         804   
5      NaN       310           2     overcast clouds          04n         804   
6      NaN       350           1    scattered clouds          03n         802   
7      NaN        10           2    scattered clouds          03n         802   
8      NaN       350           2     overcast clouds          04n         804   
9      NaN       340           3     overcast clouds          04n         804   

  weather.main  
0       Clouds  
1       Clouds  
2       Clouds  
3       Clouds  
4       Clouds  
5       Clouds  
6       Clouds  
7       Clouds  
8       Clouds  
9       Clouds