Mixed Date Formats Update

Question

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

Without more information, it's impossible to tell if things like "02032020" mean 2020-03-02 or 2020-02-03 (iso format). If you can't control the input, your best bet is trying to parse it until you don't get exceptions anymore — asdf101, Apr 15 '21 at 15:01
could you clarify, what specifically is the question? you say you have *no issue converting the standard dates or integers* - so what *does* give you issues? Can you add a [mre]? — FObersteiner, Apr 15 '21 at 15:02

score 1 · Accepted Answer · answered Apr 15 '21 at 15:19

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.

Mixed Date Formats Update

1 Answers1