title: CSV to nested JSON using Python/pandas
body: <p>I'm trying to convert a flat CSV to a nested JSON format. This is my data:</p>
  
  <pre><code># data.csv
  company_id,company_name,income_type,income_amt
  1,"Foobar Inc","royalties",5000000
  2,"ACME Corp","sales",3000000
  2,"ACME Corp","rent",1000000
  </code></pre>
  
  <p>And need to convert to the following JSON structure:</p>
  
  <pre><code>{"data": [{
              "company_id": 1,
              "name": "Foobar Inc",
              "income": ["royalties": 5000000]
          }, 
          {
              "company_id": 2,
              "company_name": "ACME Corp",
              "income": [
                  "sales": 3000000,
                  "rent": 1000000
              ]
          }]
  }
  </code></pre>
  
  <p>But my current code (based on <a href="https://stackoverflow.com/questions/35969611/python-csv-to-nested-json">this</a> and using Python and the pandas library):</p>
  
  <pre><code># script.py
  import json
  import pandas as pd
  
  df = pd.read_csv('data.csv')
  
  def get_nested_rec(key, grp):
  rec = {}
  
      rec['company_id'] = key[0]
      rec['company_name'] = key[1]
  
      for field in ['income_type']:
          income_types = list(grp[field].unique())
          rec['income'] = income_types
  
      return rec
  
  records = []
  
  for key, grp in df.groupby(['company_id','company_name','income_type','income_amt']):
      rec = get_nested_rec(key, grp)
      records.append(rec)
  
  records = dict(data = records)
  
  print(json.dumps(records, indent=4))
  </code></pre>
  
  <p>Outputs this format:</p>
  
  <pre><code>{"data": [
          {
              "company_id": 1,
              "company_name": "Foobar Inc", 
              "income": [
                  "royalties"
              ]
          }, 
          {
              "company_id": 2,
              "company_name": "ACME Corp",
              "income": [
                  "sales"
              ]
          }, 
          {
              "company_id": 2,
              "company_name": "ACME Corp",
              "income": [
                  "rent"
              ]
          }
      ]}
  </code></pre>
  
  <p>Hitting a wall in figuring out how to combine rows with the same <code>company_id</code> into a single object and add in the <code>income_amt</code> values.</p>