CODEWITHSUNDEEP
excelpython-3.xpandas
herbert ichama
herbert ichama

Reputation: 81

Date Manipulation and Comparisons Python,Pandas and Excel

I have a datetime column[TRANSFER_DATE] in an excel sheet shows dates formated as 1/4/2019 0:45 when this date is selected, in it appears as 01/04/2019 00:45:08 am using a python scrip to read this column[TRANSFER_DATE] which shows the datetime as 01/04/2019 00:45:08

However when i try to compare the column[TRANSFER_DATE] whith another date, I get this error Can only use .dt accessor with datetimelike " ValueError: : "Can only use .dt accessor with datetimelike values" while evaluating

implying those values are not actually recognized as datetime values

mask_part_date = data.loc[data['TRANSFER_DATE'].dt.date.astype(str) == '2019-04-12']

Upvotes: 0

Views: 232

Answers (1)

Pepino
Pepino

Reputation: 330

As seen in this question, the Excel import might have silently failed for some of the values in the column. If you check the column type with:

data.dtypes

it might show as object instead of datetime64.

If you force your column to have datetime values, that might solve your issue:

data['TRANSFER_DATE'] = pd.to_datetime(data['TRANSFER_DATE'], errors='coerce')

You will spot the non-converted values as NaT and you can debug those manually.

Regarding your comparison, after the dataframe conversion to datetime objects, this might be more efficient:

mask_part_date = data.loc[data['TRANSFER_DATE'] == pd.Timestamp('2019-04-12')]

Upvotes: 1

Related Questions