Question for slice object(datetime) type index in DataFrame

user_15607687 0
Viewed 52

i have a DataFrame named 'holidays', Date is index, as below, why Out[7] and Out[8] could happen? how to slice the row with index name 2020-01-01 like Out[9], many thanks

holidays:
Out[1]:
            CNY  USD  HKD  JPY  EUR  AUD  CAD
Date                                         
2020-01-01    1    1    1    1    1    1    1
2020-01-02    0    0    0    1    0    0    0
2020-01-03    0    0    0    1    0    0    0
2020-01-06    0    0    0    0    0    0    0
2020-01-07    0    0    0    0    0    0    0

holidays.index:
Out[2]:
Index([2020-01-01, 2020-01-02, 2020-01-03, 2020-01-06, 2020-01-07], dtype='object', name='Date', length=130)

holidays.index[0]：
Out[3]:datetime.date(2020, 1, 1)

holidays.index[0] == '2020-01-01'
Out[4]: False

xx = holidays.index[0]

xx
Out[5]: datetime.date(2020, 1, 1)

holidays.index[0] == xx
Out[6]: True

holidays.index[0] == datetime.date(2020, 1, 1)
Out[7]:
Traceback (most recent call last):

  File "<ipython-input-25-f72e3a7fa605>", line 1, in <module> holidays.index[0] == datetime.date(2020, 1, 1)

TypeError: descriptor 'date' for 'datetime.datetime' objects doesn't apply to a 'int' object

cfets_holidays['2020-01-01']
Out[8]:
Traceback (most recent call last):

  File "C:\Users\****\Anaconda3\lib\site-packages\pandas\core\indexes\base.py", line 3080, in get_loc
    return self._engine.get_loc(casted_key)

  File "pandas\_libs\index.pyx", line 70, in pandas._libs.index.IndexEngine.get_loc

  File "pandas\_libs\index.pyx", line 101, in pandas._libs.index.IndexEngine.get_loc

  File "pandas\_libs\hashtable_class_helper.pxi", line 4554, in pandas._libs.hashtable.PyObjectHashTable.get_item

  File "pandas\_libs\hashtable_class_helper.pxi", line 4562, in pandas._libs.hashtable.PyObjectHashTable.get_item

KeyError: '2020-01-01'

The above exception was the direct cause of the following exception:
Traceback (most recent call last):

  File "<ipython-input-33-a412e24bcffd>", line 1, in <module>
    holidays['2020-01-01']

  File "C:\Users\****\Anaconda3\lib\site-packages\pandas\core\frame.py", line 3024, in __getitem__
    indexer = self.columns.get_loc(key)

  File "C:\Users\****\Anaconda3\lib\site-packages\pandas\core\indexes\base.py", line 3082, in get_loc
    raise KeyError(key) from err

KeyError: '2020-01-01'

cfets_holidays.iloc[0:1]
Out[9]: 
            CNY  USD  HKD  JPY  EUR  AUD  CAD
Date                                         
2020-01-01    1    1    1    1    1    1    1
pandas dataframe datetime python
1 Answers

You can upgrade first to last version of pandas (tested in pandas 1.2.3) and then is possible use:

#compare by string get False
print (holidays.index[0] == '2020-01-01')
False

#compare by date get True
print (holidays.index[0] == datetime.date(2020, 1, 1))
True

#pandas want select column '2020-01-01' - not exist so failed
# print (holidays['2020-01-01'])
# KeyError: '2020-01-01'

#pandas want select string index '2020-01-01' - not exist so failed
# print (holidays.loc['2020-01-01'])
# KeyError: '2020-01-01'

#pandas select index by dates - working
print (holidays.loc[[datetime.date(2020, 1, 1)]])
            CNY  USD  HKD  JPY  EUR  AUD  CAD
2020-01-01    1    1    1    1    1    1    1

If convert dates to DatetimeIndex is possible use exact indexing:

df.index = pd.to_datetime(df.index)

print (df.index)
DatetimeIndex(['2020-01-01', '2020-01-02', '2020-01-03', '2020-01-06',
               '2020-01-07'],
              dtype='datetime64[ns]', freq=None)


print (holidays.loc[['2020-01-01']])
            CNY  USD  HKD  JPY  EUR  AUD  CAD
2020-01-01    1    1    1    1    1    1    1
user_29010020
Related