Merge data frames with partial id

Question

Say I have these two data frames:

> df1 <- data.frame(name = c('John Doe',
                             'Jane F. Doe',
                             'Mark Smith Simpson',
                             'Sam Lee'))
> df1
                name
1           John Doe
2        Jane F. Doe
3 Mark Smith Simpson
4            Sam Lee

> df2 <- data.frame(family = c('Doe', 'Smith'), size = c(2, 6))
> df2
  family size
1    Doe    2
2  Smith    6

I want to merge both data frames in order to get this:

                name family size
1           John Doe    Doe    2
2        Jane F. Doe    Doe    2
3 Mark Smith Simpson  Smith    6
4            Sam Lee   <NA>   NA

But I can't wrap my head around a way to do this apart from the following very convoluted solution, which is becoming very messy with my real data, which has over 100 "family names":

> df3 <- within(df1, {
    family <- ifelse(test = grepl('Doe', name),
                     yes  = 'Doe',
                     no   = ifelse(test = grepl('Smith', name),
                                   yes  = 'Smith',
                                   no   = NA))
  })
> merge(df3, df2, all.x = TRUE)
  family               name size
1    Doe           John Doe    2
2    Doe        Jane F. Doe    2
3  Smith Mark Smith Simpson    6
4   <NA>            Sam Lee   NA

I've tried taking a look into pmatch as well as the solutions provided at R partial match in data frame, but still haven't found what I'm looking for.

MrFlick · Answer 1 · 2014-06-25T18:19:01.507

Here is one strategy, you could use lapply with grep match over all the family names. This will find them at any position. First let me define a helper function

transindex<-function(start=1) {
    function(x) {
        start<<-start+1
        ifelse(x, start-1, NA)
    }
}

and I will also be using the function coalesce.R to make things a bit simpler. Here the code i'd run to match up df2 to df1

idx<-do.call(coalesce, lapply(lapply(as.character(df2$family), 
     function(x) grepl(paste0("\\b", x, "\\b"), as.character(df1$name))),
     transindex()))

Starting on the inside and working out, i loop over all the family names in df2 and grep for those values (adding "\b" to the pattern so i match entire words). grepl will return a logical vector (TRUE/FALSE). I then apply the above helper function transindex() to change those vector to be either the index of the row in df2 that matched, or NA. Since it's possible that a row may match more than one family, I simply choose the first using the coalesce helper function.

Not that I can match up the rows in df1 to df2, I can bring them together with

cbind(df1, size=df2[idx,])

                    name family size
# 1             John Doe    Doe    2
# 1.1        Jane F. Doe    Doe    2
# 2   Mark Smith Simpson  Smith    6
# NA             Sam Lee   <NA>   NA

My apologies for the incorrect answer. I should have clarified with the user asking the question rather than assuming they wanted the last part of the last name. The answer has been removed. — ccapizzano, Jun 25 '14 at 18:18
@ccapizzano No worries. You actually pointed out an error in my results that i've been able to correct. I just wanted to make sure you knew that your sample didn't match the test data in case you wanted to update it. I didn't intend to be harsh if it came off that way. — MrFlick, Jun 25 '14 at 18:20

score 1 · Answer 2 · answered Jun 25 '14 at 18:10

Rather than attempting to use regular expressions and partial matches, you could split the names up into a lookup-table format, where each component of a person's name is kept in a row, and matched to their full name:

df1 <- data.frame(name = c('John Doe',
                           'Jane F. Doe',
                           'Mark Smith Simpson',
                           'Sam Lee'),
                  stringsAsFactors = FALSE)
df2 <- data.frame(family = c('Doe', 'Smith'), size = c(2, 6),
                  stringsAsFactors = FALSE)


library(tidyr)
library(dplyr)

str_df <- function(x) {
  ss <- strsplit(unlist(x)," ")
  data.frame(family = unlist(ss),stringsAsFactors = FALSE)
  }

splitnames <- df1 %>%
  group_by(name) %>%
  do(str_df(.))

splitnames 

                 name  family
1         Jane F. Doe    Jane
2         Jane F. Doe      F.
3         Jane F. Doe     Doe
4            John Doe    John
5            John Doe     Doe
6  Mark Smith Simpson    Mark
7  Mark Smith Simpson   Smith
8  Mark Smith Simpson Simpson
9             Sam Lee     Sam
10            Sam Lee     Lee

Now you can just merge or join this with df2 to get your answer:

left_join(df2,splitnames)

Joining by: "family"
  family size               name
1    Doe    2        Jane F. Doe
2    Doe    2           John Doe
3  Smith    6 Mark Smith Simpson

Potential problem: if one person's first name is the same as somebody else's last name, you'll get some incorrect matches!

score 0 · Answer 3 · answered Jun 25 '14 at 20:41

Another apporoach that looks valid, at least with the sample data:

df1name = as.character(df1$name)
df1name
#[1] "John Doe"           "Jane F. Doe"        "Mark Smith Simpson" "Sam Lee"           
regmatches(df1name, regexpr(paste(df2$family, collapse = "|"), df1name), invert = T) <- ""
df1name
#[1] "Doe"   "Doe"   "Smith" ""     
cbind(df1, df2[match(df1name, df2$family), ])
#                  name family size
#1             John Doe    Doe    2
#1.1        Jane F. Doe    Doe    2
#2   Mark Smith Simpson  Smith    6
#NA             Sam Lee   <NA>   NA

Merge data frames with partial id

3 Answers3