Convert Hour column to datetime failes

user_12285101 0
Viewed 101

I have the following table in pandas:

>>>TIMESTAMP                date         Hour         patient1     patient2    patient3   patient4 ....
0  2019-06-13 12:00:00     2019-06-13   12:00:00      -0.456       -0.124      -0.451     -2.257
1  2019-06-13 12:03:00     2019-06-13   12:03:00      -0.456       -0.134      -0.781     -2.357
2  2019-06-13 12:06:00     2019-06-13   12:06:00      -0.876       -0.238      -0.983     -1.824
...
   2019-07-04 22:03:00     2019-07-04   12:03:00      -0.568       -0.532      -0.451      0.789
n  2019-07-04 22:06:00     2019-07-04   12:06:00      -0.021       -0.981      -0.293      1.824

I have timestamp column which is datetime and I have the column date and hour which I split from the TIMESTAMP column as following:

# #two cOlumns for hour and for date
df['Date'] = [d.date() for d in df['Timestamp']]
df['Hour'] = [d.time() for d in df['Timestamp']]

The problem is that I want to select from my database only rows that are between 2019-06-20 to 2019-07-02 and that the hour will be between 07:00 to 17:00.That doesn't work due to problems with the datetime format.

I was trying to do it using between:


df=df[(df['date'].between('2019-06-20','2019-07-02'))&(df['Hour'].between('07:00','17:00'))]

but then I have gotten this error:

TypeError: '>=' not supported between instances of 'datetime.date' and 'str'

so I checked the dtypes and found that date and hour are object type, so I tried to change their type like this:

df['date'] = pd.to_datetime(df['date']).dt.date
df['Hour'] = pd.to_datetime(df['Hour']).dt.time

but got error:

TypeError: <class 'datetime.time'> is not convertible to datetime

My end goal is to be able to select only rows that are inside the date and hour ranges that I want (between 2019-06-20 to 2019-07-02 and that the hour will be between 07:00 to 17:00).

pandas between datetime python
2 Answers

Use Series.dt.floor for remove times instead date and for comapre times use time constructor:

print (df)
                           date      Hour  patient1  patient2  patient3
TIMESTAMP                                                              
2019-06-13 12:00:00  2019-06-25  12:00:00    -0.456    -0.124    -0.451 <-date for match
2019-06-13 12:03:00  2019-06-13  12:03:00    -0.456    -0.134    -0.781
2019-06-13 12:06:00  2019-06-13  12:06:00    -0.876    -0.238    -0.983
2019-07-04 22:03:00  2019-07-04  12:03:00    -0.568    -0.532    -0.451
2019-07-04 22:06:00  2019-07-04  12:06:00    -0.021    -0.981    -0.293

from datetime import time

df['date'] = pd.to_datetime(df['date']).dt.floor('d')
#it looks already time column, so should be removed
#df['Hour'] = pd.to_datetime(df['Hour'].astype(str)).dt.time

df=df[(df['date'].between('2019-06-20','2019-07-02')&
       df['Hour'].between(time(7,0,0),time(17,0,0)))]

print (df)
                          date      Hour  patient1  patient2  patient3
TIMESTAMP                                                             
2019-06-13 12:00:00 2019-06-25  12:00:00    -0.456    -0.124    -0.451
user_29010020

You better change the time and date to numbers (int) in the following format: date to -> YYYYMMDD time to -> HHMM

By the following code:

df['time'] = df['Timestamp'].dt.strftime('%H%M').astype(int)
df['date'] = df['Timestamp'].dt.strftime('%Y%m%d').astype(int)

Then you can easily compare with integers:

df=df[(df['date'].between(20190620,20190702))&(df['Hour'].between(700,1700))]
user_108363090
Related