While Checking duplicate with name within email data

user_16577173 0
Viewed 38

I am trying check duplicate name in email column if there is duplicate name with other domain.

also how to ignore NA and blank cell.

how I can handle separator because sometimes separators are (",",".","_","-") also if any name is duplicate then it should appear for all whoever is duplicate.

df <- data.frame(EMP.ID = c(88111,"BBB4477","BBB4058","BBB5832","BBB0338","BBB1814","BBB6543",875430,875970,"BBB0243","BBB1943","BBB9344","BBB9701","BBB1814","BBB8648","BBB4373","BBB7270","BBB6165","BBB7460","BBB7528","BBB6092"),
                 name = c("link adam","dy tt","link adam","gbesada","dy tt","slew lang","dy tt","gbesada","jachaval","allo nyyn","mbautis","grand fring","jali","kintom dang","namoti","shan mig","NA","NA","NA","NA",NA),
                 email = c("link.adam@gmail.com","dy_tt@abcd.com","link_adam@gmail.com","gb,esada@abcd.com","dy_tt@abcd.com","slew.lang@abcd.com","dy-tt@abcd.com","gb,esada@abcd.com","jachaval@abcd.com","allo@abcd.com","mbautis@abcd.com","grand.fring@abcd.com","jali@abcd.com","kintom.dang@abcd.com","namoti@abcd.com","shan.mig@abcd.com","mbautis@XYZ.com","slew.lang@abcd.com",NA,"NA",NA))


df %>%
  mutate(across(c(name,email), tolower)) %>% 
  mutate(email_name1 = str_extract(email, "([^@]+)@")) %>% 
  mutate(email_name1 = str_replace_all(email_name1, "[\\W_]", "")) %>%
  group_by(email_name1) %>% 
  mutate(count = n())

The Output should be

enter image description here

dplyr r
2 Answers

I think you could use the following solution:

library(dplyr)

df %>%
  mutate(trimmed = gsub("[._,-]", "", email)) %>%
  group_by(trimmed) %>%
  mutate(is_dup = n(), 
         is_dup = if_else(is_dup > 1 & !is.na(is_dup), "Duplicate", "")) %>%
  ungroup() %>%
  select(!trimmed)

# A tibble: 21 x 4
   EMP.ID  name      email               is_dup     
   <chr>   <chr>     <chr>               <chr>      
 1 88111   link adam link.adam@gmail.com "Duplicate"
 2 BBB4477 dy tt     dy_tt@abcd.com      "Duplicate"
 3 BBB4058 link adam link_adam@gmail.com "Duplicate"
 4 BBB5832 gbesada   gb,esada@abcd.com   "Duplicate"
 5 BBB0338 dy tt     dy_tt@abcd.com      "Duplicate"
 6 BBB1814 slew lang slew.lang@abcd.com  "Duplicate"
 7 BBB6543 dy tt     dy-tt@abcd.com      "Duplicate"
 8 875430  gbesada   gb,esada@abcd.com   "Duplicate"
 9 875970  jachaval  jachaval@abcd.com   ""         
10 BBB0243 allo nyyn allo@abcd.com       ""         
# ... with 11 more rows
user_143145200
  1. remove all non-alphanumeric "[[:punct:]]"
  2. check for duplicates also with fromLast argument of duplicated
  3. wrap it in a case_when statement
df %>% 
    mutate(Duplicate =
               case_when(
                   is.na(email) ~ email,
                   duplicated(str_replace(email, "[[:punct:]]", "")) |
                       duplicated(str_replace(email, "[[:punct:]]", ""),
                                  fromLast =TRUE) ~ "Duplicate",
                   TRUE ~ ""))

Output:

    EMP.ID        name                email Duplicate
1    88111   link adam  link.adam@gmail.com Duplicate
2  BBB4477       dy tt       dy_tt@abcd.com Duplicate
3  BBB4058   link adam  link_adam@gmail.com Duplicate
4  BBB5832     gbesada    gb,esada@abcd.com Duplicate
5  BBB0338       dy tt       dy_tt@abcd.com Duplicate
6  BBB1814   slew lang   slew.lang@abcd.com Duplicate
7  BBB6543       dy tt       dy-tt@abcd.com Duplicate
8   875430     gbesada    gb,esada@abcd.com Duplicate
9   875970    jachaval    jachaval@abcd.com          
10 BBB0243   allo nyyn        allo@abcd.com          
11 BBB1943     mbautis     mbautis@abcd.com          
12 BBB9344 grand fring grand.fring@abcd.com          
13 BBB9701        jali        jali@abcd.com          
14 BBB1814 kintom dang kintom.dang@abcd.com          
15 BBB8648      namoti      namoti@abcd.com          
16 BBB4373    shan mig    shan.mig@abcd.com          
17 BBB7270          NA      mbautis@XYZ.com          
18 BBB6165          NA   slew.lang@abcd.com Duplicate
19 BBB7460          NA                 <NA>      <NA>
20 BBB7528          NA                   NA          
21 BBB6092        <NA>                 <NA>      <NA>
user_133216470
Related