Convert CSV with nested headers to JSON

user_13215995 0
Viewed 1277

So far, I have this code (with help from a tutorial):

import csv, json

csvFilePath = "convertcsv.csv"
jsonFilePath = "newResult.json"

# Read the CSV and add the data to a dictionary...
data = {}

with open(csvFilePath) as csvFile:
    csvReader = csv.DictReader(csvFile)
    for csvRow in csvReader:
        data = csvRow

# Write data to a JSON file...
with open(jsonFilePath, "w") as jsonFile:
    jsonFile.write(json.dumps(data, indent=4))

My desired output is this:

{
  "userID": "string", 
  "username": "string", 
  "age": "string",
  "location": {
    "streetName": "string", 
    "streetNo": "string", 
    "city": "string"
  }
}

I don't know how to represent the "location".

My actual result is this:

{
    "userID": "string", 
    "username": "string", 
    "age": "string",
    "location/streetName": "string", 
    "location/streetNo": "string", 
    "location/city": "string", 
}

How can I seperate streetName, streetNo and city and put them into "location"?

csv parsing python json
3 Answers

Below is a simple script should do what you want. The result will be a json object with the "userID" as keys. Note that, to test deeper nesting, I used a csv file with slightly different headers - but it will work just as well with your original example.

import csv, json

infile = 'convertcsv.csv'
outfile = 'newResult.json'

data = {}

def process(header, value, record):
    key, other = header.partition('/')[::2]
    if other:
        process(other, value, record.setdefault(key, {}))
    else:
        record[key] = value

with open(infile) as stream:
    reader = csv.DictReader(stream)
    for row in reader:
        data[row['userID']] = record = {}
        for header, value in row.items():
            process(header, value, record)

with open(outfile, "w") as stream:
    json.dump(data, stream, indent=4)

INPUT:

userID,username,age,location/street/name,location/street/number,location/city
0,AAA,20,This Street,5,This City
1,BBB,42,That Street,5,That City
2,CCC,34,Other Street,5,Other City

OUTPUT:

{
    "0": {
        "userID": "0",
        "username": "AAA",
        "age": "20",
        "location": {
            "street": {
                "name": "This Street",
                "number": "5"
            },
            "city": "This City"
        }
    },
    "1": {
        "userID": "1",
        "username": "BBB",
        "age": "42",
        "location": {
            "street": {
                "name": "That Street",
                "number": "5"
            },
            "city": "That City"
        }
    },
    "2": {
        "userID": "2",
        "username": "CCC",
        "age": "34",
        "location": {
            "street": {
                "name": "Other Street",
                "number": "5"
            },
            "city": "Other City"
        }
    }
}
user_9844210

I'd add some custom logic to achieve this, note that this is for the first level only, if you want more, you should create a recoursive function:

# Write data to a JSON file...
with open(jsonFilePath, "w") as jsonFile:
    for i, v in data.items():
        if '/' in i:
            parts = i.split('/', 1)
            data[parts[0]] = {parts[1]: v}
            data.pop(i)

    jsonFile.write(json.dumps(data, indent=4))
user_43914010

You can use something like this:

# https://www.geeksforgeeks.org/convert-csv-to-json-using-python/

import csv 
import json 
  
  
# Function to convert a CSV to JSON 
# Takes the file paths as arguments 
def make_json(csvFilePath, jsonFilePath): 
      
    # create a dictionary 
    data = {} 
      
    # Open a csv reader called DictReader 
    with open(csvFilePath, encoding='utf-8') as csvf: 
        csvReader = csv.DictReader(csvf) 
          
        # Convert each row into a dictionary  
        # and add it to data 
        for rows in csvReader: 
              
            # Assuming a column named 'No' to 
            # be the primary key 
            key = rows['No'] 
            data[key] = rows 
  
    # Open a json writer, and use the json.dumps()  
    # function to dump data 
    with open(jsonFilePath, 'w', encoding='utf-8') as jsonf: 
        jsonf.write(json.dumps(data, indent=4)) 
          
# Driver Code 
  
# Decide the two file paths according to your  
# computer system 
csvFilePath = r'Names.csv'
jsonFilePath = r'Names.json'
  
# Call the make_json function 
make_json(csvFilePath, jsonFilePath)

For more information check out https://www.geeksforgeeks.org/convert-csv-to-json-using-python/

Admin1
Related