2

I am new to the field and I want to compare and merge 500 hundred tab-delimited files, each with two columns ("Position" and "Poly"). Examples of file1, and file2 (and other files) and the final output that I am looking for are as follows-

File1:

Position Poly
100001  C
228201  T
341791  T
380441  C
392640  T
459055  A
459079  T
480253  T
570405  T
492633  A

File2:

Position        Poly
100001  C
312002  C
341791  T
380441  C
412640  A
459055  A
459079  T
464056  T
570405  T
492633  A

Output:

Position        File1 File2
100001  C       C
228201  T       -
312002  -       C
341791  T       T
380441  C       C
392640  T       -
412640  -       A
459055  A       A
459079  T       T
480253  T       -
464056  -       T
570405  T       T
492633  A       A

Basically, I want to convert them into a single matrix and put dash if a position is missing in any file.

3 Answers 3

2

The join function from GNU coreutils does almost what you want - I can't find a way to handle both files' omissions in one go, but

sort -u \
<(join --header --nocheck-order -t$'\t' -a1 -o 1.1,1.2,2.2 -11 -21 -e'-' file1 file2) \ 
<(join --header --nocheck-order -t$'\t' -a2 -o 2.1,1.2,2.2 -11 -21 -e'-' file1 file2)
100001  C       C
228201  T       -
312002  -       C
341791  T       T
380441  C       C
392640  T       -
412640  -       A
459055  A       A
459079  T       T
464056  -       T
480253  T       -
492633  A       A
570405  T       T
Position        Poly    Poly

The header line gets sorted to the bottom - if that's a problem you can strip it off by piping through sed '$d' or head -n -1. Alternatively, if unsorted output is acceptable, you could remove duplicates with awk instead of sort -u i.e.

awk '!a[$1]++' \
<(join --header --nocheck-order -t$'\t' -a1 -o 1.1,1.2,2.2 -11 -21 -e'-' file1 file2) \
<(join --header --nocheck-order -t$'\t' -a2 -o 2.1,1.2,2.2 -11 -21 -e'-' file1 file2)
Position        Poly    Poly
100001  C       C
228201  T       -
341791  T       T
380441  C       C
392640  T       -
459055  A       A
459079  T       T
480253  T       -
570405  T       T
492633  A       A
312002  -       C
412640  -       A
464056  -       T
0

A gawk solution, work with every two files, and you don't mind the order of output:

$ awk '
    FNR == NR {
        if (FNR == 1) {header = "Position "FILENAME;next}
        a[$1] = $2;
        next;
    }
    {
        if (FNR == 1) {header = header" "FILENAME;next}
        if ($1 in a) {
            a[$1] = a[$1]" "$2;
        }
        else {
            a[$1] = "- "$2;
        }
    }
    END {
        print header;
        for (i in a) {
            print i,length(a[i]) == 1 ? a[i]" -" : a[i];
        }
    }
' file1 file2
Position file1 file2
412640 - A
380441 C C
392640 T -
570405 T T
341791 T T
459079 T T
464056 - T
228201 T -
312002 - C
100001 C C
480253 T -
492633 A A
459055 A A
1
  • This awk command works absolutely fine with two files, but do not work when I add more files. Probably, I did phrase my question properly before. I want to compare all 500 files (File1 to File500) together. All files are tab-limited with two columns.
    – user68902
    Commented May 31, 2014 at 9:54
0

If python is ok and no problem in order of output works with two files:

f = open("path to file1", "r")
g = open("path to file2", "r")
pos_1 = []
pos_2 = []
poly_1 = []
poly_2 = []
for line in f:
    line = line.strip('\n')
    a, b = line.split(' ')
    pos_1.append(a)
    poly_1.append(b)
for line in g:
    line = line.strip('\n')
    a, b = line.split(' ')
    pos_2.append(a)
    poly_2.append(b)
res = pos_1 + pos_2
result = []
for pos in res:
    val1 = "-"
    val2 = "-"
    if pos in pos_1:
        val1 = poly_1[pos_1.index(pos)]
    if pos in pos_2:
        val2 = poly_2[pos_2.index(pos)]
    t = (pos, val1, val2)
    result.append(t)
result = set(result) // to remove duplicates
for val in result:
    pos = val[0]
    val1 = val[1]
    val2 = val[2]
    ans = str(pos)+" "+str(val1)+" "+str(val2)
    print ans

This is in python 2.7 and for python 3 replace print by print() function.

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .