检查日期间隔是否重叠
检查日期间隔是否重叠
提问于 2022-09-18 15:05:50
我有下面的数据文件，其中每个客户端都有一个不同的程序周期。我正在尝试创建一个循环，以检查客户端是否在选定的期间(每月频率)参与了一个程序。
Client <- c("A", "B", "C", "D", "E", "F", "G", "H", "I", "J", "K", "L")
EntryDate <- as.Date(c("2004-01-01","2004-02-11", "2004-03-21","2004-04-14","2004-05-22","2004-05-08","2004-07-20",
"2004-07-28","2004-09-30","2004-10-25","2004-11-28","2004-12-30"))
ExitDate <- as.Date(c("2005-01-22","2004-03-01", "2004-05-21","2004-04-28","2004-08-09","2004-08-08","2005-06-20",
"2004-10-28","2004-10-30","2004-11-01","2004-12-28","2005-12-30"))
df <- data.frame(Client,EntryDate, ExitDate)
df$interval_client = interval(df$EntryDate,df$ExitDate)
Client EntryDate ExitDate interval_client
1 A 2004-01-01 2005-01-22 2004-01-01 UTC--2005-01-22 UTC
2 B 2004-02-11 2004-03-01 2004-02-11 UTC--2004-03-01 UTC
3 C 2004-03-21 2004-05-21 2004-03-21 UTC--2004-05-21 UTC
4 D 2004-04-14 2004-04-28 2004-04-14 UTC--2004-04-28 UTC
5 E 2004-05-22 2004-08-09 2004-05-22 UTC--2004-08-09 UTC
6 F 2004-05-08 2004-08-08 2004-05-08 UTC--2004-08-08 UTC
7 G 2004-07-20 2005-06-20 2004-07-20 UTC--2005-06-20 UTC
8 H 2004-07-28 2004-10-28 2004-07-28 UTC--2004-10-28 UTC
9 I 2004-09-30 2004-10-30 2004-09-30 UTC--2004-10-30 UTC
10 J 2004-10-25 2004-11-01 2004-10-25 UTC--2004-11-01 UTC
11 K 2004-11-28 2004-12-28 2004-11-28 UTC--2004-12-28 UTC
12 L 2004-12-30 2005-12-30 2004-12-30 UTC--2005-12-30 UTC基本上，我希望看到某个客户在哪个月(2004年1月至2005年12月)参加了一个项目。我试过不同的密码，但没有人给我想要的结果。
years <- 2004:2005
months <- 1:12
# option 1
for (y in years){
for (m in months){
df[ ,paste0(y, '-', m)] <- ifelse(df$interval_client %within% interval(ymd(paste0(y, '-', m, '-', 1)), ceiling_date(ymd(paste0(y, '-', m, '-', 1)),"month")-1), 1, 0)
}
}
# option 2
for (y in years){
for (m in months){
df[ ,paste0(y, '-', m)] <- ifelse(df$interval_client >= as.Date(paste0(y, '-', m, '-', 1), '%Y-%m-%d') & df$interval_client <= ceiling_date(as.Date(paste0(y, '-', m, '-', 1), '%Y-%m%d'),"month")-1 ,1, 0)
}
}我想要这样的东西(只显示前3行)
Client EntryDate ExitDate interval_client 2004-1 2004-2 2004-3 2004-4 2004-5 2004-6 2004-7 2004-8 2004-9
1 A 2004-01-01 2005-01-22 2004-01-01 UTC--2005-01-22 UTC 1 1 1 1 1 1 1 1 1
2 B 2004-02-11 2004-03-01 2004-02-11 UTC--2004-03-01 UTC 0 1 1 0 0 0 0 0 0
3 C 2004-03-21 2004-05-21 2004-03-21 UTC--2004-05-21 UTC 0 0 1 1 1 0 0 0 0
2004-10 2004-11 2004-12 2005-1 2005-2 2005-3 2005-4 2005-5 2005-6 2005-7 2005-8 2005-9 2005-10 2005-11 2005-12
1 1 1 1 1 0 0 0 0 0 0 0 0 0 0 0
2 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0
3 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0回答 2
Stack Overflow用户
回答已采纳
发布于 2022-09-19 01:30:50
下面是使用data.table的另一种方法，它应该给出类似的结果。您可以从输入和退出日期创建一个月的seq值(使用floor_date从月份的第一天开始)。然后使用dcast将其转换为一年月的宽格式.
library(lubridate)
library(data.table)
setDT(df)[ , list(Client = Client, month = format(seq(floor_date(EntryDate, "month"), floor_date(ExitDate, "month"), by = "month"), format = "%Y-%m")), by = 1:nrow(df)][
, dcast(.SD, Client ~ month, fun = length)
]输出
Client 2004-01 2004-02 2004-03 2004-04 2004-05 2004-06 2004-07 2004-08 2004-09 2004-10 2004-11 2004-12 2005-01 2005-02 2005-03 2005-04 2005-05 2005-06 2005-07 2005-08 2005-09
1: A 1 1 1 1 1 1 1 1 1 1 1 1 1 0 0 0 0 0 0 0 0
2: B 0 1 1 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0
3: C 0 0 1 1 1 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0
4: D 0 0 0 1 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0
5: E 0 0 0 0 1 1 1 1 0 0 0 0 0 0 0 0 0 0 0 0 0
6: F 0 0 0 0 1 1 1 1 0 0 0 0 0 0 0 0 0 0 0 0 0
7: G 0 0 0 0 0 0 1 1 1 1 1 1 1 1 1 1 1 1 0 0 0
8: H 0 0 0 0 0 0 1 1 1 1 0 0 0 0 0 0 0 0 0 0 0
9: I 0 0 0 0 0 0 0 0 1 1 0 0 0 0 0 0 0 0 0 0 0
10: J 0 0 0 0 0 0 0 0 0 1 1 0 0 0 0 0 0 0 0 0 0
11: K 0 0 0 0 0 0 0 0 0 0 1 1 0 0 0 0 0 0 0 0 0
12: L 0 0 0 0 0 0 0 0 0 0 0 1 1 1 1 1 1 1 1 1 1
2005-10 2005-11 2005-12
1: 0 0 0
2: 0 0 0
3: 0 0 0
4: 0 0 0
5: 0 0 0
6: 0 0 0
7: 0 0 0
8: 0 0 0
9: 0 0 0
10: 0 0 0
11: 0 0 0
12: 1 1 1Stack Overflow用户
发布于 2022-09-18 17:28:48
这可能是一种选择。我不需要interval_client，只是在ExitDate和EntryDate之间设置了一个条件。
for (y in years){
for (m in months){
df[ ,paste0(y, '-', m)] <- ifelse(ExitDate >= ym(paste0(y, '-', m)) &
EntryDate <= ym(paste0(y, '-', m)),
1, 0)
}
}
#Client EntryDate ExitDate interval_client 2004-1 2004-2 2004-3 2004-4 2004-5 2004-6 2004-7 2004-8 2004-9 2004-10
#1 A 2004-01-01 2005-01-22 2004-01-01 UTC--2005-01-22 UTC 1 1 1 1 1 1 1 1 1 1
#2 B 2004-02-11 2004-03-01 2004-02-11 UTC--2004-03-01 UTC 0 0 1 0 0 0 0 0 0 0
#3 C 2004-03-21 2004-05-21 2004-03-21 UTC--2004-05-21 UTC 0 0 0 1 1 0 0 0 0 0
#4 D 2004-04-14 2004-04-28 2004-04-14 UTC--2004-04-28 UTC 0 0 0 0 0 0 0 0 0 0
#5 E 2004-05-22 2004-08-09 2004-05-22 UTC--2004-08-09 UTC 0 0 0 0 0 1 1 1 0 0
#6 F 2004-05-08 2004-08-08 2004-05-08 UTC--2004-08-08 UTC 0 0 0 0 0 1 1 1 0 0
#7 G 2004-07-20 2005-06-20 2004-07-20 UTC--2005-06-20 UTC 0 0 0 0 0 0 0 1 1 1
#8 H 2004-07-28 2004-10-28 2004-07-28 UTC--2004-10-28 UTC 0 0 0 0 0 0 0 1 1 1
#9 I 2004-09-30 2004-10-30 2004-09-30 UTC--2004-10-30 UTC 0 0 0 0 0 0 0 0 0 1
#10 J 2004-10-25 2004-11-01 2004-10-25 UTC--2004-11-01 UTC 0 0 0 0 0 0 0 0 0 0
#11 K 2004-11-28 2004-12-28 2004-11-28 UTC--2004-12-28 UTC 0 0 0 0 0 0 0 0 0 0
#12 L 2004-12-30 2005-12-30 2004-12-30 UTC--2005-12-30 UTC 0 0 0 0 0 0 0 0 0 0页面原文内容由Stack Overflow提供。腾讯云小微IT领域专用引擎提供翻译支持
原文链接：
https://stackoverflow.com/questions/73763824
复制相关文章
相似问题
腾讯云开发者
Copyright © 2013 - 2026 Tencent Cloud. All Rights Reserved. 腾讯云 版权所有
深圳市腾讯计算机系统有限公司 ICP备案/许可证号：粤B2-20090059 
粤公网安备44030502008569号
腾讯云计算（北京）有限责任公司 京ICP证150476号 | 京ICP备11018762号