Merging multiple two-column text files into one data frame based on one column

Question

This question is a follow-up to this one, which got negative responses and no answers. So, I'm trying to do this using R.

I have many (more than 30) files like this:

File1
```
5 A1
23 A3
1 B2
289 BX5
90 D3
```
File2
```
2 A1
10 A2
3 B1
1 BX4
90 D3
24 E0
```
File3
```
4 A0
11 A2
1 B1
2 D3
```

And I would like to combine all of them to produce a data frame like this:

A0 0 0 4
A1 5 2 0
A2 0 10 11
A3 23 0 0
B1 0 3 1
B2 1 0 0
BX4 0 1 0
BX5 289 0 0
D3 90 90 2
E0 0 24 0

Based on this, I tried to read two files using read.table while specifying the second column as the row names and then I merged the data frames by row names, like this:

> df1 <- read.table("File1", row.names = 2)
> df1
     V1
A1    5
A3   23
B2    1
BX5 289
D3   90
> df2 <- read.table("File2", row.names = 2)
> df2
     V1
A1    2
A2   10
B1    3
BX4   1
D3   90
E0   24
> m1 <- merge(df1, df2, by=0, all=TRUE)
> m1[is.na(m1)] <- 0
> m1
    Row.names V1.x V1.y
1          A1    5    2
2          A2    0   10
3          A3   23    0
4          B1    0    3
5          B2    1    0
6         BX4    0    1
7         BX5  289    0
8          D3   90   90
9          E0    0   24

So far so good, but when I tried to merge the resulting data frame to the third one, it doesn't work as I hoped for. And because of that, I'm not sure how I will continue to merge all the 30-something files into one data frame. Previously I thought I would modify the multmerge function described here, but now I'm stuck.

So, would anybody please help me with this? Thanks in advance.

EDIT: I would also really appreciate if anyone could suggest me a better title for this question.

As this question is marked as duplicate ("has been asked before and already has an answer"), could the people who marked it point out to me where it has been asked? I couldn't find it in Stackoverflow; or I just didn't search hard enough? [This question](http://stackoverflow.com/questions/22617593/merge-multiple-data-frames-by-row-names) for me is different from mine, and I couldn't see any answers of my question there. — sentausa, Dec 15 '15 at 13:36

score 0 · Accepted Answer · answered Dec 14 '15 at 21:10

I have tried to adapt the Reduce-part from the multmerge function for your issue.

#read in the data (can be replaced with filenames
#like f1 <- read.table(file, header=F)
#or even lapply(list.files(mypath), read.table, header=F) 
#to get all dataframes in a list
f1 <- read.table(text="5 A1
23 A3
1 B2
289 BX5
90 D3", header=F)

f2 <- read.table(text="2 A1
10 A2
3 B1
1 BX4
90 D3
24 E0", header=F)

f3 <- read.table(text="4 A0
11 A2
1 B1
2 D3", header=F)

#put files in list
myfiles <- list(f1,f2,f3)

#changing colnames because I like keeping my data in order/knowing where it came from.
myfiles <- lapply(1:length(myfiles),function(x){
  r <- myfiles[[x]]
  colnames(r) <- c(paste0("f",x),"ID")
  r
})

#using the Reduce-function
res <- Reduce(function(x,y) {merge(x,y,all=T, by="ID")}, myfiles)
res[is.na(res)]<-0
res

> res
    ID  f1 f2 f3
1   A1   5  2  0
2   A3  23  0  0
3   B2   1  0  0
4  BX5 289  0  0
5   D3  90 90  2
6   A2   0 10 11
7   B1   0  3  1
8  BX4   0  1  0
9   E0   0 24  0
10  A0   0  0  4

Thanks! It works as what I wanted. So, it seems that I was wrong from the beginning to use merge by row, while it is supposed to be merge by column, right? — sentausa, Dec 15 '15 at 13:28
Based on your provided input and output, you are merging by column. — Heroka, Dec 15 '15 at 13:29

Pierre Lapointe · Answer 2 · 2015-12-15T14:07:31.330

Here's how to do it with dplyr. First you need to load your data without assigning row names. Below, I reuse your file1,file2,file3 structures, but you might as well read them in the proper format like you did with df1,df2, df3. You need a Names column to join on. Then you do two consecutive full_join. I then sort the data and change NAs to 0.

file1 <-data.frame(Names=rownames(file1),V1=file1,row.names = NULL)
file2 <-data.frame(Names=rownames(file2),V1=file2,row.names = NULL)
file3 <-data.frame(Names=rownames(file3),V1=file3,row.names = NULL)

library(dplyr)
out <-file1  %>%
full_join(file2,by = "Names") %>%
full_join(file3,by = "Names") %>%
arrange(Names)
out[is.na(out)]<-0
#> out
#   Names V1.x V1.y V1
#1     A0    0    0  4
#2     A1    5    2  0
#3     A2    0   10 11
#4     A3   23    0  0
#5     B1    0    3  1
#6     B2    1    0  0
#7    BX4    0    1  0
#8    BX5  289    0  0
#9     D3   90   90  2
#10    E0    0   24  0

Update

To deal with an arbitrary number of files, we have to introduce a loop.

myfiles <- list(file1,file2,file3)
out <-file1                       #first file
for (i in myfiles[-1]){           #all but first file
out <-full_join(out,i,by = "Names")
}
out <-arrange(out,Names)
out[is.na(out)]<-0
out
> out
   Names V1.x V1.y V1
1     A0    0    0  4
2     A1    5    2  0
3     A2    0   10 11
4     A3   23    0  0
5     B1    0    3  1
6     B2    1    0  0
7    BX4    0    1  0
8    BX5  289    0  0
9     D3   90   90  2
10    E0    0   24  0

Thanks for the answer. What is this `%>%`? I couldn't find it by Googling. And how to do this for more than 3 files? We don't need to type full_join many times, right? — sentausa, Dec 15 '15 at 07:44
How would you generalize this to an arbitrary number of files? — Heroka, Dec 15 '15 at 13:27
@sentausa `%>%` is a forward-pipe operator from the `magrittr` package. `dplyr` imports this package. — Pierre Lapointe, Dec 15 '15 at 14:00
@Heroka For an arbitrary numbre of files, you need a loop. See my updated answer — Pierre Lapointe, Dec 15 '15 at 14:08

Merging multiple two-column text files into one data frame based on one column

2 Answers2