select rows from a datetime column with pandas

user_11257521 0
Viewed 868

I hava a dataframe with many columns, one of which (receivedtime) has been properly converted to datetime

id   receivedtime   
1    2020-09-08 00:35:12  
2    2020-09-09 06:16:09  
3    2020-09-10 12:47:04
4    2020-09-08 15:57:05

and so on. I want to select only those from a given day (this is similar to this post:subsets from a datetime column, which helps but dont work for me). I have tried to do a simple substetting:

df[df["receivedtime"]=="2020-09-10"]

but I got an empty dataframe in return. I believe it has to do with the hours minutes and seconds.Obviously one way is to strip that info from datetime, but I want to preserve the whole structure. I even tried using dt.datetime but nothing seems to work. What is wrong in here?

Any help is appreciated.

pandas slice datetime
3 Answers

This is because your receivedtime is not always a sharp date, i.e. having time equal to 00:00:00. Try, for example, dt.normalize() to get the dates from the timestamp:

 df[df["receivedtime"].dt.normalize()=="2020-09-10"]

Output:

   id        receivedtime
2   3 2020-09-10 12:47:04
user_42384080

You can try the below to convert your datetime to date

df[pd.to_datetime(df["receivedtime"]).dt.strftime('%Y-%m-%d')=="2020-09-10"]
user_69148640

First create a DataFrame "new" by splitting the date at a whitespace

new = df["receivedtime"].str.split(" ", expand = True)
new

output

             0        1
0    2020-09-08 00:35:12  
1    2020-09-09 06:16:09  
2    2020-09-10 12:47:04  
3    2020-09-08 15:57:05

Then use the boolean masking

df[new[0]=="2020-09-08"]

Output:

    receivedtime
0   2020-09-08 00:35:12
3   2020-09-08 15:57:05

You will get all the dates with time

user_136407000
Related