How to filter pandas dataframe based on date value with exact match

user_7967955 0
Viewed 28001

I have been trying to filter my data frame for the specific date although the date is present in the data frame but it doesn't return any results

Data in Data frame based on query

df[df['Date'] > '2017-03-20']

returns this results

StaffID     Date        
90047   2017-03-20 19:00:00     
90049   2017-03-20 19:00:00     
90049   2017-03-27 19:00:00

although when i am running this query

df[df['Date'] == '2017-03-20']

or

df.loc[df['Date'] == '2017-03-20']

it returns me no results at all just an empty data frame

StaffID     Date

my data frame column types are 

StaffID                int64
Date          datetime64[ns]

and i have tried above query by comparing data frame date with string as well as by converting the string date into datetime64[ns] still the same results any help please would be appreciated

pandas dataframe numpy python
3 Answers

Use dt.date astype string then compare i.e 

df[df['Date'].dt.date.astype(str) == '2017-03-20']

Output: 

  StaffID                Date
0    90047 2017-03-20 19:00:00
1    90049 2017-03-20 19:00:00
user_48006520

You can do string comparison

df[df['Date'].astype(str).str[:10] == '2017-03-20']


    StaffID Date
0   90047   2017-03-20 19:00:00
1   90049   2017-03-20 19:00:00
user_62873080

The date i was using is '2017-03-20 19:00:00' which is > than '2017-03-20 00:00:00' thats why it wasn't comparing it right the best way to do it is

df.Date = df.Date.dt.date
dateToMatch = np.datetime64('2017-03-20')
df[df.Date == dateToMath]

above code returns 

   StaffID     Date
0   90047   2017-03-20
1   90049   2017-03-20

this will only extract date from my date column and replace the old column which had time

Credit: Wen who answered me in comment.

user_79679550
Related