Senior Pomidor
Reputation: 318
Convert JSON to full table
How with pandas convert shopify Json response
pastebin.com/8Kf8FHf4
to full Table without column included json child
now i use this code
import pandas as pd
import numpy as np
import re
import requests
def get_all_orders():
last=0
orders=pd.DataFrame()
while True:
url = f"https://{apikey}:{password}@{hostname}/admin/api/{version}/{resource}.json?limit=250&fulfillment_status=unfulfilled&since_id={last}"
response = requests.request("GET", url)
df=pd.DataFrame(response.json()['orders'])
orders=pd.concat([orders,df])
last=df['id'].iloc[-1]
if len(df)<250:
break
return(orders)
df=get_all_orders()
I want get rid of that childs in columns
Upvotes: 1
Views: 71
Answers (1)
balderman
Reputation: 23815
The code below will make a flat dict for each order in the data set (trimmed since it is too big).
The result is a DF with 1 row and 166 columns
import collections
import pandas as pd
def flatten(d, parent_key='', sep='_'):
items = []
for k, v in d.items():
new_key = parent_key + sep + k if parent_key else k
if isinstance(v, collections.MutableMapping):
items.extend(flatten(v, new_key, sep=sep).items())
else:
items.append((new_key, v))
return dict(items)
data = {
"orders": [
{
"id": 450789469,
"admin_graphql_api_id": "gid://shopify/Order/450789469",
"app_id": None,
"browser_ip": "0.0.0.0",
"buyer_accepts_marketing": False,
"cancel_reason": None,
"cancelled_at": None,
"cart_token": "68778783ad298f1c80c3bafcddeea02f",
"checkout_id": 901414060,
"checkout_token": "bd5a8aa1ecd019dd3520ff791ee3a24c",
"client_details": {
"accept_language": None,
"browser_height": None,
"browser_ip": "0.0.0.0",
"browser_width": None,
"session_hash": None,
"user_agent": None
},
"closed_at": None,
"confirmed": True,
"contact_email": "[email protected]",
"created_at": "2008-01-10T11:00:00-05:00",
"currency": "USD",
"customer_locale": None,
"device_id": None,
"discount_codes": [
{
"code": "TENOFF",
"amount": "10.00",
"type": "fixed_amount"
}
],
"email": "[email protected]",
"financial_status": "partially_refunded",
"fulfillment_status": None,
"gateway": "authorize_net",
"landing_site": "http://www.example.com?source=abc",
"landing_site_ref": "abc",
"location_id": None,
"name": "#1001",
"note": None,
"note_attributes": [
{
"name": "custom engraving",
"value": "Happy Birthday"
},
{
"name": "colour",
"value": "green"
}
],
"number": 1,
"order_number": 1001,
"order_status_url": "https://apple.myshopify.com/690933842/orders/b1946ac92492d2347c6235b4d2611184/authenticate?key=imasecretipod",
"payment_gateway_names": [
"bogus"
],
"phone": "+557734881234",
"presentment_currency": "USD",
"processed_at": "2008-01-10T11:00:00-05:00",
"processing_method": "direct",
"reference": "fhwdgads",
"referring_site": "http://www.otherexample.com",
"source_identifier": "fhwdgads",
"source_name": "web",
"source_url": None,
"subtotal_price": "597.00",
"subtotal_price_set": {
"shop_money": {
"amount": "597.00",
"currency_code": "USD"
},
"presentment_money": {
"amount": "597.00",
"currency_code": "USD"
}
},
"tags": "",
"tax_lines": [
{
"price": "11.94",
"rate": 0.06,
"title": "State Tax",
"price_set": {
"shop_money": {
"amount": "11.94",
"currency_code": "USD"
},
"presentment_money": {
"amount": "11.94",
"currency_code": "USD"
}
}
}
],
"sku": "IPOD2008GREEN",
"taxable": True,
"quantity": 1,
"requires_shipping": True,
},
"presentment_money": {
"amount": "0.00",
"currency_code": "USD"
}
},
"variant_id": 49148385,
"variant_inventory_management": "shopify",
"variant_title": "red",
"vendor": None,
"tax_lines": [
{
"price": "3.98",
"price_set": {
"shop_money": {
"amount": "3.98",
"currency_code": "USD"
},
"presentment_money": {
"amount": "3.98",
"currency_code": "USD"
}
},
"rate": 0.06,
"title": "State Tax"
}
],
"discount_allocations": [
{
"amount": "3.33",
"amount_set": {
"shop_money": {
"amount": "3.33",
"currency_code": "USD"
},
"presentment_money": {
"amount": "3.33",
"currency_code": "USD"
}
},
"discount_application_index": 0
}
]
},
{
"id": 703073504,
"admin_graphql_api_id": "gid://shopify/LineItem/703073504",
"fulfillable_quantity": 0,
"fulfillment_service": "manual",
"fulfillment_status": None,
"gift_card": False,
"grams": 200,
"name": "IPod Nano - 8gb - black",
"price": "199.00",
"price_set": {
"shop_money": {
"amount": "199.00",
"currency_code": "USD"
},
"presentment_money": {
"amount": "199.00",
"currency_code": "USD"
}
},
"product_exists": True,
"product_id": 632910392,
"properties": [
],
"quantity": 1,
"requires_shipping": True,
"sku": "IPOD2008BLACK",
"taxable": True,
"title": "IPod Nano - 8gb",
"total_discount": "0.00",
"total_discount_set": {
"shop_money": {
"amount": "0.00",
"currency_code": "USD"
},
"presentment_money": {
"amount": "0.00",
"currency_code": "USD"
}
},
"variant_id": 457924702,
"variant_inventory_management": "shopify",
"variant_title": "black",
"vendor": None,
"tax_lines": [
{
"price": "3.98",
"price_set": {
"shop_money": {
"amount": "3.98",
"currency_code": "USD"
},
"presentment_money": {
"amount": "3.98",
"currency_code": "USD"
}
},
"rate": 0.06,
"title": "State Tax"
}
],
"discount_allocations": [
{
"amount": "3.33",
"amount_set": {
"shop_money": {
"amount": "3.33",
"currency_code": "USD"
},
"presentment_money": {
"amount": "3.33",
"currency_code": "USD"
}
},
"discount_application_index": 0
}
]
}
],
"payment_details": {
"credit_card_bin": None,
"avs_result_code": None,
"cvv_result_code": None,
"credit_card_number": "•••• •••• •••• 4242",
"credit_card_company": "Visa"
},
"refunds": [
{
"id": 509562969,
"admin_graphql_api_id": "gid://shopify/Refund/509562969",
"created_at": "2021-09-18T13:31:15-04:00",
"note": "it broke during shipping",
"order_id": 450789469,
"processed_at": "2021-09-18T13:31:15-04:00",
"restock": True,
"user_id": 799407056,
"order_adjustments": [
],
"transactions": [
{
"id": 179259969,
"admin_graphql_api_id": "gid://shopify/OrderTransaction/179259969",
"amount": "209.00",
"authorization": "authorization-key",
"created_at": "2005-08-05T12:59:12-04:00",
"currency": "USD",
"device_id": None,
"error_code": None,
"gateway": "bogus",
"kind": "refund",
"location_id": None,
"message": None,
"order_id": 450789469,
"parent_id": 801038806,
"processed_at": "2005-08-05T12:59:12-04:00",
"receipt": {
},
"source_name": "web",
"status": "success",
"test": False,
"user_id": None
}
],
"refund_line_items": [
{
"id": 104689539,
"line_item_id": 703073504,
"location_id": 487838322,
"quantity": 1,
"restock_type": "legacy_restock",
"subtotal": 195.66,
"subtotal_set": {
"shop_money": {
"amount": "195.66",
"currency_code": "USD"
},
"presentment_money": {
"amount": "195.66",
"currency_code": "USD"
}
},
"total_tax": 3.98,
"total_tax_set": {
"shop_money": {
"amount": "3.98",
"currency_code": "USD"
},
"presentment_money": {
"amount": "3.98",
"currency_code": "USD"
}
},
"line_item": {
"id": 703073504,
"admin_graphql_api_id": "gid://shopify/LineItem/703073504",
"fulfillable_quantity": 0,
"fulfillment_service": "manual",
"fulfillment_status": None,
"gift_card": False,
"grams": 200,
"name": "IPod Nano - 8gb - black",
"price": "199.00",
"price_set": {
"shop_money": {
"amount": "199.00",
"currency_code": "USD"
},
"presentment_money": {
"amount": "199.00",
"currency_code": "USD"
}
},
"product_exists": True,
"product_id": 632910392,
"properties": [
],
"quantity": 1,
"requires_shipping": True,
"sku": "IPOD2008BLACK",
"taxable": True,
"title": "IPod Nano - 8gb",
"total_discount": "0.00",
"total_discount_set": {
"shop_money": {
"amount": "0.00",
"currency_code": "USD"
},
"presentment_money": {
"amount": "0.00",
"currency_code": "USD"
}
},
"variant_id": 457924702,
"variant_inventory_management": "shopify",
"variant_title": "black",
"vendor": None,
"tax_lines": [
{
"price": "3.98",
"price_set": {
"shop_money": {
"amount": "3.98",
"currency_code": "USD"
},
"presentment_money": {
"amount": "3.98",
"currency_code": "USD"
}
},
"rate": 0.06,
"title": "State Tax"
}
],
"discount_allocations": [
{
"amount": "3.33",
"amount_set": {
"shop_money": {
"amount": "3.33",
"currency_code": "USD"
},
"presentment_money": {
"amount": "3.33",
"currency_code": "USD"
}
},
"discount_application_index": 0
}
]
}
},
{
"id": 709875399,
"line_item_id": 466157049,
"location_id": 487838322,
"quantity": 1,
"restock_type": "legacy_restock",
"subtotal": 195.67,
"subtotal_set": {
"shop_money": {
"amount": "195.67",
"currency_code": "USD"
},
"presentment_money": {
"amount": "195.67",
"currency_code": "USD"
}
},
"total_tax": 3.98,
"total_tax_set": {
"shop_money": {
"amount": "3.98",
"currency_code": "USD"
},
"presentment_money": {
"amount": "3.98",
"currency_code": "USD"
}
},
"line_item": {
"id": 466157049,
"admin_graphql_api_id": "gid://shopify/LineItem/466157049",
"fulfillable_quantity": 0,
"fulfillment_service": "manual",
"fulfillment_status": None,
"gift_card": False,
"grams": 200,
"name": "IPod Nano - 8gb - green",
"price": "199.00",
"price_set": {
"shop_money": {
"amount": "199.00",
"currency_code": "USD"
},
"presentment_money": {
"amount": "199.00",
"currency_code": "USD"
}
},
"product_exists": True,
"product_id": 632910392,
"properties": [
{
"name": "Custom Engraving Front",
"value": "Happy Birthday"
},
{
"name": "Custom Engraving Back",
"value": "Merry Christmas"
}
],
"quantity": 1,
"requires_shipping": True,
"sku": "IPOD2008GREEN",
"taxable": True,
"title": "IPod Nano - 8gb",
"total_discount": "0.00",
"total_discount_set": {
"shop_money": {
"amount": "0.00",
"currency_code": "USD"
},
"presentment_money": {
"amount": "0.00",
"currency_code": "USD"
}
},
"variant_id": 39072856,
"variant_inventory_management": "shopify",
"variant_title": "green",
"vendor": None,
"tax_lines": [
{
"price": "3.98",
"price_set": {
"shop_money": {
"amount": "3.98",
"currency_code": "USD"
},
"presentment_money": {
"amount": "3.98",
"currency_code": "USD"
}
},
"rate": 0.06,
"title": "State Tax"
}
],
"discount_allocations": [
{
"amount": "3.34",
"amount_set": {
"shop_money": {
"amount": "3.34",
"currency_code": "USD"
},
"presentment_money": {
"amount": "3.34",
"currency_code": "USD"
}
},
"discount_application_index": 0
}
]
}
}
]
}
],
"shipping_address": {
"first_name": "Bob",
"address1": "Chestnut Street 92",
"phone": "555-625-1199",
"city": "Louisville",
"zip": "40202",
"province": "Kentucky",
"country": "United States",
"last_name": "Norman",
"address2": "",
"company": None,
"latitude": 45.41634,
"longitude": -75.6868,
"name": "Bob Norman",
"country_code": "US",
"province_code": "KY"
},
"shipping_lines": [
{
"id": 369256396,
"carrier_identifier": None,
"code": "Free Shipping",
"delivery_category": None,
"discounted_price": "0.00",
"discounted_price_set": {
"shop_money": {
"amount": "0.00",
"currency_code": "USD"
},
"presentment_money": {
"amount": "0.00",
"currency_code": "USD"
}
},
"phone": None,
"price": "0.00",
"price_set": {
"shop_money": {
"amount": "0.00",
"currency_code": "USD"
},
"presentment_money": {
"amount": "0.00",
"currency_code": "USD"
}
},
"requested_fulfillment_service_id": None,
"source": "shopify",
"title": "Free Shipping",
"tax_lines": [
],
"discount_allocations": [
]
}
]
}
]
}
orders = [flatten(o) for o in data['orders']]
df = pd.DataFrame(orders)
print(df)
Upvotes: 1
Related Questions
- Pandas JSON Table
- Transform json data into a dataframe
- How to turn this JSON into a proper table instead of dot separated columns
- How do I convert Shopify API json into a readable dataframe?
- Convert JSON file into a custom table using Python Pandas
- Turning a Json table into a standard df
- How to convert JSON into a table using python?
- Json to Pandas Table in Python
- convert json into pandas dataframe
- Convert Json data to Python DataFrame