python - How to convert CSV file to a specific JSON format with nested objects?

Question

I want to populate my json message with data from a CSV file. I want each row to be a "new" json object. I am using Python and will connect the the code to an API once done. Some of the data needs tp be categorized under "personalinfo" and "carinfo" and I need the correct data to be populated under the right category as under the "Expected json message output" below.

This is what I have so far:

import csv
import json

csvfile = open('test.csv', 'r')
jsonfile = open('file.json', 'w')

fieldnames = ("firstname","r", "lastname","r", "age","r", "gender","r",
              "model","r", "price","r", "loan","r")
reader = csv.DictReader(csvfile, fieldnames)
out = json.dumps( [ row for >row in reader ] )
jsonfile.write(out)

I do not know how to add the two "personalinfo" and "carinfo" categories.

Example csv table:

 FirstName  LastName    Age gender  Car model Price loan
    Anna    Andersson   28  F       Audi    A8 40    FALSE

Expected json message output:

{
    "personalinfo": {
        "firstname": "Anna",
        "lastname": "Andersson",
        "age": "28",
        "gender": "F",

        "carinfo": [{
            "car": "Audi",
            "model": "A8"
        }],

        "price": 40,
        "loan": "FALSE"
    }
}

Next record should be a new json object.

Answer 1

You need to convert each row of data in the csv file into a JSON object laid-out the way you described. This can be accomplished by calling a single function which takes the row dictionaries from the csv file using a csv.DictReader and does just that:

import csv
import json

def make_record(row):
    return {
               "personalinfo": {
                   "firstname": row["FirstName"],
                   "lastname": row["LastName"],
                   "age": row["Age"],
                   "gender": row["gender"],
                   "carinfo": [
                       {
                           "car": row["Car"],
                           "model": row["model"]
                       }
                   ],
                   "price": int(row["Price"]),
                   "loan": row["loan"]
               }
           }


with open('csv_test.csv', 'r', newline='') as csvfile, 
     open('json_file.json', 'w') as jsonfile:
    reader = csv.DictReader(csvfile, delimiter='	')
    out = json.dumps([make_record(row) for row in reader], indent=4)
    jsonfile.write(out)

# Show results.
with open('json_file.json', 'r') as jsonfile:
    print('results:')
    print(json.dumps(json.load(jsonfile), indent=4))