CODEWITHSUNDEEP
rdataframematchingrow-removal
Yuval Harris
Yuval Harris

Reputation: 73

Remove any data frame rows containing a value in one column which has multiple matches in another column

Let's say I have this (simplified) data frame:

C1 <- c('a','a','b','b','c','c')
C2 <- c(10,10,20,21,30,30)
C3 <- c(1.1,2.2,3.3,4.4,5.5,6.6)
df <- data.frame(C1,C2,C3)
C1 C2 C3
a 10 1.1
a 10 2.2
b 20 3.3
b 21 4.4
c 30 5.5
c 30 6.6

What I'm trying to do is to delete any rows containing a C1 value which has more than one match in the C2 column. In this case I would like to delete the entire rows containing 'b' in the C1 column (because 'b' has two matches - both 20 and 21 - in column C2).

This should result with this df:

C1 C2 C3
a 10 1.1
a 10 2.2
c 30 5.5
c 30 6.6

Any help would be really appreciated!

Thanks,

Yuval

Upvotes: 1

Views: 235

Answers (2)

Damian
Damian

Reputation: 1433

dplyr is another way to do this. Use group_by to process each C1 group separately, then filter each group, keeping only groups with a single value of C2

library(dplyr)

C1 <- c('a','a','b','b','c','c')
C2 <- c(10,10,20,21,30,30)
C3 <- c(1.1,2.2,3.3,4.4,5.5,6.6)
df <- data.frame(C1,C2,C3)

df <- df %>%
    group_by(C1) %>%
    filter(length(unique(C2)) == 1) %>%
    ungroup()

print(df)

Output

# A tibble: 4 x 3
  C1       C2    C3
  <chr> <dbl> <dbl>
1 a        10   1.1
2 a        10   2.2
3 c        30   5.5
4 c        30   6.6

Upvotes: 1

windyvation
windyvation

Reputation: 561

Subset is one way to do this:

C1 <- c('a','a','b','b','c','c')
C2 <- c(10,10,20,21,30,30)
C3 <- c(1.1,2.2,3.3,4.4,5.5,6.6)
df <- data.frame(C1,C2,C3)

df_table <- table(df$C2)
df_subset <- subset(df, C2 %in% names(df.table[df.table > 1]))
df_subset

Upvotes: 1

Related Questions