changing all dates to standard date time in dataframe

Question

I have a dataframe with date column where it looks like this. There are more than one date column such as end date, fiscal year date etc.

Plan Start Date
8/16/2017 0:00
5/31/2017 0:00
5/31/2017 0:00
5/31/2017 0:00
5/31/2017 0:00
4/21/2016 0:00
2/25/2016 0:00
12/15/2016 0:00
12/15/2016 0:00
12/15/2016 0:00
42373
42373
42367
42367
42367
42367
42460
42460
42460
42460
42460
42759
42333

I am trying to write a function where it basically changes those integrers to appropriate date format and format this column as datetime[64]. this column format is current object type.

I have written below function

def change_date_df(df):
    format_dates_df = [col for col in df.columns if 'Date' in col];
    for date in format_dates_df:
        df[date] = pd.to_datetime(df[date]).apply(lambda x: x.strftime('%d-%m-%y')if not pd.isnull(x) else '');
    return df;

Its giving back now a

ValueError: mixed datetimes and integers in passed array

Im guessing these numbers are not being converted to dates. but Im not sure how else i can adjust my code.

Any idea?

Adam

Solution

Referencing How to convert a given ordinal number (from Excel) to a date, convert the ordinal values to datetime using from_excel_ordinal -

m = df['Plan Start Date'].str.isdigit()

Or, if you have a column of objects -

df['Plan Start Date'].astype(str).str.isdigit()

Next, apply the function on a subset of the rows using apply -

df.loc[m, 'Plan Start Date'] = \
df.loc[m, 'Plan Start Date']\
  .astype(int)\
  .apply(from_excel_ordinal)

Finally, convert the entire column to datetime using pd.to_datetime, giving a uniform result -

df['Plan Start Date'] = pd.to_datetime(df['Plan Start Date'], errors='coerce')

df

   Plan Start Date
0       2017-08-16
1       2017-05-31
2       2017-05-31
3       2017-05-31
4       2017-05-31
5       2016-04-21
6       2016-02-25
7       2016-12-15
8       2016-12-15
9       2016-12-15
10      2016-01-04
11      2016-01-04
12      2015-12-29
13      2015-12-29
14      2015-12-29
15      2015-12-29
16      2016-03-31
17      2016-03-31
18      2016-03-31
19      2016-03-31
20      2016-03-31
21      2017-01-24
22      2015-11-25

Comments

[Adam]

HIhi i tried this when i did m = df['Plan Start Date'].str.isdigit(), it shows as NaN.

[cs95]

@Adam Okay... I see the problem! Try this: df['Plan Start Date'].astype(str).str.isdigit().

[truckbot]

@cs95 It's now 3 years since you've posted your answer, and you've saved me lol I was stunned to see that the code immediately worked. Thank you.