python convert dictionary to dataframe

Question

I try to convert json to dataframe but I could not find what i want

here is dictionary and result I got

{'20210df12820df1456-ssddsd': {'2': {'num': '2',
                                     'product_name': 'apple',
                                     'product_price': '20900'},
                               'order': {'add_info': None,
                                         'basket_count': '2',
                                         'deli_price': '2500',
                                         'id': 'nhdd@abvc',
                                         'is_member': 'MEMBER',
                                         'mem_type': 'PERSON',
                                         'order_date': '2021-01-28 20:14:56',
                                         'ordernum': '20210df12820df1456-ssddsd',
                                         'pay_price': '43100',
                                         'reserve': '840',
                                         'start_price': '43100',
                                         'total_product_price': '41800',
                                         'used_emoney': '0',
                                         'used_reserve': '0'},
                               'pay_history': [{'add_price': '0',
                                                'deli_price': '2500',
                                                'discount_price': '-1200',
                                                'order_price': '43100',
                                                'pay_date': '2021-01-28 '
                                                            '20:15:14',
                                                'pay_price': '43100',
                                                'pay_type': 'creditcard',
                                                'paymethod': 'C',
                                                'total_price': '41800',
                                                'used_emoney': '0',
                                                'used_reserve': '0'}],
                               'payment': {'card_flag': '0000',
                                           'card_partcancel_code': '00',
                                           'card_state': 'Y',
                                           'in_card_price': '43100',
                                           'pay_date': '2021-01-28 20:15:14',
                                           'pay_status': 'Y',
                                           'paymethod': 'C',
                                           'simple_pay': 'NPY'},
                               'product': {'1': {'num': '1',
                                                 'product_name': 'banana',
                                                 'product_price': '20900'}}}}

json_data = response.json()
result =json_data['list']
df = pd.DataFrame(result).transpose()
df.head()

I would like to make this dictionary to four dataframe. but i have this result

I expected like below

order = df[['order']]
payment = df[['payment']]
pay_history = df[['pay_history']]
product = df[['product']]

something like this.

any ideas??

Answer 1

Since the data for each category (order, payment, pay history, product) are organized differently, you should consider iterating through each category, adding additional data (such as ordernum for indexing purpose) and putting them in their own list that you will later use to convert them into DataFrame object

import pandas as pd

json_data = {'20210df12820df1456-ssddsd': {'order': {'ordernum': '20210df12820df1456-ssddsd', 'order_date': '2021-01-28 20:14:56', 'is_member': 'MEMBER', 'start_price': '43100', 'pay_price': '43100', 'deli_price': '2500', 'total_product_price': '41800', 'basket_count': '2', 'id': 'nhdd@abvc', 'mem_type': 'PERSON', 'used_emoney': '0', 'used_reserve': '0', 'add_info': None, 'reserve': '840'}, 'payment': {'paymethod': 'C', 'pay_date': '2021-01-28 20:15:14', 'card_state': 'Y', 'pay_status': 'Y', 'simple_pay': 'NPY', 'card_flag': '0000', 'card_partcancel_code': '00', 'in_card_price': '43100'}, 'pay_history': [{'pay_date': '2021-01-28 20:15:14', 'pay_type': 'creditcard', 'total_price': '41800', 'deli_price': '2500', 'discount_price': '-1200', 'add_price': '0', 'order_price': '43100', 'pay_price': '43100', 'used_reserve': '0', 'used_emoney': '0', 'paymethod': 'C'}], 'product': {'1': {'num': '1', 'product_name': 'banana', 'product_price': '20900'}, '2': {'num': '2', 'product_name': 'apple', 'product_price': '20900'}}}}

order_data = []
payment_data = []
pay_history_data = []
product_data = []

for key in json_data:
    order_data.append(json_data[key]['order'])

    payment = dict(json_data[key]['payment'])
    payment['ordernum'] = key
    pay_history_data.append(payment)

    pay_history = json_data[key]['pay_history']
    if 'pay_history' in json_data[key]:
        for p in pay_history:
            p_clone = dict(p)
            p_clone['ordernum'] = key
            payment_data.append(p_clone)

    product = json_data[key]['product']
    if 'product' in json_data[key]:
        for product_key in product:
            p_clone = dict(product[product_key])
            p_clone['ordernum'] = key
            product_data.append(p_clone)

order_df = pd.DataFrame(order_data)
payment_df = pd.DataFrame(payment_data)
pay_history_df = pd.DataFrame(pay_history_data)
product_df = pd.DataFrame(product_data)

Edit: If you're experiencing KeyError exception in iterating through pay_history, it could be that in some order, there are no pay_history key in the json data of that order so you can avoid this by first checking if the key exists in the json file before proceeding to iterating through the pay_history (if 'pay_history' in json_data[key]:), same thing can be done before iterating through product (if 'product' in json_data[key]:).

Answer 2

What you need is pandas json_normalize function. I wrote some example codes.

import pandas as pd

from pandas.io.json import json_normalize

data = {'20210df12820df1456-ssddsd': {'order': {'ordernum': '20210df12820df1456-ssddsd', 'order_date': '2021-01-28 20:14:56', 'is_member': 'MEMBER', 
                                                'start_price': '43100', 'pay_price': '43100', 'deli_price': '2500', 'total_product_price': '41800',
                                                'basket_count': '2', 'id': 'nhdd@abvc', 'mem_type': 'PERSON', 'used_emoney': '0', 'used_reserve': '0', 'add_info': None, 'reserve': '840'},
                                      'payment': {'paymethod': 'C', 'pay_date': '2021-01-28 20:15:14', 'card_state': 'Y', 'pay_status': 'Y', 'simple_pay': 'NPY', 'card_flag': '0000', 'card_partcancel_code': '00', 'in_card_price': '43100'}, 
                                      'pay_history': [{'pay_date': '2021-01-28 20:15:14', 'pay_type': 'creditcard', 'total_price': '41800', 'deli_price': '2500', 'discount_price': '-1200', 'add_price': '0', 'order_price': '43100', 'pay_price': '43100', 'used_reserve': '0', 'used_emoney': '0', 'paymethod': 'C'}],
                                      'product': {'1': {'num': '1', 'product_name': 'banana', 'product_price': '20900'}}, '2': {'num': '2', 'product_name': 'apple', 'product_price': '20900'}}}

df = pd.DataFrame(data).transpose()

order = json_normalize(df['order'])

the result will look like: