Home > Mobile >  Dataframe cannot loc datetime index
Dataframe cannot loc datetime index

Time:02-17

Hi I'm trying to fetch a record from SQLite which contains a series of stock price then I want to check if a certain date has existed in the database or not.

I've tried googling stackoverflow but couldn't find any solution that works.

This is my code

data = pd.read_sql('AALI', idxengine)
data = data.set_index(pd.to_datetime(data['Date']))
data

Output:

enter image description here

Then you can see the index type

data.index

Output:

DatetimeIndex(['2019-01-02', '2019-01-03', '2019-01-04', '2019-01-07',
               '2019-01-08', '2019-01-09', '2019-01-10', '2019-01-11',
               '2019-01-14', '2019-01-15',
               ...
               '2022-01-31', '2022-02-02', '2022-02-03', '2022-02-04',
               '2022-02-07', '2022-02-08', '2022-02-09', '2022-02-10',
               '2022-02-11', '2022-02-14'],
              dtype='datetime64[ns]', name='Date', length=764, freq=None)

I want to get a row of a certain date

date = pd.to_datetime('2022-02-11', format='%Y-%m-%d')

data.loc(date)

But it's returning this error

---------------------------------------------------------------------------
KeyError                                  Traceback (most recent call last)
~/.local/lib/python3.8/site-packages/pandas/core/generic.py in _get_axis_number(cls, axis)
    459         try:
--> 460             return cls._AXIS_TO_AXIS_NUMBER[axis]
    461         except KeyError:

KeyError: Timestamp('2022-02-11 00:00:00')

During handling of the above exception, another exception occurred:

ValueError                                Traceback (most recent call last)
<ipython-input-236-a79f3c284d2d> in <module>
      1 date = pd.to_datetime('2022-02-11', format='%Y-%m-%d')
      2 
----> 3 data.loc(date)

~/.local/lib/python3.8/site-packages/pandas/core/indexing.py in __call__(self, axis)
    605 
    606         if axis is not None:
--> 607             axis = self.obj._get_axis_number(axis)
    608         new_self.axis = axis
    609         return new_self

~/.local/lib/python3.8/site-packages/pandas/core/generic.py in _get_axis_number(cls, axis)
    460             return cls._AXIS_TO_AXIS_NUMBER[axis]
    461         except KeyError:
--> 462             raise ValueError(f"No axis named {axis} for object type {cls.__name__}")
    463 
    464     @final

ValueError: No axis named 2022-02-11 00:00:00 for object type DataFrame

CodePudding user response:

The problem is that you use .loc() instead of .loc[]

You don't need to convert your date as a datetime, use .loc with a string:

df.loc['2022-02-11']
  • Related