CODEWITHSUNDEEP
pythonpandas
royalewithcheese
royalewithcheese

Reputation: 502

When converting into datetime why is the result parsing wrong year and month using pandas?

When I read the data from excel it is parsed as following,

           a
0        44140
1        44266
2        44266
3        44265
4        44265
         ...  
39640    44143
39641    44109
39642    44232
39643    44125
39644    44222

Since I want to convert them to %d%m%Y format, I use the following,

df1['a_final'] = pd.to_datetime(df1['a'], unit='D').dt.strftime("%d/%m/%Y")

I get the following output which is wrong,

0        07/11/2090
1        13/03/2091
2        13/03/2091
3        12/03/2091
4        12/03/2091
            ...    
39640    10/11/2090
39641    07/10/2090
39642    07/02/2091
39643    23/10/2090
39644    28/01/2091

The correct output should be,

0        05/11/2020
1        11/03/2021
2        11/03/2021
3        10/03/2021
4        10/03/2021
            ...    
39640    08/11/2020
39641    05/10/2020
39642    05/02/2021
39643    21/10/2020
39644    26/01/2021

How should I tackle this?

Upvotes: 1

Views: 93

Answers (1)

jezrael
jezrael

Reputation: 862511

You can add origin parameter to to_datetime:

df1['a_final']=pd.to_datetime(df1['a'],unit='D',origin='1899-12-30').dt.strftime("%d/%m/%Y")
print (df1)
           a     a_final
0      44140  05/11/2020
1      44266  11/03/2021
2      44266  11/03/2021
3      44265  10/03/2021
4      44265  10/03/2021
39640  44143  08/11/2020
39641  44109  05/10/2020
39642  44232  05/02/2021
39643  44125  21/10/2020
39644  44222  26/01/2021

Upvotes: 2

Related Questions