Python how to standardise your dates to a particular datetime format

Question

My data might be of different date formats (dd-mm-yyyy hh mm, yyyy-mm-dd hh mm etc) and is in string format I want to push standardized data into my database. Also I want to convert into DateTime instead of string to use it in other date-related functions. How can I do it? I tried pd.Datetime() and inserted the required format (the one I want to standardized to) but I'm getting this error :

ValueError: time data '2015-05-04 09:15:00' does not match format '%d-%m-%Y %H:%M' (match).

I have used flask to make an upload screen and using request.files to get the csv file in my python

Please help me with it Thanks

Answer 1

You can use the infer_datetime_format parameter in pd.to_datetime() to quickly get around most formatting issues. Make sure you validate the output:

df['Date'] = pd.to_datetime(df['Date'],infer_datetime_format=True)

For example, looking at different formats with the following test data:

df = pd.DataFrame({'date':['2020-10-25','24-10-2020','2020-10-26 10:00:05']})
                  date
0           2020-10-25
1           24-10-2020
2  2020-10-26 10:00:05

Using:

df['date'] = pd.to_datetime(df['date'],infer_datetime_format=True)

Outputs:

                date
0 2020-10-25 00:00:00
1 2020-10-24 00:00:00
2 2020-10-26 10:00:05