Extract rows from file 1 based on range of columns from file 2

user_8059655 0
Viewed 94

I have two files

File 1:

chrom   chromStart      chromEnd   clinSign    geneId  rcvAcc  hgvsCod hgvsProt
chr1    930187  930188      VUS  SNV      SAMD11   RCV001050361    NM_152486.3:c.106G>A    NP_689699.2:p.Ala36Thr
 chr1    939398  939446     Benign  deletion       SAMD11   RCV000948524    NM_152486.2:c.683_706+24delCCCCTCATCACCTCCCCAGCCACGGTGAGGACCCACCCTGGCATGATC

File 2:

CHROM   POS  REF  ALT  FILTER     GT     BD    
chr1    1609489 AAC     A       PASS    0/1     FP
chr1    930188 T       G       LowGQ  0/1     FP
chr1    939400 TGC     T       PASS    0/1     FP

I'm trying to query file 2 based on CHROM:POS (1st & 2nd column) against the range of the first three columns from File 1 (chrom:chromStart:ChromEnd) and then have an output

chrom   chromStart      chromEnd     clinSign         geneId  rcvAcc  hgvsCod hgvsProt  CHROM        POS  REF  ALT  FILTER      GT     BD     
chr1    930187  930188         VUS  SNV     SAMD11   RCV001050361    NM_152486.3:c.106G>A    NP_689699.2:p.Ala36Thr  chr1    930188 T       G       LowGQ  0/1     FP
chr1    939398  939446     Benign  deletion        SAMD11   RCV000948524    NM_152486.2:c.683_706+24delCCCCTCATCACCTCCCCAGCCACGGTGAGGACCCACCCTGGCATGATC  chr1    939400 TGC     T       PASS    0/1     FP

So far I have tried

awk '
NR==FNR{ start[$1] = $2; end[$1] = $3; next }
(FNR==1) || ( ($1 in start) && ($2 >= start[$1]) && ($2 <= end[$1]) )
' file1 file2> test.txt 

awk 'FNR == NR { low[$1] = $2; high[$1] = $3; next }
>     $2 > low[$1] && $2 < high[$1] { print }' file1 file2 > test.txt

but both result in an empty file as output

any suggestions are appreciated. thank you

range awk
2 Answers
$ cat tst.awk
NR == 1 { hdr = $0 }
NR == FNR {
    c = ++cnt[$1]
    begs[$1,c] = $2
    ends[$1,c] = $3
    vals[$1,c] = $0
    next
}
FNR == 1 {
    print hdr, $0
    next
}
{
    for (c=1; c<=cnt[$1]; c++) {
        beg = begs[$1,c]
        end = ends[$1,c]
        if ( (beg <= $2) && ($2 <= end) ) {
            print vals[$1,c], $0
            next
        }
    }
}

.

$ awk -f tst.awk file1 file2
chrom   chromStart      chromEnd   clinSign    geneId  rcvAcc  hgvsCod hgvsProt CHROM   POS  REF  ALT  FILTER     GT     BD
chr1    930187  930188      VUS  SNV      SAMD11   RCV001050361    NM_152486.3:c.106G>A    NP_689699.2:p.Ala36Thr chr1    930188 T       G       LowGQ  0/1     FP
 chr1    939398  939446     Benign  deletion       SAMD11   RCV000948524    NM_152486.2:c.683_706+24delCCCCTCATCACCTCCCCAGCCACGGTGAGGACCCACCCTGGCATGATC chr1    939400 TGC     T       PASS    0/1     FP

If you can have multiple ranges match for a given chrom then just remove the final next statement - it's only there for efficiency if there's always just 1 match.

user_17450010 
> cat test.awk    

FNR==NR {
    if (NR==1) { title = $0; next }
    positions[$1] = positions[$1]" "$2
    r[$1,$2] = $0
    next
}

FNR==1 { print $0, title; next }

{
    split(positions[$1], p)
    for (i in p)
        if (p[i]>=$2 && p[i]<=$3) {
            print $0, r[$1,p[i]]
            next
        }
}

test

> awk -f test.awk file2 file1

chrom   chromStart      chromEnd   clinSign    geneId  rcvAcc  hgvsCod hgvsProt CHROM   POS  REF  ALT  FILTER     GT     BD    
chr1    930187  930188      VUS  SNV      SAMD11   RCV001050361    NM_152486.3:c.106G>A    NP_689699.2:p.Ala36Thr chr1    930188 T       G       LowGQ  0/1     FP
 chr1    939398  939446     Benign  deletion       SAMD11   RCV000948524    NM_152486.2:c.683_706+24delCCCCTCATCACCTCCCCAGCCACGGTGAGGACCCACCCTGGCATGATC chr1    939400 TGC     T       PASS    0/1     FP

First pass, we store positions in a pseudo-2d array, which is actually a string with space separated numbers, indices are the first fields. At the second pass, we split it to numbers and we do the check per row.

Edit: Thanks to Ed Morton for the help to fix it.

Here we parse the files in the opposite order than Ed's answer, but again the last next statement can be removed in case we can have multiple matches for the same "chrom" and we want to print all of them.

user_75896360
Related