CODEWITHSUNDEEP
r
user1471980
user1471980

Reputation: 10626

how do you subset data frame based on hour, minute, seconds the most efficient way

I am trying to subset a data frame based on time:

df

Hostname Date  cpu  
Server101  1/1/2015 00:00:00 10
Server101  1/1/2015 00:00:00 10
Server101  1/1/2015 08:00:00 10
Server101  1/1/2015 06:00:00 10

I need to grab the data from 09:00:00 till 17:00:00

so this is what I do:

library(lubridate)
df<-transform(df, time= format(df$Date,'%H:%M:%S'))
df$time<-times(df$time)

df<-subset(df, time>times(c("09:00:00")) & time<times(c("17:00:00"")))

subset function to subset based on time is taking a very long time to complete. Is there a better way to do this, fastest?

Upvotes: 1

Views: 688

Answers (1)

LyzandeR
LyzandeR

Reputation: 37879

You may want to consider data.table with the ITime class (which is based on POSIXlt). It would probably be the fastest choice:

Data:

df <- read.table(header=T, text="Hostname Date  cpu  
Server101  '1/1/2015 00:00:00' 10
Server101  '1/1/2015 00:00:00' 10
Server101  '1/1/2015 08:00:00' 10
Server101  '1/1/2015 10:00:00' 10")

Solution:

library(data.table)
df$Date <- as.POSIXct(df$Date, format='%d/%m/%Y %H:%M:%S')
#setDT coverts the df to a data.table
#as.ITime converts the date to an ITime class
#in the last chain you subset the data table
setDT(df)[,time:=as.ITime(Date)][time>=as.ITime('09:00:00') & time<=as.ITime('17:00:00')]

On your example data set (just changed the last row to get a result):

setDT(df)[,time:=as.ITime(Date)][time>=as.ITime('09:00:00') & time<=as.ITime('17:00:00')]
    Hostname                Date cpu     time
1: Server101 2015-01-01 10:00:00  10 10:00:00

Upvotes: 3

Related Questions