Pandas json_normalize产生令人困惑的“KeyError”消息？

Question

我正在尝试将嵌套的JSON转换为Pandas数据帧。我一直在成功地使用json_normalize，直到遇到某种JSON。我做了一个更小的版本来重现这个问题。

from pandas.io.json import json_normalize

json=[{"events": [{"schedule": {"date": "2015-08-27",
     "location": {"building": "BDC", "floor": 5},
     "ID": 815},
    "group": "A"},
   {"schedule": {"date": "2015-08-27",
     "location": {"building": "BDC", "floor": 5},
 "ID": 816},
"group": "A"}]}]

然后我跑：

json_normalize(json[0],'events',[['schedule','date'],['schedule','location','building'],['schedule','location','floor']])

希望看到这样的东西：

ID      group   schedule.date   schedule.location.building schedule.location.floor  
'815'   'A'     '2015-08-27'            'BDC'                       5
'816'   'A'     '2015-08-27'            'BDC'                       5

但我得到的却是这个错误：

In [2]: json_normalize(json[0],'events',[['schedule','date'],['schedule','location','building'],['schedule','location','floor']])
---------------------------------------------------------------------------
KeyError                                  Traceback (most recent call last)
<ipython-input-2-b588a9e3ef1d> in <module>()
----> 1 json_normalize(json[0],'events',[['schedule','date'],['schedule','location','building'],['schedule','location','floor']])

/Users/logan/Library/Enthought/Canopy_64bit/User/lib/python2.7/site-packages/pandas/io/json.pyc in json_normalize(data, record_path, meta, meta_prefix, record_prefix)
    739                 records.extend(recs)
    740
--> 741     _recursive_extract(data, record_path, {}, level=0)
    742
    743     result = DataFrame(records)

/Users/logan/Library/Enthought/Canopy_64bit/User/lib/python2.7/site-packages/pandas/io/json.pyc in _recursive_extract(data, path, seen_meta, level)
    734                         meta_val = seen_meta[key]
    735                     else:
--> 736                         meta_val = _pull_field(obj, val[level:])
    737                     meta_vals[key].append(meta_val)
    738

/Users/logan/Library/Enthought/Canopy_64bit/User/lib/python2.7/site-packages/pandas/io/json.pyc in _pull_field(js, spec)
    674         if isinstance(spec, list):
    675             for field in spec:
--> 676                 result = result[field]
    677         else:
    678             result = result[spec]

KeyError: 'schedule'

Answer 1

当json的结构不一致时，我得到了KeyError。也就是说，当json中缺少一个嵌套结构时，我得到了KeyError。

https://pandas.pydata.org/pandas-docs/stable/generated/pandas.io.json.json_normalize.html

从pandas文档站点上提到的示例中，如果您使其中一个记录上的嵌套标记（counties）丢失，则会得到一个KeyError。要避免这种情况，您可能必须确保忽略丢失的标记，或者只考虑使用数据填充的嵌套列/标记的记录。