Multiple pattern/string matching in R

Question

I have two data frames one is a map with over 20000 possibilities, another one contains 3 columns of 30000 rows of data. I need to use the map to figure out the correct name. Here is a simple example of what I need:

For instance,

data <- data.frame(
  V1 = c('baa','bb','aa','cc','dd','ee','caa'),
  V2 = c('ff','gg','hh','yy','jj','kk','hh')
)
# V1 V2
# baa ff
# bb gg
# aa hh
# cc yy
# dd jj
# ee kk
# caa hh

map <- data.frame(
  V1 = c('aa','gg','cc','jj','kk'), 
  V2  = c(1:5)
) 
# V1 V2 
# aa 1
# gg 2
# cc 3
# jj 4
# kk 5

>what.I.need
V1 V2 V3
baa ff 1
bb gg 2
aa hh 1
cc yy 3
dd jj 4
ee kk 5
caa hh 1

I tried using grep, but I can't seem to figure out how to make it work with a map of 20000 possibilities and have it populate the 3rd column in "what.I.need". Thank you in advance.

It is optimal for those helping if you present your example data in a format easy to input into R. As in df1 <- data.frame(data), shown in the link above. — Anonymous coward
– Anonymous coward, Commented Jul 3, 2018 at 21:41
I can't share the data, even a sample. The columns in "data" have some matching entries. I'll try to put something together or find something similar. — Gulnaz Hoxmeier-Omasheva
– Gulnaz Hoxmeier-Omasheva, Commented Jul 3, 2018 at 21:42

Lennyy · Accepted Answer · 2018-07-03 21:55:31Z

1

df1 <- read.table(text = "
V1 V2
aa ff
bb gg
aa hh
cc yy
dd jj
ee kk
aa hh", h = T, stringsAsFactors = F)

df2 <- read.table(text = "
V1 V3 
aa 1
gg 2
cc 3
jj 4
kk 5", h = T, stringsAsFactors = F)


library(tidyr)
library(dplyr)

df1 %>% 
  gather(V2, V1, V1, V2) %>% 
  full_join(df2) %>% 
  filter(!is.na(V3)) %>% 
  full_join(df1) -> df1

df1$V3 <- c(df1$V3[!is.na(df1$V3)])

df1 %>% 
  filter(!V2 %in% c("V1","V2")) %>% 
  select(V1,V2,V3)

  V1 V2 V3
1 aa ff  1
2 bb gg  1
3 aa hh  3
4 cc yy  1
5 dd jj  2
6 ee kk  4
7 aa hh  5

I have the feeling it could get more concise than this. :)

answered Jul 3, 2018 at 21:55

Lennyy

6,1522 gold badges13 silver badges25 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

MrFlick Over a year ago

Note the OP changed the sample data so they are not always exact matches any more.

Anonymous coward · Accepted Answer · 2018-07-03 22:05:59Z

0

library(dplyr)
library(tidyr)

df1 <- data.frame(V1 = c("aa", "bb", "aa", "cc", "dd", "ee", "aa"), V2 = c("ff", "gg", "hh", "yy", "jj", "kk", "hh"), stringsAsFactors = FALSE)
df2 <- data.frame(V1 = c("aa", "gg", "cc", "jj", "kk"), V2 = c(1,2,3,4,5), stringsAsFactors = FALSE)

left_join(df1, df2, by = c("V2" = "V1")) %>% 
left_join(., df2, by = "V1") %>% 
  mutate(V3 = ifelse(is.na(V2.y), V2.y.y, V2.y)) %>% 
  select(-V2.y, -V2.y.y)

This creates this table, then drops V2.y and V2.y.y.

  V1 V2.x V2.y V2.y.y V3
1 aa   ff   NA      1  1
2 bb   gg    2     NA  2
3 aa   hh   NA      1  1
4 cc   yy   NA      3  3
5 dd   jj    4     NA  4
6 ee   kk    5     NA  5
7 aa   hh   NA      1  1

Which gives you this:

  V1 V2.x V3
1 aa   ff  1
2 bb   gg  2
3 aa   hh  1
4 cc   yy  3
5 dd   jj  4
6 ee   kk  5
7 aa   hh  1

answered Jul 3, 2018 at 22:05

Anonymous coward

2,1011 gold badge16 silver badges30 bronze badges

3 Comments

MrFlick Over a year ago

Note the OP changed the sample data so they are not always exact matches any more.

Gulnaz Hoxmeier-Omasheva Over a year ago

Thank you for your help! I couldn't figure out this route. I tried doing for loops, but it didn't work either. Eventually I found another way to deal with the data that didn't involve pattern matching.

Anonymous coward Over a year ago

@GulnazHoxmeier-Omasheva can you edit your question to include your solution, or post it as an answer? To work with your edits, I'd have to change my answer to maybe a fuzzy join/pattern matching.

Raja · Accepted Answer · 2018-07-04 11:54:06Z

0

You may try this:

data <- data.frame(
  V1 = c('aa','bb','aa','cc','dd','ee','aa'),
  V2 = c('ff','gg','hh','yy','jj','kk','hh'), stringsAsFactors = F
)

map <- data.frame(
  V1 = c('aa','gg','cc','jj','kk'), 
  V2  = c(1:5), stringsAsFactors = F
)

data$V3.1 <- map$V2[match(data$V1, map$V1)]
data$V3.2 <- map$V2[match(data$V2,map$V1)]
data$V3 <- ifelse(!is.na(data$V3.1), data$V3.1, data$V3.2)
data
# V1 V2 V3.1 V3.2 V3
# 1 aa ff    1   NA  1
# 2 bb gg   NA    2  2
# 3 aa hh    1   NA  1
# 4 cc yy    3   NA  3
# 5 dd jj   NA    4  4
# 6 ee kk   NA    5  5
# 7 aa hh    1   NA  1

answered Jul 4, 2018 at 11:54

Raja

1671 silver badge12 bronze badges

Collectives™ on Stack Overflow

Multiple pattern/string matching in R

3 Answers 3

1 Comment

3 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

1 Comment

3 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related