Mixed Date Formats Update

changing all dates to standard date time in dataframe

This link helps, but doesn’t quite get me, or more likely I am not making the leap to get there. Currently, mixed dates “2021-04-15”, “04152021”, “04/15/2021”, “NaN/NULL” all exist in each of these columns.

Independently, I have no issue converting the standard dates or integers.

FileDates = ['HIRE1','HIRE2','DATE3','DOB','DATE4','DATE5','DATE6','DATEn+x']
for c in FileDates:
    df[c]=pd.to_datetime(df[c],errors = 'ignore')

import datetime
datetime.datetime.strptime('01012020','%m%d%Y').strftime('%m/%d/%Y')
'01/01/2020'

Question – how should or how can I pass through and reformat the dates to be readable in one or two passes? I assume I’m missing something basic.

Right now, go through and all “dates” update, but if I attempt to update Integer dates, I error on unexpected format (m/d/y) or those that are Null.

Update – Thank you, in addition the following link was helpful. Pandas format datetime with many different date types

Answer

If your list of formats only include “2021-04-15”, “04152021”, “04/15/2021” and “NaN/NULL” and you want to convert it to ISO format, you could try this:

def convert_datetime(input):
    """Try to parse a datetime as yyyy-mm-dd, dd/mm/yyyy and ddmmyyyy,
    and convert it to ISO format"""
    try:
        return datetime.strptime(input, "%Y-%m-%d").isoformat()
    except ValueError:
        pass

    try:
        return datetime.strptime(input, "%d/%m/%Y").isoformat()
    except ValueError:
        pass

    try:
        return datetime.strptime(input, "%d%m%Y").isoformat()
    except ValueError:
        pass

    # the input could not be parsed, return a default value
    return None

If your possible formats also contain one of these formats but with month and day flipped however, you will run into trouble and you can’t be certain the dates will be parsed correctly every time.