Convert column to datetime using pd.to_datetime with mixed YYYY and YY format-CodePudding

I am trying to convert my column to datetime but pandas is giving my the following error:

ValueError: time data 'Order Date' does not match format '%m/%d/%Y %H:%M' (match)

This is the function I am trying to run:

df['Order Date'] = pd.to_datetime(df['Order Date'], format='%m/%d/%Y %H:%M')

This is a snapshot of the Order Date column:

Order Date
4/19/2019 8:46

Any ideas why this is not running? Thanks!

second question

I also would like to be able to convert mixed YYYY and YY for the year format (sometimes '4/19/2019 8:46' and sometimes '4/19/19 8:46')

CodePudding user response：

Double check your column, it looks like the string 'Order Date' is in the column. The conversion on the example you provided works fine.

import pandas as pd

pd.to_datetime('4/19/2019 8:46', format='%m/%d/%Y %H:%M')

Output: Timestamp('2019-04-19 08:46:00')

conversion of mixed YYYY and YY years:

df = pd.DataFrame({'date': ['4/19/2019 8:46', '4/19/19 8:46']})
#              date
# 0  4/19/2019 8:46
# 1    4/19/19 8:46

pd.to_datetime(df['date'].str.replace('^(\d{1,2}/\d{1,2}/)\d\d(\d\d)', r'\1\2',
                                      regex=True),
               format='%m/%d/%y %H:%M')

output:

0   2019-04-19 08:46:00
1   2019-04-19 08:46:00