Iterate through a Pandas DataFrame of Float time stamps and convert to date time

Question

I have a Pandas Dataframe with 2000+ rows with date in float format as below:

42704.99686342593 representing datetime value of (2016, 11, 30, 23, 55, 29)

What I want to do is iterate each row in the dataframe and convert the float to the correct datetime format ideally d/m/Y H/M/S and save this to a new dataframe.

Using Python 2.7.

I couldn't find any duplicate questions and was unable to solve the issue with solutions to similar questions so any help appreciated.

Thanks.

Include df.head() in your question. – Mohammad Yusuf Jan 04 '17 at 13:27 — Mohammad Yusuf, Jan 04 '17 at 13:27
How does 42704 equal Nov 11, 2016? – Ted Petrou Jan 04 '17 at 13:29 — Ted Petrou, Jan 04 '17 at 13:29

score 3 · Accepted Answer · edited May 23 '17 at 11:53

3

It seems you use serial date what is Excel format.

The simpliest is substract 25569 and use to_datetime with parameter unit='d':

df =  pd.DataFrame({'date':[42704.99686342593,42704.99686342593]})
print (df)
           date
0  42704.996863
1  42704.996863

print (pd.to_datetime(df.date - 25569, unit='d'))

0   2016-11-30 23:55:28.963200
1   2016-11-30 23:55:28.963200
Name: date, dtype: datetime64[ns]

Another solutions are substract timedelta or offset:

print (pd.to_datetime(df.date, unit='d') - pd.to_timedelta('25569 Days'))
0   2016-11-30 23:55:28.963200
1   2016-11-30 23:55:28.963200
Name: date, dtype: datetime64[ns]

print (pd.to_datetime(df.date, unit='d') - pd.offsets.Day(25569))
0   2016-11-30 23:55:28.963200
1   2016-11-30 23:55:28.963200
Name: date, dtype: datetime64[ns]

Thank you Ted Petrou for link.

edited May 23 '17 at 11:53

Community

1
1

answered Jan 04 '17 at 13:29

jezrael

822,522
95
1,334
1,252

What's that subtraction and multiplication value? – Mohammad Yusuf Jan 04 '17 at 13:31
@MYGz, i guess it's a result of a reverse ingeneering – MaxU - stand with Ukraine Jan 04 '17 at 13:31
I don't understand those, who have invented this `serial date` - it's a little bit perverse logic: `datetime.datetime(1970,1,1) - datetime.datetime(1899,12,30) == 25569` – MaxU - stand with Ukraine Jan 04 '17 at 13:50
It is bug, I try find it. – jezrael Jan 04 '17 at 13:53
You should include a page of how you found out about the excel time bug and how to convert it like this. http://excel.tips.net/T002051_Converting_UNIX_Date_Time_Stamps.html – Ted Petrou Jan 04 '17 at 13:53
Never mind. I see that you did include it already with a link – Ted Petrou Jan 04 '17 at 13:55
@jezrael -Thank you, it worked well. I modified it slightly into a function which I then applied to the entire Dataframe but the answer you provided was at the core. – GoodCat Jan 05 '17 at 16:09

Iterate through a Pandas DataFrame of Float time stamps and convert to date time

1 Answers1