title: How to build a JSON file with nested records from a flat data table?
body: <p>I'm looking for a Python technique to build a nested JSON file from a flat table in a pandas data frame. For example how could a pandas data frame table such as:</p>
  
  <pre><code>teamname  member firstname lastname  orgname         phone        mobile
  0        1       0      John      Doe     Anon  916-555-1234                 
  1        1       1      Jane      Doe     Anon  916-555-4321  916-555-7890   
  2        2       0    Mickey    Moose  Moosers  916-555-0000  916-555-1111   
  3        2       1     Minny    Moose  Moosers  916-555-2222
  </code></pre>
  
  <p>be taken and exported to a JSON that looks like:</p>
  
  <pre><code>{
  "teams": [
  {
  "teamname": "1",
  "members": [
    {
      "firstname": "John", 
      "lastname": "Doe",
      "orgname": "Anon",
      "phone": "916-555-1234",
      "mobile": "",
    },
    {
      "firstname": "Jane",
      "lastname": "Doe",
      "orgname": "Anon",
      "phone": "916-555-4321",
      "mobile": "916-555-7890",
    }
  ]
  },
  {
  "teamname": "2",
  "members": [
    {
      "firstname": "Mickey",
      "lastname": "Moose",
      "orgname": "Moosers",
      "phone": "916-555-0000",
      "mobile": "916-555-1111",
    },
    {
      "firstname": "Minny",
      "lastname": "Moose",
      "orgname": "Moosers",
      "phone": "916-555-2222",
      "mobile": "",
    }
  ]
  }       
  ]
  
  }
  </code></pre>
  
  <p>I have tried doing this by creating a dict of dicts and dumping to JSON.  This is my current code:</p>
  
  <pre><code>data = pandas.read_excel(inputExcel, sheetname = 'SCAT Teams', encoding = 'utf8')
  memberDictTuple = [] 
  
  for index, row in data.iterrows():
      dataRow = row
      rowDict = dict(zip(columnList[2:], dataRow[2:]))
  
      teamRowDict = {columnList[0]:int(dataRow[0])}
  
      memberId = tuple(row[1:2])
      memberId = memberId[0]
  
      teamName = tuple(row[0:1])
      teamName = teamName[0]
  
      memberDict1 = {int(memberId):rowDict}
      memberDict2 = {int(teamName):memberDict1}
  
      memberDictTuple.append(memberDict2)
  
  memberDictTuple = tuple(memberDictTuple)
  formattedJson = json.dumps(memberDictTuple, indent = 4, sort_keys = True)
  print formattedJson
  </code></pre>
  
  <p>This produces the following output.  Each item is nested at the correct level under "teamname" 1 or 2, but records should be nested together if they have the same teamname.  How can I fix this so that teamname 1 and teamname 2 each have 2 records nested within?</p>
  
  <pre><code>[
      {
          "1": {
              "0": {
                  "email": "john.doe@wildlife.net", 
                  "firstname": "John", 
                  "lastname": "Doe", 
                  "mobile": "none", 
                  "orgname": "Anon", 
                  "phone": "916-555-1234"
              }
          }
      }, 
      {
          "1": {
              "1": {
                  "email": "jane.doe@wildlife.net", 
                  "firstname": "Jane", 
                  "lastname": "Doe", 
                  "mobile": "916-555-7890", 
                  "orgname": "Anon", 
                  "phone": "916-555-4321"
              }
          }
      }, 
      {
          "2": {
              "0": {
                  "email": "mickey.moose@wildlife.net", 
                  "firstname": "Mickey", 
                  "lastname": "Moose", 
                  "mobile": "916-555-1111", 
                  "orgname": "Moosers", 
                  "phone": "916-555-0000"
              }
          }
      }, 
      {
          "2": {
              "1": {
                  "email": "minny.moose@wildlife.net", 
                  "firstname": "Minny", 
                  "lastname": "Moose", 
                  "mobile": "none", 
                  "orgname": "Moosers", 
                  "phone": "916-555-2222"
              }
          }
      }
  ]
  </code></pre>