Python - CSV to JSON - nested

Question

I have this csv:

product_id, width, height
14866, 200, 200
14866, 300, 300

I'm using the csv import and json to try to create a json to make a api request.

This is what my python looks like right now:

import csv
import json


json_dict = {}
results = []
with open('sizes.csv',encoding='utf-8-sig') as f:
   for row in csv.DictReader(f,):
        results.append(
            {'id': int(row['product_id']),
             'product': 
             {'product_creative_size_attributes':[{'width':str(row['width']),'height': str(row['height'])}]}})

json_dict["sizes"] = results

output_json = print(json.dumps(json_dict,indent=4))

Which results in this json:

{
    "sizes": [
        {
            "id": 14866,
            "product": {
                "product_creative_size_attributes": [
                    {
                        "width": "200",
                        "height": "200"
                    }
                ]
            }
        },
        {
            "id": 14866,
            "product": {
                "gam_product_creative_size_attributes": [
                    {
                        "width": "300",
                        "height": "300"
                    }
                ]
            }
        }
    ]
}

the json that i'm trying to achieve is to have the sizes for the same product_id to be nested like this:

{
    "sizes": [
        {
            "id": 14866,
            "product": {
                "product_creative_size_attributes": [
                    {
                        "width": "200",
                        "height": "200"
                    },
                    {
                        "width": "300",
                        "height": "300"
                    }
                ]
            }
        }
    ]
}

consider populating the output in 2 stages. First identify unique product_ids, and create {'id': <product_id>, 'product': {'product_creative_size_attributes': []}} for each one. Then loop through each csv row again appending to the particular product's nested array. — FraggaMuffin
– FraggaMuffin, Commented Nov 21, 2019 at 0:22
@Boris yes i want them group by product_id - but I need the "product_creative_size_attributes" to be nested under "product" — jHan
– jHan, Commented Nov 21, 2019 at 0:24
@FraggaMuffin i'm a super python beginner - could you show me like an example python that shows what you are suggesting? — jHan
– jHan, Commented Nov 21, 2019 at 0:30
Note that there are two different values associated with "product" — which one should "win" and end up in the combined result? — martineau
– martineau, Commented Nov 21, 2019 at 0:46

RoadRunner · Accepted Answer · 2019-11-21 08:56:22Z

2

I would first group by product ids with a collections.defaultdict of lists, then assemble your final dictionary afterwards and serialize it to JSON.

Demo:

from csv import DictReader
from collections import defaultdict
from json import dumps

result = {}
products = defaultdict(list)

with open("sizes.csv") as f:

    # make sure headers don't have whitespace, since they are used as dict keys later
    header = [h.strip() for h in next(f).split(",")]

    for row in DictReader(f, fieldnames=header):
        products[int(row["product_id"])].append(
            {"width": int(row["width"].strip()), "height": int(row["height"].strip())}
        )

# transform your grouped products into your desired result
result["sizes"] = [
    {"id": k, "product": {"product_creative_size_attributes": v}}
    for k, v in products.items()
]

output_json = dumps(result, indent=4)

print(output_json)

Output:

{
    "sizes": [
        {
            "id": 14866,
            "product": {
                "product_creative_size_attributes": [
                    {
                        "width": 200,
                        "height": 200
                    },
                    {
                        "width": 300,
                        "height": 300
                    }
                ]
            }
        }
    ]
}

edited Nov 21, 2019 at 8:56

answered Nov 21, 2019 at 0:30

RoadRunner

26.4k6 gold badges46 silver badges77 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

jHan Over a year ago

does collections.defaultdict allow me to create nesting inside of nested items? for example if I wanted: "sizes": [ { "id": 14866, "product": { "product_creative_size_attributes": [ { "width": 200, "height": 200, "companion_sizes": [ {"width": 100, "height": 100}, {"width": 300, "height": 300}] }

cxↄ · Accepted Answer · 2019-11-21 01:40:49Z

import csv
import json

k_product_id  = 0
k_width       = 1
k_height      = 2
json_dict     = {}
results       = []

with open('sizes.csv', encoding='utf-8-sig') as f:
  reader = csv.reader(f)
  # Avoid header
  next(reader, None)
  for row in reader:
    # Find index of existing product id
    existing_id = list(i for i,v in enumerate(results) if v['id'] == int(row[k_product_id]))

    # If there is no existing product id
    if not existing_id:
      results.append(\
                      {\
                        "id": int(row[k_product_id]),\
                        "product":\
                        {\
                          "product_creative_size_attributes":\
                            [\
                              {\
                                "width": int(row[k_width]),\
                                "height": int(row[k_height])\
                              }\
                            ]\
                        }\
                      }\
                    )
    # If there is existing product id
    else:
      results[existing_id[0]]['product']['product_creative_size_attributes']\
        .append(\
                  {\
                    "width": int(row[k_width]),\
                    "height": int(row[k_height])\
                  }\
               )

json_dict['sizes'] = results
output_json = json.dumps(json_dict, indent=4)

Collectives™ on Stack Overflow

Python - CSV to JSON - nested

2 Answers 2

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related