I am trying to convert the following JSON code in to a csv file and parsing the file via python. I am not sure how do I go beyond "variants" section.
There are various array-lists that I have to read and most of the attributes are optional though.
Would like to see how effectively, I can read each attribute under each list so that I can write them as comma separated record into an output file which serves as input to my ETL processing load.
This is my code:
import json
with open(rb'E:\\ETL\\JaneM\\Test\\JaneM-PFFormattedTest.json', encoding="utf8") as pf:
data = json.load(pf)
for p in data["products"]:
print(p['title'])
print(p['admin_graphql_api_id'])
for v in p["variants"]:
print(v['id'])
#for v in p{"Variants"}:
# print(v{product_id})
The below given snapshot is a JSON input file:
{
"products":[
{
"id":1560417337456,
"title":"\"Embrace The Good\" One Liner",
"body_html":"\u003cp\u003eGive your day a boost with our “Embrace The Good” One Liner. Place this sign on a shelf or photo ledge for a daily encouragement to embrace the good in your life. This two-piece set includes a single acrylic cutout that rests in a wooden block.\u003c\/p\u003e",
"vendor":"Julia Market",
"product_type":"Accents",
"created_at":"2018-11-01T14:11:23-05:00",
"handle":"embrace-the-good-one-liner",
"updated_at":"2020-07-02T09:17:37-05:00",
"published_at":"2019-09-27T14:17:37-05:00",
"template_suffix":"",
"published_scope":"global",
"tags":"accents, all-products, bs-sale-2020, customtext2, gifts, gifts-for-friends, home-menu, netsuite-shipping_category-standard, Office, oos but not discontinued, sail-into-summer, September-sale, signs-decor-promo-2020, spring2019, standard discount collection",
"admin_graphql_api_id":"gid:\/\/shopify\/Product\/1560417337456",
"variants":[
{
"id":15313179869296,
"product_id":1560417337456,
"title":"Default Title",
"price":"15.00",
"sku":"ONELINER-EMBRACE",
"position":1,
"inventory_policy":"deny",
"compare_at_price":null,
"fulfillment_service":"manual",
"inventory_management":"shopify",
"option1":"Default Title",
"option2":null,
"option3":null,
"created_at":"2018-11-01T14:11:23-05:00",
"updated_at":"2020-07-02T09:17:37-05:00",
"taxable":true,
"barcode":"112019151733",
"grams":181,
"image_id":null,
"weight":0.399,
"weight_unit":"lb",
"inventory_item_id":15437614448752,
"inventory_quantity":51,
"old_inventory_quantity":51,
"tax_code":"",
"requires_shipping":true,
"admin_graphql_api_id":"gid:\/\/shopify\/ProductVariant\/15313179869296"
}
],
"options":[
{
"id":2125698269296,
"product_id":1560417337456,
"name":"Title",
"position":1,
"values":[
"Default Title"
]
}
],
"images":[
{
"id":4863201345587,
"product_id":1560417337456,
"position":1,
"created_at":"2019-01-18T09:59:41-06:00",
"updated_at":"2019-01-18T10:00:28-06:00",
"alt":"acrylic quote sign with wooden stand that says \"embrace the good\"",
"width":2048,
"height":2048,
"src":"https:\/\/cdn.shopify.com\/s\/files\/1\/0207\/8508\/products\/embrace-the-good-one-liner-ONELINER-EMBRACE.jpg?v=1547827228",
"variant_ids":[
],
"admin_graphql_api_id":"gid:\/\/shopify\/ProductImage\/4863201345587"
},
{
"id":4863216975923,
"product_id":1560417337456,
"position":2,
"created_at":"2019-01-18T10:09:15-06:00",
"updated_at":"2019-01-18T10:09:23-06:00",
"alt":"acrylic and wood quote sign",
"width":2048,
"height":2048,
"src":"https:\/\/cdn.shopify.com\/s\/files\/1\/0207\/8508\/products\/acrylic-and-wood-one-liner.jpg?v=1547827763",
"variant_ids":[
],
"admin_graphql_api_id":"gid:\/\/shopify\/ProductImage\/4863216975923"
}
],
"image":{
"id":4863201345587,
"product_id":1560417337456,
"position":1,
"created_at":"2019-01-18T09:59:41-06:00",
"updated_at":"2019-01-18T10:00:28-06:00",
"alt":"acrylic quote sign with wooden stand that says \"embrace the good\"",
"width":2048,
"height":2048,
"src":"https:\/\/cdn.shopify.com\/s\/files\/1\/0207\/8508\/products\/embrace-the-good-one-liner-ONELINER-EMBRACE.jpg?v=1547827228",
"variant_ids":[
],
"admin_graphql_api_id":"gid:\/\/shopify\/ProductImage\/4863201345587"
}
},
{
"id":1560417370224,
"title":"\"Be Brave\" One Liner",
"body_html":"\u003cp\u003eEncourage yourself or a loved one to move past fear with our “Be Brave” One Liner. This two-piece set includes a single acrylic cutout of the phrase “Be Brave” that rests in a wooden block. Style this sign on a shelf, desk or photo ledge for daily encouragement.\u003c\/p\u003e",
"vendor":"Magnolia Market",
"product_type":"Accents",
"created_at":"2018-11-01T14:11:23-05:00",
"handle":"be-brave-one-liner",
"updated_at":"2020-07-02T08:57:29-05:00",
"published_at":"2019-09-27T14:17:37-05:00",
"template_suffix":"",
"published_scope":"global",
"tags":"accents, all-products, bs-sale-2020, customtext2, madeinUSA, netsuite-shipping_category-standard, Office, oos but not discontinued, sail-into-summer, September-sale, signs-decor-promo-2020, spring2019, standard discount collection",
"admin_graphql_api_id":"gid:\/\/shopify\/Product\/1560417370224",
"variants":[
{
"id":15313179934832,
"product_id":1560417370224,
"title":"Default Title",
"price":"15.00",
"sku":"ONELINER-BEBRAVE",
"position":1,
"inventory_policy":"deny",
"compare_at_price":null,
"fulfillment_service":"manual",
"inventory_management":"shopify",
"option1":"Default Title",
"option2":null,
"option3":null,
"created_at":"2018-11-01T14:11:23-05:00",
"updated_at":"2020-07-02T08:57:29-05:00",
"taxable":true,
"barcode":"112019151734",
"grams":181,
"image_id":null,
"weight":0.399,
"weight_unit":"lb",
"inventory_item_id":15437614514288,
"inventory_quantity":287,
"old_inventory_quantity":287,
"tax_code":"",
"requires_shipping":true,
"admin_graphql_api_id":"gid:\/\/shopify\/ProductVariant\/15313179934832"
}
],
"options":[
{
"id":2125698302064,
"product_id":1560417370224,
"name":"Title",
"position":1,
"values":[
"Default Title"
]
}
],
"images":[
{
"id":4863203147827,
"product_id":1560417370224,
"position":1,
"created_at":"2019-01-18T10:01:42-06:00",
"updated_at":"2019-01-18T10:01:56-06:00",
"alt":"acrylic quote sign with wooden stand that says \"be brave\"",
"width":2048,
"height":2048,
"src":"https:\/\/cdn.shopify.com\/s\/files\/1\/0207\/8508\/products\/be-brave-one-liner-ONELINER-BEBRAVE.jpg?v=1547827316",
"variant_ids":[
],
"admin_graphql_api_id":"gid:\/\/shopify\/ProductImage\/4863203147827"
},
{
"id":4863214190643,
"product_id":1560417370224,
"position":2,
"created_at":"2019-01-18T10:08:11-06:00",
"updated_at":"2019-01-18T10:08:19-06:00",
"alt":"acrylic and wood quote sign",
"width":2048,
"height":2048,
"src":"https:\/\/cdn.shopify.com\/s\/files\/1\/0207\/8508\/products\/avrylic-and-wood-one-liner_706f5afb-14ef-4de7-b059-94db84baf12c.jpg?v=1547827699",
"variant_ids":[
],
"admin_graphql_api_id":"gid:\/\/shopify\/ProductImage\/4863214190643"
}
],
"image":{
"id":4863203147827,
"product_id":1560417370224,
"position":1,
"created_at":"2019-01-18T10:01:42-06:00",
"updated_at":"2019-01-18T10:01:56-06:00",
"alt":"acrylic quote sign with wooden stand that says \"be brave\"",
"width":2048,
"height":2048,
"src":"https:\/\/cdn.shopify.com\/s\/files\/1\/0207\/8508\/products\/be-brave-one-liner-ONELINER-BEBRAVE.jpg?v=1547827316",
"variant_ids":[
],
"admin_graphql_api_id":"gid:\/\/shopify\/ProductImage\/4863203147827"
}
}
,
{
"id":6332761412,
"title":"\"Aww Shucks\" Greeting Card (INACTIVE)",
"body_html":"",
"vendor":"AH",
"product_type":"Books + Paper",
"created_at":"2016-07-29T09:07:36-05:00",
"handle":"aww-shucks-greeting-card",
"updated_at":"2020-06-08T00:05:37-05:00",
"published_at":null,
"template_suffix":"",
"published_scope":"web",
"tags":"lsob-10, standard discount collection",
"admin_graphql_api_id":"gid:\/\/shopify\/Product\/6332761412",
"variants":[
{
"id":21828312900,
"product_id":6332761412,
"title":"Default Title",
"price":"4.50",
"sku":"BD1615 (INACTIVE)",
"position":1,
"inventory_policy":"deny",
"compare_at_price":null,
"fulfillment_service":"manual",
"inventory_management":"shopify",
"option1":"Default Title",
"option2":null,
"option3":null,
"created_at":"2016-07-29T09:07:36-05:00",
"updated_at":"2020-03-10T15:11:49-05:00",
"taxable":true,
"barcode":null,
"grams":0,
"image_id":null,
"weight":0.0,
"weight_unit":"g",
"inventory_item_id":16074523204,
"inventory_quantity":0,
"old_inventory_quantity":0,
"tax_code":"",
"requires_shipping":true,
"admin_graphql_api_id":"gid:\/\/shopify\/ProductVariant\/21828312900"
}
],
"options":[
{
"id":7615401988,
"product_id":6332761412,
"name":"Title",
"position":1,
"values":[
"Default Title"
]
}
],
"images":[
{
"id":14530688388,
"product_id":6332761412,
"position":1,
"created_at":"2016-07-29T09:07:38-05:00",
"updated_at":"2016-07-29T09:07:38-05:00",
"alt":null,
"width":140,
"height":180,
"src":"https:\/\/cdn.shopify.com\/s\/files\/1\/0207\/8508\/products\/Screenshot_2016-07-28_16.24.39.png?v=1469801258",
"variant_ids":[
],
"admin_graphql_api_id":"gid:\/\/shopify\/ProductImage\/14530688388"
}
],
"image":{
"id":14530688388,
"product_id":6332761412,
"position":1,
"created_at":"2016-07-29T09:07:38-05:00",
"updated_at":"2016-07-29T09:07:38-05:00",
"alt":null,
"width":140,
"height":180,
"src":"https:\/\/cdn.shopify.com\/s\/files\/1\/0207\/8508\/products\/Screenshot_2016-07-28_16.24.39.png?v=1469801258",
"variant_ids":[
],
"admin_graphql_api_id":"gid:\/\/shopify\/ProductImage\/14530688388"
}
}
,
{
"id":1055314692,
"title":"\"It is Well\" Shiplap Sign",
"body_html":"\u003cp\u003eOur \"It is Well\" Shiplap Sign comes in two sizes and features the title of one of the most influential hymns ever written, which was originally published in the 1800s. Made right here in Texas, the sign is distressed and weathered to give it character of a relic that has endured the test of time.\u003c\/p\u003e",
"vendor":"8FD",
"product_type":"Wall Decor",
"created_at":"2015-08-05T15:10:42-05:00",
"handle":"it-is-well-ship-lap-sign",
"updated_at":"2020-06-28T02:59:21-05:00",
"published_at":null,
"template_suffix":"",
"published_scope":"web",
"tags":"discontinued:number:1, flow:oos, ios-hidden, lsob-10, netsuite-shipping_category-standard, standard discount collection, unpublish-app",
"admin_graphql_api_id":"gid:\/\/shopify\/Product\/1055314692",
"variants":[
{
"id":44383342724,
"product_id":1055314692,
"title":"Large",
"price":"75.00",
"sku":"WellSign15",
"position":1,
"inventory_policy":"deny",
"compare_at_price":"125.00",
"fulfillment_service":"manual",
"inventory_management":"shopify",
"option1":"Large",
"option2":null,
"option3":null,
"created_at":"2017-07-03T14:56:06-05:00",
"updated_at":"2020-06-28T02:59:21-05:00",
"taxable":true,
"barcode":"132017039159",
"grams":4536,
"image_id":null,
"weight":10.0002,
"weight_unit":"lb",
"inventory_item_id":32807497988,
"inventory_quantity":0,
"old_inventory_quantity":0,
"tax_code":"",
"requires_shipping":true,
"admin_graphql_api_id":"gid:\/\/shopify\/ProductVariant\/44383342724"
},
{
"id":4047331332,
"product_id":1055314692,
"title":"Small",
"price":"46.80",
"sku":"WellSign15-S",
"position":2,
"inventory_policy":"deny",
"compare_at_price":"78.00",
"fulfillment_service":"manual",
"inventory_management":"shopify",
"option1":"Small",
"option2":null,
"option3":null,
"created_at":"2015-08-05T15:10:42-05:00",
"updated_at":"2020-06-28T02:59:21-05:00",
"taxable":true,
"barcode":"132017039160",
"grams":4536,
"image_id":27157975492,
"weight":10.0002,
"weight_unit":"lb",
"inventory_item_id":6752772100,
"inventory_quantity":0,
"old_inventory_quantity":0,
"tax_code":"",
"requires_shipping":true,
"admin_graphql_api_id":"gid:\/\/shopify\/ProductVariant\/4047331332"
}
],
"options":[
{
"id":1325397124,
"product_id":1055314692,
"name":"Size",
"position":1,
"values":[
"Large",
"Small"
]
}
],
"images":[
{
"id":27157975492,
"product_id":1055314692,
"position":1,
"created_at":"2017-07-03T14:59:42-05:00",
"updated_at":"2019-11-22T16:46:38-06:00",
"alt":"\"It is Well\" Shiplap Sign",
"width":600,
"height":600,
"src":"https:\/\/cdn.shopify.com\/s\/files\/1\/0207\/8508\/products\/001_PRD_2017_06_20-117_grande_22e04f4b-e74d-4a12-9f74-0c91f25dc633.jpg?v=1574462798",
"variant_ids":[
4047331332
],
"admin_graphql_api_id":"gid:\/\/shopify\/ProductImage\/27157975492"
}
],
"image":{
"id":27157975492,
"product_id":1055314692,
"position":1,
"created_at":"2017-07-03T14:59:42-05:00",
"updated_at":"2019-11-22T16:46:38-06:00",
"alt":"\"It is Well\" Shiplap Sign",
"width":600,
"height":600,
"src":"https:\/\/cdn.shopify.com\/s\/files\/1\/0207\/8508\/products\/001_PRD_2017_06_20-117_grande_22e04f4b-e74d-4a12-9f74-0c91f25dc633.jpg?v=1574462798",
"variant_ids":[
4047331332
],
"admin_graphql_api_id":"gid:\/\/shopify\/ProductImage\/27157975492"
}
}
]
}
Following error is thrown while running the below given code with full volume of JSON data file
PS C:\Users\marunachalam> & python c:/Users/marunachalam/Downloads/Extract-ProductFeed.py
Traceback (most recent call last):
File "c:/Users/marunachalam/Downloads/Extract-ProductFeed.py", line 16, in <module>
df = df.apply(lambda x: x.explode()).reset_index(drop=True)
File "C:\Users\marunachalam\AppData\Local\Programs\Python\Python37-32\lib\site-packages\pandas\core\frame.py", line 6878, in apply
return op.get_result()
File "C:\Users\marunachalam\AppData\Local\Programs\Python\Python37-32\lib\site-packages\pandas\core\apply.py", line 186, in get_result
return self.apply_standard()
File "C:\Users\marunachalam\AppData\Local\Programs\Python\Python37-32\lib\site-packages\pandas\core\apply.py", line 316, in apply_standard
return self.wrap_results(results, res_index)
File "C:\Users\marunachalam\AppData\Local\Programs\Python\Python37-32\lib\site-packages\pandas\core\apply.py", line 354, in wrap_results
return self.wrap_results_for_axis(results, res_index)
File "C:\Users\marunachalam\AppData\Local\Programs\Python\Python37-32\lib\site-packages\pandas\core\apply.py", line 396, in wrap_results_for_axis
result = self.obj._constructor(data=results)
File "C:\Users\marunachalam\AppData\Local\Programs\Python\Python37-32\lib\site-packages\pandas\core\frame.py", line 435, in __init__
mgr = init_dict(data, index, columns, dtype=dtype)
File "C:\Users\marunachalam\AppData\Local\Programs\Python\Python37-32\lib\site-packages\pandas\core\internals\construction.py", line 254, in init_dict
return arrays_to_mgr(arrays, data_names, index, columns, dtype=dtype)
File "C:\Users\marunachalam\AppData\Local\Programs\Python\Python37-32\lib\site-packages\pandas\core\internals\construction.py", line 69, in arrays_to_mgr
arrays = _homogenize(arrays, index, dtype)
File "C:\Users\marunachalam\AppData\Local\Programs\Python\Python37-32\lib\site-packages\pandas\core\internals\construction.py", line 311, in _homogenize
val = val.reindex(index, copy=False)
File "C:\Users\marunachalam\AppData\Local\Programs\Python\Python37-32\lib\site-packages\pandas\core\series.py", line 4030, in reindex
return super().reindex(index=index, **kwargs)
File "C:\Users\marunachalam\AppData\Local\Programs\Python\Python37-32\lib\site-packages\pandas\core\generic.py", line 4544, in reindex
axes, level, limit, tolerance, method, fill_value, copy
File "C:\Users\marunachalam\AppData\Local\Programs\Python\Python37-32\lib\site-packages\pandas\core\generic.py", line 4567, in _reindex_axes
allow_dups=False,
File "C:\Users\marunachalam\AppData\Local\Programs\Python\Python37-32\lib\site-packages\pandas\core\generic.py", line 4613, in _reindex_with_indexers
copy=copy,
File "C:\Users\marunachalam\AppData\Local\Programs\Python\Python37-32\lib\site-packages\pandas\core\internals\managers.py", line 1251, in reindex_indexer
self.axes[axis]._can_reindex(indexer)
File "C:\Users\marunachalam\AppData\Local\Programs\Python\Python37-32\lib\site-packages\pandas\core\indexes\base.py", line 3099, in _can_reindex
raise ValueError("cannot reindex from a duplicate axis")
ValueError: cannot reindex from a duplicate axis
flat_data_dict = flatten_json(data)dict which can be saved to csv by using SO: How do I write a Python dictionary to a csv file?def flatten_json(nested_json: dict, exclude: list=[''], sep: str='_') -> dict:
"""
Flatten a list of nested dicts.
"""
out = dict()
def flatten(x: (list, dict, str), name: str='', exclude=exclude):
if type(x) is dict:
for a in x:
if a not in exclude:
flatten(x[a], f'{name}{a}{sep}')
elif type(x) is list:
i = 0
for a in x:
flatten(a, f'{name}{i}{sep}')
i += 1
else:
out[name[:-1]] = x
flatten(nested_json)
return out
df = pd.DataFrame([flatten_json(x) for x in data['products']])
id title body_html vendor product_type created_at handle updated_at published_at template_suffix published_scope tags admin_graphql_api_id variants_0_id variants_0_product_id variants_0_title variants_0_price variants_0_sku variants_0_position variants_0_inventory_policy variants_0_compare_at_price variants_0_fulfillment_service variants_0_inventory_management variants_0_option1 variants_0_option2 variants_0_option3 variants_0_created_at variants_0_updated_at variants_0_taxable variants_0_barcode variants_0_grams variants_0_image_id variants_0_weight variants_0_weight_unit variants_0_inventory_item_id variants_0_inventory_quantity variants_0_old_inventory_quantity variants_0_tax_code variants_0_requires_shipping variants_0_admin_graphql_api_id options_0_id options_0_product_id options_0_name options_0_position options_0_values_0 images_0_id images_0_product_id images_0_position images_0_created_at images_0_updated_at images_0_alt images_0_width images_0_height images_0_src images_0_admin_graphql_api_id images_1_id images_1_product_id images_1_position images_1_created_at images_1_updated_at images_1_alt images_1_width images_1_height images_1_src images_1_admin_graphql_api_id image_id image_product_id image_position image_created_at image_updated_at image_alt image_width image_height image_src image_admin_graphql_api_id variants_1_id variants_1_product_id variants_1_title variants_1_price variants_1_sku variants_1_position variants_1_inventory_policy variants_1_compare_at_price variants_1_fulfillment_service variants_1_inventory_management variants_1_option1 variants_1_option2 variants_1_option3 variants_1_created_at variants_1_updated_at variants_1_taxable variants_1_barcode variants_1_grams variants_1_image_id variants_1_weight variants_1_weight_unit variants_1_inventory_item_id variants_1_inventory_quantity variants_1_old_inventory_quantity variants_1_tax_code variants_1_requires_shipping variants_1_admin_graphql_api_id options_0_values_1 images_0_variant_ids_0 image_variant_ids_0
0 1560417337456 "Embrace The Good" One Liner <p>Give your day a boost with our “Embrace The Good” One Liner. Place this sign on a shelf or photo ledge for a daily encouragement to embrace the good in your life. This two-piece set includes a single acrylic cutout that rests in a wooden block.<\/p> Julia Market Accents 2018-11-01T14:11:23-05:00 embrace-the-good-one-liner 2020-07-02T09:17:37-05:00 2019-09-27T14:17:37-05:00 global accents, all-products, bs-sale-2020, customtext2, gifts, gifts-for-friends, home-menu, netsuite-shipping_category-standard, Office, oos but not discontinued, sail-into-summer, September-sale, signs-decor-promo-2020, spring2019, standard discount collection gid:\/\/shopify\/Product\/1560417337456 15313179869296 1560417337456 Default Title 15.00 ONELINER-EMBRACE 1 deny None manual shopify Default Title None None 2018-11-01T14:11:23-05:00 2020-07-02T09:17:37-05:00 True 112019151733 181 None 0.3990 lb 15437614448752 51 51 True gid:\/\/shopify\/ProductVariant\/15313179869296 2125698269296 1560417337456 Title 1 Default Title 4863201345587 1560417337456 1 2019-01-18T09:59:41-06:00 2019-01-18T10:00:28-06:00 acrylic quote sign with wooden stand that says "embrace the good" 2048 2048 https:\/\/cdn.shopify.com\/s\/files\/1\/0207\/8508\/products\/embrace-the-good-one-liner-ONELINER-EMBRACE.jpg?v=1547827228 gid:\/\/shopify\/ProductImage\/4863201345587 4.863217e+12 1.560417e+12 2.0 2019-01-18T10:09:15-06:00 2019-01-18T10:09:23-06:00 acrylic and wood quote sign 2048.0 2048.0 https:\/\/cdn.shopify.com\/s\/files\/1\/0207\/8508\/products\/acrylic-and-wood-one-liner.jpg?v=1547827763 gid:\/\/shopify\/ProductImage\/4863216975923 4863201345587 1560417337456 1 2019-01-18T09:59:41-06:00 2019-01-18T10:00:28-06:00 acrylic quote sign with wooden stand that says "embrace the good" 2048 2048 https:\/\/cdn.shopify.com\/s\/files\/1\/0207\/8508\/products\/embrace-the-good-one-liner-ONELINER-EMBRACE.jpg?v=1547827228 gid:\/\/shopify\/ProductImage\/4863201345587 NaN NaN NaN NaN NaN NaN NaN NaN NaN NaN NaN NaN NaN NaN NaN NaN NaN NaN NaN NaN NaN NaN NaN NaN NaN NaN NaN NaN NaN NaN
1 1560417370224 "Be Brave" One Liner <p>Encourage yourself or a loved one to move past fear with our “Be Brave” One Liner. This two-piece set includes a single acrylic cutout of the phrase “Be Brave” that rests in a wooden block. Style this sign on a shelf, desk or photo ledge for daily encouragement.<\/p> Magnolia Market Accents 2018-11-01T14:11:23-05:00 be-brave-one-liner 2020-07-02T08:57:29-05:00 2019-09-27T14:17:37-05:00 global accents, all-products, bs-sale-2020, customtext2, madeinUSA, netsuite-shipping_category-standard, Office, oos but not discontinued, sail-into-summer, September-sale, signs-decor-promo-2020, spring2019, standard discount collection gid:\/\/shopify\/Product\/1560417370224 15313179934832 1560417370224 Default Title 15.00 ONELINER-BEBRAVE 1 deny None manual shopify Default Title None None 2018-11-01T14:11:23-05:00 2020-07-02T08:57:29-05:00 True 112019151734 181 None 0.3990 lb 15437614514288 287 287 True gid:\/\/shopify\/ProductVariant\/15313179934832 2125698302064 1560417370224 Title 1 Default Title 4863203147827 1560417370224 1 2019-01-18T10:01:42-06:00 2019-01-18T10:01:56-06:00 acrylic quote sign with wooden stand that says "be brave" 2048 2048 https:\/\/cdn.shopify.com\/s\/files\/1\/0207\/8508\/products\/be-brave-one-liner-ONELINER-BEBRAVE.jpg?v=1547827316 gid:\/\/shopify\/ProductImage\/4863203147827 4.863214e+12 1.560417e+12 2.0 2019-01-18T10:08:11-06:00 2019-01-18T10:08:19-06:00 acrylic and wood quote sign 2048.0 2048.0 https:\/\/cdn.shopify.com\/s\/files\/1\/0207\/8508\/products\/avrylic-and-wood-one-liner_706f5afb-14ef-4de7-b059-94db84baf12c.jpg?v=1547827699 gid:\/\/shopify\/ProductImage\/4863214190643 4863203147827 1560417370224 1 2019-01-18T10:01:42-06:00 2019-01-18T10:01:56-06:00 acrylic quote sign with wooden stand that says "be brave" 2048 2048 https:\/\/cdn.shopify.com\/s\/files\/1\/0207\/8508\/products\/be-brave-one-liner-ONELINER-BEBRAVE.jpg?v=1547827316 gid:\/\/shopify\/ProductImage\/4863203147827 NaN NaN NaN NaN NaN NaN NaN NaN NaN NaN NaN NaN NaN NaN NaN NaN NaN NaN NaN NaN NaN NaN NaN NaN NaN NaN NaN NaN NaN NaN
2 6332761412 "Aww Shucks" Greeting Card (INACTIVE) AH Books + Paper 2016-07-29T09:07:36-05:00 aww-shucks-greeting-card 2020-06-08T00:05:37-05:00 None web lsob-10, standard discount collection gid:\/\/shopify\/Product\/6332761412 21828312900 6332761412 Default Title 4.50 BD1615 (INACTIVE) 1 deny None manual shopify Default Title None None 2016-07-29T09:07:36-05:00 2020-03-10T15:11:49-05:00 True None 0 None 0.0000 g 16074523204 0 0 True gid:\/\/shopify\/ProductVariant\/21828312900 7615401988 6332761412 Title 1 Default Title 14530688388 6332761412 1 2016-07-29T09:07:38-05:00 2016-07-29T09:07:38-05:00 None 140 180 https:\/\/cdn.shopify.com\/s\/files\/1\/0207\/8508\/products\/Screenshot_2016-07-28_16.24.39.png?v=1469801258 gid:\/\/shopify\/ProductImage\/14530688388 NaN NaN NaN NaN NaN NaN NaN NaN NaN NaN 14530688388 6332761412 1 2016-07-29T09:07:38-05:00 2016-07-29T09:07:38-05:00 None 140 180 https:\/\/cdn.shopify.com\/s\/files\/1\/0207\/8508\/products\/Screenshot_2016-07-28_16.24.39.png?v=1469801258 gid:\/\/shopify\/ProductImage\/14530688388 NaN NaN NaN NaN NaN NaN NaN NaN NaN NaN NaN NaN NaN NaN NaN NaN NaN NaN NaN NaN NaN NaN NaN NaN NaN NaN NaN NaN NaN NaN
3 1055314692 "It is Well" Shiplap Sign <p>Our "It is Well" Shiplap Sign comes in two sizes and features the title of one of the most influential hymns ever written, which was originally published in the 1800s. Made right here in Texas, the sign is distressed and weathered to give it character of a relic that has endured the test of time.<\/p> 8FD Wall Decor 2015-08-05T15:10:42-05:00 it-is-well-ship-lap-sign 2020-06-28T02:59:21-05:00 None web discontinued:number:1, flow:oos, ios-hidden, lsob-10, netsuite-shipping_category-standard, standard discount collection, unpublish-app gid:\/\/shopify\/Product\/1055314692 44383342724 1055314692 Large 75.00 WellSign15 1 deny 125.00 manual shopify Large None None 2017-07-03T14:56:06-05:00 2020-06-28T02:59:21-05:00 True 132017039159 4536 None 10.0002 lb 32807497988 0 0 True gid:\/\/shopify\/ProductVariant\/44383342724 1325397124 1055314692 Size 1 Large 27157975492 1055314692 1 2017-07-03T14:59:42-05:00 2019-11-22T16:46:38-06:00 "It is Well" Shiplap Sign 600 600 https:\/\/cdn.shopify.com\/s\/files\/1\/0207\/8508\/products\/001_PRD_2017_06_20-117_grande_22e04f4b-e74d-4a12-9f74-0c91f25dc633.jpg?v=1574462798 gid:\/\/shopify\/ProductImage\/27157975492 NaN NaN NaN NaN NaN NaN NaN NaN NaN NaN 27157975492 1055314692 1 2017-07-03T14:59:42-05:00 2019-11-22T16:46:38-06:00 "It is Well" Shiplap Sign 600 600 https:\/\/cdn.shopify.com\/s\/files\/1\/0207\/8508\/products\/001_PRD_2017_06_20-117_grande_22e04f4b-e74d-4a12-9f74-0c91f25dc633.jpg?v=1574462798 gid:\/\/shopify\/ProductImage\/27157975492 4.047331e+09 1.055315e+09 Small 46.80 WellSign15-S 2.0 deny 78.00 manual shopify Small NaN NaN 2015-08-05T15:10:42-05:00 2020-06-28T02:59:21-05:00 True 132017039160 4536.0 2.715798e+10 10.0002 lb 6.752772e+09 0.0 0.0 True gid:\/\/shopify\/ProductVariant\/4047331332 Small 4.047331e+09 4.047331e+09
pandas.json_normalize to parse the dictlists of dicts, which need to be exploded with pandas.Series.explode, so each dict is a separate row.
lists in the columns are of equal length.
lists are not the same length, ValueError: cannot reindex from a duplicate axis occurs.json_normalize on any additional columns of dictspandas.concat to combine df and the dataframes in normalized, into a single dataframe.import pandas as pd
# read data as shown in the question
df = pd.json_normalize(data, 'products')
# expand any lists; only works if the list in the columns are all of equal length
df = df.apply(lambda x: x.explode()).reset_index(drop=True)
# normalize the columns of dicts into a list of dataframe
columns_of_dicts = ['variants', 'options', 'images']
normalize = [pd.json_normalize(df[col]) for col in columns_of_dicts]
# combine all of the dataframes into a single dataframe
df = pd.concat([df] + normalize, axis=1).drop(columns=columns_of_dicts).reset_index(drop=True)
# explode any new lists; in this case variant_ids
df = df.apply(lambda x: x.explode()).reset_index(drop=True)
# save to a csv
df.to_csv('my_json_data.csv', index=False)
display(df) id title body_html vendor product_type created_at handle updated_at published_at template_suffix published_scope tags admin_graphql_api_id image.id image.product_id image.position image.created_at image.updated_at image.alt image.width image.height image.src image.variant_ids image.admin_graphql_api_id id product_id title price sku position inventory_policy compare_at_price fulfillment_service inventory_management option1 option2 option3 created_at updated_at taxable barcode grams image_id weight weight_unit inventory_item_id inventory_quantity old_inventory_quantity tax_code requires_shipping admin_graphql_api_id id product_id name position values id product_id position created_at updated_at alt width height src variant_ids admin_graphql_api_id
0 abab6332761412 "Aww Scks" Greeting Card (INACTIVE) AH Books + Paper 2016-07-29T09:07:36-05:00 aww-shucks-greeting-card 2020-06-08T00:05:37-05:00 None web lsob-10, standard discount collection gid:\/\/verify\/Product\/ab6332761412 14530688388 ab6332761412 1 2016-07-29T09:07:38-05:00 2016-07-29T09:07:38-05:00 None 140 180 https:\/\/cdn.verify.com\/s\/files\/1\/0207\/8508\/products\/Screenshot_2016-07-28_16.24.39.png?v=1469801258 NaN gid:\/\/verify\/ProductImage\/14530688388 21828312900 ab6332761412 Default Title 4.50 BD1615 (INACTIVE) 1 deny None manual verify Default Title None None 2016-07-29T09:07:36-05:00 2020-03-10T15:11:49-05:00 True None 0 None 0.000 g 16074523204 0 0 True gid:\/\/verify\/ProductVariant\/21828312900 7615401988 ab6332761412 Title 1 Default Title 14530688388 ab6332761412 1 2016-07-29T09:07:38-05:00 2016-07-29T09:07:38-05:00 None 140 180 https:\/\/cdn.verify.com\/s\/files\/1\/0207\/8508\/products\/Screenshot_2016-07-28_16.24.39.png?v=1469801258 NaN gid:\/\/verify\/ProductImage\/14530688388
1 1560417370224 "Be Brave" One Liner <p>Encourage yourself or a loved one to move past fear with our “Be Brave” One Liner. This two-piece set includes a single acrylic cutout of the phrase “Be Brave” that rests in a wooden block. Style this sign on a shelf, desk or photo ledge for daily encouragement.<\/p> Jane M Market Accents 2018-11-01T14:11:23-05:00 be-brave-one-liner 2020-07-02T08:57:29-05:00 2019-09-27T14:17:37-05:00 global accents, all-products, bs-sale-2020, customtext2, madeinUSA, netsuite-shipping_category-standard, Office, oos but not discontinued, sail-into-summer, September-sale, signs-decor-promo-2020, spring2019, standard discount collection gid:\/\/verify\/Product\/1560417370224 4863203147827 1560417370224 1 2019-01-18T10:01:42-06:00 2019-01-18T10:01:56-06:00 acrylic quote sign with wooden stand that says "be brave" 2048 2048 https:\/\/cdn.verify.com\/s\/files\/1\/0207\/8508\/products\/be-brave-one-liner-ONELINER-BEBRAVE.jpg?v=1547827316 NaN gid:\/\/verify\/ProductImage\/4863203147827 15313179934832 1560417370224 Default Title 15.00 ONELINER-BEBRAVE 1 deny None manual verify Default Title None None 2018-11-01T14:11:23-05:00 2020-07-02T08:57:29-05:00 True 112019151734 181 None 0.399 lb 15437614514288 287 287 True gid:\/\/verify\/ProductVariant\/15313179934832 2125698302064 1560417370224 Title 1 Default Title 4863203147827 1560417370224 1 2019-01-18T10:01:42-06:00 2019-01-18T10:01:56-06:00 acrylic quote sign with wooden stand that says "be brave" 2048 2048 https:\/\/cdn.verify.com\/s\/files\/1\/0207\/8508\/products\/be-brave-one-liner-ONELINER-BEBRAVE.jpg?v=1547827316 NaN gid:\/\/verify\/ProductImage\/4863203147827
2 1560417370224 "Be Brave" One Liner <p>Encourage yourself or a loved one to move past fear with our “Be Brave” One Liner. This two-piece set includes a single acrylic cutout of the phrase “Be Brave” that rests in a wooden block. Style this sign on a shelf, desk or photo ledge for daily encouragement.<\/p> Jane M Market Accents 2018-11-01T14:11:23-05:00 be-brave-one-liner 2020-07-02T08:57:29-05:00 2019-09-27T14:17:37-05:00 global accents, all-products, bs-sale-2020, customtext2, madeinUSA, netsuite-shipping_category-standard, Office, oos but not discontinued, sail-into-summer, September-sale, signs-decor-promo-2020, spring2019, standard discount collection gid:\/\/verify\/Product\/1560417370224 4863203147827 1560417370224 1 2019-01-18T10:01:42-06:00 2019-01-18T10:01:56-06:00 acrylic quote sign with wooden stand that says "be brave" 2048 2048 https:\/\/cdn.verify.com\/s\/files\/1\/0207\/8508\/products\/be-brave-one-liner-ONELINER-BEBRAVE.jpg?v=1547827316 NaN gid:\/\/verify\/ProductImage\/4863203147827 15313179934832 1560417370224 Default Title 15.00 ONELINER-BEBRAVE 1 deny None manual verify Default Title None None 2018-11-01T14:11:23-05:00 2020-07-02T08:57:29-05:00 True 112019151734 181 None 0.399 lb 15437614514288 287 287 True gid:\/\/verify\/ProductVariant\/15313179934832 2125698302064 1560417370224 Title 1 Default Title 4863214190643 1560417370224 2 2019-01-18T10:08:11-06:00 2019-01-18T10:08:19-06:00 acrylic and wood quote sign 2048 2048 https:\/\/cdn.verify.com\/s\/files\/1\/0207\/8508\/products\/avrylic-and-wood-one-liner_706f5afb-14ef-4de7-b059-94db84baf12c.jpg?v=1547827699 NaN gid:\/\/verify\/ProductImage\/4863214190643
'id' 4863203147827
'images' key had a list with 2 dicts, which were expanded to separate rows.