CSV to nested JSON using pandas - Output slightly off

Question

I'm having trouble tailoring this code to meet my needs. I feel like I'm close but I'm falling short. The goal is to create a nested JSON from a csv file. I have the desired output, CSV data, and my current code below. Any help is appreciated.

Current code:

import json
import pandas as pd

df = pd.read_csv('txn_data.csv')

def get_nested_rec(key, grp):
    rec = {}
    rec['date'] = key[0]
    rec['name'] = key[1]
    rec['value'] = key[2]

    for field in ['name','value']:
        rec[field] = list(grp[field].unique())

    return rec

records = []
for key, grp in df.groupby(['date']):
    rec = get_nested_rec(key, grp)
    records.append(rec)

records = dict(data = records)

print(json.dumps(records, indent=4))

CSV data:

date,name,value
1/1/13,Quick Serve,304127
1/1/13,Restaurant,1843286
1/1/13,Retail,239675
1/2/13,Quick Serve,422847
1/2/13,Restaurant,1582848
1/2/13,Retail,394358

Desired output of JSON:

desired_output = [  
   {  
      "date":"2017-01-01",
      "details":[  
         {  
            "name":"Retail",
            "value":9192
         },
         {  
            "name":"Restaurant",
            "value":6753
         },
         {  
            "name":"Quickserve",
            "value":1219
         }
      ]
   },
   {  
      "date":"2017-02-01",
      "details":[  
         {  
            "name":"Retail",
            "value":9192
         },
         {  
            "name":"Restaurant",
            "value":6753
         },
         {  
            "name":"Quickserve",
            "value":1219
         }
      ]
   }
]

What I'm currently getting:

{
    "data": [
        {
            "date": "1", 
            "name": [
                "Automotive", 
                "Durable Goods", 
                "Entertainment", 
                "Food", 
                "Lodging", 
                "Petroleum", 
                "Quick Serve", 
                "Restaurant", 
                "Retail", 
                "Service", 
                "Transportation & Utilities", 
                "Unknown"
            ], 
            "value": [
                91406, 
                9889, 
                172676, 
                358922, 
                63502, 
                1982048, 
                304127, 
                1843286, 
                239675, 
                106462, 
                25924, 
                909
            ]
        }, 
        {
            "date": "1", 
            "name": [
                "Automotive", 
                "Durable Goods", 
                "Entertainment", 
                "Food", 
                "Lodging", 
                "Petroleum", 
                "Quick Serve", 
                "Restaurant", 
                "Retail", 
                "Service", 
                "Transportation & Utilities", 
                "Unknown"
            ], 
            "value": [
                146041, 
                33090, 
                103159, 
                336956, 
                66726, 
                2191346, 
                422847, 
                1582848, 
                394358, 
                339989, 
                49477, 
                494
            ]
        }
    ]
}

O.Suleiman · Accepted Answer · 2017-11-19T08:24:50.490

I would try to solve this task with a simpler approach as follows:

import json
import pandas as pd

df = pd.read_csv('test.csv')
l_data = []
data = {}

for key,grp in df.groupby('date'):
    data['date'] = key
    data['details'] = df.loc[df['date'] == key][['name','value']].to_json(orient='records')
    l_data.append(data)

In [32]:
print(json.dumps(l_data))

Out[32]:
[  
   {  
      "date":"1/2/13",
      "details":[  
         {  
            "name":"Quick Serve",
            "value":422847
         },
         {  
            "name":"Restaurant",
            "value":1582848
         },
         {  
            "name":"Retail",
            "value":394358
         }
      ]
   },
   {  
      "date":"1/2/13",
      "details":[  
         {  
            "name":"Quick Serve",
            "value":422847
         },
         {  
            "name":"Restaurant",
            "value":1582848
         },
         {  
            "name":"Retail",
            "value":394358
         }
      ]
   }
]

score 1 · Answer 2 · answered Nov 19 '17 at 08:38

I've adapted your code to output in the format you requested.

import json
import pandas as pd

df = pd.read_csv('txn_data.csv')

def get_nested_rec(key, grp):
    rec = {}
    rec['date'] = key
    rec['details'] = []

    for index, row in grp.iterrows():
        rec['details'].append({
            'name': row['name'],
            'value': row['value']
        })

    return rec

records = []
for key, grp in df.groupby(['date']):
    rec = get_nested_rec(key, grp)
    records.append(rec)

records = dict(data = records)

print(json.dumps(records, indent=4))

And here is the resulting output:

{
    "data": [
        {
            "date": "1/1/13", 
            "details": [
                {
                    "name": "Quick Serve", 
                    "value": 304127
                }, 
                {
                    "name": "Restaurant", 
                    "value": 1843286
                }, 
                {
                    "name": "Retail", 
                    "value": 239675
                }
            ]
        }, 
        {
            "date": "1/2/13", 
            "details": [
                {
                    "name": "Quick Serve", 
                    "value": 422847
                }, 
                {
                    "name": "Restaurant", 
                    "value": 1582848
                }, 
                {
                    "name": "Retail", 
                    "value": 394358
                }
            ]
        }
    ]
}

CSV to nested JSON using pandas - Output slightly off

2 Answers2