Merge two data.frames where one of the data frame contain an extra row

Question

I have two data frames where both data frame has one common column. One of the data frame has an extra row.

> df1
                 type        x            y
1                   A       10           417
2                   B        2           575
3                   C        3            14
4                   D      944           205
5                   E       44            87
6                   F      355           883
7                   G       73           150

> df2      
   X1 X2 X3 X4 X5 X6 term
1  9 28  3 34  5 39    B
2 43  7 39 41 46 32    C
3 17 30  0 27  0  9    D
4 42 32  1 10 26 49    E
5 21 17 34 28 35 12    F
6 16 10 18 25  2  0    G

I want to append column x,y from df1 to df2 while inserting row containing term A while introducing NA for the rest of the columns.

The goal is to obtain following:

   X1 X2 X3 X4 X5 X6 x   y  term
   NA NA NA NA NA NA 10 417  A
   9 28  3 34  5 39  2  575  B
  43  7 39 41 46 32  3   14  C
  17 30  0 27  0  9 944 205  D
  42 32  1 10 26 49  44  87  E
  21 17 34 28 35 12 355 883  F
  16 10 18 25  2  0  73 150  G

I tried the merge function using common column as term but didn't work then I found a solution

cbindPad <- function(...){
  args <- list(...)
  n <- sapply(args,nrow)
  mx <- max(n)
  pad <- function(x, mx){
    if (nrow(x) < mx){
      nms <- colnames(x)
      padTemp <- matrix(NA, mx - nrow(x), ncol(x))
      colnames(padTemp) <- nms
      if (ncol(x)==0) {
        return(padTemp)
      } else {
        return(rbind(x,padTemp))
      }
    }
    else{
      return(x)
    }
  }
  rs <- lapply(args,pad,mx)
  return(do.call(cbind,rs))
}

But this didn't work either.

Answer 1

Use merge. Note that common columns have different names: type for df1 and term for df2, so you should pass this info to merge function via by.x and by.y arguments. Also set all=TRUE.

> merge(df1, df2, by.x="type", by.y="term", all=TRUE)
  type   x   y X1 X2 X3 X4 X5 X6
1    A  10 417 NA NA NA NA NA NA
2    B   2 575  9 28  3 34  5 39
3    C   3  14 43  7 39 41 46 32
4    D 944 205 17 30  0 27  0  9
5    E  44  87 42 32  1 10 26 49
6    F 355 883 21 17 34 28 35 12
7    G  73 150 16 10 18 25  2  0