3

I have 100 files with 57,816 rows each.I want to do an outer join of these files by merging on basis of the common column.

I am using R programming for this but this is very slow.

fileList <- list.files(, pattern=".txt")
pm_list=lapply(fileList, read.table)
merged_pm=merge_all(pm_list, by = "gene_short_name")

Is there any quick way to do this in bash? The other method that I can use is SQL but I have to create 100 tables first and then load them up before join,which is not very efficient way to do.

The number of rows are equal in each file, thats why I want to merge based on common column and can't use cbind from R as the constants in the common column are a bit up and down in different files and not present at same location in each file.Below are two sample files.I want to join based on 'gene_short_name'

gene_short_name FPKM56

MT-TF   0.90
MT-TV   0
MT-RNR1 310.015
MT-TL1  0
MT-TM   0

File 2 is below:

gene_short_name FPKM53

MT-TF   0
MT-TV   0.344
MT-TM   0.10
MT-TL1  0
MT-RNR1 0
MT-ND2  158.332
  • 2
    Could you show us the first few lines of a couple files by re-editing your post? I'm having a problem understanding the question without examples. – PaperMonkey Jan 2 '15 at 20:11
  • The delimiter is tab.Yes,the contents are same. – Ron Jan 2 '15 at 21:33
3

The following script ought to do an outer join on column (field) 1 of all the tab-delimited files passed as arguments. It uses the join command, which does an outer join on sorted files, 2 files at a time.

It will join every line in the files, including the header lines. If you want the headers to be excluded, change the two sort commands to something that produces a sorted file that omits them.

#!/bin/sh
if test $# -lt 2
then
    echo usage: gjoin file1 file2 ...
    exit 1
fi
sort -t $'\t' -k 1 "$1" > result
shift
for f in "$@"
do
    sort -t $'\t' -k 1 "$f" > temp
    join -1 1 -2 1 -t $'\t' result temp > newresult
    mv newresult result
done
cat result
rm result temp

If you have an older shell, $'\t' will not be replaced by a tab, so you'll need to use 'TAB', where you put a literal tab between the quotes.

Optimizations are possible if, instead of /bin/sh, you can use a modern shell such as bash or ksh; for instance, the lines

sort -t $'\t' -k 1 "$f" > temp
join -1 1 -2 1 -t $'\t' result temp > newresult

can be replaced by

join -1 1 -2 1 -t $'\t' result <(sort -t $'\t' -k 1 "$f") > newresult
2

Since, per your post, the keys in first column are always the same (only the order is different) I think you could do this faster with sort, cut and paste. You could sort one of the files (excluding the first two lines) and then sort the rest of the files (again, excluding the first two lines) and extract only the second column from each of them and paste the results. Example:
1.txt:

g_s_n   FPKM56

MT-ND2  21.06
MT-TF   0.90
MT-TV   1
MT-RNR1 310.015
MT-TL1  1
MT-TM   1

2.txt:

g_s_n   FPKM53

MT-TF   0
MT-TV   0.344
MT-TM   0.10
MT-TL1  0
MT-RNR1 0
MT-ND2  158.332

3.txt:

g_s_n   FPKM58

MT-RNR1 0.82
MT-TM   7
MT-TF   1.20
MT-TV   4
MT-ND2  4.05
MT-TL1  2

running:

paste <({ head -n 2; sort; } <1.txt) <({ head -n 2; sort; } <2.txt | cut -f2) \
<({ head -n 2; sort; } <3.txt | cut -f2)

produces:

g_s_n   FPKM56  FPKM53  FPKM58

MT-ND2  21.06   158.332 4.05
MT-RNR1 310.015 0   0.82
MT-TF   0.90    0   1.20
MT-TL1  1   0   2
MT-TM   1   0.10    7
MT-TV   1   0.344   4

How it works: { head -n 2; sort; } <1.txt sorts the first file (except the first two lines) so the first column (common) is now sorted:

g_s_n   FPKM56

MT-ND2  21.06
MT-RNR1 310.015
MT-TF   0.90
MT-TL1  1
MT-TM   1
MT-TV   1

Same for the other files: { head -n 2; sort; } <other_files.txt | cut -f2, only this time we extract the second column (after the sort, the first column is identical for all files):

FPKM53

158.332
0
0
0
0.10
0.344

and:

FPKM58

4.05
0.82
1.20
2
7
4

these are all merged by paste.


Sure, the above works fine for a limited number of files and if your shell supports process substitution. Otherwise you'll have to write a script and use temporary files (as Mark does in his answer), paste files in batches of 10, 20 etc depending on your system limitations.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.