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
