Merging files on the basis of columns

Question

I have multiple files with many rows and three columns and need to merge them on the basis of first two columns match. File1

12   13   a
13   15   b
14   17   c
 4    9   d
 .    .   .
 .    .   .
 81   23  h

File 2

12   13   e
3    10   b
14   17   c
 4    9   j
 .    .   .
 .    .   .
 1   2    k

File 3

12   13   m
13   15   k
1     7   x
24    9   d
 .    .   .
 .    .   .
 1   2    h

and so on. I want to merge them to obtain the following result

12   13   a  e  m
13   15   b  k
14   17   c  c
 4    9   d  j
 3   10   b
24    9   d
 .    .   .
 .    .   .
 81   23  h
  1    2  k
  1    7  x

See the following post: http://stackoverflow.com/questions/1299871/how-to-join-data-frames-in-r-inner-outer-left-right — Ruthger Righart, Oct 04 '14 at 20:22
@BenBolker, I'm not sure if `merge` is the best option for this problem. — A5C1D2H2I1M1N2O1R2T1, Oct 05 '14 at 04:27

score 1 · Answer 1 · answered Oct 05 '14 at 04:26

The first thing that usually comes to mind with these types of problems is merge, perhaps in conjunction with a Reduce(function(x, y) merge(x, y, by = "somecols", all = TRUE), yourListOfDataFrames).

However, merge is not always the most efficient function, especially since it looks like you want to "collapse" all the values to fill in the rows from left to right, which would not be the default merge behavior.

Instead, I suggest you stack everything into one long data.frame and reshape it after you have added an index variable.

Here are two approaches:

Option 1: "dplyr" + "tidyr"

Use mget to put all of your data.frames into a list.
Use rbind_all to convert that list into a single data.frame.
Use sequence(n()) in mutate from "dplyr" to group the data and create an index.
Use spread from "tidyr" to transform from a "long" format to a "wide" format.

library(dplyr)
library(tidyr)

combined <- rbind_all(mget(ls(pattern = "^file\\d")))
combined %>%
  group_by(V1, V2) %>% 
  mutate(time = sequence(n())) %>% 
  ungroup() %>% 
  spread(time, V3, fill = "")
# Source: local data frame [7 x 5]
# 
#   V1 V2 1 2 3
# 1  1  7 x    
# 2  3 10 b    
# 3  4  9 d j  
# 4 12 13 a e m
# 5 13 15 b k  
# 6 14 17 c c  
# 7 24  9 d

Option 2: "data.table"

Use mget to put all of your data.frames into a list.
Use rbindlist to convert that list into a single data.table.
Use sequence(.N) to generate your sequence by your groups.
Use dcast.data.table to convert the "long" data.table into a "wide" one.

library(data.table)

dcast.data.table(
  rbindlist(mget(ls(pattern = "^file\\d")))[, 
    time := sequence(.N), by = list(V1, V2)],
  V1 + V2 ~ time, value.var = "V3", fill = "")
#    V1 V2 1 2 3
# 1:  1  7 x    
# 2:  3 10 b    
# 3:  4  9 d j  
# 4: 12 13 a e m
# 5: 13 15 b k  
# 6: 14 17 c c  
# 7: 24  9 d

Both of these answers assume we are starting with the following sample data:

file1 <- structure(
  list(V1 = c(12L, 13L, 14L, 4L), V2 = c(13L, 15L, 17L, 9L), 
       V3 = c("a", "b", "c", "d")), .Names = c("V1", "V2", "V3"), 
  class = "data.frame", row.names = c(NA, -4L))
file2 <- structure(
  list(V1 = c(12L, 3L, 14L, 4L), V2 = c(13L, 10L, 17L, 9L), 
       V3 = c("e", "b", "c", "j")), .Names = c("V1", "V2", "V3"), 
  class = "data.frame", row.names = c(NA, -4L))
file3 <- structure(
  list(V1 = c(12L, 13L, 1L, 24L), V2 = c(13L, 15L, 7L, 9L), 
       V3 = c("m", "k", "x", "d")), .Names = c("V1", "V2", "V3"), 
  class = "data.frame", row.names = c(NA, -4L))

Merging files on the basis of columns

1 Answers1

Option 1: "dplyr" + "tidyr"

Option 2: "data.table"

Linked