R- Column match, create new column with another column of corresponding value

Question

I have two data frame:

df1<- data.frame(place=c("KARACA ADANA","ASIL BOLU","GAZIANTEP","YUKARI/MERSIN"))

df2<- data.frame(city=c("ADANA","BOLU","ANTEP","MERSIN"), neighbor=c("KARACA","ASIL","GAZI","YUKARI"))

I need to match columns df1$place and df2$neighbor. If df1$place contains the word in df2$neighbor, it should create a new column to df1$newcol by copying the corresponding value of df2$city of matches.

df1$newcol <- data.frame(place=c("KARACA ADANA","ASIL BOLU","GAZIANTEP","YUKARI/MERSIN") ,city=c("ADANA","BOLU","ANTEP","MERSIN"))

Ian Campbell · Accepted Answer · 2020-06-04T14:52:00.247

1

Here's an approach with sapply from base R:

If you want only whole words to match, you could use a regular expression. \\b looks for a word boundary.

ind <- unlist(sapply(df2$neighbor, function(x) grep(paste0("\\b",x,"\\b"),df1$place)))
ind2 <- rep(1:length(df2$neighbor),
            times = sapply(df2$neighbor, function(x) length(grep(paste0("\\b",x,"\\b"),df1$place))))
df1$newcol <- NA
df1$newcol[ind] <- as.character(df2$city[ind2])
df1
#          place newcol
#1  KARACA ADANA  ADANA
#2     ASIL BOLU   BOLU
#3     GAZIANTEP   <NA>
#4 YUKARI/MERSIN MERSIN
#5 YUKARI/MERSIN MERSIN
#6     GAZIANTEP   <NA>
#7     ASIL BOLU   BOLU
#8  KARACA ADANA  ADANA

Sample Data

df1<- data.frame(place=c(c("KARACA ADANA","ASIL BOLU","GAZIANTEP","YUKARI/MERSIN"),
                         rev(c("KARACA ADANA","ASIL BOLU","GAZIANTEP","YUKARI/MERSIN"))))

edited Jun 04 '20 at 14:52

answered Jun 04 '20 at 13:16

Ian Campbell

21,281
13
26
51

This really worked !! Thank you so much – genco Jun 04 '20 at 13:47
I just realize that this code works when there is an order between matching columns, What if there isn't an order. What should I do in that case – genco Jun 04 '20 at 14:16
I am so sorry, but I have one more problem. df1 and df2 don't have the same lenght. For that reason the edited code doesn't work also – genco Jun 04 '20 at 14:34
yes, it warns In df1$newcol[ind] – genco Jun 04 '20 at 14:41
This time works :) You saved my life, thank you sooo much – genco Jun 04 '20 at 14:59
Can I ask you one more thing related to this question? – genco Jun 08 '20 at 13:03
If my data as below; how can a make two word search in a sentence and if these two word is included in the sentence, than i will take the df2$city – genco Jun 08 '20 at 13:14
df1 – genco Jun 08 '20 at 13:15
This is quite complicated. I think you should ask a new question. – Ian Campbell Jun 08 '20 at 13:19

score 0 · Answer 2 · answered Jun 04 '20 at 13:28

0

try to do it this way

library(tidyverse)
df1 %>% 
  rowwise() %>% 
  mutate(out = df2$city[str_which(place, df2$city)])

answered Jun 04 '20 at 13:28

Yuriy Saraykin

7,360
1
6
12

Please add some explanation to your code – zohar Jun 04 '20 at 14:46
Code only answers are allowed, but it's encouraged to explain the answer as well. Consider adding some explanation. – zonksoft Jun 04 '20 at 16:21

R- Column match, create new column with another column of corresponding value

2 Answers2