Convert Float Date to YYYY-MM-DD Format

Question

I have a Date column with float values and would like to convert to YYYY-MM-DD

Date

43411.74786 

43381.63381 

43339.3885

I've tried a few methods from the other threads but still can't solve it.

1. df['Date'] = pd.to_datetime(df['Date'],format='%Y/%m/%d').dt.strftime('%Y%m%d')

This changes the year to 1970.

2. df['Modified'] = pd.to_datetime(df['Modified'], unit='s')

This changes the year to 1970.

3. df['Date'] = pd.to_datetime(df['Date'], format='%Y%m%d.0')

I get an error message: time data '43411' does not match format '%Y%m%d.0' (match).

Answer 1

Try this:

pd.to_datetime(df.Date,unit='d',origin='1900-01-01')

Out[364]: 

0   2018-11-09 17:56:55.104

1   2018-10-10 15:12:41.184

2   2018-08-29 09:19:26.400

Name: Date, dtype: datetime64[ns]

In case, you are not able to remove the timestamp with this, just use, pd.to_datetime(df.Date,unit='d',origin='1900-01-01').dt.date

Comments

Perfect your answer, I have been looking for this subject for a long time and nothing was so enlightening.

However, my result is returning 2 days more than it should.



My code:



import pandas as pd

df = pd.DataFrame({'DATA_ATUALIZACAO': [44082.507682, 43193]})

df['DATA_ATUALIZACAO'] = pd.to_datetime(df['DATA_ATUALIZACAO'],unit='d',origin='1900-01-01')

print(df)



return:

            DATA_ATUALIZACAO

2020-09-10 12:11:03.724800

2018-04-05 00:00:00.000000



correct value:

Value Date

44082,50768200000 08/09/2020

43193,00000000000 03/04/2018





Please, is it possible to guide me how to solve this?