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.

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
  • 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 May 31 '14 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.

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.