Get column in datetime format and check if value is in it

user_14088919 0
Viewed 1152

Basically, I have a pandas dataframe where a column is a string of dates. I want to transform each value into datetime so I can look for a specific value there later, like this:

df = pd.DataFrame({'DATESTAMP': ['01/01/2017','02/01/2017','03/01/2017']})

df['DATESTAMP2']=pd.to_datetime(df['DATESTAMP'])

date = datetime.strptime('02/01/2017', '%d/%m/%Y')

date in df['DATESTAMP2']
False

As you can see, it doesn't detect the date. I checked why, and I found that the format of the new DATESTAMP2 column is

    df['DATESTAMP2']
Out[296]: 
0   2017-01-01
1   2017-01-02
2   2017-01-03
Name: DATESTAMP2, dtype: datetime64[ns]

df['DATESTAMP2'][2]
Out[279]: Timestamp('2017-01-03 00:00:00')

This is not datetime, it's a timestamp, which makes sense that it wouldn't be able to compare it with the date var.

Is there a way to solve this, aka to actually convert the column to datetime and not timestamp?

pandas date datetime python
1 Answers

Two issues:

  1. Your dates are in two different formats (mm/dd/yyyy and dd/mm/yyyy)
  2. The in operator isn't doing what you expect. It looks at index not values. Usually you just use .values, but for dates that doesn't usually work so you need to use to_list().

Here is the two changes in context. Note addition of the format in to to_datetime() to deal with 1) and the to_list() to deal with 2)

import pandas as pd
from datetime import datetime

df = pd.DataFrame({'DATESTAMP': ['01/01/2017','02/01/2017','03/01/2017']})

df['DATESTAMP2']=pd.to_datetime(df['DATESTAMP'], format='%d/%m/%Y')

date = datetime.strptime('02/01/2017', '%d/%m/%Y')

print(date in df['DATESTAMP2'].to_list())

The timestamp vs datetime thing is not an issue. From Timestamp Docs:

Timestamp is the pandas equivalent of python’s Datetime and is interchangeable with it in most cases.

user_82171120
Related