Assuming that both files have been sorted using sort -b
, you may compose all possible combinations of every line in the two files that have the same ID with
join ranges.txt positions.txt
For simplicity, I'm also ignoring the fact that the files have headers (do consider removing them).
This would produce, for the given data,
1 g1 5 20 4
1 g1 5 20 19
1 g1 5 20 36
1 g1 5 20 49
1 g1 5 20 90
1 g2 6 29 4
1 g2 6 29 19
1 g2 6 29 36
[...] (in total 55 lines)
Here you have the ID, the "unique ID", the two values of the range, and the position that you'd like to test.
This could be parsed by an awk
program:
join ranges.txt positions.txt |
awk '!($1 SUBSEP $5 in count) { count[$1,$5]=0 }
$5 >= $3 && $5 <= $4 && ++count[$1,$5]
END {
for (i in count)
if (count[i] == 0) {
split(i,s,SUBSEP)
print s[1], s[2], "NA"
}
}'
This would keep track of the seen IDs and position as keys in a count
array. The value would hold the number of times a position has been placed in a range. We need this to be able to say "this position has not been found in any range".
If the current line in the output of join
contains a position in the 5th field that is within the range of field 3 and 4, this count is incremented (and the line is outputted).
This outputs all lines in the output of join
that corresponds to positions within ranges. The END
block handles unmatched positions by looping over the count
array and printing out the information that you requested in the question, in the format you requested there whenever the count
value is zero.
For the given data, this produces
1 g1 5 20 19
1 g2 6 29 19
1 g3 17 35 19
1 g6 70 95 90
1 g7 87 93 90
2 g9 10 33 20
2 g11 90 132 93
2 g11 90 132 120
2 89 NA
1 36 NA
1 4 NA
2 1 NA
1 49 NA
To collapse this data based on the "unique ID", we could pass it through another awk
program. (I'm avoiding saving it all in an array in the same awk
program as it could be quite a lot of data).
awk '$NF == "NA" { print; next }
{ key = $1 SUBSEP $NF }
key == prev { group = group "," $2; next }
{ if (group != "") print id, pos, group; id = $1; pos = $NF; group = $2; }
END { if (group != "") print id, pos, group }'
This passes through any line whose last column is NA
since these are already on the correct format. It then constructs a "key" of the ID and the position. If this key is the same as for the previous line, the "unique ID" is added onto a string called group
with a comma as delimiter.
If the key is not the same as the previous, then we've found a new set of ID-position matches and must output the data for the group we've just parsed.
This is done again in the END
block to output the data for the last group.
Taking all of this together and remembering that both input files need to be sorted, we end up with
join ranges.txt positions.txt |
awk '!($1 SUBSEP $5 in count) { count[$1,$5]=0 }
$5 >= $3 && $5 <= $4 && ++count[$1,$5]
END {
for (i in count)
if (count[i] == 0) {
split(i,s,SUBSEP)
print s[1], s[2], "NA"
}
}' |
awk '$NF == "NA" { print; next }
{ key = $1 SUBSEP $NF }
key == prev { group = group "," $2; next }
{ if (group != "") print id, pos, group
prev = key; id = $1; pos = $NF; group = $2; }
END { if (group != "") print id, pos, group }'
The output of this is
1 19 g1,g2,g3
1 90 g6,g7
2 20 g9
2 93 g11
2 89 NA
1 36 NA
1 4 NA
2 1 NA
1 49 NA
2 120 g11
which, apart form the ordering, is identical to what you expected. To fix the ordering, pass this through sort -k1,1n -k2,2n
.