Replace multiple strings/values based on separate list

Question

I have a data frame that looks similar to this:

EVENT  ID            GROUP   YEAR    X.1         X.2           X.3            Y.1           Y.2           Y.3
1      1 John Smith  GROUP1  2015  1 John Smith  5 Adam Smith  12 Mike Smith  20 Sam Smith  7 Luke Smith  3 George Smith

Each row repeats for new logs, but the values in X.1 : Y.3 change often. The ID's and the ID's present in X.1 : Y.3 have a numeric value and then the name ID, i.e., "1 John Smith" or "20 Sam Smith" will be the string.

I have an issue where in certain instances, the ID will remain as "1 John Smith" but in X.1 : Y.3 the number may change preceding "John Smith", so for example it might be "14 John Smith". The names will always be correct, it's just the number that sometimes gets mixed up.

I have a list of 200+ ID's that are impacted by this mismatch - what is the most efficient way to replace the values in X.1 : Y.3 so that they match the correct ID in column ID?

I won't know which column "14 John Smith" shows up in, it could be X.1, or Y.2, or Y.3 depending on the row.

I can use a replace function in a dplyr line of code, or gsub for each 200+ ID's and for each column effected, but it seems very inefficient. Is there a quicker way than repeated something like the below x times?

df%>%mutate(X.1=replace(X.1, grepl('John Smith', X.1), "1 John Smith"))%>%as.data.frame()

Answer 1

Sometimes it helps to temporarily reshape the data. That way we can operate on all the X and Y values without iterating over them.

library(stringr)
library(tidyr)

## some data to work with
exd <- read.csv(text = "EVENT,ID,GROUP,YEAR,X.1,X.2,X.3,Y.1,Y.2,Y.3
1,1 John Smith,GROUP1,2015,19 John Smith,11 Adam Smith,9 Sam Smith,5 George Smith,13 Mike Smith,12 Luke Smith
2,2 John Smith,GROUP1,2015,1 George Smith,9 Luke Smith,19 Adam Smith,7 Sam Smith,17 Mike Smith,11 John Smith
3,3 John Smith,GROUP1,2015,5 George Smith,18 John Smith,12 Sam Smith,6 Luke Smith,2 Mike Smith,4 Adam Smith",
stringsAsFactors = FALSE)

## re-arrange to put X and Y columns into a single column
exd <- gather(exd, key = "var", value = "value", X.1, X.2, X.3, Y.1, Y.2, Y.3)

## find the X and Y values that contain the ID name
matches <- str_detect(exd$value, str_replace_all(exd$ID, "^\\d+ *", ""))

## replace X and Y values with the matching ID
exd[matches, "value"] <- exd$ID[matches]

## put it back in the original shape
exd <- spread(exd, key = "var", value = value)

exd
##   EVENT           ID  GROUP YEAR            X.1           X.2           X.3            Y.1           Y.2           Y.3
## 1     1 1 John Smith GROUP1 2015   1 John Smith 11 Adam Smith   9 Sam Smith 5 George Smith 13 Mike Smith 12 Luke Smith
## 2     2 2 John Smith GROUP1 2015 1 George Smith  9 Luke Smith 19 Adam Smith    7 Sam Smith 17 Mike Smith  2 John Smith
## 3     3 3 John Smith GROUP1 2015 5 George Smith  3 John Smith  12 Sam Smith   6 Luke Smith  2 Mike Smith  4 Adam Smith

Answer 2

I think that the most efficient way to accomplish this is by building a loop. The reason is that you will have to repeat the function to replace the names for every name in your ID list. With a loop, you can automate this.

I will make some assumptions first:

1. The ID list can be read as a character vector
2. You don't have any typos in the ID list or in your data.frame, including different lowercase and uppercase letters in the names.
3. Your ID list does not contain the numbers. In case that it does contain numbers, you have to use gsub to erase them.
4. The example can work with a data.frame (DF) with the same structure that you put in your question.

>

ID <- c("John Smith", "Adam Smith", "George Smith")

for(i in 1:length(ID)) { 
    DF[, 5:10][grep(ID[i], DF[, 5:10])] <- ID[i]
}

With each round this loop will:

• Identify the positions in the columns X.1:Y.3 (columns 5 to 10 in your question) where the name "i" appears.

• Then, it will change all those values to the one in the "i" position of the ID vector.

• So, the first iteration will do: 1) Search for every position where the name "John Smith" appears in the data frame. 2) Replace all those "# John Smith" with "John Smith".

Note: If you simply want to delete the numbers, you can use gsub to replace them. Take into account that you probably want to erase the first space between the number and the name too. One way to do this is using gsub and a regular expression:

DF[, 5:10] <- gsub("[0-9]+ ", "", DF[, 5:10])

Answer 3

Not sure if you're set on dplyr and piping, but I think this is a plyr solution that does what you need. Given this example dataset:

> df
  EVENT           ID  GROUP YEAR            X.1           X.2           X.3            Y.1           Y.2           Y.3
1     1 1 John Smith GROUP1 2015  19 John Smith 11 Adam Smith   9 Sam Smith 5 George Smith 13 Mike Smith 12 Luke Smith
2     2 2 John Smith GROUP1 2015 1 George Smith  9 Luke Smith 19 Adam Smith    7 Sam Smith 17 Mike Smith 11 John Smith
3     3 3 John Smith GROUP1 2015 5 George Smith 18 John Smith  12 Sam Smith   6 Luke Smith  2 Mike Smith  4 Adam Smith

This adply function goes row by row and replaces any matching X:Y column values with the one from the ID column:

library(plyr)

adply(df, .margins = 1, function(x) {
  idcol <- as.character(x$ID)
  searchname <- trimws(gsub('[[:digit:]]+', "", idcol))
  sapply(x[5:10], function(y) {
    ifelse(grepl(searchname, y), idcol, as.character(y))
  })
})

Output:

  EVENT           ID  GROUP YEAR            X.1           X.2           X.3            Y.1           Y.2           Y.3
1     1 1 John Smith GROUP1 2015   1 John Smith 11 Adam Smith   9 Sam Smith 5 George Smith 13 Mike Smith 12 Luke Smith
2     2 2 John Smith GROUP1 2015 1 George Smith  9 Luke Smith 19 Adam Smith    7 Sam Smith 17 Mike Smith  2 John Smith
3     3 3 John Smith GROUP1 2015 5 George Smith  3 John Smith  12 Sam Smith   6 Luke Smith  2 Mike Smith  4 Adam Smith

Data:

names <- c("EVENT","ID",'GROUP','YEAR', paste(rep(c("X.", "Y."), each = 3), 1:3, sep = ""))
first <- c("John", "Sam", "Adam", "Mike", "Luke", "George")
set.seed(2017)
randvals <- t(sapply(1:3, function(x) paste(sample(1:20, size = 6), 
      paste(sample(first, replace = FALSE, size = 6), "Smith"))))
df <- cbind(data.frame(1:3, paste(1:3, "John Smith"), "GROUP1", 2015), randvals)
names(df) <- names