☰
›

Tick data to Python pandas

samphel
samphel in Python client
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.


Tagged:
  • tahseen
    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
    samphel
    @tahseen thankyou very much
This discussion has been closed.