ValueError: mixed datetimes and integers in passed array

Question

I would like to convert the timestamp column to datetime format in a pandas dataframe. I used:

df['My_Datetime'] = pd.to_datetime(df['My_Datetime'])

df looks like:

    ID            Timestamp
0   AM12C         2002-11-30 00:00:00
2   BX900         2008-06-30 00:00:00
3   C1200         2012-06-30 00:00:00
                  ...

and it caught error:

ValueError: mixed datetimes and integers in passed array

I removed the duplicates:

df.drop_duplicates(keep=False, inplace=True)

and tried again, but no luck.

Does this answer your question? [changing all dates to standard date time in dataframe](https://stackoverflow.com/questions/48180685/changing-all-dates-to-standard-date-time-in-dataframe) — Himanshu Poddar, Jul 11 '22 at 05:08
Looks like you have integers in your column, is this expected? — mozway, Jul 11 '22 at 05:08
Hi @mozway Thanks, this was not expected but after checking the data it's indeed the case — nilsinelabore, Jul 11 '22 at 05:51

René · Accepted Answer · 2022-08-22T11:03:05.930

2

I expect adding errors='coerce' will help:

import sys
import pandas as pd
from io import StringIO
from datetime import datetime

data = StringIO("""ID;Timestamp
AM12C;2002-11-30 00:00:00
BX900;2008-06-30 00:00:00
C1200;2012-06-30 00:00:00
Z9999;1000
""")

df = pd.read_csv(data, sep=";")
df['Timestamp'] = pd.to_datetime(df['Timestamp'], errors='coerce')
print(df.info())

Result:

      ID  Timestamp
0  AM12C 2002-11-30
1  BX900 2008-06-30
2  C1200 2012-06-30
3  Z9999        NaT

edited Aug 22 '22 at 11:03

answered Jul 11 '22 at 05:29

René

4,594
5
23
52

It will hide the error, but I believe this should not happen in the first place, OP should better ensure why there is a mixed type. – mozway Jul 11 '22 at 05:49
Can you add the output from `print(df.to_csv(index=False))` to your question to make the issue more reproducible? – René Jul 11 '22 at 06:09

score 1 · Answer 2 · answered Jul 11 '22 at 05:22

To get this error, you would need to already have converted to datetime, then replaced one value with an integer:

s = pd.Series(['2002-11-30 00:00:00', '2008-06-30 00:00:00', 0])
s = pd.to_datetime(s)
# works fine until here

s.iloc[-1] = 0
pd.to_datetime(s)
# now raises an error

output ValueError: mixed datetimes and integers in passed array

So, don't convert to datetime multiple times, and make sure to understand where this integer is coming from after the first conversion to datetime.

ValueError: mixed datetimes and integers in passed array

2 Answers2