Merging 2 data sets with different number of rows, matched on a column, and creating NA values

Bostonbball21 0
Viewed 21

I'm trying to accomplish something that allows me to merge two datasets with differing number of rows, match them on a common column and create NA values where there isn't matching data. For some reason, when I'm merging, the newly created data frame is auto filling values that should be NA and creating extra rows that I don't want. I'm trying to merge df_add (which has a total of 6 rows) into df_main (which has a total of 4 rows) and match the 2 on column "match_id" in df_main and "other_id" in df_add.

df_main <- data.frame (match_id  = c("1", "1", "2", "2"),
                  index_date = c("2006-09-13", "2006-09-13", "2006-09-13", "2006-09-13"),
                  type =  c("Good", "Good", "Bad", "Bad")
                  )

df_add <- data.frame (other_id  = c("1", "1", "1", "2", "2", "2"),
                  measure_date = c("2005-01-01", "2005-03-13", "2005-04-19", "2005-06-22", "2005-09-29", "2005-11-03"),
                  wt =  c(10, 11, 15, 60, 42, 33)
                  )

This code is the closest I've gotten so far - it gives me the 6 rows that I want with the NA values but it doesn't match "match_id" and "other_id"

merge(df_main, df_add, by = 0, all = TRUE)[-1]

This is what I want my final merged data set to look like with only a total of 6 rows:

df_goal <- data.frame (match_id  = c("1", "1", "1", "2", "2", "2"),
                       index_date = c("2006-09-13", "2006-09-13", NA, "2006-09-13", "2006-09-13", NA),
                       type =  c("Good", "Good", NA, "Bad", "Bad", NA),
                       measure_date = c("2005-01-01", "2005-03-13", "2005-04-19", "2005-06-22", "2005-09-29", "2005-11-03"),
                       wt =  c(10, 11, 15, 60, 42, 33)
                  )

df_goal

Is there a way to accomplish this in r? Any help would be greatly appreciated!

na merge r
2 Answers

This is really not a merge operation, mostly a cbind by-id.

ids <- unique(c(df_main$match_id, df_add$other_id))
ids
# [1] "1" "2"
mains <- split(df_main, df_main$match_id)
adds <- split(df_add, df_add$other_id)
do.call(rbind,
  Map(function(x1, x2) {
    nr <- max(nrow(x1), nrow(x2))
    cbind(
      rbind(x1, x1[0,][rep(NA, nr - nrow(x1)),]),
      rbind(x2, x2[0,][rep(NA, nr - nrow(x2)),])
    )
  }, mains[ids], adds[ids])
)
#      match_id index_date type other_id measure_date wt
# 1.1         1 2006-09-13 Good        1   2005-01-01 10
# 1.2         1 2006-09-13 Good        1   2005-03-13 11
# 1.NA     <NA>       <NA> <NA>        1   2005-04-19 15
# 2.3         2 2006-09-13  Bad        2   2005-06-22 60
# 2.4         2 2006-09-13  Bad        2   2005-09-29 42
# 2.NA     <NA>       <NA> <NA>        2   2005-11-03 33

The use of [ids] is solely to ensure that the _id variables are in the same order. This will run into problems if an id is in one and not the other, though if that's a possibility then it's possible to overcome that ...

r2evans0

Below is a solution with the package data.table. I have added the variable id_row to define a grouping order with the *_id columns. Then you merge on this as well through an outer join.

library(data.table)

df_main <- data.frame (match_id  = c("1", "1", "2", "2"),
                       index_date = c("2006-09-13", "2006-09-13", "2006-09-13", "2006-09-13"),
                       type =  c("Good", "Good", "Bad", "Bad")
)

df_add <- data.frame (other_id  = c("1", "1", "1", "2", "2", "2"),
                      measure_date = c("2005-01-01", "2005-03-13", "2005-04-19", "2005-06-22", "2005-09-29", "2005-11-03"),
                      wt =  c(10, 11, 15, 60, 42, 33)
)

df_goal <- data.frame (match_id  = c("1", "1", "1", "2", "2", "2"),
                       index_date = c("2006-09-13", "2006-09-13", NA, "2006-09-13", "2006-09-13", NA),
                       type =  c("Good", "Good", NA, "Bad", "Bad", NA),
                       measure_date = c("2005-01-01", "2005-03-13", "2005-04-19", "2005-06-22", "2005-09-29", "2005-11-03"),
                       wt =  c(10, 11, 15, 60, 42, 33)
)

# convert to data.table
setDT(df_main)
setDT(df_add)

# define a row counter by either match_id and other_id
df_main[ , id_row := 1L:.N, by = match_id]
df_add[ , id_row := 1L:.N, by = other_id]

# rename other_id to match_id
setnames(df_add, "other_id", "match_id")

# set joining keys
setkey(df_main, match_id, id_row)
setkey(df_add, match_id, id_row)

# do an outer join
out = df_main[ df_add ]
out
#>    match_id index_date type id_row measure_date wt
#> 1:        1 2006-09-13 Good      1   2005-01-01 10
#> 2:        1 2006-09-13 Good      2   2005-03-13 11
#> 3:        1       <NA> <NA>      3   2005-04-19 15
#> 4:        2 2006-09-13  Bad      1   2005-06-22 60
#> 5:        2 2006-09-13  Bad      2   2005-09-29 42
#> 6:        2       <NA> <NA>      3   2005-11-03 33

Created on 2022-09-23 with reprex v2.0.2

Francesco Grossetti0
Related