Comparing dates in pandas timestamp to datetime,date-CodePudding

I have a pandas df. The dataframe is read from an excel. The column shortdate in the dataframe contains the dates. What I am trying to do is to obtain last weeks date, and then pull all the rows in the df that are between last weeks date and todays date. The problem is that the dates in the df and the dates in the excel file are different formats so I cant compare them properly. one is a time stamp and one is a type date.

In the case below I was just testing with a known value in the df to see if it works. but it doesn't work. it returns an empty df because the values do not exist in the shortdate column.

Main.py

todays_date = datetime.today().date() #todays date
date_of_last_week = todays_date - timedelta(days=7) #this is last weeks date

print('last weeks date :', date_of_last_week)
print('last weeks type is :', type(date_of_last_week))
entry_1000 = df['shortdate'][1000]
print('1000 entry date is:',entry_1000)
print('1000 entry type is:', type(entry_1000))
print('1000 entry date just date is:',entry_1000.date())
print('1000 entry just date type is:', type(entry_1000.date()))

just_date = entry_1000.date()
print(df_eb.loc[df['shortdate'] == just_date])

Results:

last weeks date : 2021-10-15
last weeks type is : <class 'datetime.date'>
1000 entry date is: 2020-03-28 00:00:00
1000 entry type is: <class 'pandas._libs.tslibs.timestamps.Timestamp'>
1000 entry date just date is: 2020-03-28
1000 entry just date type is: <class 'datetime.date'>
Empty DataFrame
Columns: [record, old record, Status, shortdate]
Index: []

CodePudding user response：

You can convert the shortdate column to datetime data-type using below code

df.shortdate = df.shortdate.apply(lambda x: x.date())