[FIXED] How can I import a nested json object into a pandas dataframe?

Issue

I have a json object like this:

[{'currency_pair': 'UOS_USDT',
  'orders': [{'account': 'spot',
              'amount': '1282.84',
              'create_time': '1655394430',
              'create_time_ms': 1655394430129,
              'currency_pair': 'UOS_USDT',
              'fee': '0',
              'fee_currency': 'UOS',
              'fill_price': '0',
              'filled_total': '0',
              'gt_discount': False,
              'gt_fee': '0',
              'iceberg': '0',
              'id': '169208865523',
              'left': '1282.84',
              'point_fee': '0',
              'price': '0.1949',
              'rebated_fee': '0',
              'rebated_fee_currency': 'USDT',
              'side': 'buy',
              'status': 'open',
              'text': 'apiv4',
              'time_in_force': 'gtc',
              'type': 'limit',
              'update_time': '1655394430',
              'update_time_ms': 1655394430129}],
  'total': 1},
 {'currency_pair': 'RMRK_USDT',
  'orders': [{'account': 'spot',
              'amount': '79.365',
              'create_time': '1655394431',
              'create_time_ms': 1655394431249,
              'currency_pair': 'RMRK_USDT',
              'fee': '0',
              'fee_currency': 'RMRK',
              'fill_price': '0',
              'filled_total': '0',
              'gt_discount': False,
              'gt_fee': '0',
              'iceberg': '0',
              'id': '169208877018',
              'left': '79.365',
              'point_fee': '0',
              'price': '2.52',
              'rebated_fee': '0',
              'rebated_fee_currency': 'USDT',
              'side': 'buy',
              'status': 'open',
              'text': 'apiv4',
              'time_in_force': 'gtc',
              'type': 'limit',
              'update_time': '1655394431',
              'update_time_ms': 1655394431249}],
  'total': 1}]

I want to convert it to a dataframe.

The data comes from an api call to a crypto exchange. I converted this to json, using the .json() method. So it's proper json. I have tried:

df = pd.DataFrame(data)
df = pd.DataFrame(data["orders")
df = pd.DataFrame(data["currency_pair"]["orders"])

and every other imaginable path.

I want a df which has as columns ["currency_pair", "amount", "create_time", "price", "side"]

I some times get an error TypeError: list indices must be integers or slices, not str or the df works but the orders object is not unpacked. All help gratefully received. Thank you.

Solution

import pandas as pd

data = [{'currency_pair': 'UOS_USDT',
  'orders': [{'account': 'spot',
              'amount': '1282.84',
              'create_time': '1655394430',
              'create_time_ms': 1655394430129,
              'currency_pair': 'UOS_USDT',
              'fee': '0',
              'fee_currency': 'UOS',
              'fill_price': '0',
              'filled_total': '0',
              'gt_discount': False,
              'gt_fee': '0',
              'iceberg': '0',
              'id': '169208865523',
              'left': '1282.84',
              'point_fee': '0',
              'price': '0.1949',
              'rebated_fee': '0',
              'rebated_fee_currency': 'USDT',
              'side': 'buy',
              'status': 'open',
              'text': 'apiv4',
              'time_in_force': 'gtc',
              'type': 'limit',
              'update_time': '1655394430',
              'update_time_ms': 1655394430129}],
  'total': 1},
 {'currency_pair': 'RMRK_USDT',
  'orders': [{'account': 'spot',
              'amount': '79.365',
              'create_time': '1655394431',
              'create_time_ms': 1655394431249,
              'currency_pair': 'RMRK_USDT',
              'fee': '0',
              'fee_currency': 'RMRK',
              'fill_price': '0',
              'filled_total': '0',
              'gt_discount': False,
              'gt_fee': '0',
              'iceberg': '0',
              'id': '169208877018',
              'left': '79.365',
              'point_fee': '0',
              'price': '2.52',
              'rebated_fee': '0',
              'rebated_fee_currency': 'USDT',
              'side': 'buy',
              'status': 'open',
              'text': 'apiv4',
              'time_in_force': 'gtc',
              'type': 'limit',
              'update_time': '1655394431',
              'update_time_ms': 1655394431249}],
  'total': 1}]

Use:

df = pd.json_normalize(data, record_path=['orders'])

And keep the columns you need.

It's only one line and it should cover your case since 'currency_pair' that you want is already in the 'orders' dictionary and from what I understand from your data it will always be the same as the 'currency_pair' value outside 'orders. As you said you don't need 'total' too.

Use:

df = pd.json_normalize(data, record_path=['orders'], meta=['currency_pair', 'total'], record_prefix='orders_')

If you want them all

Answered By - diml

Answer Checked By - Katrina (PHPFixing Volunteer)