I change the field of 'date' to datetime in order to calculate the difference between their cells and 'today'

df['date']=pd.to_datetime(df['date'], format='%Y-%d-%m %H:%M:%S',errors='ignore')

so i can try below code for several random indexes such as below:

pd.Timestamp.now().normalize() - df['date'][1003]

Timedelta('2598 days 00:00:00')

as you see it works.

but when i run this code i got error:

df['Diff']=df['date'].agg(lambda x:pd.Timestamp.now().normalize()-x)

TypeError: unsupported operand type(s) for -: 'Timestamp' and 'str'

CodePudding user response：

EDIT:

how can I recognize bad dates? i need all of dates with any NaT.

Filter missing values NaT generated if not match pattern %Y-%d-%m %H:%M:%S or if datetime not exist like 2020-02-30 12:02:10:

out = df.loc[pd.to_datetime(df['date'],format='%Y-%d-%m %H:%M:%S',errors='coerce').isna(), 'date']

Problem is with errors='ignore' it working different like you think.

It return same ouput if at least one wrong datetime, so is possible get mixed datetimes with strings or only string column (like original column date).

If need datetime column need replace bad dates to NaT by:

df['date']=pd.to_datetime(df['date'], format='%Y-%d-%m %H:%M:%S',errors='coerce')

df['Diff'] = pd.Timestamp.now().normalize() - df['date']