meitei
January 12, 2021, 1:05am
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)))
mmuurr
January 12, 2021, 3:15am
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
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
system
Closed
January 19, 2021, 3:31am
4
This topic was automatically closed 7 days after the last reply. New replies are no longer allowed. If you have a query related to it or one of the replies, start a new topic and refer back with a link.