我有一些大型注冊資料集，我正在嘗試創建兩件事：
- 我想標記每個不間斷的每月觀察 (
final_df1) - 我想創建一個不間斷跨度的資料集（
final_df2）
例如：
library(tidyverse)
library(lubridate)
library(magrittr)
df<-tibble(id=c(rep("X",10),rep("Y",20)),
date=c(ymd("20120101")%m %months(c(1:5,7:11)),ymd("20120401")%m %months(c(1:10,12:17,19:22))))
final_df1 <- df %>% mutate(cont_enroll=c(rep(1,5),rep(0,5),rep(1,10),rep(0,10)))
final_df2 <- tibble(id=c(rep("X",2),rep("Y",3)),
span_start=c(ymd("20120101")%m %months(1),
ymd("20120101")%m %months(7),
ymd("20120401")%m %months(1),
ymd("20120101")%m %months(12),
ymd("20120101")%m %months(19)),
span_end=c(ymd("20120101")%m %months(5),
ymd("20120101")%m %months(11),
ymd("20120101")%m %months(10),
ymd("20120101")%m %months(17),
ymd("20120101")%m %months(22))
)
我覺得在 {lubridate} 和 {data.table} 之間必須有一種簡單的方法來做到這一點，但我正在起草空白。有小費嗎？
uj5u.com熱心網友回復：
按 'id' 分組，interval用之前的 'date' ( lag) 值和當前 'date' 創建一個，除以months，檢查是否小于 2，并取累積最小值 ( cummin)。創建'find_df_new'后，我們按'id'和'cont_enroll'列的run-length-id分組，并summarise用'date'的first和last值分別創建'span_start'和'span_end'
library(dplyr)
library(lubridate)
library(data.table)
final_df_new <- df %>%
group_by(id) %>%
mutate(cont_enroll2 = cummin(interval(lag(date, default = first(date)),
date) /months(1) < 2)) %>%
ungroup
final_df_new %>%
group_by(id, grp = rleid(cont_enroll2)) %>%
summarise(span_start = first(date), span_end = last(date), .groups = 'drop')
uj5u.com熱心網友回復：
我認為你可以用ivs包很好地解決這個問題。您的日期似乎真的代表 1 個月的間隔，并且 ivs 包專門用于處理這種型別的資料。
我們可以計算final_df2，iv_groups()它回傳合并所有重疊間隔后剩余的非重疊間隔。
final_df2然后每組的第一行代表第一個連續間隔，因此您只需要檢查每個范圍是否在該間隔內即可確定它是否是不間斷集合的一部分 get final_df1。
請注意，我的final_df2外觀與您的不同，您的編碼方式是否可能有錯誤？
library(dplyr)
library(lubridate)
library(ivs)
df <- tibble(
id = c(
rep("X", 10),
rep("Y", 20)
),
date = c(
ymd("20120101") %m % months(c(1:5,7:11)),
ymd("20120401") %m % months(c(1:10,12:17,19:22))
)
)
df
#> # A tibble: 30 × 2
#> id date
#> <chr> <date>
#> 1 X 2012-02-01
#> 2 X 2012-03-01
#> 3 X 2012-04-01
#> 4 X 2012-05-01
#> 5 X 2012-06-01
#> 6 X 2012-08-01
#> 7 X 2012-09-01
#> 8 X 2012-10-01
#> 9 X 2012-11-01
#> 10 X 2012-12-01
#> # … with 20 more rows
df <- df %>%
mutate(start = date, end = date months(1), .keep = "unused") %>%
mutate(range = iv(start, end), .keep = "unused")
df
#> # A tibble: 30 × 2
#> id range
#> <chr> <iv<date>>
#> 1 X [2012-02-01, 2012-03-01)
#> 2 X [2012-03-01, 2012-04-01)
#> 3 X [2012-04-01, 2012-05-01)
#> 4 X [2012-05-01, 2012-06-01)
#> 5 X [2012-06-01, 2012-07-01)
#> 6 X [2012-08-01, 2012-09-01)
#> 7 X [2012-09-01, 2012-10-01)
#> 8 X [2012-10-01, 2012-11-01)
#> 9 X [2012-11-01, 2012-12-01)
#> 10 X [2012-12-01, 2013-01-01)
#> # … with 20 more rows
# `iv_groups()` returns the groups that remain after merging all overlapping ranges.
# It gives you `final_df2`.
continuous <- df %>%
group_by(id) %>%
summarise(range = iv_groups(range), .groups = "drop")
continuous
#> # A tibble: 5 × 2
#> id range
#> <chr> <iv<date>>
#> 1 X [2012-02-01, 2012-07-01)
#> 2 X [2012-08-01, 2013-01-01)
#> 3 Y [2012-05-01, 2013-03-01)
#> 4 Y [2013-04-01, 2013-10-01)
#> 5 Y [2013-11-01, 2014-03-01)
# The first continuous range per id
first_continuous <- continuous %>%
group_by(id) %>%
slice(1) %>%
ungroup() %>%
rename(range_continuous = range)
first_continuous
#> # A tibble: 2 × 2
#> id range_continuous
#> <chr> <iv<date>>
#> 1 X [2012-02-01, 2012-07-01)
#> 2 Y [2012-05-01, 2013-03-01)
# Join the first continuous range df back onto the original df and see if
# the current `range` falls within the first continuous range or not.
# This gives you `final_df1`.
left_join(df, first_continuous, by = "id") %>%
mutate(continuous = iv_pairwise_overlaps(range, range_continuous, type = "within"))
#> # A tibble: 30 × 4
#> id range range_continuous continuous
#> <chr> <iv<date>> <iv<date>> <lgl>
#> 1 X [2012-02-01, 2012-03-01) [2012-02-01, 2012-07-01) TRUE
#> 2 X [2012-03-01, 2012-04-01) [2012-02-01, 2012-07-01) TRUE
#> 3 X [2012-04-01, 2012-05-01) [2012-02-01, 2012-07-01) TRUE
#> 4 X [2012-05-01, 2012-06-01) [2012-02-01, 2012-07-01) TRUE
#> 5 X [2012-06-01, 2012-07-01) [2012-02-01, 2012-07-01) TRUE
#> 6 X [2012-08-01, 2012-09-01) [2012-02-01, 2012-07-01) FALSE
#> 7 X [2012-09-01, 2012-10-01) [2012-02-01, 2012-07-01) FALSE
#> 8 X [2012-10-01, 2012-11-01) [2012-02-01, 2012-07-01) FALSE
#> 9 X [2012-11-01, 2012-12-01) [2012-02-01, 2012-07-01) FALSE
#> 10 X [2012-12-01, 2013-01-01) [2012-02-01, 2012-07-01) FALSE
#> # … with 20 more rows
由reprex 包(v2.0.1)創建于 2022-05-13
轉載請註明出處，本文鏈接：https://www.uj5u.com/yidong/474732.html
上一篇：如何使用日期時間從字串中提取日期