[FIXED] pd.to_datetime returning NaT when date is valid

Issue

I am trying to convert a string to a datetime type in Python using Pandas, I scraped the data from a webpage. A sample of the data is given below. When I convert this using the pd.to_datetime function I receive NaT values but I'm not sure why, the object type is changed to datetime successfully however.

I have two values which should be "2021" but are "20201". I have replaced these and then converted to date time:

df['Date'] = df['Date'].replace("20201", "2021", inplace=True)
df['Date'] = pd.to_datetime(df['Date'])

I have also tried the below:

df['Date'] = pd.to_datetime(df['Date'], format = "%d/%m/%Y", errors = "coerce")
df['Date'] = pd.to_datetime(df['Date'], format = "%d/%m/%Y")

If I do not replace these values and instead just convert to datetime directly, ignoring the "20201" is out of range error, the code works fine and does not produce NaT values.

df['Date'] = pd.to_datetime(df['Date'], errors = "ignore")

Date	A	B	C
07/01/20201	a	b	2
08/01/20201	b	c	2
09/01/2022	c	d	1
10/01/2022	d	e	1
13/01/2022	e	f	3
14/01/2022	f	g	3
17/01/2022	g	h	3

Updated Dict:

  {'Unnamed: 0': {351: 351, 352: 352},
 'Date': {351: '17/4/20201', 352: '17/4/20201'},
 'Selection': {351: 'Pour La Victoire', 352: 'Wiley Post'},
 'Stake': {351: 1.0, 352: 1.0},
 'Odds Advised': {351: 6.5, 352: 2.5},
 'Profit / Loss': {351: -1.0, 352: -1.0},
 'Bet Type': {351: 'Win', 352: 'Win'}}

Solution

Problem might be that you use inplace=True and Series.replace replaces the whole string, you can use Series.str.replace

df['Date'].replace("20201", "2021", inplace=True)
# or
df['Date'] = df['Date'].replace("20201", "2021")

df['Date'] = pd.to_datetime(df['Date'])

print(df)

        Date    A    B    C
0 2021-07-01    a    b  2.0
1 2021-08-01    b    c  2.0
2 2022-09-01    c    d  1.0
3 2022-10-01    d    e  1.0
4 2022-01-13    e    f  3.0
5 2022-01-14    f    g  3.0
6 2022-01-17    g    h  3.0

Answered By - Ynjxsjmh