CODEWITHSUNDEEP
rdataframeposixctlubridate
Zachary Weixelbaum
Zachary Weixelbaum

Reputation: 914

R convert number into time

Someone gave me really bad data in Excel, where the date (such as July 1, 2015) is 20150701 and the time (such as 11:41:23) is 114123. There are over 50,000 rows of data and I need to convert these all into proper date and time objects. These aren't the number of seconds from any epoch, it is just the date or time without the dashes or the colons.

I imported them into a data frame and converted the dates using the ymd() function, but I can't find a function to do that 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 this before running the last line. Once I run the last line, the TIMEIN column turns into all NA's:

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

I need it to look like this for all 50,000 rows. I included POSIXct as a tag, because I don't know if there could be a way to use that to help convert:

DATEIN      TIMEIN      etc...
2012-02-01  20:00:00    etc...
etc...

Upvotes: 4

Views: 3669

Answers (3)

GaB
GaB

Reputation: 1134

Or simpler than the ones above, using the pipes in tidyverse you can get the following: 

# make sure you have dates stores as POSIXct 
# call in tidyverse library to make use of pipes and use the code bellow

df_hms <- df %>%
  mutate(time = hms::as.hms(TIMEIN))

Upvotes: 0

TrigonaMinima
TrigonaMinima

Reputation: 1978

You can try this too to get the specified time, but then you'd have to get rid of the date too.

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

Edit- Okay, as.POSIXct() works on date and time. So, to merge the whole into one you can do something like this.

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

Upvotes: 4

eipi10
eipi10

Reputation: 93881

If TIMEIN is always six characters (i.e., there's a leading zero for times before 10 AM), then you can do this:

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

Upvotes: 4

Related Questions