CODEWITHSUNDEEP
jsonpython-3.xpandasdataframe
Marx Babu
Marx Babu

Reputation: 760

Python Multilevel Json to Pandas data frame with index and extraction third level data

Hello I have read few post before writing here.

Here i have a JSON like the below.

{'jsonrpc': '2.0', 'result':
{'trades': 
    [{'trade_seq': 339, 'trade_id': '71103925', 'timestamp': 1585866220152, 'tick_direction': 2, 'price': 0.091, 'iv': 266.73, 'instrument_name': 'BTC-3APR20-6250-C', 'index_price': 6821.89, 'direction': 'sell', 'amount': 0.1}, {'trade_seq': 338, 'trade_id': '71037635', 'timestamp': 1585847774328, 'tick_direction': 0, 'price': 0.122, 'iv': 200.56, 'instrument_name': 'BTC-3APR20-6250-C', 'index_price': 7101.86, 'direction': 'sell', 'amount': 8.0},
    {'trade_seq': 337, 'trade_id': '71023360', 'timestamp': 1585847012712, 'tick_direction': 0, 'price': 0.12, 'iv': 188.74, 'instrument_name': 'BTC-3APR20-6250-C', 'index_price': 7089.71, 'direction': 'buy', 'amount': 1.0}, {'trade_seq': 336, 'trade_id': '71009013', 'timestamp': 1585843885138, 'tick_direction': 0, 'price': 0.0945, 'iv': 121.64, 'instrument_name': 'BTC-3APR20-6250-C', 'index_price': 6899.63, 'direction': 'buy', 'amount': 0.2},
    {'trade_seq': 335, 'trade_id': '70994062', 'timestamp': 1585838997413, 'tick_direction': 1, 'price': 0.08, 'iv': 109.25, 'instrument_name': 'BTC-3APR20-6250-C', 'index_price': 6787.83, 'direction': 'buy', 'amount': 0.1}, {'trade_seq': 334, 'trade_id': '70991283', 'timestamp': 1585838566468, 'tick_direction': 0, 'price': 0.08, 'iv': 125.43, 'instrument_name': 'BTC-3APR20-6250-C', 'index_price': 6782.84, 'direction': 'buy', 'amount': 0.1},
    {'trade_seq': 333, 'trade_id': '70990456', 'timestamp': 1585838539284, 'tick_direction': 0, 'price': 0.079, 'iv': 137.22, 'instrument_name': 'BTC-3APR20-6250-C', 'index_price': 6768.48, 'direction': 'buy', 'amount': 0.1}, {'trade_seq': 332, 'trade_id': '70990138', 'timestamp': 1585838532936, 'tick_direction': 0, 'price': 0.075, 'iv': 117.79, 'instrument_name': 'BTC-3APR20-6250-C', 'index_price': 6744.82, 'direction': 'buy', 'amount': 5.0},
    {'trade_seq': 331, 'trade_id': '70988457', 'timestamp': 1585838152445, 'tick_direction': 0, 'price': 0.07, 'iv': 113.82, 'instrument_name': 'BTC-3APR20-6250-C', 'index_price': 6708.84, 'direction': 'buy', 'amount': 5.4}, {'trade_seq': 330, 'trade_id': '70982630', 'timestamp': 1585833578887, 'tick_direction': 2, 'price': 0.06, 'iv': 95.59, 'instrument_name': 'BTC-3APR20-6250-C', 'index_price': 6636.8, 'direction': 'buy', 'amount': 2.0}
    ],
     'has_more': True
    }, 'usIn': 1585982752359369, 'usOut': 1585982752359671, 'usDiff': 302, 'testnet': False}

Here i want to convert this json to pandas data frame and mainly i wish to get the pandas data frame of 'trades' like

trade_seq trade_id timestamp ... direction amount

Appreciate any easy method to do this conversion to pandas ,I am confused with this addition in the dictionary {'jsonrpc': '2.0', 'result': {'trades':.

Appreciate your help

Upvotes: 0

Views: 151

Answers (2)

greenPlant
greenPlant

Reputation: 492

    import pandas as pd
    df = pd.DataFrame(j['result']['trades'])

Upvotes: 1

jezrael
jezrael

Reputation: 863031

Use json.json_normalize:

from pandas.io.json import json_normalize

df = json_normalize(d['result'], 'trades')
print (df)    
   trade_seq  trade_id      timestamp  tick_direction   price      iv  \
0        339  71103925  1585866220152               2  0.0910  266.73   
1        338  71037635  1585847774328               0  0.1220  200.56   
2        337  71023360  1585847012712               0  0.1200  188.74   
3        336  71009013  1585843885138               0  0.0945  121.64   
4        335  70994062  1585838997413               1  0.0800  109.25   
5        334  70991283  1585838566468               0  0.0800  125.43   
6        333  70990456  1585838539284               0  0.0790  137.22   
7        332  70990138  1585838532936               0  0.0750  117.79   
8        331  70988457  1585838152445               0  0.0700  113.82   
9        330  70982630  1585833578887               2  0.0600   95.59   

     instrument_name  index_price direction  amount  
0  BTC-3APR20-6250-C      6821.89      sell     0.1  
1  BTC-3APR20-6250-C      7101.86      sell     8.0  
2  BTC-3APR20-6250-C      7089.71       buy     1.0  
3  BTC-3APR20-6250-C      6899.63       buy     0.2  
4  BTC-3APR20-6250-C      6787.83       buy     0.1  
5  BTC-3APR20-6250-C      6782.84       buy     0.1  
6  BTC-3APR20-6250-C      6768.48       buy     0.1  
7  BTC-3APR20-6250-C      6744.82       buy     5.0  
8  BTC-3APR20-6250-C      6708.84       buy     5.4  
9  BTC-3APR20-6250-C      6636.80       buy     2.0

Upvotes: 1

Related Questions