I had date values in two formats (6/13/2018 and 6-13-2018). I had to calculate date difference. Below is my working.
Question: Number of days are coming out to be incorrect for few items.
X['Date of Closing'] = X['Date of Closing'].str.replace('/','-')
X['Date of First Contact'] = X['Date of First Contact'].str.replace('/','-')
X['Date Difference'] = (pd.to_datetime(X['Date of Closing'])- pd.to_datetime(X['Date of First Contact'])).dt.days
Example:
Date of First Contact | Date of Giving Proposal | Date of Closing \
0 13-01-2014 26-02-2014 26-02-2014
1 28-01-2014 2/2/2014 2-2-2014
2 11-1-2014 26-01-2014 26-01-2014
3 18-01-2014 18-01-2014 18-01-2014
4 14-01-2014 14-01-2014 14-01-2014
5 5-1-2014 14-01-2014 14-01-2014
Output:
44 - Correct
5 - Correct
-279 - Incorrect
0 - Correct
0 - Correct
-107 - Incorrect
I believe need parameter dayfirst=True or format:
X['Date Difference'] = (pd.to_datetime(X['Date of Closing'], dayfirst=True)-
pd.to_datetime(X['Date of First Contact'], dayfirst=True)).dt.days
X['Date Difference'] = (pd.to_datetime(X['Date of Closing'], format='%d-%m-%Y')-
pd.to_datetime(X['Date of First Contact'], format='%d-%m-%Y')).dt.days
print (X)
Date of First Contact Date of Giving Proposal Date of Closing \
0 13-01-2014 26-02-2014 26-02-2014
1 28-01-2014 2/2/2014 2-2-2014
2 11-1-2014 26-01-2014 26-01-2014
3 18-01-2014 18-01-2014 18-01-2014
4 14-01-2014 14-01-2014 14-01-2014
5 5-1-2014 14-01-2014 14-01-2014
Date Difference
0 44
1 5
2 15
3 0
4 0
5 9