Desperately Need Advice on Converting Date Column

Question

I have a dataset that has mixed data types in the Date column. For example, the column looks like this:

ID     Date
1     2019-01-01
2     2019-01-02
3     2019-11-01
4     40993
5     40577
6     39949

When I just try to convert the column using pd.to_datetime, I get an error message "mixed datetimes and integers in passed array".

I would really appreciate it if someone could help me out with this! Ideally, it would be nice to have all rows in 'yyyy-mm-dd' format. Thank you!

Where are those integers from? are they some weird date format? You need to create a function that turns them into dates in YMD format. — Michael Hearn, Nov 11 '19 at 02:10
Possible duplicate of [Convert Excel style date with pandas](https://stackoverflow.com/questions/38454403/convert-excel-style-date-with-pandas) — AMC, Nov 11 '19 at 03:58

score 1 · Accepted Answer · answered Nov 11 '19 at 02:57

I'm guessing those are excel date format?

Convert Excel style date with pandas

import xlrd

def read_date(date):
    try:
        return xlrd.xldate.xldate_as_datetime(int(date), 0)
    except:
        return pd.to_datetime(date)


df['New Date'] = df['Date'].apply(read_date)

df

Out[1]: 
   ID        Date   New Date
0   1  2019-01-01 2019-01-01
1   2  2019-01-02 2019-01-02
2   3  2019-11-01 2019-11-01
3   4       40993 2012-03-25
4   5       40577 2011-02-03
5   6       39949 2009-05-16

Desperately Need Advice on Converting Date Column

1 Answers1