Convert pandas column to datetime with unusual max value

user_6266810 0
Viewed 2857

I have a DataFrame read in from a database. It holds dates telling me the validity of a row, unfortunately in a Decimal format (14 digits like this 20190130110429); also if the entry is still valid (i.e. the date is in the future) the column holds 999999999999999 (15 nines).

When I try to convert it to datetime with 

pd.to_datetime(df['date'],format='%Y%m%d%H%M%S')

it fails for the 999... entries. I tried to consider both cases separately

df['date'] = df['date'].astype(str)
mask = df['date']=='999999999999999'
df.loc[~mask,'date'] = pd.to_datetime(df.loc[~mask,'date'],format='%Y%m%d%H%M%S')
df.loc[mask,'date'] = pd.datetime.max # also tried np.datetime64(pd.datetime.max,unit='s')

But this mixes the types:

[(x,type(x)) for x in df['date']

[(datetime.datetime(9999, 12, 31, 23, 59, 59, 999999), datetime.datetime),
 (1548846269000000000, int),
...
]

Any ideas how to nicely handle this?

PS: Just before sending the question, I just solved it by 

    df['date'] = df['date'].astype(str)
    df['date'] = df['date'].str.replace('999999999999999',pd.Timestamp.max.strftime('%Y%m%d%H%M%S'))
    df['date'] = pd.to_datetime(df['date'],format='%Y%m%d%H%M%S')

This gives me '2262-04-11 23:47:16.854775807' as the max date. (How) Is it possible store the datetime64[s] or [us] maximum instead of the datetime64[ns]?

pandas datetime
1 Answers

pd.to_datetime returns a Timestamp:

pd.to_datetime('20190130110429')
# Timestamp('2019-01-30 11:04:29')

This is pandas' native datetime format. OTOH, pd.datetime is a different type (datetime.datetime). They are different and sometimes not compatible. What you have is a good example of this. Specifically, you'll notice they have different max values.

pd.datetime.max
# datetime.datetime(9999, 12, 31, 23, 59, 59, 999999)
pd.Timestamp.max
# Timestamp('2262-04-11 23:47:16.854775807')

Since the former is not compatible with the latter, the result is an object column (pandas cannot convert it into a scalar Timestamp column) and the result is a weird mix of dates and integers.

My suggestion would be replace and fillna:

df['date'] = (
    pd.to_datetime(df['date'].replace('999999999999999', np.nan))
      .fillna(pd.Timestamp.max))
user_49090870
Related