python - 如何将 JSON 数据读取到 Python 中

Question

我以 JSON 格式调用了世界卫生组织的一些数据，我想将其读入 Pandas DataFrame。

我从这个页面调用了这个: WHO Measles First Dose Rate

{u'dimension': [{u'display': u'Indicator', u'label': u'GHO'},
  {u'display': u'PUBLISH STATES', u'label': u'PUBLISHSTATE'},
  {u'display': u'Year', u'label': u'YEAR'},
  {u'display': u'WHO region', u'label': u'REGION'},
  {u'display': u'World Bank income group', u'label': u'WORLDBANKINCOMEGROUP'},
  {u'display': u'Country', u'label': u'COUNTRY'}],
 u'fact': [{u'Value': u'25',
   u'dim': {u'COUNTRY': u'Afghanistan',
    u'GHO': u'Measles-containing-vaccine first-dose (MCV1) immunization coverage among 1-year-olds (%)',
    u'PUBLISHSTATE': u'Published',
    u'REGION': u'Eastern Mediterranean',
    u'WORLDBANKINCOMEGROUP': u'Low-income',
    u'YEAR': u'1993'}},
  {u'Value': u'57',
   u'dim': {u'COUNTRY': u'Afghanistan',
    u'GHO': u'Measles-containing-vaccine first-dose (MCV1) immunization coverage among 1-year-olds (%)',
    u'PUBLISHSTATE': u'Published',
    u'REGION': u'Eastern Mediterranean',
    u'WORLDBANKINCOMEGROUP': u'Low-income',
    u'YEAR': u'2013'}},
  {u'Value': u'62',
   u'dim': {u'COUNTRY': u'Angola',
    u'GHO': u'Measles-containing-vaccine first-dose (MCV1) immunization coverage among 1-year-olds (%)',
    u'PUBLISHSTATE': u'Published',
    u'REGION': u'Africa',
    u'WORLDBANKINCOMEGROUP': u'Upper-middle-income',
    u'YEAR': u'1996'}},
  {u'Value': u'94',
   u'dim': {u'COUNTRY': u'Andorra',
    u'GHO': u'Measles-containing-vaccine first-dose (MCV1) immunization coverage among 1-year-olds (%)',
    u'PUBLISHSTATE': u'Published',
    u'REGION': u'Europe',
    u'WORLDBANKINCOMEGROUP': u'High-income',
    u'YEAR': u'2005'}},
  {u'Value': u'34',
   u'dim': {u'COUNTRY': u'United Arab Emirates',
    u'GHO': u'Measles-containing-vaccine first-dose (MCV1) immunization coverage among 1-year-olds (%)',
    u'PUBLISHSTATE': u'Published',
    u'REGION': u'Eastern Mediterranean',
    u'WORLDBANKINCOMEGROUP': u'High-income',
    u'YEAR': u'1980'}},

我已经尝试过

#Setting Up and loading JSON into object ready to turn into dataframe
url = "http://apps.who.int/gho/athena/data/GHO/WHS8_110.json?profile=simple&filter=COUNTRY:*"
response = requests.get(url)
response_json = response.content
json.loads(response_json)
whoDataSetVaccinationRate = json.loads(response_json)    

#Attempt to load JSON Data into Pandas Dataframe
whoDataSetVaccinationRateDF = pd.DataFrame(whoDataSetVaccinationRate['fact']
, columns=['COUNTRY', 'YEAR','REGION'])
whoDataSetVaccinationRateDF

这似乎是这样的 - 但我只在数据框中得到 COUNTRY 和 YEAR 的 NaN 值:

而且我意识到我希望它在数据框中以不同的方式布局 - 而且我不知道如何调用它。这就是我希望我的数据框的外观:

Answer 1

使用json_normalize与 pivot :

from pandas.io.json import json_normalize   
import urllib.request, json 

#https://stackoverflow.com/a/12965254
url = "http://apps.who.int/gho/athena/data/GHO/WHS8_110.json?profile=simple&filter=COUNTRY:*"

with urllib.request.urlopen(url) as url:
    data = json.loads(url.read().decode())

df = json_normalize(data['fact']).pivot('dim.COUNTRY','dim.YEAR','Value').astype(float)
print (df.head())

dim.YEAR     1980  1981  1982  1983  1984  1985  1986  1987  1988  1989  ...   \
dim.COUNTRY                                                              ...    
Afghanistan  11.0   NaN   8.0   9.0  14.0  14.0  14.0  31.0  34.0  22.0  ...    
Albania      90.0  90.0  93.0  96.0  96.0  96.0  96.0  96.0  96.0  96.0  ...    
Algeria       NaN   NaN   NaN   NaN   NaN  68.0  67.0  73.0  81.0  82.0  ...    
Andorra       NaN   NaN   NaN   NaN   NaN   NaN   NaN   NaN   NaN   NaN  ...    
Angola        NaN   NaN   NaN  26.0  35.0  44.0  44.0  55.0  56.0  48.0  ...    

dim.YEAR     2007  2008  2009  2010  2011  2012  2013  2014  2015  2016  
dim.COUNTRY                                                              
Afghanistan  55.0  59.0  60.0  62.0  64.0  59.0  57.0  60.0  62.0  62.0  
Albania      97.0  98.0  97.0  99.0  99.0  98.0  99.0  98.0  97.0  96.0  
Algeria      92.0  88.0  92.0  95.0  95.0  95.0  95.0  95.0  95.0  94.0  
Andorra      94.0  98.0  98.0  99.0  99.0  98.0  95.0  96.0  96.0  97.0  
Angola       71.0  61.0  57.0  72.0  64.0  72.0  66.0  60.0  55.0  49.0  

[5 rows x 37 columns]