Converting worded date format to datetime format in pandas-CodePudding

Today one of my script gave an error for an invalid datetime format as an input. The script is expecting the datetime input as '%m/%d/%Y', but it got it in an entirely different format. For example, the date should have been 5/2/2022 but it was May 2, 2022. To add a bit more information for clarity, the input is coming for a Google sheet and the entire date is in a single cell (rather than different cells for month, date and year).

Is there a way to convert this kind of worded format to the desired datetime format before the script starts any kind of processing?

CodePudding user response：

If you're in presence of the full month name, try this:

>>> pd.to_datetime(df["Date"], format="%B %d, %Y")
0   2022-05-02
Name: Date, dtype: datetime64[ns]

According to the Python docs:

%B: "Month as locale’s full name".
%d: "Day of the month as a zero-padded decimal number". (Although it seems to work in this case)
%Y: "Year with century as a decimal number."

Now, if you want to transform this date to the format you initially expected, just transform the series using .dt.strftime:

>>> pd.to_datetime(df["Date"], format="%B %d, %Y").dt.strftime("%m/%d/%Y")
0    05/02/2022
Name: Date, dtype: object