How to remove data based on the timestamp

user_19137046 0
Viewed 45

I have a data frame that looks like this:

head(data1)
# A tibble: 6 × 10
  Date       Time     Axis1 Axis2 Axis3    VM Standing Stepping Cycling
  <date>     <chr>    <dbl> <dbl> <dbl> <dbl>    <dbl>    <dbl>   <dbl>
1 2022-03-17 11:29:00     0     0     0     0        0        0       0
2 2022-03-17 11:29:00     0     0     0     0        0        0       0
3 2022-03-17 11:29:00     0     0     0     0        0        0       0
4 2022-03-17 11:29:00     0     0     0     0        0        0       0
5 2022-03-17 11:29:00     0     0     0     0        0        0       0
6 2022-03-17 11:29:00     0     0     0     0        0        0       0
# … with 1 more variable: New_Sitting <dbl>

It has a data point every second all day, every day for a week. Is it possible to remove the data points outside of the workday? (Say only keep the data between 7am-5pm of the weekdays)

r
2 Answers

Use the following: Note that we first create a datetime object, then we filter whereby the weekday is between 1:5, and the time is between 07(7am) and 17(5pm)

library(tidyverse)
library(lubridate)

df %>%
  mutate(date_time = ymd_hms(paste(Date, Time)))%>%
  filter(format(date_time, "%u")%in%1:5, 
         date_time>=ymd_h(paste(Date, "07")),
         date_time <= ymd_h(paste(Date, "17"))) %>%
  select(-date_time)
user_83802720

You may use weekdays() and data.table::between().

res <- dat[!weekdays(dat$time) %in% c("Saturday", "Sunday") &
  data.table::between(as.integer(gsub('\\D', '', substring(dat$time, 12))), 7e4, 17e4), ]

Gives

names(table(substr(res$time, 12, 13)))
# [1] "07" "08" "09" "10" "11" "12" "13" "14" "15" "16" "17"

names(table(weekdays(res$time)))
# [1] "Friday"    "Monday"    "Thursday"  "Tuesday"  
# [5] "Wednesday"

Data

dat <- data.frame(time=seq.POSIXt(as.POSIXct('2022-01-01'), as.POSIXct('2022-01-14'), 'hour'))
dat$X <- rnorm(nrow(dat))
user_65740380
Related