Matching part of a string in a data frame to a string in another data frame

Question

I have a two separate data frames that looks like this:

#data frame 1
set.seed(5)
first<-c("Jane, Sarah","Bill, Conrad", "Jim, Dave", "Mark, Ben", "Mike, Frank")
month<-c("Feb","Jan","Dec","Jun","Aug")
df1<-data.frame(first,month)

#data frame 2
first<-c("John", "Brendan", "Mark", "Dave", "Sarah", "Julie", "Frank", "Henry")
vals<-seq(8)*floor(runif(8,min=10, max=100))
df2<-data.frame(first,vals)

What I want to do append to the first data frame the values from the second data frame when there is a match to either name (there won't be a match to both, just one). If there is no match, the value can can be assigned a '0'.

The idea is to end up with a final data frame that looks like this:

#data frame final
first<-c("Jane, Sarah","Bill", "Jim, Dave", "Mark", "Mike, Frank")
month<-c("Feb","Jan","Dec","Jun","Aug")
vals<-c(95,0,140,276,399)
df3<-data.frame(first,month,vals)

I have tried using grep to match but can't seem to get the values to match. Any ideas on how to append these values for a partial match?

ok, fixed that. Changed Mike to "Brendan" to make it match just one. Also fixed the df3 to first,month, vals. — user2900006
– user2900006, Commented Jun 15, 2017 at 12:21

Mike H. · Accepted Answer · 2017-06-15 12:52:25Z

1

Would this work for you? We extract all the words from the first column and then lapply over the results to get the matches.

library(stringr)

df_res <- df1
df_res$vals <- lapply(str_extract_all(df1$first, "\\w+"), function(x) {res <- df2$vals[match(x, df2$first)]
                                                                       res[is.na(res)] <- 0
                                                                       max(res)
                                                             })

df_res
#         first month vals
#1  Jane, Sarah   Feb   95
#2 Bill, Conrad   Jan    0
#3    Jim, Dave   Dec  140
#4    Mark, Ben   Jun  276
#5  Mike, Frank   Aug  399

answered Jun 15, 2017 at 12:52

Mike H.

14.4k2 gold badges33 silver badges39 bronze badges

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

1 Comment

user2900006 Over a year ago

This works well for the model example I set up. Thanks! I'll try this on the working dataset I have here and see how it goes. If there are questions, I'll let you know...

Collectives™ on Stack Overflow

Matching part of a string in a data frame to a string in another data frame

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related