Merge six files based on column 1

Question

I have 6 csv files that I want to merge based on the string identity in Column 1. I want to preserve the Count-number in column 2 for each files. Se example below:

file1:

TAGCTTATCAGACTGATGTTGAC 88902
TAGCACCATTTGAAATCAGTGTT 62017
TAGCTTATCAGACTGATGTTGA  25339
GCATGGGTGGTTCAGTGGTAGAATTCTC    18477
TAGCACCATCTGAAATCGGTTA  12522
CACGGTCCCCCGCGAGGGGGGCCCGGG 11814
TAAAGTGCTGACAGTGCAGAT   10870
GCGCCCTTAGCTCAGTTGGATAGAGCAA    10353
TGTGCAAATCTATGCAAAACTGA 8689

file2:

TAGCTTATCAGACTGATGTTGAC 290460
TAGCTTATCAGACTGATGTTGA  85960
GCATGGGTGGTTCAGTGGTAGAATTCTC    33273
TAGCACCATTTGAAATCAGTGTT 25284
TAGCACCATCTGAAATCGGTTA  21199
AGTTGGTTAGAGCAACCGG 18608
AGCAGCATTGTACAGGGCTATGA 18449
TGTGCAAATCCATGCAAAACTGA 17968
TAGCTTATCAGACTGATGTTGACA    15530
CACGGTCCCCCGCGAGGGGGGCCCGGG 13258
TGTGCAAATCTATGCAAAACTGA 12847
CCTAAGGCAGGACTGATGACTGGGGTG 12725
GCCGCCGGTGAAATACCACTACTC    11971
TGAGGTAGTAGGTTGTATAGTT  10398

file3:

TAGCTTATCAGACTGATGTTGAC 181279
TAGCTTATCAGACTGATGTTGA  78661
AGTTGGTTAGAGCAACCGG 24225
CACGGTCCCCCGCGAGGGGGGCCCGGG 22252
AAAAGCTGGGTTGAGAGGGCGA  21334
TGTGCAAATCCATGCAAAACTGA 18541
CGGCGGGTGTTGACGCGATG    17818
TAGCACCATCTGAAATCGGTTA  15642
CCTAAGGCAGGACTGATGACTGGGGTG 14003
TAGCTTATCAGACTGATGTTGACA    12549
TAGCACCATTTGAAATCAGTGTT 12515
AGCAGCATTGTACAGGGCTATGA 12205

Output:

ID                        file1         file2             file3
TAGCTTATCAGACTGATGTTGAC   88902         290460            181279
....
....

Can you share with us your attempts thus far? – rchang Dec 23 '14 at 13:55 — rchang, Dec 23 '14 at 13:55

akrun · Answer 1 · 2014-12-23T14:15:33.987

Keep all the data.frames in a list and use Reduce/merge. Assuming that the name of the first column is ID across all datasets and second column name is file1, file2 etc..

Reduce(function(...) merge(..., by='ID'), list(df1, df2, df3))
#                           ID file1  file2  file3
#1 CACGGTCCCCCGCGAGGGGGGCCCGGG 11814  13258  22252
#2      TAGCACCATCTGAAATCGGTTA 12522  21199  15642
#3     TAGCACCATTTGAAATCAGTGTT 62017  25284  12515
#4      TAGCTTATCAGACTGATGTTGA 25339  85960  78661
#5     TAGCTTATCAGACTGATGTTGAC 88902 290460 181279

If there are no headers, you could read it using read.table/read.csv with header=FALSE. Then, the column names would be V1, V2 for all the datasets. I would also read it in a list using lapply. Assuming that you already read it individually,

 setNames(Reduce(function(...) merge(..., by='V1'),
          list(df1, df2, df3)),c('ID', paste0('file',1:3)) )

If you want to read all the files in a list from the working directory

 files <- list.files(pattern='^file\\d+.csv')
 lst <- lapply(files, function(x) read.csv(x,header=FALSE))
 setNames(Reduce(function(...) merge(..., by='V1'),
          lst),c('ID', paste0('file',seq_along(files))) )

I think the names of the data sets are `file1`, `file2`, etc. Rather these are the column names — David Arenburg, Dec 23 '14 at 14:08
@DavidArenburg Thanks, yes it looks like that. So, perhaps, the column names should `V1`, `V2` etc autogenerated. — akrun, Dec 23 '14 at 14:10

Merge six files based on column 1

1 Answers1