How to compare two date columns in a dataframe using pandas?

Question

I have to compare two date columns in a dataframe, I have tried np.where method

end_date = datetime.strptime(df3['end_date'], '%Y-%m-%d')

start_date = datetime.strptime(df3['start_date'], '%Y-%m-%d')

diff = end_date - start_date

if diff > 0:
    raise Exception("employee's leave 'start_date' cannot be greater than leave 'end_date'")

error: strptime() argument 1 must be str, not Series

Answer 1

There's a easiest way.

You don't have to convert them with datetime. You can do it directly with pandas.

There's a api available with pandas as to_datetime

To substract between two days, first convert the string to datetime.

df3['dt_end_date'] = pd.to_datetime(df3['end_date'])
df3['dt_start_date'] = pd.to_datetime(df3['start_date'])

df3['difference'] = df3['dt_end_date'] - df3['dt_start_date']

That's it you will have the answer !!!

Answer 2

I think you're confusing how to use it dataframe here.

If you consider a normal value, you can use datetime module but you should use to_datetime() to convert a type of columne and dt attribute to count days.

For your goal, you can make another columne for diff to save timedelta, then you can check if there is negative value in the diff columns, as follows:

import pandas as pd

df3 = pd.DataFrame({
    'start_date': ['2020-01-09', '2020-02-02'],
    'end_date': ['2020-01-11', '2020-02-01']
})

df3['end_date'] = pd.to_datetime(df3['end_date'], format='%Y-%m-%d')
df3['start_date'] = pd.to_datetime(df3['start_date'], format='%Y-%m-%d')

df3['diff'] = df3['end_date'].dt.day - df3['start_date'].dt.day

print(df3)

#  start_date   end_date  diff
#0 2020-01-09 2020-01-11     2
#1 2020-02-02 2020-02-01    -1

if any(val < 0 for val in df3['diff'].values): # to check if negative value exists
    raise Exception("employee's leave 'start_date' cannot be greater than leave 'end_date'")

You can raise Exception if there is any negative days, as follows:

Traceback (most recent call last):
  File "C:/Users/ABC/stackoverflow/main.py", line 16, in <module>
    raise Exception("employee's leave 'start_date' cannot be greater than leave 'end_date'")
Exception: employee's leave 'start_date' cannot be greater than leave 'end_date'