Convert a String with dots to Date in Python

Question

I'm using pandas and i imported a csv file with a column "Date", the problem is that pandas think it's a string because it's formatted like "21.03.2022" (dd/mm/yy) and because of the dots i can't use pandas.to_datetime(). The errors are:

1. TypeError: Unrecognized value type: <class 'str'>
2. TypeError: time data '21.03.2022' does not match format '%d:%m:%Y' (match)

I tried using the pandas.replace() but it doesn't work with internal values. I'm having the same problem when i try to convert string like "3.140,80" to number for the same reason.

And could someone include how to do the same but with R. I'm sorry if the question is too noob-livel. Thanks already :)

Answer 1

pandas.to_datetime is very good as determining automatically the formats. You can make it explicit while remaining flexible with dayfirst=True:

s = pd.Series(['21.03.2022'])
s2 = pd.to_datetime(s, dayfirst=True)

or fully explicit:

s2 = pd.to_datetime(s, format='%d.%m.%Y')

output:

0   2022-03-21
dtype: datetime64[ns]