convert csv to json (nested objects)

Question

I am new to python, and I am having to convert a csv file to json in following format:

CSV File :

firstname, lastname, email, customerid, dateadded, customerstatus
john, doe, [email protected], 124,26/11/18,active
jane, doe, [email protected], 125,26/11/18,active

JSON format:

{
    firstname: "John",
    lastname: "Doe",
    emailOrPhone: "[email protected]",
    extraFields: [{
            name: "customerid",
            value: "124"
        },
        {
            name: "dateadded",
            value: "26/11/18"
        },
        {
            name: "dateadded",
            value: "26/11/18"
        }
    ]
}, {
    firstname: "Jane",
    lastname: "Doe",
    emailOrPhone: "[email protected]",
    extraFields: [{
            name: "customerid",
            value: "125"
        },
        {
            name: "dateadded",
            value: "26/11/18"
        },
        {
            name: "dateadded",
            value: "26/11/18"
        }
    ]
}


current code I am using:
import requests
import json
import time
import csv
import json
import glob
import os
import logging


for filename in glob.glob('D:\\api\\Extract.csv'):
    csvfile = os.path.splitext(filename)[0]
    jsonfile = csvfile + '.json'

    with open(csvfile+'.csv') as f:
        reader = csv.DictReader(f)
        rows = list(reader)

    with open(jsonfile, 'w') as f:
        json.dump(rows, f)

url = 'api_url'

with open("D:\\api\\Extract.json", "r") as read_file:
    data = json.load(read_file)

    for item in data:


        headers = {"Authorization" : "key", "Content-Type" : "application/json"}

        r = requests.post(url, data= json.dumps(item), headers= headers)



        logging.basicConfig(level=logging.DEBUG,
                    format='%(asctime)s %(message)s',
                    handlers=[logging.FileHandler("D:\\api\\log_file.log"),
                              logging.StreamHandler()])

I can produce parent values in json, but I am not sure how do I get sub-nodes and parse column name as values and iterate through entire file like that. Above code converts csv to simple json objects, I want to achieve nested objects. I am thinking maybe appending would be the solution, but not sure how to pass column as value and corresponding data as value.

Answer 1

You can use csv.DictReader which gives you access to the column name as you're iterating each row. Then you can build each item as follows:

import json
import csv

primary_fields = ['firstname', 'lastname', 'email']
result = []
with open('mydata.csv') as csv_file:
    reader = csv.DictReader(csv_file, skipinitialspace=True)
    for row in reader:
        d = {k: v for k, v in row.items() if k in primary_fields}
        d['extraFields'] = [{'name': k, 'value': v} for k, v in row.items() if k not in primary_fields]
        result.append(d)

print(json.dumps(result, indent=2))

Output

[
  {
    "firstname": "john",
    "lastname": "doe",
    "email": "[email protected]",
    "extraFields": [
      {
        "name": "customerid",
        "value": "124"
      },
      {
        "name": "dateadded",
        "value": "26/11/18"
      },
      {
        "name": "customerstatus",
        "value": "active"
      }
    ]
  },
  {
    "firstname": "jane",
    "lastname": "doe",
    "email": "[email protected]",
    "extraFields": [
      {
        "name": "customerid",
        "value": "125"
      },
      {
        "name": "dateadded",
        "value": "26/11/18"
      },
      {
        "name": "customerstatus",
        "value": "active"
      }
    ]
  }
]

If you want to set custom field names in your final json (e.g. emailOrPhone for email), you can always manually set field names for d and set the appropriate value