Pandas - Converting column of type float to date time

Question

I have a Dataframe with 2 columns that are float64 objects. I am trying to convert these to dates.

col1, col2
43835.0, 0.145833
43835.0, 0.166667

Expected output:

col1, col2
05/01/2020,3:30:00 AM
05/01/2020, 4:00:00 AM

When I try pd.to_datetime(df['col1']) it convert the values to 1970-01-01 00:00:00.000043835 and 1970-01-01 respectively

@jezrael, thanks for sharing. It did help for `col1` but not for `col2` — scott martin, Jan 06 '20 at 09:36
@jezrael actually `col2` is the time component whereas `col1` is the date component — scott martin, Jan 06 '20 at 09:38

jezrael · Accepted Answer · 2020-01-06T09:49:58.717

First is possible convert to datetimes fist column and second to timedeltas:

df['col1'] = pd.to_timedelta(df['col1'], unit='d') + pd.datetime(1899, 12, 30)
df['col2'] = pd.to_timedelta(df['col2'], unit='d').dt.floor('S')
print (df)
        col1     col2
0 2020-01-05 03:29:59
1 2020-01-05 04:00:00

One idea for custom dates and times, but becuase precision output is a bit different:

s = df['col1'] + df['col2']
dates = pd.to_timedelta(s, unit='d').add(pd.datetime(1899, 12, 30)).dt.floor('S')

df['col1'] = dates.dt.strftime('%d/%m/%Y')
df['col2'] = dates.dt.strftime('%H:%M:%S %p')
print (df)
         col1         col2
0  05/01/2020  03:29:59 AM
1  05/01/2020  04:00:00 AM

Pandas - Converting column of type float to date time

1 Answers1