Tick data to Python pandas

samphel

Hi guyz,
Thankyou very much for the help till now i have received from this forum, further to improve my algo trading , i have come across yet another problem , request fellow members to help me out in this matter.

So as we know below mentioned is the complete FULL tick data we receive:

[{'tradable': True, 'mode': 'full', 'instrument_token': 415745, 'last_price': 117.45, 'last_quantity': 5, 'average_price': 118.38, 'volume': 13598855, 'buy_quantity': 0, 'sell_quantity': 3520, 'ohlc': {'open': 119.25, 'high': 120.2, 'low': 117.0, 'close': 119.65}, 'change': -1.838696197241958, 'last_trade_time': datetime.datetime(2020, 1, 27, 15, 59, 54), 'oi': 0, 'oi_day_high': 0, 'oi_day_low': 0, 'timestamp': datetime.datetime(2020, 1, 27, 17, 30, 51), 'depth': {'buy': [{'quantity': 0, 'price': 0.0, 'orders': 0}, {'quantity': 0, 'price': 0.0, 'orders': 0}, {'quantity': 0, 'price': 0.0, 'orders': 0}, {'quantity': 0, 'price': 0.0, 'orders': 0}, {'quantity': 0, 'price': 0.0, 'orders': 0}], 'sell': [{'quantity': 3520, 'price': 117.45, 'orders': 16}, {'quantity': 0, 'price': 0.0, 'orders': 0}, {'quantity': 0, 'price': 0.0, 'orders': 0}, {'quantity': 0, 'price': 0.0, 'orders': 0}, {'quantity': 0, 'price': 0.0, 'orders': 0}]}}

Now as i'm sending this data to pandas dataframe

df = pd.Dataframe(ticks)
df_new = df[['instrument_token', 'volume', 'last_price','ohlc]]
print(df_new)

Now as i need columns as you can see above only inst token,volume ltp, and the ohlc, buy in the ohlc part i'm receiving another dictionary in the pandas dataframe as:
instrument_token volume last_price ohlc
0 3050241 134241973 42.40 {'open': 42.3, 'high': 43.45, 'low': 41.85, 'c...
1 415745 13598855 117.45 {'open': 119.25, 'high': 120.2, 'low': 117.0, ...

But i need the OHLC separately as open, high low and close but i'm not able to do so , so my question is how to access the "ohlc" columns from above tick data.

tahseen

You need to flatten the nested data using pandas and then create the data frame out of it

import datetime
from pandas.io.json import json_normalize

ticks = [{'tradable': True, 'mode': 'full', 'instrument_token': 415745, 'last_price': 117.45,'last_quantity': 5, 'average_price': 118.38, 'volume': 13598855, 'buy_quantity': 0, 'sell_quantity': 3520, 'ohlc': {'open': 119.25, 'high': 120.2, 'low': 117.0, 'close': 119.65},'change': -1.838696197241958, 'last_trade_time': datetime.datetime(2020, 1, 27,15, 59, 54), 'oi': 0, 'oi_day_high': 0, 'oi_day_low':0,'timestamp':datetime.datetime(2020, 1, 27, 17, 30, 51), 'depth': {'buy': [{'quantity':0, 'price': 0.0, 'orders': 0}, {'quantity': 0, 'price': 0.0, 'orders': 0}, {'quantity':0, 'price': 0.0, 'orders': 0}, {'quantity': 0, 'price': 0.0, 'orders': 0}, {'quantity':0, 'price': 0.0, 'orders': 0}], 'sell': [{'quantity': 3520, 'price': 117.45, 'orders':16},{'quantity': 0, 'price': 0.0, 'orders': 0}, {'quantity': 0, 'price': 0.0, 'orders':0}, {'quantity': 0, 'price': 0.0, 'orders': 0}, {'quantity': 0, 'price': 0.0, 'orders':0}]}}]

# data in ticks are flattened
df = json_normalize(ticks)

# Now only keep columns that you want
df = df[['instrument_token', 'volume', 'last_price','ohlc.open','ohlc.high', 'ohlc.low','ohlc.close']]

# Print and see the dataframe
print(df)

samphel

@tahseen thankyou very much