r - 根据不同场景创建用于粘贴或删除元素的循环

Question

假设我有以下数据集:

mydf <- data.frame( "MemberID"=c("111","0111A","0111B","112","0112A","113","0113B"),
                    "resign.date"=c("2013/01/01",NA,NA,"2014/03/01",NA,NA,NA))                                            

注:111,112和113是家族代表的ID。

我想做两件事:

a) 如果我有家庭代表的辞职日期，例如在 111 的情况下，我想为 0111A 和 0111B 粘贴相同的辞职日期(如果您想知道，这些代表 111 的配偶和子女)< br/>b) 如果我没有家庭代表的辞职日期，例如 113，我只想删除第 113 和 0113B 行。

我生成的数据框应如下所示:

mydf <- data.frame("MemberID"=c("111","0111A","0111B","112","0112A"),
                    "resign.date"=c("2013/01/01","2013/01/01","2013/01/01","2014/03/01","2014/03/01"))

提前致谢。

Answer 1

如果 resign.date 仅存在于(某些)MembersID 而没有尾随字母，则使用 data.table 的解决方案

library(data.table)

df <- data.table( "MemberID"=c("0111","0111A","0111B","0112","0112A","0113","0113B"),
                "resign.date"=c("2013/01/01",NA,NA,"2014/03/01",NA,NA,NA)) 

df <- df[order(MemberID)] ## order data : MemberIDs w/out trailing letters first by ID
df[, myID := gsub("\\D+", "", MemberID)] ## create myID col : MemberID w/out trailing letters

df[ , my.resign.date := resign.date[1L], by = myID] ##assign first occurrence of resign date by myID
df <- df[!is.na(my.resign.date)] ##drop rows if my.resign.date is missing

编辑

如果 MemberID 不一致(有些有前导 0，有些没有)，您可以按照以下方法尝试变通

df <- data.table( "MemberID"=c("111","0111A","0111B","112","0112A","113","0113B"),
              "resign.date"=c("2013/01/01",NA,NA,"2014/03/01",NA,NA,NA)) 

df[, myID := gsub("(?<![0-9])0+", "", gsub("\\D+", "", MemberID), perl = TRUE)]
df <- df[order(myID, -MemberID)]

df[ , my.resign.date := resign.date[1L], by = myID]
df <- df[!is.na(my.resign.date)]