如何以R中的日期范围作为"grepl“条件
如何以R中的日期范围作为"grepl“条件
提问于 2022-10-04 14:05:13
假设我有两个数据格式-- df1和df2
df1 = structure(list(surname = c("Duisenberg", "Trichet", "Draghi"),
`start term` = structure(c(896659200, 1067644800, 1320105600
), class = c("POSIXct", "POSIXt"), tzone = "UTC"), `end term` = structure(c(1067558400,
1320019200, 1572480000), class = c("POSIXct", "POSIXt"), tzone = "UTC")), row.names = c(1L,
9L, 15L), class = "data.frame") %>% data.frame(stringsAsFactors = F)
surname start.term end.term
1 Duisenberg 1998-06-01 2003-10-31
9 Trichet 2003-11-01 2011-10-31
15 Draghi 2011-11-01 2019-10-31
df2= data.frame(Date = c("2010-01-01","1997-01-01","2020-01-01","2004-01-01","2012-01-01","1999-01-01","2000-01-01","2020-01-01","2022-01-01","1996-01-01"), speaker = c("Mario Draghi","W.L. Duisenberg","Ciao","Jean-Claude Trichet","M. Draghi","W.L. Duisenberg","Jean-Claude Trichet","Bye","Ciao","Mario Draghi"), stringsAsFactors = F)
Date speaker
1 2010-01-01 Mario Draghi
2 1997-01-01 W.L. Duisenberg
3 2020-01-01 Ciao
4 2004-01-01 Jean-Claude Trichet
5 2012-01-01 M. Draghi
6 1999-01-01 W.L. Duisenberg
7 2000-01-01 Jean-Claude Trichet
8 2020-01-01 Bye
9 2022-01-01 Ciao
10 1996-01-01 Mario Draghi当df1中的名称以这样的形式出现在df2中时，我可以很容易地找到：
which(grepl(paste0(df1$surname, collapse = "|"), df2$speaker, ignore.case = TRUE))
[1] 1 2 4 5 6 7 10相反，更难说的是：df1中的名称只有在df2中的日期超出了df1 (start.term和end.term)的界限时才出现在df1中。
其结果应是：
[1] 1 2 10我该怎么做呢？有人能帮我吗？
谢谢!
回答 2
Stack Overflow用户
回答已采纳
发布于 2022-10-04 14:26:47
我认为基本上您想在这里对匹配的名称执行一个联接操作。所以第一步是找出这些是什么：
library(dplyr)
surnames_regex <- paste0(df1$surname, collapse = "|")
df2$matching_name <- strsplit(df2$speaker, split = "\\s") |>
lapply(
\(name) {
matching_name <- grep(surnames_regex, name, v = T)
matching_name <- ifelse(
length(matching_name) > 0,
matching_name[1],
NA_character_
)
matching_name
}
) |>
unlist()
df2
# Date speaker matching_name
# 1 2010-01-01 Mario Draghi Draghi
# 2 1997-01-01 W.L. Duisenberg Duisenberg
# 3 2020-01-01 Ciao <NA>
# 4 2004-01-01 Jean-Claude Trichet Trichet
# 5 2012-01-01 M. Draghi Draghi
# 6 1999-01-01 W.L. Duisenberg Duisenberg
# 7 2000-01-01 Jean-Claude Trichet Trichet
# 8 2020-01-01 Bye <NA>
# 9 2022-01-01 Ciao <NA>
# 10 1996-01-01 Mario Draghi Draghi然后，简单地将这些名称连接起来，并根据您定义的条件进行过滤：
df2 |>
inner_join(
df1,
by = c("matching_name" = "surname")
) |>
filter(
Date < start.term |
Date > end.term
)
# Date speaker matching_name start.term end.term
# 1 2010-01-01 Mario Draghi Draghi 2011-11-01 2019-10-31
# 2 1997-01-01 W.L. Duisenberg Duisenberg 1998-06-01 2003-10-31
# 3 2000-01-01 Jean-Claude Trichet Trichet 2003-11-01 2011-10-31
# 4 1996-01-01 Mario Draghi Draghi 2011-11-01 2019-10-31Stack Overflow用户
发布于 2022-10-04 14:51:53
确保所有的日期都是一致的格式，即使所有的日期类。然后，这可以在一个SQL语句中完成。
library(sqldf)
df1a <- transform(df1, start.term = as.Date(start.term),
end.term = as.Date(end.term))
df2a <- transform(df2, Date = as.Date(Date))
sqldf("select distinct b.rowid, *
from df1a a
join df2a b on
(b.Date < a.[start.term] or b.Date > a.[end.term]) and
b.speaker like '%' || a.surname || '%'")给予：
rowid surname start.term end.term Date speaker
1 1 Draghi 2011-11-01 2019-10-31 2010-01-01 Mario Draghi
2 2 Duisenberg 1998-06-01 2003-10-31 1997-01-01 W.L. Duisenberg
3 7 Trichet 2003-11-01 2011-10-31 2000-01-01 Jean-Claude Trichet
4 10 Draghi 2011-11-01 2019-10-31 1996-01-01 Mario Draghi页面原文内容由Stack Overflow提供。腾讯云小微IT领域专用引擎提供翻译支持
原文链接：
https://stackoverflow.com/questions/73949166
复制相关文章
相似问题
腾讯云开发者
Copyright © 2013 - 2026 Tencent Cloud. All Rights Reserved. 腾讯云 版权所有
深圳市腾讯计算机系统有限公司 ICP备案/许可证号：粤B2-20090059 
粤公网安备44030502008569号
腾讯云计算（北京）有限责任公司 京ICP证150476号 | 京ICP备11018762号