Join with using minute of datetime

General
#1

Hello,
How can I join two datasets using a similar minute of datetime column?
So, I want to add temp from temp.data to df using the same minute.
1st value of temp will be the 1st row in df and the second value will be in the last row of df.

  y=as.POSIXct("1976-09-03 23:00:00")
  y=as.data.frame(y)
  colnames(y)=c("datetime")
  (df=y%>%
    add_row(datetime = as_datetime(y$datetime)+minutes(15)) %>% 
    add_row(datetime = as_datetime(y$datetime)+minutes(30)) %>% 
    add_row(datetime = as_datetime(y$datetime)+minutes(45)))

  (temp.data <- data.frame(
    datetime=as.POSIXct(c("1977-01-06 19:00:00","1977-01-06 19:45:00")),
    temp=c(0,2.54)))

Expected output:

  (df <- data.frame(
    datetime=as.POSIXct(c("1976-09-04 04:00:00","1976-09-04 04:15:00",
                          "1976-09-04 04:30:00","1976-09-04 04:45:00")),
    temp=c(0,NA,NA,2.54)))
#2

If you're asking about joins on timestamps within one minute of each other, then you seek inequality joins. See this dplyr issue for historical context, and check out the fuzzyjoin package, specifically in your case the difference_* join family.

If you want an equality join on just the minute component of a timestamp, then you can use lubridate::minute(x) to extract the minute from a timestamp:

lubridate::minute(lubridate::ymd_hms("2020-01-01 12:34:56"))  ## yields 34
#3 
suppressPackageStartupMessages({
  library(dplyr)
  library(lubridate)
})

y <- as.POSIXct("1976-09-03 23:00:00")
y <- as.data.frame(y)
colnames(y) <- c("datetime")

DF <- y%>%
    add_row(datetime = as_datetime(y$datetime)+minutes(15)) %>% 
    add_row(datetime = as_datetime(y$datetime)+minutes(30)) %>% 
    add_row(datetime = as_datetime(y$datetime)+minutes(45))

DF_t <- data.frame(
  datetime=as.POSIXct(c("1977-01-06 19:00:00","1977-01-06 19:45:00")),
  temp=c(0,2.54))

DF_t %>% mutate(Minute = minute(datetime)) -> DF_t
the_minutes <- DF_t$Minute

DF %>% mutate(Minute = minute(datetime),
              temp = ifelse(Minute %in% the_minutes, Minute,NA)) -> DF 

full_join(DF_t,DF,by = "Minute") %>%
  select(-Minute,-temp.y) %>%
  rename(temp = temp.x) %>%
  mutate(datetime = ifelse(
    is.na(temp),
    datetime.x,
    datetime.y),
    datetime = as_datetime(datetime)) %>%
  select(datetime.y,temp) %>%
  rename(datetime = datetime.y) -> DF_r

DF_r
#>              datetime temp
#> 1 1976-09-04 06:00:00 0.00
#> 2 1976-09-04 06:45:00 2.54
#> 3 1976-09-04 06:15:00   NA
#> 4 1976-09-04 06:30:00   NA

Created on 2021-01-11 by the reprex package (v0.3.0.9001)

1 Like