title: Using json_normalize to flatten nested json
body: <p>I'm trying to flatten a json file using json_normalize in Python (Pandas), but being a noob at this I always seem to end up in a KeyError.</p>
  
  <p>What I would like to achieve is a DataFrame with all the Plays in a game.</p>
  
  <p>I've tried numerous variants of paths and prefixes, but no success. Googled a lot as well, but I'm still falling short.</p>
  
  <p>What I would like to end up with is a DataFrame like:
  period, time, type, player1, player2, xcord, ycord</p>
  
  <pre><code>import pandas as pd
  import json
  
  with open('PlayByPlay.json') as data_file:    
      data = json.load(data_file)
  
  from pandas.io.json import json_normalize
  records = json_normalize(data)
  
  plays = records['data.game.plays.play'][0]
  plays
  </code></pre>
  
  <p>Would generate</p>
  
  <pre><code>{'aoi': [8470324, 8473449, 8475158, 8475215, 8477499, 8477933],
   'apb': [],
   'as': 0,
   'asog': 0,
   'desc': 'Zack Kassian hit Kyle Okposo',
   'eventid': 7,
   'formalEventId': 'EDM7',
   'hoi': [8471678, 8475178, 8475660, 8476454, 8476457, 8476472],
   'hpb': [],
   'hs': 0,
   'hsog': 0,
   'localtime': '5:12 PM',
   'p1name': 'Zack Kassian',
   'p2name': 'Kyle Okposo',
   'p3name': '',
   'period': 1,
   'pid': 8475178,
   'pid1': 8475178,
   'pid2': 8473449,
   'pid3': '',
   'playername': 'Zack Kassian',
   'strength': 701,
   'sweater': '44',
   'teamid': 22,
   'time': '00:28',
   'type': 'Hit',
   'xcoord': 22,
   'ycoord': 38}
  </code></pre>
  
  <p>Json</p>
  
  <pre><code>     {'data': {'game': {'awayteamid': 7,
     'awayteamname': 'Buffalo Sabres',
     'awayteamnick': 'Sabres',
     'hometeamid': 22,
     'hometeamname': 'Edmonton Oilers',
     'hometeamnick': 'Oilers',
     'plays': {'play': [{'aoi': [8470324,
         8473449,
         8475158,
         8475215,
         8477499,
         8477933],
        'apb': [],
        'as': 0,
        'asog': 0,
        'desc': 'Zack Kassian hit Kyle Okposo',
        'eventid': 7,
        'formalEventId': 'EDM7',
        'hoi': [8471678, 8475178, 8475660, 8476454, 8476457, 8476472],
        'hpb': [],
        'hs': 0,
        'hsog': 0,
        'localtime': '5:12 PM',
        'p1name': 'Zack Kassian',
        'p2name': 'Kyle Okposo',
        'p3name': '',
        'period': 1,
        'pid': 8475178,
        'pid1': 8475178,
        'pid2': 8473449,
        'pid3': '',
        'playername': 'Zack Kassian',
        'strength': 701,
        'sweater': '44',
        'teamid': 22,
        'time': '00:28',
        'type': 'Hit',
        'xcoord': 22,
        'ycoord': 38},
       {'aoi': [8471742, 8475179, 8475215, 8475220, 8475235, 8475728],
        'apb': [],
        'as': 0,
        'asog': 0,
        'desc': 'Jesse Puljujarvi Tip-In saved by Robin Lehner',
        'eventid': 59,
        'formalEventId': 'EDM59',
        'hoi': [8473468, 8474034, 8475660, 8477498, 8477934, 8479344],
        'hpb': [],
        'hs': 0,
        'hsog': 1,
        'localtime': '5:13 PM',
        'p1name': 'Jesse Puljujarvi',
        'p2name': 'Robin Lehner',
        'p3name': '',
        'period': 1,
        'pid': 8479344,
        'pid1': 8479344,
        'pid2': 8475215,
        'pid3': '',
        'playername': 'Jesse Puljujarvi',
        'strength': 701,
        'sweater': '98',
        'teamid': 22,
        'time': '01:32',
        'type': 'Shot',
        'xcoord': 81,
        'ycoord': 3}]}},
    'refreshInterval': 0}}
  </code></pre>