Geek Answers Handbook

R convert number in time

Someone gave me really bad data in Excel, where the date (for example, July 1, 2015) is 20150701 and the time (for example, 11:41:23) is 114123. There are more than 50,000 rows of data, and I need to convert all this in relevant date and time objects. This is not the number of seconds from any era, it is simply a date or time without a dash or colon.

I imported them into a data frame and converted the dates using the ymd () function, but I can not find a function that does this for time, hms () gives me an error:

package(lubridate)
df <- readWorksheetFromFile(file="cktime2012.xls", sheet=1)
df$date <- ymd(df$date)
df$time <- hms(df$time)
# Warning message:
#  In .parse_hms(..., order = "HM", quiet = quiet) :
#   Some strings failed to parse

and I get a data frame that looks like before starting the last line. After I run the last row, the TIMEIN column turns into all NA:

DATEIN      TIMEIN  etc...
2012-02-01  200000  etc...
etc...

, 50 000 . POSIXct , , :

DATEIN      TIMEIN      etc...
2012-02-01  20:00:00    etc...
etc...
+4
2

TIMEIN (.. 10:00), :

df$TIMEIN = paste0(substr(df$TIMEIN,1,2),":",substr(df$TIMEIN,3,4),":", substr(df$TIMEIN,5,6))
df$TIMEIN = hms(df$TIMEIN)
+4

, , .

> as.POSIXct("200000", format="%H%M%S")
[1] "2015-07-01 20:00:00 IST"

Edit - , as.POSIXct() . , , - .

> as.POSIXct("20120201 200000", format="%Y%m%d %H%M%S")
[1] "2012-02-01 20:00:00 IST"
+4

More articles:


All Articles