How to convert a column of data in a DataFrame filled with string representation of non-uniformed date formats to datetime?

Question

Let's say:

>>> print(df)

    location        date
    paris     23/02/2010
    chicago    3-23-2013
    ...
    new york  04-23-2013
    helsinki  13/10/2015

Currently, df["date"] is in str. I want to convert the date column to datetime using

>>> df["date"] = pd.to_datetime(df["date"])

I would get ValueError due to ParserError. This is because the format of the date is inconsistent (i.e. dd/mm/yyyy, then next one is m/dd/yyyy).

If I were to write the code below, it still wouldn't work due to the date not being uniformed and delimiters being different:

>>> df["date"] = pd.to_datetime(df["date"], format="%d/%m/%Y")

The last option that I could think of was to write the code below, which replaces all of the dates that are not formatted like the first date to NaT:

>>> df["date"] = pd.to_datetime(df["date"], errors="coerce")

How do I convert the whole date column to datetime while having the dates not uniform in terms of the delimiters, and the orders of days, months and years?

Anurag Dhadse · Accepted Answer · 2020-09-04 13:27:55Z

1

use, apply method of pandas

df['date'] = df.apply(lambda x: pd.to_datetime(x['date']),axis = 1)

edited Sep 4, 2020 at 13:27

answered Sep 4, 2020 at 13:20

Anurag Dhadse

1,8732 gold badges15 silver badges28 bronze badges

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

Collectives™ on Stack Overflow

How to convert a column of data in a DataFrame filled with string representation of non-uniformed date formats to datetime?

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related