How to make tool that's robust to user-generated typos?

Question

Background: A client is generating datasets (excel files). They asked I make an app to analyze the datasets, e.g. summary tables and figures. I'm doing this in R shiny.

Problem: There are many typos in the user-generated data. For example, Alice is sometimes entered as Alce, alice, alice., Aalice, or Jennifer (middle name). Some errors are easy to correct for, like trailing whitespace and upper/lowercase. Others are nearly impossible, like knowing Jennifer (middle name) is really Alice.

How can I make my tool robust to errors in the data when I don't have control over data entry?

Answer 1

TL;DR

1. Use fuzzy string matching to account for spelling mistakes.
2. Solving Jennifer for Alice will require you to know where to look in your DB for these cases, or talk to make a better Excel file to force people to only input first names (e.g. make a an entry to a cell restricted to a given list).

Fuzzy String Matching

In R, you can use adist or the stringdist package. These can be used to measure the distance from an entry (e.g. Aalice to a list of potential matches [Alice, Bianca, Chris].

Here is an articles explaining how to use both.

An extract from the article:

source1.devices<-read.csv('[path_to_your_source1.csv]')
source2.devices<-read.csv('[path_to_your_source2.csv]')
# To make sure we are dealing with charts
source1.devices$name<-as.character(source1.devices$name)
source2.devices$name<-as.character(source2.devices$name)

# It creates a matrix with the Standard Levenshtein distance between the name fields of both sources
dist.name<-adist(source1.devices$name,source2.devices$name, partial = TRUE, ignore.case = TRUE)

# We now take the pairs with the minimum distance
min.name<-apply(dist.name, 1, min)

match.s1.s2<-NULL  
for(i in 1:nrow(dist.name))
{
    s2.i<-match(min.name[i],dist.name[i,])
    s1.i<-i
    match.s1.s2<-rbind(data.frame(s2.i=s2.i,s1.i=s1.i,s2name=source2.devices[s2.i,]$name, s1name=source1.devices[s1.i,]$name, adist=min.name[i]),match.s1.s2)
}
# and we then can have a look at the results
View(match.s1.s2)

All this assumes that you have a list of names that are actually valid.

Answer 2

You could solve the typos problem with regex. With regard to knowing who does and does not go by their middle name you are going to have to tell Alice that she is in the database as Jennifer.

For the regex: this is a paraphrased excerpt from the book Regular Expressions Cookbook by Jan Goyvaerts and Steven Levithan (2012). I recommend this book.

To match similar words or names:

# color or colour
colou?r

# Bat, cat, or rat
[bcr]at

# words ending with "phobia"
\\w*phobia

# Steve, Steve, or Stephen
Ste(?:ven?|phen)

A browser-based regex tester: https://spannbaueradam.shinyapps.io/r_regex_tester/

https://github.com/AdamSpannbauer/r_regex_tester_app

You will need to get creative and find a way to write a regex that works for your specific problem, or you can provide a reproducible example and cross your fingers that someone here will work it out for you.