Difference Between two dates columns

Question

I have to find difference between two date columns in python dataframe and to compare whether difference is greater than 120 or not

if working_data['CLAIMS_EVENT_DATE'] - working_data['LAST_LAPSED_DATE'] > 120:

I got below Error

invalid_comparison .format(dtype=left.dtype, typ=type(right).name))

TypeError: Invalid comparison between dtype=timedelta64[ns] and int

what about (working_data['CLAIMS_EVENT_DATE'] - working_data['LAST_LAPSED_DATE']).dt.days >120 — splash58
– splash58, Commented Oct 18, 2019 at 13:31
working_data is a dataframe name. CLAIMS_EVENT_DATE and LAST_LAPSED_DATE are column names — kirankumar M
– kirankumar M, Commented Oct 18, 2019 at 13:32

jezrael · Accepted Answer · 2019-10-18 13:32:09Z

1

If compare both get timedeltas, so for compare are possible 2 solutions - compare days by Series.dt.days with Series.any if need test if at least one value match condition:

s = (working_data['CLAIMS_EVENT_DATE'] - working_data['LAST_LAPSED_DATE'])

if (s.dt.days > 120).any():
    print ('At least one value is higher')

Or compare by Timedelta:

if (s > pd.Timedelta(120, unit='d')).any():
    print ('At least one value is higher')

If need fitler rows use boolean indexing:

df = working_data[s.dt.days > 120]

Or:

df = working_data[s > pd.Timedelta(120, unit='d')]

answered Oct 18, 2019 at 13:32

jezrael

868k103 gold badges1.4k silver badges1.3k bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Priya · Accepted Answer · 2019-10-18 13:51:38Z

0

#Convert both columns to datetime format
working_data['CLAIMS_EVENT_DATE'] = pd.to_datetime(working_data['CLAIMS_EVENT_DATE'])
working_data['LAST_LAPSED_DATE'] = pd.to_datetime(working_data['LAST_LAPSED_DATE'])

#Calculate the difference between the days
working_data['Days'] = (working_data['LAST_LAPSED_DATE']                           
                        - working_data['CLAIMS_EVENT_DATE']).days

#Create a column 'Greater' and check whether difference is greater than 120 or not
working_data.loc[working_data.Days <= 120, 'Greater'] = 'False' 
working_data.loc[working_data.Days > 120, 'Greater'] = 'True'

edited Oct 18, 2019 at 13:51

answered Oct 18, 2019 at 13:48

Priya

666 bronze badges

1 Comment

Priya Over a year ago

Yes, you are correct. The TypeError is due to the comparison between different datatypes.

Collectives™ on Stack Overflow

Difference Between two dates columns

2 Answers 2

Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related