How to match incorrect strings and replace by correct strings

user_8039978 0
Viewed 82

I have two dataframes.

One contains pairs of correct and incorrect place names:

place  <- data.frame(
  place_correct = c("London", "Birmingham", "Newcastle", "Brighton"),
  place_incorrect = c("Lundn", "Birmgham", "Nexcassle", "Briton"), stringsAsFactors = F)

The other contains a column with a mix of these correct and incorrect place names:

set.seed(123)
df <- data.frame(town = sample(c("London", "Birmingham", "Newcastle", "Brighton", 
                                 "Lundn", "Birmgham", "Nexcassle", "Briton"), 20, replace = T), stringsAsFactors = F)

What I'd like to do is match the incorrect place names in df to the incorrect place names in place and replace them by the correct place name.

EDIT:

I can do it in base R using ifelse and %in%:

df$town_correct <- ifelse(df$town %in% place$place_incorrect, 
                          place$place_correct[match(df$town, place$place_incorrect)], 
                          df$town)
df
         town town_correct
1   Newcastle    Newcastle
2   Nexcassle    Newcastle
3    Brighton     Brighton
4      Briton     Brighton
5      Briton     Brighton
6      London       London
7       Lundn       London
8      Briton     Brighton
9       Lundn       London
10   Brighton     Brighton
11     Briton     Brighton
12   Brighton     Brighton
13   Birmgham   Birmingham
14      Lundn       London
15     London       London
16     Briton     Brighton
17 Birmingham   Birmingham
18     London       London
19  Newcastle    Newcastle
20     Briton     Brighton

But how can it be done in dplyr?

match if-statement r
3 Answers

I would use this multisub function:

place  <- data.frame(
    place_correct = c("London", "Birmingham", "Newcastle", "Brighton"),
    place_incorrect = c("Lundn", "Birmgham", "Nexcassle", "Briton"), stringsAsFactors = F)

set.seed(123)
df <- data.frame(town = sample(c("London", "Birmingham", "Newcastle", "Brighton", 
                                 "Lundn", "Birmgham", "Nexcassle", "Briton"), 20, replace = T), stringsAsFactors = F)


multisub <- function(target, output, string) {
    replacement.list <- apply(cbind(target, output), 1, as.list)
    mygsub <- function(l, x) gsub(pattern = l[1], replacement = l[2], x, perl=TRUE)
    Reduce(mygsub, replacement.list, init = string, right = TRUE)
}


df$town_correct <- with(place, multisub(place_incorrect, place_correct, df$town))
df
#>          town town_correct
#> 1   Nexcassle    Newcastle
#> 2   Nexcassle    Newcastle
#> 3   Newcastle    Newcastle
#> 4    Birmgham   Birmingham
#> 5   Newcastle    Newcastle
#> 6  Birmingham   Birmingham
#> 7  Birmingham   Birmingham
#> 8    Birmgham   Birmingham
#> 9   Newcastle    Newcastle
#> 10      Lundn       London
#> 11   Brighton     Brighton
#> 12   Birmgham   Birmingham
#> 13   Birmgham   Birmingham
#> 14     London       London
#> 15 Birmingham   Birmingham
#> 16  Newcastle    Newcastle
#> 17     Briton     Brighton
#> 18      Lundn       London
#> 19  Newcastle    Newcastle
#> 20  Newcastle    Newcastle

Created on 2020-05-17 by the reprex package (v0.3.0)

Edit:

This is probably not the most efficient solution, but here is a solution with ifelse, after checking for match:

df$town_correct <- vapply(df$town, function(x) ifelse(x %in% place$place_incorrect, 
place[match(x, place$place_incorrect, nomatch=0), "place_correct"], x), 
FUN.VALUE = NA_character_, USE.NAMES = FALSE)
df
#>          town town_correct
#> 1   Nexcassle    Newcastle
#> 2   Nexcassle    Newcastle
#> 3   Newcastle    Newcastle
#> 4    Birmgham   Birmingham
#> 5   Newcastle    Newcastle
#> 6  Birmingham   Birmingham
#> 7  Birmingham   Birmingham
#> 8    Birmgham   Birmingham
#> 9   Newcastle    Newcastle
#> 10      Lundn       London
#> 11   Brighton     Brighton
#> 12   Birmgham   Birmingham
#> 13   Birmgham   Birmingham
#> 14     London       London
#> 15 Birmingham   Birmingham
#> 16  Newcastle    Newcastle
#> 17     Briton     Brighton
#> 18      Lundn       London
#> 19  Newcastle    Newcastle
#> 20  Newcastle    Newcastle
user_127287480

The same ifelse() statement you have working in base R will also work with dplyr:

library(dplyr)

df %>%
  mutate(correct_town = if_else(town %in% place$place_incorrect, 
                            place$place_correct[match(town, place$place_incorrect)], 
                            town))

         town correct_town
1   Nexcassle    Newcastle
2   Nexcassle    Newcastle
3   Newcastle    Newcastle
4    Birmgham   Birmingham
5   Newcastle    Newcastle
6  Birmingham   Birmingham
7  Birmingham   Birmingham
8    Birmgham   Birmingham
9   Newcastle    Newcastle
10      Lundn       London
11   Brighton     Brighton
12   Birmgham   Birmingham
13   Birmgham   Birmingham
14     London       London
15 Birmingham   Birmingham
16  Newcastle    Newcastle
17     Briton     Brighton
18      Lundn       London
19  Newcastle    Newcastle
20  Newcastle    Newcastle

Or a stringr::str_replace_all() alternative is:

df %>%
  mutate(correct_town = stringr::str_replace_all(town, setNames(place$place_correct, place$place_incorrect)))
user_28352610

In situations like these it makes sense to use left_join from dplyr package. You can use below code:

    df<-left_join(df, place, by = c("town" = "place_incorrect"))
    df$Town_correct<-ifelse(is.na(df$place_correct), df$town, df$place_correct)
    df$place_correct<-NULL
user_80073730
Related