1
Date,Time,Lots,Status
"10-28-15","00:04:50","13-09","1111111110000000"
"10-28-15","00:04:50","13-10","1111100000000000"
"10-28-15","00:04:50","13-11","1111111011100000"
"10-28-15","00:04:50","13-12","1111011111000000"
"10-28-15","00:04:57","13-13","1111111111000000"
"10-28-15","00:04:57","13-14","1111111111110000"
"10-28-15","00:04:57","13-15","1111111100000000"
"10-28-15","00:04:57","13-16","1111111111000000"
"10-28-15","00:05:04","13-17","1111111110000000"
"10-28-15","00:05:04","13-18","1111101100000000"
"10-28-15","00:05:04","13-19","1111111111100000"
"10-28-15","00:05:04","13-20","1111111111100000"
"10-28-15","00:05:11","13-21","1111110100000000"
"10-28-15","00:05:11","13-22","1000011111100000"
"10-28-15","00:05:11","13-23","1101011111110000"
"10-28-15","00:05:11","13-24","1111111111000000"
"10-28-15","00:05:19","13-25","1011000000000000"
"10-28-15","00:05:19","13-26","0000000000000000"
"10-28-15","00:05:19","13-27","1111011110000000"
"10-28-15","00:05:19","13-28","1010000000000000"

say dfrm above "sample", I need to convert the time into time interval of 15 minutes. How do I do that? Eg: I have 4 intervals for every hour. 00:04:50 will go into 00:04:45 - 00:04:50. Thanks!

I have tried using:

format(as.POSIXlt(as.POSIXct('2000-1-1', "UTC") + round(as.numeric(sample$V3)/300)*300), format = "%H:%M:%S")
Jaap
  • 81,064
  • 34
  • 182
  • 193
Siewmei Loh
  • 45
  • 1
  • 8

2 Answers2

1

I hope I understood your question correctly, but I think you could do it like this:

# example data frame:
myDat <- data.frame(date = rep("2010-08-15",30),
                time = sprintf("%02i:%02i:%02i",rep(12:14,each=10),rep(c(0,15,16,29:31,44:45,59,1),3),sample(1:59)[30]))

#produce a datetime variable
myDat$datetime <- strptime(x = paste(myDat$date,myDat$time),format = "%Y-%m-%d %H:%M:%S")
# extract the minutes
myDat$min <- as.integer(as.character(myDat$datetime,format="%M"))
# find the interval to put them in
myDat$ival <- findInterval(myDat$min,c(15,30,45,60)); myDat$ival <- factor(myDat$ival)
levels(myDat$ival) <- c("00","15","30","45")
# concatenate minute interval and hour
myDat$timeIval <- sprintf("%s:%s:00",as.character(myDat$datetime,format="%H"),myDat$ival)
myDat[order(myDat$datetime),]
sam
  • 158
  • 7
  • hi thanks for your help but it seems like i got error showing "Error in as.double(x) : cannot coerce type 'builtin' to vector of type 'double' " – Siewmei Loh Nov 03 '15 at 02:04
  • Hey! I edited the last line of my code. The example I posted should work now. I wrote `min` instead of `myDat$min` - that might have been the problem? – sam Nov 03 '15 at 06:39
  • hey sam, thanks a lot. but the interval part seems to turn out to be "0" . I'm still trying out. any idea using the round function? round(as.numeric(sample$V2)/300)*300) – Siewmei Loh Nov 04 '15 at 02:43
  • Have a look at `?findInterval`. You might need to adjust the regular expression in the `sub` function if you're using that way? But I am still not sue whether I understand your question correctly. How do you want the intervals to look like? Shall the first quarter of hour five for example fall into the same interval (factor level) as the first quarter of hour six or not? Otherwise the above posted link to the other question on this topic should help, I think. – sam Nov 04 '15 at 06:26
  • Hi sam. refering to the question above. What I need is sending the times in column time to 4 intervals. Example: if i have c( 12:36:00,12:46:00, 12:56:00) then these three times will go into 12:30:00, 12: 45:00, 12:45:00.. – Siewmei Loh Nov 04 '15 at 06:31
  • Hi. I edited my answer. It's not elegant in any way but it might do the thing you want. – sam Nov 04 '15 at 07:07
0

I'd first convert the data to POSIXct

time <- as.POSIXct(
  strptime(paste0(df$column1, " ", df$column2), format="%m-%d-%y %H:%M:%S")
)

and then use seq.POSIXct and cut to generate a factor variable for the 15 min intervals.

interval <- cut(
  time,
  breaks = seq(starttime, endtime, as.difftime(15, units="mins))
)

You may want to add -Inf or Inf to the breaks to avoid generating NA values.

snaut
  • 2,261
  • 18
  • 37