(Python) Convert float to datetime

Question

I have a dataset like this:

df = pd.DataFrame({'name': ['Amy', 'Chris', 'Sam'], 'date': [1.597104e+12, 1.600906e+12, np.nan]})
print(df)

   name          date
0   Amy  1.597104e+12
1 Chris  1.600906e+12
2   Sam           NaT

I checked the types of date which shows float64, then I used pd.to_datetime to convert it. But I got the result that is not correct.

df['date'] = pd.to_datetime(df['date'])
print(df)

    name                     date
0    Amy  1970-01-01 00:26:37.104
1  Chris  1970-01-01 00:26:40.906
2    Sam                      NaT

I also tried df['date'] = pd.to_timedelta(df['date'], unit='d') + pd.to_datetime('1899-12-30'), but the result below is still incorrect.

    name                           date
0    Amy  2055-09-23 23:20:48.000057344
1  Chris  1839-08-30 13:23:48.304879616
2    Sam                            NaT

I really have no clue about fixing this, so can someone assist here? Thank you in advance!

Answer 1

That's a Unix time_t value (seconds since 1970-01-01) but in milliseconds. The first one is 2020-08-11. Pandas expects floating point time values to be in nanoseconds. So, this will work:

df['date'] = pd.to_datetime(df['date'] * 1000000)

As in:

>>> pd.to_datetime(df['date']*1000000)
0   2020-08-11 00:00:00
1   2020-09-24 00:06:40
2                   NaT
Name: date, dtype: datetime64[ns]
>>>

Answer 2

As Tim mentioned, your units weren't the default units for pandas.to_datetime(). And, while his method certainly works, its probably not what you would want for a very large number of conversions since the extra step adds about 8% more computing time. So, this

vs this

Both of which give you:

    name    date
0   Amy     2020-08-11 00:00:00
1   Chris   2020-09-24 00:06:40
2   Sam     NaT