Create a column that lists factor levels between dates listed in another columnn-CodePudding

I'm working with some time-series data and am trying to add a column that says 'peak' or 'off-peak' depending on the date in another column. I'm trying to work it into a pipe using dplyr. Here is my code:

fish_comp_small<- read.csv('Fish composition dai data 2020-21 (small fish).csv') %>%
  mutate(Date_time_S = as.POSIXct(paste(as.Date(as.character(Date_S),"%Y-%m-%d"), Time_S, sep=" "),format = "%Y-%m-%d %H:%M", tz="Asia/Bangkok")) %>%
  mutate(Date_time_E = as.POSIXct(paste(as.Date(as.character(Date_E),"%Y-%m-%d"), Time_E, sep=" "),format = "%Y-%m-%d %H:%M", tz="Asia/Bangkok"))

I just need to create a new column where 'peak' will be put in a row between dates '2020-11-27' and '2020-11-28' and between '2020-12-24' and '2020-12-25'. I'd like to say 'off-peak' everywhere else. I've tried using if_else to no success.

here is the reprex data:

df<- structure(list(Trip = c("T2", "T2", "T2", "T2", "T2", "T2", "T2", 
"T2", "T2", "T2"), dai_name = c("3C", "3C", "3C", "3C", "3C", 
"3C", "3C", "3C", "3C", "3C"), sampleID = c("S01", "S01", "S01", 
"S01", "S01", "S01", "S01", "S01", "S01", "S01"), Date_S = c("2020-11-27", 
"2020-11-27", "2020-11-28", "2020-11-28", "2020-12-15", "2020-12-15", 
"2020-12-24", "2020-12-25", "2021-01-07", "2021-01-23"), Time_S = c("8:22:00", 
"8:22:00", "8:22:00", "8:22:00", "8:22:00", "8:22:00", "8:22:00", 
"8:22:00", "8:22:00", "8:22:00"), Date_E = c("2020-11-27", "2020-11-27", 
"2020-11-28", "2020-11-28", "2020-12-15", "2020-12-15", "2020-12-24", 
"2020-12-25", "2021-01-07", "2021-01-23"), Time_E = c("10:35:00", 
"10:35:00", "10:35:00", "10:35:00", "10:35:00", "10:35:00", "10:35:00", 
"10:35:00", "10:35:00", "10:35:00"), Total.Catch.per.haul.kg. = c(5.9, 
5.9, 5.9, 5.9, 5.9, 5.9, 5.9, 5.9, 5.9, 5.9), Subsample.kg. = c(5.02, 
5.02, 5.02, 5.02, 5.02, 5.02, 5.02, 5.02, 5.02, 5.02), ScientificName = c("Pangasius.larnaudii", 
"Crossocheilus.atritimess", "Syncrossus.helodes", "Yasuhikotakia.eos", 
"Yasuhikotakia.modesta", "Amblyrhynchichthys.micracanthus", "Crossocheilus.reticulatus", 
"Pangasius.conchophilus", "Phalacronotus.sp1", "Coilia.lindmani"
), Abundance = c(2L, 1L, 1L, 1L, 1L, 4L, 4L, 1L, 1L, 6L), Weight.g. = c(224.9, 
2.7, 16, 9.5, 4.4, 97.9, 7.4, 3.1, 28.8, 44.5), note = c("Sampling", 
"Sampling", "Sampling", "Sampling", "Sampling", "Sampling", "Sampling", 
"Sampling", "Sampling", "Sampling"), Date_time_S = structure(c(1606440120, 
1606440120, 1606440120, 1606440120, 1606440120, 1606440120, 1606440120, 
1606440120, 1606440120, 1606440120), class = c("POSIXct", "POSIXt"
), tzone = "Asia/Bangkok"), Date_time_E = structure(c(1606448100, 
1606448100, 1606448100, 1606448100, 1606448100, 1606448100, 1606448100, 
1606448100, 1606448100, 1606448100), class = c("POSIXct", "POSIXt"
), tzone = "Asia/Bangkok")), row.names = c(NA, 10L), class = "data.frame")

CodePudding user response：

Try the following lubridate solution and first create "peak" intervals, then use an ifelse() statement to check Date_S for either interval:

library(lubridate)
# create peak intervals
peak1 <- interval(ymd("2020-11-27"), ymd("2020-11-28"))
peak2 <- interval(ymd("2020-12-24"), ymd("2020-12-25"))

# convert character date columns to date formats (lubridate)
dts <- c("Date_S", "Date_E")
df[dts] <- lapply(df[dts], ymd)

#determine peak/notpeak using ifelse
df$peak <- ifelse(df$Date_S %within% peak1 | df$Date_S %within% peak2, 
                  "Peak", "Not Peak")

Output (only relevant columns)

# > df[,c("Date_S", "peak")]
#        Date_S     peak
# 1  2020-11-27     Peak
# 2  2020-11-27     Peak
# 3  2020-11-28     Peak
# 4  2020-11-28     Peak
# 5  2020-12-15 Not Peak
# 6  2020-12-15 Not Peak
# 7  2020-12-24     Peak
# 8  2020-12-25     Peak
# 9  2021-01-07 Not Peak
# 10 2021-01-23 Not Peak