CSV to JSON converter (Grouping by same keys values)

Question

I'm trying to convert csv format to JSON, I googled I'm not getting the correct way to modify it to get the desired one.

This is my code in python:

import csv
import json

def csv_to_json(csvFilePath, jsonFilePath):
    jsonArray = []

    #reading csv (encoding is important)
    with open(csvFilePath, encoding='utf-8') as csvf:
        #csv library function
        csvReader = csv.DictReader(csvf)

        #convert each csv row into python dictionary
        for column in csvReader:
            #add this python dictionary to json array
            jsonArray.append(column)

    #convertion
    with open(jsonFilePath, 'w', encoding='utf-8') as jsonf:
        jsonString = json.dumps(jsonArray, indent=4)
        jsonf.write(jsonString)

csvFilePath='example.csv'
jsonFilePath='output.json'
csv_to_json(csvFilePath, jsonFilePath)

and this is my csv file format:

My actual JSON Output:

[
    {
        "Area": "IT",
        "Employee": "Carl",        
    },
    {
        "Area": "IT",
        "Employee": "Walter",      
    },
    {
        "Area": "Financial Resources",
        "Employee": "Jennifer",      
    }
]

My desired JSON Output:

[
    {
        "Area": "IT",
        "Employee": ["Carl","Walter"],
    },
    {
      "Area": "Financial Resources",
      "Employee": ["Jennifer"],
    }
    
]

Thank you in advance!

user_17829451 · Answer

Something like this should work.

def csv_to_json(csvFilePath, jsonFilePath):
    areas = {}
    with open(csvFilePath, encoding='utf-8') as csvf:
        csvReader = csv.DictReader(csvf)
        for column in csvReader:
            area, employee = column["Area"], column["Employee"] # split values 
            if area in areas:  # add all keys and values to one dictionary
                areas[area].append(employee)
            else:
                areas[area] = [employee]
    # convert dictionary to desired output format.
    jsonArray = [{"Area": k, "Employee": v} for k,v in areas.items()]
    with open(jsonFilePath, 'w', encoding='utf-8') as jsonf:
        jsonString = json.dumps(jsonArray, indent=4)
        jsonf.write(jsonString)

CSV to JSON converter (Grouping by same keys values)

1 Answers