Catching filter exception in pandas loc in multi index dataframe-CodePudding

I have a multi index time series data as below -

stock_list

datetimestamp,stock_id,period_value,date,stock_code,broker_token,period,open,high,low,close,volume,open_interest,candle_no
2021-10-25 09:15:00,AAA,1,2021-10-25,AAA,10100000007,1 min,1018.0,1018.9,1011.1,1014.25,5575,0.0,0
2021-10-25 09:16:00,AAA,1,2021-10-25,AAA,10100000007,1 min,1013.0,1013.0,1000.05,1002.6,9682,0.0,1
2021-10-25 09:17:00,AAA,1,2021-10-25,AAA,10100000007,1 min,1000.7,1001.3,994.0,995.0,13121,0.0,2
2021-10-25 09:18:00,AAA,1,2021-10-25,AAA,10100000007,1 min,994.7,997.65,994.0,995.2,5378,0.0,3
2021-10-25 09:19:00,AAA,1,2021-10-25,AAA,10100000007,1 min,995.3,998.4,995.15,998.0,3205,0.0,4
2021-10-25 09:16:00,BBB,1,2021-10-25,BBB,10100000005378,1 min,357.55,358.4,357.55,358.4,78,0.0,0
2021-10-25 09:17:00,BBB,1,2021-10-25,BBB,10100000005378,1 min,357.6,357.85,357.0,357.0,502,0.0,1
2021-10-25 09:18:00,BBB,1,2021-10-25,BBB,10100000005378,1 min,357.0,357.0,355.15,355.15,325,0.0,2
2021-10-25 09:19:00,BBB,1,2021-10-25,BBB,10100000005378,1 min,355.85,355.85,355.55,355.55,23,0.0,3
2021-10-25 09:20:00,BBB,1,2021-10-25,BBB,10100000005378,1 min,356.0,356.2,355.65,356.2,22,0.0,4
2021-10-25 09:21:00,BBB,1,2021-10-25,BBB,10100000005378,1 min,355.1,355.8,355.1,355.1,436,0.0,5

I am trying to read the open, code, volume for each of the stocks one by one in a loop for each time stamp from a multi index dataframe.

Below is the code -


    period_file_index = ['datetimestamp', 'stock_id', 'period_value']
    period_file_df.set_index(period_file_index, inplace=True)
    period_file_df = period_file_df.sort_index(level=[0,1,2])
    
    index_slice = pd.IndexSlice
    for stock in stock_list
        open_price = period_file_df.loc[index_slice[('2021-10-25 09:15:00'), stock, 1]]['open']

I am getting the below error.

File

"/Users/macdev1/PycharmProjects/SMLogin2/library/utility/archive_data_analysis.py", line 218, in analyze_archive_dated_period_file_v0
    open_price = period_file_df.loc[index_slice[('2021-10-25 09:15:00'), stock, 1]]['open']
  File "/Library/Frameworks/Python.framework/Versions/3.9/lib/python3.9/site-packages/pandas/core/indexing.py", line 889, in __getitem__
    return self._getitem_tuple(key)
  File "/Library/Frameworks/Python.framework/Versions/3.9/lib/python3.9/site-packages/pandas/core/indexing.py", line 1060, in _getitem_tuple
    return self._getitem_lowerdim(tup)
  File "/Library/Frameworks/Python.framework/Versions/3.9/lib/python3.9/site-packages/pandas/core/indexing.py", line 799, in _getitem_lowerdim
    return self._handle_lowerdim_multi_index_axis0(tup)
  File "/Library/Frameworks/Python.framework/Versions/3.9/lib/python3.9/site-packages/pandas/core/indexing.py", line 1088, in _handle_lowerdim_multi_index_axis0
    raise ek
  File "/Library/Frameworks/Python.framework/Versions/3.9/lib/python3.9/site-packages/pandas/core/indexing.py", line 1080, in _handle_lowerdim_multi_index_axis0
    return self._get_label(tup, axis=axis)
  File "/Library/Frameworks/Python.framework/Versions/3.9/lib/python3.9/site-packages/pandas/core/indexing.py", line 1073, in _get_label
    return self.obj.xs(label, axis=axis)
  File "/Library/Frameworks/Python.framework/Versions/3.9/lib/python3.9/site-packages/pandas/core/generic.py", line 3733, in xs
    loc, new_index = index._get_loc_level(
  File "/Library/Frameworks/Python.framework/Versions/3.9/lib/python3.9/site-packages/pandas/core/indexes/multi.py", line 3038, in _get_loc_level
    raise KeyError(key) from e
KeyError: ('2021-10-25 09:15:00', 'BBB', 1)

The reason is that there is no record for BBB for the datetimestamp - '2021-10-25 09:15:00' for period value 1.

I was able to overcome the error by taking the minimum for each of the timestamps, but this is adding an extra time as this has to be generated for each stock.

Would appreciate guidance on how I can catch this exception and if it occurs move to the next stock.

CodePudding user response：

Use try - except KeyError:

stock_list = ['AAA','BBB']
index_slice = pd.IndexSlice
for stock in stock_list:
    try:
        open_price = period_file_df.loc[index_slice[('2021-10-25 09:15:00'), stock, 1]]['open']
        print (open_price)
    except KeyError:
        print ('keyerror')