CODEWITHSUNDEEP
rconditional-statementsrowsubset
Mylène
Mylène

Reputation: 3

R: remove row if certain value can be found in other row

I work with a dataset where I want to remove a row if the tag is 1 and there is an id number where the tag is 3. EDIT: tags value can only be: NA, 1, 2 or 3. id numbers are distinct and are only find three times if there exists a tag 1, tag 2 and tag 3.

> dat1 = data.frame(id=c(15399,15404,15405,15407,15407,15407,15403), tag=c(NA,NA,1,1,2,3,1))

> dat1 id tag 1 15399 NA 2 15404 NA 3 15405 1 4 15407 1 5 15407 2 6 15407 3 7 15403 1

I need to return this:

> dat1 id tag 1 15399 NA 2 15404 NA 3 15405 1 5 15407 2 6 15407 3 7 15403 1

Could someone help me? I only figured out how to remove all the ID's where the tag is 3:

> subset(dat1,!dat1$id %in% dat1$id[dat1$tag == 3]) id tag 1 15399 NA 2 15404 NA 3 15405 1 7 15403 1

Upvotes: 0

Views: 1233

Answers (3)

akrun
akrun

Reputation: 886948

We can do this with data.table

library(data.table)
setDT(dat1)[, .SD[any(tag != 1) & tag !=1 | all(tag==1) |is.na(tag)] , by = id]
#      id tag
#1: 15399  NA
#2: 15404  NA
#3: 15405   1
#4: 15407   2
#5: 15407   3
#6: 15403   1

If the condition is to delete the row that have 'tag' as 1 where there is also a 'tag' 3 for a particular 'id', then

setDT(dat1)[, .SD[!(all(c(1,3) %in% tag) & tag == 1)]  , id]
#      id tag
#1: 15399  NA
#2: 15404  NA
#3: 15405   1
#4: 15407   2
#5: 15407   3
#6: 15403   1

Or with dplyr

library(dplyr)
dat1 %>% 
   group_by(id) %>%
   filter(any(tag != 1) & tag !=1 | all(tag==1) |is.na(tag))

Based on the second condition

dat1 %>%
    group_by(id) %>%
    filter(!(all(c(1,3) %in% tag) & tag ==1))
# A tibble: 6 x 2
# Groups: id [5]
#     id   tag
#  <dbl> <dbl>
#1 15399    NA
#2 15404    NA
#3 15405     1
#4 15407     2
#5 15407     3
#6 15403     1

Upvotes: 1

amonk
amonk

Reputation: 1795

 dat1 = data.frame(id=c(15399,15404,15405,15407,15407,15407,15403), 
 tag=c(NA,NA,1,1,2,3,1))#construct the data
 dat1_tag3<-dat1[dat1$tag==3,]#keep the rows with tag equals to 3
 dat1_tag3<-dat1_tag3[!is.na(dat1_tag3$id),]#remove NA's
 dat2remove<-dat1[(dat1$id %in% unique(dat1_tag3$id) & dat1$tag==1),]#find rows that need to be excluded

 all<-rbind(dat1,dat2remove)#rbinding the two datasets
 all<-all[!(duplicated(all[c("id","tag")]) | duplicated(all[c("id","tag")], fromLast = TRUE)),]#removing duplicates (as pairs)

    id tag
1 15399  NA
2 15404  NA
3 15405   1
5 15407   2
6 15407   3
7 15403   1

Upvotes: 0

jyr
jyr

Reputation: 692

dat1[!duplicated(dat1$id,fromLast = TRUE)|duplicated(dat1$id)&dat1$tag!="1",]

You can do it simply like this but first you need to order data by tag. Its not very pretty way but it should work. 

> dat1[!duplicated(dat1$id,fromLast = TRUE)|duplicated(dat1$id)&dat1$tag!="1",]
     id tag
1 15399  NA
2 15404  NA
3 15405   1
5 15407   2
6 15407   3
7 15403   1

Upvotes: 1

Related Questions