我有兩個必須加入的資料框。但是當加入兩個資料表時，我想df2在開始時間和結束時間復制每一行。新資料框的其余行應顯示為NA.
我試過了，left join但它不會在開始和結束時間內復制行。
df <- dplyr::left_join(df1, df2, by = "Session_start")
這兩個資料框如下所示。
head(df1)
# Session_start Robot_ID
# 1 2022-07-07 00:05:19 R1
# 2 2022-07-07 00:05:20 R2
# 3 2022-07-07 00:05:21 R3
# 4 2022-07-07 00:05:22 R4
# 5 2022-07-07 00:05:23 R5
# 6 2022-07-07 00:05:24 R6
df2
# Session_start Session_End Animal_ID
# 1 2022-07-07 00:05:19 2022-07-07 00:05:21 ID1
# 2 2022-07-07 00:05:24 2022-07-07 00:05:26 ID2
# 3 2022-07-07 00:05:27 2022-07-07 00:05:31 ID3
# 4 2022-07-07 00:05:33 2022-07-07 00:05:34 ID4
所需的輸出將是：
| 會話開始 | 機器人_ID | 會話_結束 | Animal_ID |
|---|---|---|---|
| 2022-07-07 00:05:19 | R1 | 2022-07-07 00:05:21 | ID1 |
| 2022-07-07 00:05:20 | R2 | 2022-07-07 00:05:21 | ID1 |
| 2022-07-07 00:05:21 | R3 | 2022-07-07 00:05:21 | ID1 |
| 2022-07-07 00:05:22 | R4 | 不適用 | 不適用 |
| 2022-07-07 00:05:23 | R5 | 不適用 | 不適用 |
| 2022-07-07 00:05:24 | R6 | 2022-07-07 00:05:26 | ID1 |
| 2022-07-07 00:05:25 | R7 | 2022-07-07 00:05:26 | ID2 |
| 2022-07-07 00:05:26 | R8 | 2022-07-07 00:05:26 | ID2 |
| 2022-07-07 00:05:27 | R9 | 2022-07-07 00:05:31 | ID3 |
| 2022-07-07 00:05:28 | R10 | 2022-07-07 00:05:31 | ID3 |
| 2022-07-07 00:05:29 | R11 | 2022-07-07 00:05:31 | ID3 |
| 2022-07-07 00:05:30 | R12 | 2022-07-07 00:05:31 | ID3 |
| 2022-07-07 00:05:31 | R13 | 2022-07-07 00:05:31 | ID3 |
| 2022-07-07 00:05:32 | R14 | 不適用 | 不適用 |
| 2022-07-07 00:05:33 | R15 | 2022-07-07 00:05:34 | ID4 |
| 2022-07-07 00:05:34 | R16 | 2022-07-07 00:05:34 | ID4 |
我如何使用 R 來做到這一點？
資料
df1 <- data.frame(Session_start=c("2022-07-07 00:05:19", "2022-07-07 00:05:20", "2022-07-07 00:05:21",
"2022-07-07 00:05:22", "2022-07-07 00:05:23", "2022-07-07 00:05:24",
"2022-07-07 00:05:25", "2022-07-07 00:05:26", "2022-07-07 00:05:27",
"2022-07-07 00:05:28", "2022-07-07 00:05:29", "2022-07-07 00:05:30",
"2022-07-07 00:05:31", "2022-07-07 00:05:32", "2022-07-07 00:05:33",
"2022-07-07 00:05:34"),
Robot_ID =c("R1", "R2", "R3", "R4", "R5", "R6", "R7", "R8", "R9", "R10",
"R11", "R12", "R13", "R14", "R15", "R16"))
df2 <- data.frame(Session_start=c("2022-07-07 00:05:19", "2022-07-07 00:05:24",
"2022-07-07 00:05:27", "2022-07-07 00:05:33"),
Session_End=c("2022-07-07 00:05:21", "2022-07-07 00:05:26",
"2022-07-07 00:05:31", "2022-07-07 00:05:34"),
Animal_ID =c("ID1", "ID2", "ID3", "ID4"))
uj5u.com熱心網友回復：
data.table具有非 equi，更新連接可能會使這變得更好：
library(data.table)
setDT(df1)
setDT(df2)
df1[
df2,
on=.(Session_start>=Session_start, Session_start<=Session_End),
c("Animal_ID","Session_End") := .(i.Animal_ID, i.Session_End)
]
df1
## Session_start Robot_ID Animal_ID Session_End
## 1: 2022-07-07 08:05:19 R1 ID1 2022-07-07 08:05:21
## 2: 2022-07-07 08:05:20 R2 ID1 2022-07-07 08:05:21
## 3: 2022-07-07 08:05:21 R3 ID1 2022-07-07 08:05:21
## 4: 2022-07-07 08:05:22 R4 <NA> <NA>
## 5: 2022-07-07 08:05:23 R5 <NA> <NA>
## 6: 2022-07-07 08:05:24 R6 ID2 2022-07-07 08:05:26
## 7: 2022-07-07 08:05:25 R7 ID2 2022-07-07 08:05:26
## 8: 2022-07-07 08:05:26 R8 ID2 2022-07-07 08:05:26
## 9: 2022-07-07 08:05:27 R9 ID3 2022-07-07 08:05:31
##10: 2022-07-07 08:05:28 R10 ID3 2022-07-07 08:05:31
##11: 2022-07-07 08:05:29 R11 ID3 2022-07-07 08:05:31
##12: 2022-07-07 08:05:30 R12 ID3 2022-07-07 08:05:31
##13: 2022-07-07 08:05:31 R13 ID3 2022-07-07 08:05:31
##14: 2022-07-07 08:05:32 R14 <NA> <NA>
##15: 2022-07-07 08:05:33 R15 ID4 2022-07-07 08:05:34
##16: 2022-07-07 08:05:34 R16 ID4 2022-07-07 08:05:34
uj5u.com熱心網友回復：
首先，找到索引w，其中會話開始df位于df2using的會話間隔之間outer()。接下來cbind是各自的切片。最后merge剩下的。
w <- outer(df1[, 1], as.data.frame(t(df2[1:2])),
Vectorize(\(x, y) x >= y[1] & x <= y[2])) |>
apply(2, which)
Map(\(x, y) cbind(df1[x, ], df2[y, -1]), w, seq_len(nrow(df2))) |>
do.call(what=rbind) |> merge(df1, all=TRUE)
# Session_start Robot_ID Session_End Animal_ID
# 1 2022-07-07 00:05:19 R1 2022-07-07 00:05:21 ID1
# 2 2022-07-07 00:05:20 R2 2022-07-07 00:05:21 ID1
# 3 2022-07-07 00:05:21 R3 2022-07-07 00:05:21 ID1
# 4 2022-07-07 00:05:22 R4 <NA> <NA>
# 5 2022-07-07 00:05:23 R5 <NA> <NA>
# 6 2022-07-07 00:05:24 R6 2022-07-07 00:05:26 ID2
# 7 2022-07-07 00:05:25 R7 2022-07-07 00:05:26 ID2
# 8 2022-07-07 00:05:26 R8 2022-07-07 00:05:26 ID2
# 9 2022-07-07 00:05:27 R9 2022-07-07 00:05:31 ID3
# 10 2022-07-07 00:05:28 R10 2022-07-07 00:05:31 ID3
# 11 2022-07-07 00:05:29 R11 2022-07-07 00:05:31 ID3
# 12 2022-07-07 00:05:30 R12 2022-07-07 00:05:31 ID3
# 13 2022-07-07 00:05:31 R13 2022-07-07 00:05:31 ID3
# 14 2022-07-07 00:05:32 R14 <NA> <NA>
# 15 2022-07-07 00:05:33 R15 2022-07-07 00:05:34 ID4
# 16 2022-07-07 00:05:34 R16 2022-07-07 00:05:34 ID4
注意：即使沒有它，解決方案也可以作業（日期按字母順序比較），"POSIXct"當您使用日期時間時，您應該始終使用格式。如果您還沒有，請轉換它：
df1$Session_start <- as.POSIXct(df1$Session_start)
df2[1:2] <- lapply(df2[1:2], as.POSIXct)
uj5u.com熱心網友回復：
這個答案比 thelatemail 和 jay.sf 的要長得多，但我還是會貼出來，讓你有更多的想法。
我的方法是使用輔助變數，使用 lubridate 確保我使用正確的格式，然后開始傳播 Animal_ID 和 Session_End 資料。
# Loading libraries -------------------------------------------------------
library(dplyr)
library(lubridate)
# Defining datasets -------------------------------------------------------
Session_start <-
c(
"2022-07-07 00:05:19",
"2022-07-07 00:05:24",
"2022-07-07 00:05:27",
"2022-07-07 00:05:33"
)
Session_End <-
c(
"2022-07-07 00:05:21",
"2022-07-07 00:05:26",
"2022-07-07 00:05:31",
"2022-07-07 00:05:34"
)
Animal_ID <- c("ID1", "ID2", "ID3", "ID4")
df2 <- data.frame(Session_start, Session_End, Animal_ID)
Session_start <-
c(
"2022-07-07 00:05:19",
"2022-07-07 00:05:20",
"2022-07-07 00:05:21",
"2022-07-07 00:05:22",
"2022-07-07 00:05:23",
"2022-07-07 00:05:24",
"2022-07-07 00:05:25",
"2022-07-07 00:05:26",
"2022-07-07 00:05:27",
"2022-07-07 00:05:28",
"2022-07-07 00:05:29",
"2022-07-07 00:05:30",
"2022-07-07 00:05:31",
"2022-07-07 00:05:32",
"2022-07-07 00:05:33",
"2022-07-07 00:05:34"
)
Robot_ID <-
c(
"R1",
"R2",
"R3",
"R4",
"R5",
"R6",
"R7",
"R8",
"R9",
"R10",
"R11",
"R12",
"R13",
"R14",
"R15",
"R16"
)
df1 <- data.frame(Session_start, Robot_ID)
# Joining with data propagation -------------------------------------------
df <-
dplyr::left_join(df1, df2, by = "Session_start") |>
arrange(Session_start) |>
mutate(
Session_start =
Session_start |>
lubridate::as_datetime(),
Session_End =
Session_End |>
lubridate::as_datetime()
) |>
mutate(
is_na_Session_End = if_else(
condition = is.na(Session_End),
true = FALSE,
false = TRUE
),
number_of_non_NA_Session_End = cumsum(is_na_Session_End)
) |>
group_by(number_of_non_NA_Session_End) |>
mutate(Session_End =
Session_End |>
first(),
Animal_ID =
Animal_ID |>
first()) |>
mutate(
Session_End = if_else(
condition = Session_start <= Session_End,
true = Session_End,
false = NA_POSIXct_
),
Animal_ID = if_else(
condition = Session_start <= Session_End,
true = Animal_ID,
false = NA_character_
)
) |>
ungroup() |>
select(-is_na_Session_End,
-number_of_non_NA_Session_End) |>
as.data.frame()
df
轉載請註明出處，本文鏈接：https://www.uj5u.com/qukuanlian/513095.html
標籤：r日期加入重复时间戳
下一篇：Excel-分鐘數到時間