I have a very large database (48 thousand rows) and I want to review rows that are similar or the same but I can't find a way to do it. I have this example base: tribble( ~id, ~name, ~lastname, ~pasport, ~State, 1, "Peter", "Gomez", "1234", "Texas", 2, "Maria", "Perez", "4567", "Texas", 3,…

In your specific example, the passport is a unique identifier so you can simply count and filter when more than one instance is present library(tidyverse) sample_df <- tribble( ~id, ~name, ~lastname, ~pasport, ~State, 1, "Peter", "Gomez", "1234", "Texas", 2, "Maria", "Perez", "4567", "…

thanks for the answer but it does not work for me because I am looking for a systematic way to apply it on a larger base, and there will not be a unique identifier for each person as in this case. Maybe I should have taken the variable pasport out of the example or put a different value for the same…

Seems like you have to: identify correct spellings (by frequency) identify the misspellings - but note that some rare spellings are just rare names match those misspellings to the correct spellings group by corrected spelling

Then pasting all columns (except id) and counting those that have more than one occurence could work? Is it what you're looking for @juandmaz ? sample_df %>% unite("pasted", -id, remove = FALSE) %>% add_count(pasted) %>% filter(n > 1) %>% select(-pasted) #> # A tibble: 2 x 6 #> id n…

[image] juandmaz: I am looking for a systematic way to apply it on a larger base, and there will not be a unique identifier for each person In that case, I think you can look into "string distances" or Natural Language Processing (NLP) in general, here is an example of the use of string dista…

Hi Andres, thanks for the reply! It is a great solution that you gave me, and it is very close to what I need, but it still does not work since that way only applies to a single variable (the one that is chosen) and I need to apply it to all the variables of my base . In my example, it could only be…

[image] juandmaz: I need to apply it to name and lastname. Why not to simply apply it to a new variable that pastes name and last name together?

Do you propose that for this example create a new variable that copies name, lastname and state? and then apply to it

I think it's worth giving it a try, after all you want to account for similarity in those three fields together and not individually so I believe it makes sense to merge them.

Hi Juan, try jaccard similarity, you may have to adapt tthe following code. Hope it works! a <- c('potato', 'tomotto', 'chips', 'baloon') b <- c('car', 'chips', 'bird', 'salt') jaccard <- function(a, b) { intersection = length(intersect(a, b)) union = length(a) + length(b) - intersection …

Find similars rows in R

General

andresrcs January 5, 2022, 12:15pm 6

In that case, I think you can look into "string distances" or Natural Language Processing (NLP) in general, here is an example of the use of string distances to identify near duplicates