2

I want to combine two dataframes, df1 and df2, by different groups of a key variable in x1. It is basically some join operation, however, I do not want the rows to duplicate and do not care about the relationship among the added columns.

Assume:

df1: 

x1 x2 
A  1
A  2
A  3
B  4
B  5
C  6
C  7

df2: 

x1 x3 
A  a
A  b
A  c
A  d
A  e
A  f
B  g
C  h

The result should look like this.

df1 + df2: 

x1 x2 x3 
A  1  a
A  2  b
A  3  c
A  NA d
A  NA f
B  4  g
B  5  NA
C  6  h
C  7  NA

Does anyone have an idea? I would most appreciate your help!

Blundering Ecologist
  • 1,199
  • 2
  • 14
  • 38
Fabian H
  • 21
  • 1

2 Answers2

2

The full_join in dplyr works well for this too. See below:

  #recreate your data
  library (data.table)
  library (dplyr)

  df1 <- data.table (x1 = c("A","A","A","B","B","C","C"), x2 = seq (from = 1, to = 7))

  df2 <- data.table (x1 = c("A","A","A","A","A","A","B","C"), x3 = c("a","b","c","d","e","f","g","h" ))

  df1[, rowid := rowid(x1)]

  df2[, rowid := rowid(x1)]

  df3 <- full_join (df1, df2, by = c ("x1","rowid"))

  df3$rowid <- NULL

  setorder (df3, x1)
dinman
  • 55
  • 9
1

To replicate your resulting data.frame you can create row ids by x1 and then merge on those row ids and x1 (but I don't really know if that is what you are trying to accomplish)

library(data.table)

df1 = read.table(text = "x1 x2 
A  1
A  2
A  3
B  4
B  5
C  6
C  7", header = T)

df2 = read.table(text = "x1 x3 
A  a
A  b
A  c
A  d
A  e
A  f
B  g
C  h", header = T)

setDT(df1) 
setDT(df2)
df1[, rowid := seq(.N), by = x1] # create rowid
df2[, rowid := seq(.N), by = x1] # create rowid

merge(df1, df2, by = c("x1", "rowid"), all = T)[, rowid := NULL][]

    x1 x2 x3
 1:  A  1  a
 2:  A  2  b
 3:  A  3  c
 4:  A NA  d
 5:  A NA  e
 6:  A NA  f
 7:  B  4  g
 8:  B  5 NA
 9:  C  6  h
10:  C  7 NA
Felipe Alvarenga
  • 2,572
  • 1
  • 17
  • 36