changing a column to datetime format with specified origin

user_14220926 0
Viewed 1282
data['DATE'] = pd.to_datetime(data['DATE'], unit = 'ns', origin = "1899-12-30")

gives

---------------------------------------------------------------------------
ValueError                                Traceback (most recent call last)
<ipython-input-23-d33b780eb61c> in <module>
----> 1 data['DATE'] = pd.to_datetime(data['DATE'], unit = 'ns', origin = "1899-12-30")

~/opt/anaconda3/lib/python3.7/site-packages/pandas/core/tools/datetimes.py in to_datetime(arg, errors, dayfirst, yearfirst, utc, format, exact, unit, infer_datetime_format, origin, cache)
    700 
    701     if origin != "unix":
--> 702         arg = _adjust_to_origin(arg, origin, unit)
    703 
    704     tz = "utc" if utc else None

~/opt/anaconda3/lib/python3.7/site-packages/pandas/core/tools/datetimes.py in _adjust_to_origin(arg, origin, unit)
    499         ):
    500             raise ValueError(
--> 501                 f"'{arg}' is not compatible with origin='{origin}'; "
    502                 "it must be numeric with a unit specified"
    503             )

ValueError: '0        1970-01-01 00:00:00.000043390
2        1970-01-01 00:00:00.000043605
3        1970-01-01 00:00:00.000043329
6        1970-01-01 00:00:00.000043601
8        1970-01-01 00:00:00.000043332
                      ...             
264815   1970-01-01 00:00:00.000043408
264816   1970-01-01 00:00:00.000043614
264826   1970-01-01 00:00:00.000043549
264830   1970-01-01 00:00:00.000043416
264834   1970-01-01 00:00:00.000043461
Name: DATE, Length: 74570, dtype: datetime64[ns]' is not compatible with origin='1899-12-30'; it must be numeric with a unit specified

Any help appreciated.

pandas datetime python
2 Answers

Name: DATE, Length: 74570, dtype: datetime64[ns]' is not compatible with origin='1899-12-30'; it must be numeric with a unit specified

Solution is convert your DATE to numeric.

Dataframe['new_column']=pd.to_datetime(pd.to_numeric(Dataframe['column'],errors='coerce'),errors='coerce',origin='1899-12-30',unit='D')

Dataframe['new_column']

user_84763710

as described in the docs, if you have a format similar to julian (days since ...), you need to also provide unit='D' (for 'days'). Ex:

import pandas as pd

pd.to_datetime(1, unit='D', origin='1899-12-30')
>>> Timestamp('1899-12-31 00:00:00')
user_101974180
Related