Considering the instrument_ticker dataframe and tickers list below: I do a split to select the first item of each value obtained from this operation of each line: Now, how can I get the index of instrument_ticker where Reduced_Ticker contains a ticker_reduced item? I need to save this in a list, like the following (expected output): Answer Select what you require

Select dataframe index derived from comparing a dataframe column and a list

Considering the instrument_ticker dataframe and tickers list below:

import pandas as pd
import numpy as np

stk_off = pd.DataFrame({'Ticker': ['EWZ US 05/29/20 P27', 'HSI US 12/30/20 C24800', 'TLT US 06/19/20 C225', 'EWZ US 05/29/20 P29'],
                   'Instrument': ['EWZ', 'HSI', 'TLT', 'EWZ'],
                   'Maturity Label': ['MAI 20 D29', 'DEC 20 D30', 'JUN 20 D19', 'MAY 20 D29'],
                   'Strike': [27, 24800, 225, 29],
                   'Payout': ['P', 'C', 'C', 'P'],
                   'Maturity': ['29/05/2020', '12/30/2020', '19/06/2020', '29/05/2020'],
                   'Market': ['US NYSE', 'US NYSE', 'HK HKSE', 'US NYSE']})

stk_off['Reduced_Ticker'] = stk_off['Ticker'].apply(lambda a :" ".join(a.split(" ", 2)[:2]))

instrument_ticker = stk_off[['Instrument','Reduced_Ticker']].groupby(['Instrument']).agg(list)
instrument_ticker['Reduced_Ticker'] = instrument_ticker['Reduced_Ticker'].apply(lambda x: pd.unique(x))

ticker = ['EWZ US 05/29/20 C31','HSI US 12/30/20 C24900']

I do a split to select the first item of each value obtained from this operation of each line:

tickers_reduced = [t.split()[0] for t in tickers]

Now, how can I get the index of instrument_ticker where Reduced_Ticker contains a ticker_reduced item? I need to save this in a list, like the following (expected output):

instrument = ['EWZ', 'HSI']

Answer

Select what you require by checking the first split term of Reduced_Ticker, followed by obtaining the index of the selected rows:

selection = (
    instrument_ticker
    .explode('Reduced_Ticker')
    ['Reduced_Ticker']
    .apply(lambda x : x.split()[0] in tickers_reduced)
)

instrument = instrument_ticker.loc[selection].index.to_list()

Advertisement

Answer