Create an Integer column based on a specified time length

user_16610405 0
Viewed 37

I am working with time series data and need to be able to sort it by week in order to create a facet graph. Using the following code, I have created a column with the week number of the year based on the Date_time column:

#Creates the Date_time column 
target_detections_all_15E$Date_time <- as.POSIXct(paste(as.Date(as.character(target_detections_all_15E$Ping_date),"%Y-%m-%d"), target_detections_all_15E$Ping_time, sep=" "),format = "%Y-%m-%d %H:%M:%S", tz="Asia/Bangkok")
#eliminates zeroes in the data
target_detections_all_15E<- target_detections_all_15E[target_detections_all_15E$TS_comp !=-9.9e+37,]
#Formats  the time to create the Week column and is supposed to change the week numbers to a sequence that starts at 1.
target_detections_all_15E$Week <- as.integer(format(target_detections_all_15E$Date_time, "%V"))
target_detections_all_15E<- transform(target_detections_all_15E, Week=Week-min(Week)+1)

Reprex data:

   Ping_date   Ping_time      Date_time      Week
1  2020-12-01  18:14:54 2020-12-01 18:14:54   49
2  2020-12-01  18:14:54 2020-12-01 18:14:54   49
3  2020-12-01  18:14:54 2020-12-01 18:14:54   49
4  2020-12-07  00:14:55 2020-12-07 00:14:55   50
5  2020-12-07  00:14:55 2020-12-07 00:14:55   50
6  2020-12-07  00:14:55 2020-12-07 00:14:55   50
7  2020-12-14  18:14:56 2020-12-14 00:14:56   51
8  2020-12-14  18:14:56 2020-12-14 00:14:56   51
9  2020-12-14  18:14:56 2020-12-14 00:14:56   51
10 2020-12-14  18:14:56 2020-12-14 00:14:56   51

My issue is that the number generated in the week column is based on the day and changes to "50" on 2020-12-07. My data starts at 2020-12-01 18:14:54 and I would like the week number to be dictated by a time range, in this case 168 hours after that initial start time so the end of week one becomes 2020-12-08 18:14:54. I need this to be set so that the Week column reads "1" up till 168 hours later and then switch to "2".

Example desired dataset:

   Ping_date   Ping_time      Date_time     Week
1  2020-12-01  18:14:54 2020-12-01 18:14:54   1
2  2020-12-01  18:14:54 2020-12-01 18:14:54   1
3  2020-12-01  18:14:54 2020-12-01 18:14:54   1
4  2020-12-08  18:14:55 2020-12-08 18:14:55   2
5  2020-12-08  18:14:55 2020-12-08 18:14:55   2
6  2020-12-08  18:14:55 2020-12-08 18:14:55   2
7  2020-12-15  18:14:56 2020-12-15 18:14:56   3
8  2020-12-15  18:14:56 2020-12-15 18:14:56   3
9  2020-12-15  18:14:56 2020-12-15 18:14:56   3
10 2020-12-15  18:14:56 2020-12-15 18:14:56   3
tidyverse r date
1 Answers

Example data:

d <- tibble(Date_time = c("2020-12-01 18:14:54", "2020-12-08 18:14:55", "2020-12-15 18:14:56"))

# A tibble: 3 × 1
  Date_time          
  <chr>              
1 2020-12-01 18:14:54
2 2020-12-08 18:14:55
3 2020-12-15 18:14:56

  1. Use difftime() to compute the number of weeks between each date and the min date in your data (i.e., the first day)

  2. Use floor() to get a whole number (rounded down)

  3. Use as.numeric() to coerce this into an integer

library(dplyr)
library(lubridate)

d <- tibble(Date_time = c("2020-12-01 18:14:54", "2020-12-08 18:14:55", "2020-12-15 18:14:56"))
  
d %>%
  mutate(Date_time = as_datetime(Date_time, tz = "Asia/Bangkok"),
         Week = as.numeric(floor(difftime(Date_time, min(Date_time), units = "weeks") + 1)))
user_82231540
Related