change the values in a column of a dataframe using another dataframe that contains key pairs

user_12549160 0
Viewed 97

I have the following sample df

df <- data.frame(old=c("412oldname1","32oldname132","oldname141","132oldname2","oldname3","oldname4","oldname5"))

        old
1   412oldname1
2   32oldname132
3   oldname141
4   132oldname2
5   oldname3
6   oldname4
7   oldname5

I would like to extract rows using grepl and replace.

keypairs <- data.frame(old=c("oldname1","oldname2"),new=c("newname1","newname2"))

       old      new
1 oldname1 newname1
2 oldname2 newname2

The desired dataframe would be:

        old
1   newname1
2   newname1
3   newname1
4   newname2

Ideally, I could replace the output of grepl with the patters, then use left_join and select the new column.

So far, I have:

df %>% filter(grepl(keypairs$old,collapse="|"))
# still trying to figure out what to do at this step
%>% left_join(keypairs) %>% select(new)
tidyverse r
3 Answers

There might be a more efficient way to do it, but I have used something like this before.

library(dplyr)
library(purrr)
library(stringr)

str_grab <- function(string, pattern) pattern[str_which(string, coll(pattern))[1]]

Use this to extract out the standardized names.

df %>% 
  mutate(clean = map_chr(old, str_grab, keypairs$old)) %>% 
  left_join(keypairs, by = c(clean = "old"))
#            old    clean      new
# 1  412oldname1 oldname1 newname1
# 2 32oldname132 oldname1 newname1
# 3   oldname141 oldname1 newname1
# 4  132oldname2 oldname2 newname2
# 5     oldname3     <NA>     <NA>
# 6     oldname4     <NA>     <NA>
# 7     oldname5     <NA>     <NA>

If you only want to keep ones that match, use inner_join() instead of left_join(). Then you can use basic dplyr commands to rename and select the column you want.

df %>% 
  mutate(clean = map_chr(old, str_grab, keypairs$old)) %>% 
  inner_join(keypairs, by = c(clean = "old")) %>% 
  select(old = new)
#        old
# 1 newname1
# 2 newname1
# 3 newname1
# 4 newname2

As suspected, there was a more efficient way. Here are some benchmarks from the proposed methods.

The method provided by Martin Gal is far more efficient in execution time and lines of code.

Unit: milliseconds
            expr     min       lq      mean   median       uq     max neval
   method_adam()  7.1371  7.68660  8.706662  8.03725  8.54810 16.5197   100
 method_martin()  1.1090  1.21125  1.464750  1.29145  1.42950  7.1925   100
  method_akrun() 25.4143 26.72945 29.400662 27.64135 32.21115 50.5500   100
user_109174790

A similiar approach like Adam's:

library(dplyr)
library(stringr)
library(tibble)
# or simply
# library(tidyverse) 

df %>% 
  mutate(new = deframe(keypairs)[str_extract(old, paste(keypairs$old, collapse = "|"))])

This returns

           old      new
1  412oldname1 newname1
2 32oldname132 newname1
3   oldname141 newname1
4  132oldname2 newname2
5     oldname3     <NA>
6     oldname4     <NA>
7     oldname5     <NA>

What happens here?

  • deframe(keypairs) creates a "lookup" vector. For example: if we put "oldname1" into it, "newname1" is returned.
  • str_extract extracts the input for the lookup. We search for the right "oldname" in column old.
user_125052510

We could use fuzzyjoin

library(fuzzyjoin)
regex_left_join(df, keypairs, by = 'old') %>% 
     transmute(old = old.x, new)
           old      new
1  412oldname1 newname1
2 32oldname132 newname1
3   oldname141 newname1
4  132oldname2 newname2
5     oldname3     <NA>
6     oldname4     <NA>
7     oldname5     <NA>
user_37322710
Related