Converting improper float to date-CodePudding

My dataframe has dates like this

dates
9112015.0
20032015.0
16042014.0

I have tried this

pd.to_datetime(fraud_df['DATEOPENEDDISBURSED'], format='%d%m%Y',dayfirst=True)

But it is giving me ValueError: day is out of range for month

CodePudding user response：

You'll have to

fill the NaNs
convert to string
and zfill with zeros to get zero-prefixed days consistently

EX:

import pandas as pd
import numpy as np

dates = pd.Series([9112015.0,20032015.0,16042014.0,np.nan])

# to get zero-prefixed days: to integer, then to string and zfill with zeros
dates = dates.fillna(-1).astype('int').astype('str').str.zfill(8)

# to datetime and coerce errors, giving NaT for the NaNs in the initial Series
pd.to_datetime(dates, format="%d%m%Y", errors='coerce')
0   2015-11-09
1   2015-03-20
2   2014-04-16
3          NaT
dtype: datetime64[ns]

CodePudding user response：

Try this:

pd.to_datetime(fraud_df['DATEOPENEDDISBURSED'], format='%-d%m%Y',dayfirst=True)

from the Python strftime cheatsheet https://strftime.org/:

%-d 8 Day of the month as a decimal number. (Platform specific)