0

When I resample dataframe I get key error on the index col.

Here is my code:

  if setting == 'resample':
    Freq_r = "60Min" #Min
    df1 = df1.set_index('date')
    df1 = df1.resample(Freq_r).agg({'open': 'first', 'high': 'max','low': 'min', 'close': 'last'})
    df2 = df2.set_index('date')
    df2 = df2.resample(Freq_r).agg({'open': 'first', 'high': 'max','low': 'min', 'close': 'last'})

Without resampling data is like this:

    date    open_x  high_x  low_x   close_x v1_x    v2_x    conid_x open_y  high_y  low_y   close_y v1_y    v2_y    conid_y
0   1988-10-20 10:30:00 13.39   13.39   13.39   13390.0 1   0   QOA1988Z    14.61   14.63   14.61   14620.0 4   0   CLA1988Z
1   1988-10-31 10:30:00 12.80   12.80   12.80   12800.0 1   0   QOA1988Z    13.83   13.84   13.83   13840.0 2   0   CLA1988Z
2   1988-11-07 10:30:00 13.27   13.27   13.27   13270.0 1   0   QOA1989F    13.97   13.97   13.97   13970.0 1   0   CLA1988Z
3   1988-11-15 10:30:00 13.20   13.20   13.20   13200.0 1   0   QOA1989F    14.02   14.02   14.02   14020.0 1   0   CLA19

reset_index, inplace=True etc didn't help. How to fix this? Thank you.

Error message: KeyError Traceback (most recent call last) /usr/local/lib/python3.7/dist-packages/pandas/core/indexes/base.py in get_loc(self, key, method, tolerance) 3360 try: -> 3361 return self._engine.get_loc(casted_key) 3362 except KeyError as err:

4 frames pandas/_libs/hashtable_class_helper.pxi in pandas._libs.hashtable.PyObjectHashTable.get_item()

pandas/_libs/hashtable_class_helper.pxi in pandas._libs.hashtable.PyObjectHashTable.get_item()

KeyError: 'date'

The above exception was the direct cause of the following exception:

KeyError Traceback (most recent call last) /usr/local/lib/python3.7/dist-packages/pandas/core/indexes/base.py in get_loc(self, key, method, tolerance) 3361 return self._engine.get_loc(casted_key) 3362 except KeyError as err: -> 3363 raise KeyError(key) from err 3364 3365 if is_scalar(key) and isna(key) and not self.hasnans:

KeyError: 'date'

Night Cheetah
  • 11
  • 2
  • Could you [edit] to include the full traceback? At a quick glance it'd appear that your columns don't exist... eg: open/high/low/close... should be either open_x/open_y etc... – Jon Clements Jun 12 '22 at 08:39

1 Answers1

0

df= dfx.reset_index(level='date')

seems to have resolved it. I assigned the original df into a temp df called dfx and reset index specifying date.

I don't understand the issue or the resolution though, any explanation will help. Thank you.

Night Cheetah
  • 11
  • 2