Pandas: Subtracting two date columns and the result being an integer

Question

I have two columns in a Pandas data frame that are dates.

I am looking to subtract one column from another and the result being the difference in numbers of days as an integer.

A peek at the data:

df_test.head(10) Out[20]:    First_Date Second Date 0 2016-02-09  2015-11-19 1 2016-01-06  2015-11-30 2        NaT  2015-12-04 3 2016-01-06  2015-12-08 4        NaT  2015-12-09 5 2016-01-07  2015-12-11 6        NaT  2015-12-12 7        NaT  2015-12-14 8 2016-01-06  2015-12-14 9        NaT  2015-12-15 

I have created a new column successfully with the difference:

df_test['Difference'] = df_test['First_Date'].sub(df_test['Second Date'], axis=0) df_test.head()          Out[22]:    First_Date Second Date  Difference 0 2016-02-09  2015-11-19     82 days 1 2016-01-06  2015-11-30     37 days 2        NaT  2015-12-04         NaT 3 2016-01-06  2015-12-08     29 days 4        NaT  2015-12-09         NaT 

However I am unable to get a numeric version of the result:

df_test['Difference'] = df_test[['Difference']].apply(pd.to_numeric)       df_test.head() Out[25]:    First_Date Second Date    Difference 0 2016-02-09  2015-11-19  7.084800e+15 1 2016-01-06  2015-11-30  3.196800e+15 2        NaT  2015-12-04           NaN 3 2016-01-06  2015-12-08  2.505600e+15 4        NaT  2015-12-09           NaN 

Answer 1

How about:

df_test['Difference'] = (df_test['First_Date'] - df_test['Second Date']).dt.days 

This will return difference as int if there are no missing values(NaT) and float if there is.

Pandas have a rich documentation on Time series / date functionality and Time deltas

Answer 2

You can divide column of dtype timedelta by np.timedelta64(1, 'D'), but output is not int, but float, because NaN values:

df_test['Difference'] = df_test['Difference'] / np.timedelta64(1, 'D') print (df_test)   First_Date Second Date  Difference 0 2016-02-09  2015-11-19        82.0 1 2016-01-06  2015-11-30        37.0 2        NaT  2015-12-04         NaN 3 2016-01-06  2015-12-08        29.0 4        NaT  2015-12-09         NaN 5 2016-01-07  2015-12-11        27.0 6        NaT  2015-12-12         NaN 7        NaT  2015-12-14         NaN 8 2016-01-06  2015-12-14        23.0 9        NaT  2015-12-15         NaN 

Frequency conversion.