CODEWITHSUNDEEP
r
Michael Cantrall
Michael Cantrall

Reputation: 323

Delete a row based on a two seperate matching requirements

If there is a post about this I apologize - I searched many times for an answer and couldn't find anything that works.

What I need to do is delete all rows in the following example that are equal to 66 only if there is a duplicate animal type with anything other then a 66. 

animals <- c("dog", "dog", "dog", "cat", "cat", "cat", "mouse", "mouse", "rat", "rat")
number <- c(1,2,66,2,66,66,66,66,2,1)

df <- data.frame(animals,number)

Using that df I would want to delete row 3 because dog has other values of 1 and 2, I would want to delete both 66's for cat because there is a cat with other value of 2 but I wouldn't want to delete either mouse entries because they are both 66, and I wouldn't want to delete anything with rat because there are no 66 values.

I would end up something similar to this:

animals <- c("dog", "dog", "cat", "mouse", "mouse", "rat", "rat")
number <- c(1,2,2,66,66,2,1)

In the real data-set there are so many entries that you simply cant use a count and remove everything with an aggregate total of less then 66 (was my first instinct)

This was my second try but couldn't think through it for some reason.

df(!number == 66 | if(unique(animals) ==

maybe a which statement involved? Any help would be greatly appreciated!

Upvotes: 2

Views: 39

Answers (2)

Ronak Shah
Ronak Shah

Reputation: 389047

One way using base R ave where we check if any animal has a number other than 66, if it has then we return the ones ignoring 66 or else return all rows. 

df[with(df, ave(number != 66, animals, FUN = function(x) if (any(x)) x else !x)), ]


#  animals number
#1     dog      1
#2     dog      2
#3     cat      2
#4   mouse     66
#5   mouse     66
#6     rat      2
#7     rat      1

The dplyr version would filter the groups which has all 66 in it or ignore the rows with 66 otherwise.

library(dplyr)

df %>%
   group_by(animals) %>%
   filter(all(number == 66) | number != 66)

# animals number
#  <fct>    <dbl>
#1 dog          1
#2 dog          2
#3 cat          2
#4 mouse       66
#5 mouse       66
#6 rat          2
#7 rat          1

Upvotes: 5

A. Suliman
A. Suliman

Reputation: 13125

Using dplyr

library(dplyr)
df %>% group_by(animals) %>%
  mutate(Flag= case_when( number %in% c(1,2) ~ 1,
                          all(number == 66) ~ 1,
                          number == 66 ~ 0)) %>% 
  filter(Flag==1) %>% select(-Flag) %>% ungroup()


# A tibble: 7 x 2
animals number
<chr>    <dbl>
1 dog         1.
2 dog         2.
3 cat         2.
4 mouse      66.
5 mouse      66.
6 rat         2.
7 rat         1.

Upvotes: 1

Related Questions