Create multiple JSON files from CSV by grouping categories

November 22, 2021

here is a CSV file :

year,product,price
2021,P01,50
2022,P03,60
2021,P02,30

I’m trying to create a JSON for every year with the list of product like this :

{
  "year": "2021",
  "products": {
      "P02": 30,
      "P01": 50
  },
  "processed": "true"
}

Here is my actual code :

import json

csv = """2021,P01,50
2022,P03,60
2021,P02,30
"""
response = {}

for line in csv.splitlines():
    fields = line.split(",")
    year, product, price = fields[0], fields[1], fields[2:]
    if year not in response:
        response[year] = {}
    response[year][product] = price

print json.dumps(response)

This is the result I get :

{
  "2021": {
    "P02": [
      "30"
    ],
    "P01": [
      "50"
    ]
  },
  "2022": {
    "P03": [
      "60"
    ]
  }
}

Could you help me please to get the result I’m waiting for ?
I start to think that I should maybe use List to make it …

>Solution :

If the same product in the same year does not have different values then you can create a structure like-

{
  "2021": {
    "P0": 50,
    "P1": 30
  },
  "2022": {
    "P0": 60
  }
}

For creating a structure like that

import json

csv = """2021,P01,50
2022,P03,60
2021,P02,30
"""
response = {}
for line in csv.splitlines():
    fields = line.split(",")
    year, product, price = fields[0], fields[1], fields[2:]
    year_response = response.get(year, {})
    year_response[product] = price
    response[year] = year_response
    
# iterate the dictionary and create your custom response
for year, year_response in response.items():
    file_data = {}
    file_date["year"] = year
    file_data["products"] = year_response
    file_data["processed"] = true
    #TODO: add file_data to file now

If the same product in the same year has different values then you can simply use a list instead of a integer value for "P0"