pandas dataframe index datetime.date converts to object KeyError

Question

I retrieve some data from my MySQL database. This data has the date (not datetime) in one column and some other random data in the other columns. Let's say dtf is my dataframe. There is no index yet so I set one

    dtf.set_index('date', inplace=True)

Now I would like to get data from a specific date so I write for example

    dtf.loc['2000-01-03']

or just

    dtf['2000-01-03']

This gives me a KeyError:

    KeyError: '2000-01-03'

But I know its in there. dtf.head() shows me that.
So I did take a look at the type of the index of the first row:

    type(dtf.index[0])

and it tells me: datetime.date. All good, now what happens if I just type

    dtf.index

    Index([2000-01-03, 2000-01-04, 2000-01-05, 2000-01-06, 2000-01-07, 2000-01-10,
    2000-01-11, 2000-01-12, 2000-01-13, 2000-01-14,
    ...
    2015-09-09, 2015-09-10, 2015-09-11, 2015-09-14, 2015-09-15, 2015-09-16,
    2015-09-17, 2015-09-18, 2015-09-21, 2015-09-22],
    dtype='object', name='date', length=2763)

I am a bit confused about the dtype='object'. Shouldn't this read datetime.date?

If I use datetime in my mysql table instead of date everything works like a charm. Is this a bug or a feature? I really would like to use datetime.date because it describes my data best.

My pandas version is 0.17.0
I am using python 3.5.0
My os is arch linux

Answer 1

You should use datetime64/Timestamp rather than datetime.datetime:

dtf.index = pd.to_datetime(dtf.index)

will mean you have a DatetimeIndex and can do nifty things like loc by strings.

dtf.loc['2000-01-03']

You won't be able to do that with datetime.datetime.