Find mismatches in the same column considering variables in another column

user_14666929 0
Viewed 55

Hi I have the following dataframe

df = rbind(c('John', '1', 'a', 'a'), 
            c('John', '1', 'a', 'a'), 
            c('David', '2', 'b', 'b'), 
            c('David', '2', 'b', 'b'),
            c('Jack', '3', 'b', 'b'),
            c('Jack', '3', 'b', 'b'),
           c('David', '1', 'b', 'b'),
            c('Chris', '3', 'b', 'b'),
            c('Peter', '4', 'b', 'b')) %>%
    data.frame

colnames(df) <- c('name', paste('t', 1:3, sep = ''))

   name t1 t2 t3
1  John  1  a  a
2  John  1  a  a
3 David  2  b  b
4 David  2  b  b
5  Jack  3  b  b
6  Jack  3  b  b
7 David  1  b  b
8 Chris  3  b  b
9 Peter  4  b  b

Here, column 't1' is supposed to be unique for each name - so 1 for John, 2 for David, 3 for Jack, and so on. So basically if one variable, say 1 in t1, corresponds to two different names, then it's wrong. So I want to find rows that have 2 different names for each number in column t1. In the dataframe, there are John and David for 1, and Jack and Chris for 3. So I want to retrieve rows for those people.

I want output like this:

  name t1 t2 t3
  John  1  a  a
 David  1  b  b
  Jack  3  b  b
 Chris  3  b  b

Can this be done within dplyr tidyverse or any basic codes? Does this require forloops? I am pretty new to R so looking for simple ways to achieve this.

Thanks in advance!

additional question:

After running your codes, my original data looks something like this:

           name t1 t2 t3
1      John Doe  1  a  a
2      Doe John  1  a  a
3    Jone Doe A  1  b  b
4 David Freeman  2  b  b
5 Freeman David  2  b  b
6     Jack Chen  3  b  b
7     John Chen  3  b  b
8 Chris Baker F  4  b  b
9 Baker O Chris  4  b  b

As you can see all first, middle, last names are mixed up. There are too much variations in name formats so the codes are reading John Doe, Doe John, and John Doe A all as different person. The original data I have is so big so I can't fix the names manually.

I want to clean those names (so that John Doe, Doe John, and John Doe A are treated as one individual, but Chris Baker F and Bake O Chris should be treated as two different unique individual) so that after rerunning my code I can have output like this:

          name t1 t2 t3
1     Jack Chen  3  b  b
2     John Chen  3  b  b
3 Chris Baker F  4  b  b
4 Baker O Chris  4  b  b

Would this be possible using R?

dplyr dataframe if-statement r loops
3 Answers

In base R you can use unique + duplicated.

subset(unique(df),duplicated(t1) | duplicated(t1, fromLast = TRUE))

#   name t1 t2 t3
#1  John  1  a  a
#5  Jack  3  b  b
#7 David  1  b  b
#8 Chris  3  b  b
user_39629140

Does this work:

library(dplyr)
df %>% group_by(t1) %>% filter(n_distinct(name) > 1) %>% distinct() %>% arrange(t1)
# A tibble: 4 x 4
# Groups:   t1 [2]
  name  t1    t2    t3   
  <fct> <fct> <fct> <fct>
1 John  1     a     a    
2 David 1     b     b    
3 Jack  3     b     b    
4 Chris 3     b     b
user_107227520

Another solution

library(tidyverse)
df %>% 
  distinct(name, t1, .keep_all = T) %>% 
  group_by(t1) %>% 
  filter(n() > 1) %>% 
  ungroup()

# A tibble: 4 x 4
  name  t1    t2    t3   
  <chr> <chr> <chr> <chr>
1 John  1     a     a    
2 Jack  3     b     b    
3 David 1     b     b    
4 Chris 3     b     b
user_120254830
Related