Pandas throws ValueError while converting to datetime from string

Question

I have an excel sheet with a column that is supposed to contain date values but pandas reads it as float64. It has blanks

df:
date_int
15022016
23072017

I want to convert to a datetime object. I do:

df['date_int1'] = df['date_int'].astype(str).fillna('01011900')#To fill the blanks
df['date_int2']=pd.to_datetime(df['date_int1'],format='%d%m%Y')

I get error while converting to datetime:

TypeError: Unrecognized value type: <class 'str'>
ValueError: unconverted data remains: .0

Thank you, I get: int() argument must be a string, a bytes-like object or a number, not 'NoneType' — Victor
– Victor, Commented Mar 5, 2019 at 20:33

cs95 · Accepted Answer · 2019-03-05 20:34:56Z

1

You shouldn't convert to string until you've filled the NaNs. Otherwise, the NaNs are also stringified, and at the point there is nothing to fill.

df

     date_int
0  15022016.0
1  23072017.0
2         NaN

df['date_int'] = df['date_int'].fillna(1011900, downcast='infer').astype(str)
pd.to_datetime(df['date_int'], format='%d%m%Y', errors='coerce')

0   2016-02-15
1   2017-07-23
2   1900-01-10
Name: date_int, dtype: datetime64[ns]

answered Mar 5, 2019 at 20:34

cs95

406k106 gold badges744 silver badges797 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

John Sloper · Accepted Answer · 2019-03-05 20:34:39Z

0

See comment from @Wen-Ben. Convert the data to int first.

df.date_int = df.date_int.astype(int)

Then the rest of the code will work fine.

answered Mar 5, 2019 at 20:34

John Sloper

1,84112 silver badges14 bronze badges

Collectives™ on Stack Overflow

Pandas throws ValueError while converting to datetime from string

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related