Matching Two R Data Frames on Closeness

user_4716625 0
Viewed 64

I have the following two data frames:

df1 <- data.frame(group = rep("A", 5),
                  name = c("Brandon",
                           "Kyler",
                           "Trent",
                           "Lesa",
                           "Michael"),
                  gender = c("M", "F", "M", "F", "M"),
                  days = c(50, 45, 32, 60, 48))

df2 <- data.frame(group = rep("B", 10),
                  name = c("Erica", 
                           "Jared",
                           "Sara",
                           "Helen",
                           "Tom",
                           "Ron",
                           "Cy",
                           "Lynn",
                           "Ken",
                           "Judy"),
                  gender = c("F", "M", "F", "F", "M", "M", "M", "F", "M", "F"),
                  days = c(47, 49, 62, 80, 74, 30, 55, 58, 63, 25))

I want to filter df2 to include only the closest match to each row in the df1 data frame based on gender and days, with gender taking priority.

For example, in df1, "Brandon" has gender == M and days == 50. When we look at only gender == M in df2, we see that "Jared" is the closest to "Brandon" in days, so "Jared" would be selected for the "Brandon" match. In total, the resulting data frame would look like this:

# group  name gender days
#     B Jared      M   49
#     B Erica      F   47
#     B   Ron      M   30
#     B  Lynn      F   58
#     B    Cy      M   55

Additional rules:

  • This is a hierarchical merge, where gender match takes priority over days closeness.

  • Note that there are two equal distanced options to match to "Lesa" in df1 ("Sara" and "Lynn"). Randomly select one of the two to match to "Lesa". In the final data frame above, the example chose "Lynn".

  • "Jared" in df2 is equal distance from both "Brandon" and "Michael" in df1. Because "Jared" is already matched to "Brandon", he cannot also be matched to "Michael". Therfore, the match to "Michael" moves on to "Cy", which is the next best remaining match in terms of gender and days.

distance r
1 Answers

Data

First of all, I added stringsAsFactors = FALSE to your input dataframes because it is easier to work with strings than factors with my solution.

df1 <- data.frame(group = rep("A", 5),
              name = c("Brandon",
                       "Kyler",
                       "Trent",
                       "Lesa",
                       "Michael"),
              gender = c("M", "F", "M", "F", "M"),
              days = c(50, 45, 32, 60, 48),
              stringsAsFactors = FALSE)

df2 <- data.frame(group = rep("B", 10),
                  name = c("Erica", 
                           "Jared",
                           "Sara",
                           "Helen",
                           "Tom",
                           "Ron",
                           "Cy",
                           "Lynn",
                           "Ken",
                           "Judy"),
                  gender = c("F", "M", "F", "F", "M", "M", "M", "F", "M", "F"),
                  days = c(47, 49, 62, 80, 74, 30, 55, 58, 63, 25),
                  stringsAsFactors = FALSE)

Solution

library(tidyverse)

# empty dataframe for the output
df2_new <- data.frame(group = character(),
                      name = character(),
                      gender = character(),
                      days = numeric(),
                      stringsAsFactors = FALSE)

for(i in 1:nrow(df1)){

  # add the row of interest to the output dataframe
  df2_new[i,] <- df2 %>% 
    mutate(day_diff = abs(days - df1$days[i])) %>%
    filter(gender == df1$gender[i]) %>% 
    slice(which.min(day_diff)) %>%
    select(-day_diff)

  # remove the newly added row from the original dataset
  df2 <- df2 %>%
    filter(!(name %in% df2_new$name))

}

This is the first solution that came to mind. In this case, rows are deleted from df2 as the for cycle goes on, as you said that you

want to filter df2 to include only the closest match to each row in the df1

Output

df2_new

  group  name gender days
1     B Jared      M   49
2     B Erica      F   47
3     B   Ron      M   30
4     B  Sara      F   62
5     B    Cy      M   55

In my case the code picked Sara instead of Lynn, but it's a 50/50 choice.

user_74654620
Related