How do I convert Shopify API json into a readable dataframe?

user_12412314 0
Viewed 516

I have the following Python 3.7 code to extract order data from the Shopify Admin API in json format and format into a pandas dataframe.

r = requests.get('https://%s:%s@%s.myshopify.com/admin/api/2021-01/orders.json?status=any' % (API_KEY, PASSWORD, SHOP_NAME))
d1 = r.json()

df = pd.json_normalize(d1)
print(df)

Currently, my output looks the below: enter image description here

However, my desired output looks like this: enter image description here

For reference, d1 looks like the following: {'orders': [{'id': 30001, 'email': 'test@gmail.com', 'closed_at': None,...

Should I be using a function other than json.normalize() to achieve my desired output? I've also tried using json.dumps() to format between dictionary vs. string but I haven't had any success there.

shopify-api pandas python json
1 Answers

So, pretty simple actually. You just need to access the order key to get the values dictionary you want a pd.DataFrame from.

Here is some sample code

import pandas as pd
import json

d = {
    'orders': [
        {'id': 3111498449045, 'email': 'test@gmail.com', 'closed_at': None},
        {'id': 3111498449046, 'email': 'test@gmail.com', 'closed_at': None},
        {'id': 3111498449047, 'email': 'test@gmail.com', 'closed_at': None}
    ]
}

pd.DataFrame.from_dict(d['orders'])

To fix your code you should do:

r = requests.get('https://%s:%s@%s.myshopify.com/admin/api/2021-01/orders.json?status=any' % (API_KEY, PASSWORD, SHOP_NAME))
d1 = r.json()

df = pd.DataFrame.from_dict(d['orders'])
print(df)
user_34539010
Related