How to join two huge files based on the first two columns in awk/Bash programs?

user_8760258 0
Viewed 269

There are multiple threads explaining here and here on how to perform merging between two files using awk for example.

My problem is a bit more complicated since my files are very huge. file1.tsv is 288gb and 109 columns and file2.tsv is 16gb with 4 columns. I would like to join these files based on the first two columns:

file1.tsv (tab-separated) with 109 columns (here showing first 4 and last column):

CHROM   POS     REF     ALT ... FILTER
chr1    10031   T       C   ... AC0;AS_VQSR
chr1    10037   T       C   ... AS_VQSR
chr1    10040   T       A   ... PASS
chr1    10043   T       C   ... AS_VQSR
chr1    10055   T       C   ... AS_VQSR
chr1    10057   A       C   ... AC0

file2.tsv (tab-separated) with 4 columns:

CHROM   POS     CHROM_hg19  POS_hg19
chr1    10031   chr1        10034
chr1    10037   chr1        10042
chr1    10043   chr1        10084
chr1    10055   chr1        10253
chr1    10057   chr1        10434

I wish to add the two last columns from file2.tsv to file1.tsv by matching on CHROM and POS while keeping all non-matching rows from file1.txt:

file3.txt

CHROM   POS     REF     ALT ... FILTER        CHROM_hg19    POS_hg19
chr1    10031   T       C   ... AC0;AS_VQSR   chr1          10034
chr1    10037   T       C   ... AS_VQSR       chr1          10042
chr1    10040   T       A   ... PASS          -             -
chr1    10043   T       C   ... AS_VQSR       chr1          10084
chr1    10055   T       C   ... AS_VQSR       chr1          10253
chr1    10057   A       C   ... AC0           chr1          10434

But as you have figured, these files are big. I tried the following:

awk 'NR==FNR{a[$1,$2]=$3;next} ($1,$2) in a{print $0, a[$1,$2]}' file1.txt file2.txt

And as soon as I hit enter, I saw my memory rocketing and no results being produced. I am unsure if this will produce the correct results at the end or how much memory it will use. Is there a better way to join my files in any methods using awk or any Bash programs?

Thank you in advance.

awk bash
2 Answers

With join, sed and bash (Process Substitution):

join -t $'\t' -a 1 <(sed 's/\t/:/' file1.tsv) <(sed 's/\t/:/' file2.tsv) | sed 's/:/\t/' > file3.txt

This solution assumes that the first two columns are sorted together in ascending order in both files.

See: man join

user_37768580

If all else fails you could brute-force it and read a line from file1 then read lines from file2 until you hit a match or higher number, then read the next line from file1, etc. The advantage to that approach is that very little is being stored in memory so it should work no matter how large your files are.

This isn't quite right but I don't have any more time to think about it so consider it a start and if anyone wants to finish it off and post the finished product as an answer, be my guest:

$ cat tst.awk
BEGIN {
    f1name = ARGV[1]
    f2name = ARGV[2]
    ARGV[1] = ARGV[2] = ""
    while ( !done ) {
        if ( (f1stat = (getline line1 < f1name)) > 0 ) {
            split(line1,f1)
            f1key = f1[1] FS f1[2]
        }

        matched = 0
        while ( !eof && !matched ) {
            if ( (f2stat = (getline line2 < f2name)) > 0 ) {
                split(line2,f2)
                f2key = f2[1] FS f2[2]
                matched = (f1key == f2key)
            }
            else {
                eof = 1
            }
        }

        print line1, (matched ? f2[3] OFS f2[4] : "-" OFS "-")

        if ( (f1stat <= 0) && (f2stat <= 0) ) {
            done = 1
        }
    }
}

.

$ awk -f tst.awk file1.tsv file2.tsv
CHROM   POS     REF     ALT     ...     FILTER CHROM_hg19 POS_hg19
chr1    10031   T       C       ...     AC0;AS_VQSR chr1 10034
chr1    10037   T       C       ...     AS_VQSR chr1 10042
chr1    10040   T       A       ...     PASS - -
chr1    10043   T       C       ...     AS_VQSR - -
chr1    10055   T       C       ...     AS_VQSR - -
chr1    10057   A       C       ...     AC0 - -
chr1    10057   A       C       ...     AC0 - -
user_17450010
Related