Home > database >  Using lapply with gsub to replace word in dataframe using another dataframe as 'dictionnary
Using lapply with gsub to replace word in dataframe using another dataframe as 'dictionnary

Time:02-26

I have a dataframe called data where I want to replace some word in specific columns A & B.
I have a second dataframe called dict that is playing the role of dictionnary/hash containing the words and the values to use for replacement.

I think it could be done with purrr’s map() but I want to use apply. It's for a package and I don't want to have to load another package.
The following code is not working but it's give you the idea. I'm stuck.

columns <- c("A", "B" ) 
data[columns] <- lapply(data[columns], function(x){x}) %>% lapply(dict, function(y){
         gsub(pattern = y[,2], replacement = y[,1], x)})

This is working for one word to change...but I'm not able to pass the list of changes conainted in the dictionnary.

data[columns] <- lapply(data[columns], gsub, pattern = "FLT1", replacement = "flt1")

CodePudding user response:

@Gregor_Thomas is right, you need a for loop to have a recursive effect, otherwise you just replace one value at the time.

df <- data.frame("A"=c("PB1","PB2","OK0","OK0"),"B"=c("OK3","OK4","PB1","PB2"))
dict <- data.frame("pattern"=c("PB1","PB2"), "replacement"=c("OK1","OK2"))

apply(df[,c("A","B")],2, FUN=function(x) {
  for (i in 1:nrow(dict)) {
    x <- gsub(pattern = dict$pattern[i], replacement = dict$replacement[i],x)
  }
  return(x)
})

Or, if your dict data is too long you can generate a succession of all the gsub you need using a paste as a code generator :

paste0("df[,'A'] <- gsub(pattern = '", dict$pattern,"', replacement = '", dict$replacement,"',df[,'A'])")

It generates all the gsub lines for the "A" column :

"df[,'A'] <- gsub(pattern = 'PB1', replacement = 'OK1',df[,'A'])"
"df[,'A'] <- gsub(pattern = 'PB2', replacement = 'OK2',df[,'A'])"

Then you evaluate the code and wrap it in a lapply for the various columns :

lapply(c("A","B"), FUN = function(v) { eval(parse(text=paste0("df[,'", v,"'] <- gsub(pattern = '", dict$pattern,"', replacement = '", dict$replacement,"',df[,'",v,"'])"))) })

It's ugly but it works fine to avoid long loops.

Edit : for a exact matching between df and dict maybe you should use a boolean selection with == instead of gsub(). (I don't use match() here because it selects only the first matching

df <- data.frame("A"=c("PB1","PB2","OK0","OK0","OK"),"B"=c("OK3","OK4","PB1","PB2","AB"))
dict <- data.frame("pattern"=c("PB1","PB2","OK"), "replacement"=c("OK1","OK2","ZE"))

apply(df[,c("A","B")],2, FUN=function(x) {
for (i in 1:nrow(dict)) {
     x[x==dict$pattern[i]] <- dict$replacement[i]
    }
   return(x)
 })
  • Related