Pandas bring json file into dataframe

Question

I have a json output from an api in the format below which i would like to bring intoa pandas data frame.

Which syntax can be used with pd.read_json so that the proper format can come into columns?

I have tried to bring this into a dataframe using orient = index and orient = columns but it gives an error

{'name': 'Binance',
 'tickers': [{'base': 'BUSD',
   'target': 'USDT',
   'market': {'name': 'Binance',
    'identifier': 'binance',
    'has_trading_incentive': False},
   'last': 1.0001,
   'volume': 75286490.71165584,
   'converted_last': {'btc': 9.414e-05, 'eth': 0.00292258, 'usd': 1.0},
   'converted_volume': {'btc': 7087, 'eth': 220031, 'usd': 75445315},
   'trust_score': 'green',
   'bid_ask_spread_percentage': 0.109998,
   'timestamp': '2020-10-06T15:10:41+00:00',
   'last_traded_at': '2020-10-06T15:10:41+00:00',
   'last_fetch_at': '2020-10-06T15:10:41+00:00',
   'is_anomaly': False,
   'is_stale': False,
   'trade_url': 'https://www.binance.com/en/trade/BUSD_USDT',
   'token_info_url': None,
   'coin_id': 'binance-usd',
   'target_coin_id': 'tether'},
  {'base': 'BTC',
   'target': 'USDT',
   'market': {'name': 'Binance',
    'identifier': 'binance',
    'has_trading_incentive': False},
   'last': 10623.85,
   'volume': 39127.857693381855,
   'converted_last': {'btc': 0.99826917, 'eth': 30.992216, 'usd': 10626.77},
   'converted_volume': {'btc': 39060, 'eth': 1212659, 'usd': 415802842},
   'trust_score': 'green',
   'bid_ask_spread_percentage': 0.100094,
   'timestamp': '2020-10-06T15:09:14+00:00',
   'last_traded_at': '2020-10-06T15:09:14+00:00',
   'last_fetch_at': '2020-10-06T15:09:14+00:00',
   'is_anomaly': False,
   'is_stale': False,
   'trade_url': 'https://www.binance.com/en/trade/BTC_USDT',
   'token_info_url': None,
   'coin_id': 'bitcoin',
   'target_coin_id': 'tether'},
  {'base': 'ETH',
   'target': 'USDT',
   'market': {'name': 'Binance',
    'identifier': 'binance',
    'has_trading_incentive': False},
   'last': 341.68,
   'volume': 431859.45408737916,
   'converted_last': {'btc': 0.0321616, 'eth': 0.99848742, 'usd': 342.16},
   'converted_volume': {'btc': 13889, 'eth': 431206, 'usd': 147766505},
   'trust_score': 'green',
   'bid_ask_spread_percentage': 0.102928,
   'timestamp': '2020-10-06T15:11:09+00:00',
   'last_traded_at': '2020-10-06T15:11:09+00:00',
   'last_fetch_at': '2020-10-06T15:11:09+00:00',
   'is_anomaly': False,
   'is_stale': False,
   'trade_url': 'https://www.binance.com/en/trade/ETH_USDT',
   'token_info_url': None,
   'coin_id': 'ethereum',
   'target_coin_id': 'tether'},
  {'base': 'BTC',
   'target': 'BUSD',
   'market': {'name': 'Binance',
    'identifier': 'binance',
    'has_trading_incentive': False},
   'last': 10625.38,
   'volume': 4369.880475606273,
   'converted_last': {'btc': 0.99919879, 'eth': 31.021077, 'usd': 10630.36},
   'converted_volume': {'btc': 4366, 'eth': 135558, 'usd': 46453389},
   'trust_score': 'green',
   'bid_ask_spread_percentage': 0.100094,
   'timestamp': '2020-10-06T15:09:44+00:00',
   'last_traded_at': '2020-10-06T15:09:44+00:00',
   'last_fetch_at': '2020-10-06T15:09:44+00:00',
   'is_anomaly': False,
   'is_stale': False,
   'trade_url': 'https://www.binance.com/en/trade/BTC_BUSD',
   'token_info_url': None,
   'coin_id': 'bitcoin',
   'target_coin_id': 'binance-usd'},
  {'base': 'WBTC',
   'target': 'BTC',
   'market': {'name': 'Binance',
    'identifier': 'binance',
    'has_trading_incentive': False},
   'last': 1.00005,
   'volume': 328.59841774911257,
   'converted_last': {'btc': 1.00005, 'eth': 31.047503, 'usd': 10645.73},
   'converted_volume': {'btc': 328.615, 'eth': 10202, 'usd': 3498170},
   'trust_score': 'green',
   'bid_ask_spread_percentage': 0.148975,
   'timestamp': '2020-10-06T15:09:13+00:00',
   'last_traded_at': '2020-10-06T15:09:13+00:00',
   'last_fetch_at': '2020-10-06T15:09:13+00:00',
   'is_anomaly': False,
   'is_stale': False,
   'trade_url': 'https://www.binance.com/en/trade/WBTC_BTC',
   'token_info_url': None,
   'coin_id': 'wrapped-bitcoin',
   'target_coin_id': 'bitcoin'},
  {'base': 'ETH',
   'target': 'BTC',
   'market': {'name': 'Binance',
    'identifier': 'binance',
    'has_trading_incentive': False},
   'last': 0.032148,
   'volume': 169665.352200759,
   'converted_last': {'btc': 0.032148, 'eth': 0.99806523, 'usd': 342.02},
   'converted_volume': {'btc': 5454, 'eth': 169337, 'usd': 58028730},
   'trust_score': 'green',
   'bid_ask_spread_percentage': 0.1311,
   'timestamp': '2020-10-06T15:09:43+00:00',
   'last_traded_at': '2020-10-06T15:09:43+00:00',
   'last_fetch_at': '2020-10-06T15:09:43+00:00',
   'is_anomaly': False,
   'is_stale': False,
   'trade_url': 'https://www.binance.com/en/trade/ETH_BTC',
   'token_info_url': None,
   'coin_id': 'ethereum',
   'target_coin_id': 'bitcoin'

Does this answer your question? [Python - How to convert JSON File to Dataframe](https://stackoverflow.com/questions/41168558/python-how-to-convert-json-file-to-dataframe) — code11, Oct 06 '20 at 15:23

score 0 · Accepted Answer · answered Oct 06 '20 at 15:47

Try using json_normalize:

d is the dict you pasted above:

df = pd.json_normalize(d, record_path=['tickers'], meta=['name'])
print(df)

   base target          last        volume trust_score  ...  converted_last.usd converted_volume.btc converted_volume.eth converted_volume.usd     name
0  BUSD   USDT      1.000100  7.528649e+07       green  ...                1.00             7087.000               220031             75445315  Binance
1   BTC   USDT  10623.850000  3.912786e+04       green  ...            10626.77            39060.000              1212659            415802842  Binance
2   ETH   USDT    341.680000  4.318595e+05       green  ...              342.16            13889.000               431206            147766505  Binance
3   BTC   BUSD  10625.380000  4.369880e+03       green  ...            10630.36             4366.000               135558             46453389  Binance
4  WBTC    BTC      1.000050  3.285984e+02       green  ...            10645.73              328.615                10202              3498170  Binance
5   ETH    BTC      0.032148  1.696654e+05       green  ...              342.02             5454.000               169337             58028730  Binance

[6 rows x 25 columns]

Pandas bring json file into dataframe

1 Answers1