我的資料框包含 3 列、一個分組因子Current_Date和Start_Date（根據定義，Current_Date≥ Start_Date；日期格式為dmy），每列中有多個日期重復，并且它們之間存在不同長度的滯后。有些日期在組之間重疊，但有些則沒有。
真實資料有數十萬行長，所以我的問題是找到一種有效的方法來為每一行分配unique整個Date列（by分組因子）和seq時間視窗定義的日期（Start_Date到Current_Date）之間的重疊數，這是特定于每一行的。
下面顯示了一個虛擬資料，具有添加 Dates_in_range列的所需結果，但沒有分組因子，我不知道如何以for回圈格式處理（例如，21-10 之間的視窗中只有一個唯一日期-22 和 21-10-22，但視窗中的三個唯一日期在 21-10-22 和 28-10-22 之間）：
Current_Date Start_Date Dates_in_range
1 21-10-22 21-10-22 1
2 26-10-22 26-10-22 1
3 26-10-22 21-10-22 2
4 26-10-22 26-10-22 1
5 26-10-22 21-10-22 2
6 28-10-22 26-10-22 2
7 28-10-22 28-10-22 1
8 28-10-22 21-10-22 3
我的解決方案是基于創建兩種型別的包含日期的串列，使用for-loops，并將每種型別作為臨時列添加到資料表中：第一種型別是整個資料集（或其中的一個組）共享的所有日期的相同串列它）重復資料表中的所有行（或其中的一個組）；第二種型別是特定于行的串列，派生自Current_Date和指定的時間視窗Start_Date。intersect然后我在每行的兩個串列列之間找到一個，應用另一個for回圈。
下面附上可重現的代碼：
library(data.table)
## Load the data set
dt = data.table(Current_Date= c("21-10-22","26-10-22","26-10-22","26-10-22","26-10-22","28-10-22","28-10-22","28-10-22"),
Start_Date = c("21-10-22","26-10-22","21-10-22","26-10-22","21-10-22","26-10-22","28-10-22","21-10-22"))
# Specify dates into DMY date format
library(lubridate)
dt$Current_Date<- dmy(dt$Current_Date)
dt$Start_Date <- dmy(dt$Start_Date)
## Create a list of all current dates within the data set (= Current_Date column)
Dates_all <- as.list(dt$Current_Date)
# Add the list as a Dates_all column to the data set
dt$All_dates <- list()
for (i in 1:length(dt[, Current_Date])){
dt$All_dates[[i]] <- Dates_all
}
## Create a list of sequences of all possible dates within the date period (from Start_Date to Current_Date) for each row
Date_window <- list()
for (i in 1:length(dt[, Current_Date])){
Date_window[[i]] <- as.list(seq(as.Date(dt[i, Start_Date]), as.Date(dt[i, Current_Date]), by="days"))
}
# Add the list as a Date_window column to the data set
dt$Date_window <- Date_window
## Add the Dates_in_range column containing the number of dates from Current_Date column, occurring in the row-specific time window
for (i in 1:length(dt[, Current_Date])){
dt$Dates_in_range[[i]] <- length(intersect(dt$Date_window[[i]], dt$All_dates[[i]]))
}
# Cleanup & print
dt[, c("Date_window","All_dates") := NULL]
rm(Dates_all, Date_window, i)
print(dt)
我懷疑它可以使用foverlaps函式來完成，但我不確定在這種情況下如何應用它。
提前致謝！
uj5u.com熱心網友回復：
使用data.table，您可以執行以下操作：
library(data.table)
dt = data.table(Current_Date= c("21-10-22","26-10-22","26-10-22","26-10-22","26-10-22","28-10-22","28-10-22","28-10-22"),
Start_Date = c("21-10-22","26-10-22","21-10-22","26-10-22","21-10-22","26-10-22","28-10-22","21-10-22"))
dt[,Dates_in_range := sum(between(dt[,unique(Current_Date)], Start_Date, Current_Date)),
by=rownames(dt)]
dt
#> Current_Date Start_Date Dates_in_range
#> 1: 21-10-22 21-10-22 1
#> 2: 26-10-22 26-10-22 1
#> 3: 26-10-22 21-10-22 2
#> 4: 26-10-22 26-10-22 1
#> 5: 26-10-22 21-10-22 2
#> 6: 28-10-22 26-10-22 2
#> 7: 28-10-22 28-10-22 1
#> 8: 28-10-22 21-10-22 3
uj5u.com熱心網友回復：
使用sapply：
dt[, n := sapply(Start_Date, function(x, y) sum(x <= y), y = unique(Start_Date)), by = Current_Date]
或使用frank：
dt[, n := frank(1/as.integer(Start_Date), ties.method = "dense"), by = Current_Date]
uj5u.com熱心網友回復：
這是另一種方法，仍然使用應該可以作業的 for 回圈。基本上，我們首先獲取所有可能日期的向量，然后定義一個函式來檢查這些日期是否在由最小值或最大值定義的范圍內，然后我們使用 for 回圈遍歷資料集將該函式應用于每個行資料。當然，vapply()如果您更喜歡矢量化，我們可以在這里使用或類似的東西。
# get unique dates from all columns
dates <- unique(c(dt$Current_Date, dt$Current_Date))
# function to see how many are in a range
n_in_range <- function(d, mn, mx) {
sum(d <= mx & d >= mn)
}
#for loop
dt$Dates_in_range <- NA
for (i in 1:nrow(dt)) {
dt$Dates_in_range[i] <- n_in_range(dates, dt$Start_Date[i], dt$Current_Date[i])
}
dt
Current_Date Start_Date Dates_in_range
1: 2022-10-21 2022-10-21 1
2: 2022-10-26 2022-10-26 1
3: 2022-10-26 2022-10-21 2
4: 2022-10-26 2022-10-26 1
5: 2022-10-26 2022-10-21 2
6: 2022-10-28 2022-10-26 2
7: 2022-10-28 2022-10-28 1
8: 2022-10-28 2022-10-21 3
uj5u.com熱心網友回復：
不使用分組
library(lubridate)
library(tidyverse)
dt = data.frame(Current_Date= c("21-10-22","26-10-22","26-10-22","26-10-22","26-10-22","28-10-22","28-10-22","28-10-22"),
Start_Date = c("21-10-22","26-10-22","21-10-22","26-10-22","21-10-22","26-10-22","28-10-22","21-10-22"))
dt %>%
mutate(across(ends_with("_Date"), dmy)) %>%
mutate(Dates_in_range = map2_dbl(.x = Start_Date, .y = Current_Date, .f = ~sum(between(x = unique(Current_Date), left = .x, right = .y))))
#> Current_Date Start_Date Dates_in_range
#> 1 2022-10-21 2022-10-21 1
#> 2 2022-10-26 2022-10-26 1
#> 3 2022-10-26 2022-10-21 2
#> 4 2022-10-26 2022-10-26 1
#> 5 2022-10-26 2022-10-21 2
#> 6 2022-10-28 2022-10-26 2
#> 7 2022-10-28 2022-10-28 1
#> 8 2022-10-28 2022-10-21 3
使用reprex v2.0.2創建于 2022-10-29
轉載請註明出處，本文鏈接：https://www.uj5u.com/qiye/523806.html
下一篇：如果資料陣列回圈