CODEWITHSUNDEEP
linuxawkrange
bapors
bapors

Reputation: 909

Finding if a column is in a range

I have two files that I want to find out if a column of file1 is in a range of columns.

file1.txt
1 19
1 21
1 24
2 22
4 45

file2.txt
1 19 23 A
1 20 28 A
4 42 45 A

I am trying to see if the 1st column of file1.txt is the same with 1st column of file2.txt, whether the second column of file1.txt is in between 2nd and 3rd columns of file2.txt, and append if it is in the range.

So the output should be :

output.txt

1 19 23 A 1 19
1 19 23 A 1 21
1 20 28 A 1 24
4 42 45 A 4 45

What I am trying is to find out if first columns are the same:

awk 'NR==FNR{c[$1]++;next};c[$1] > 0' file1.txt file2.txt 

1 19 23 A
1 20 28 A
4 42 45 A

But I am not able to put the larger/ smaller conditions. How do I add it?

Upvotes: 1

Views: 149

Answers (3)

oliv
oliv

Reputation: 13249

Using awk:

$ awk 'NR==FNR{a[$1]=a[$1]" "$2;next} {split(a[$1],b);for(i in b) if(b[i]>=$2 && b[i]<=$3) print $0,$1,b[i]}' file1 file2
1 19 23 A 1 19
1 19 23 A 1 21
1 20 28 A 1 21
1 20 28 A 1 24
4 42 45 A 4 45

The first block statement stores the elements of file1 into the array a. The array index is the first column of the file and the array element is the concatenation of all numbers of the second column with the same number in the first column.

The second block statement loops over the the array a element with the same index as the first column and checks for the number in the array is in between the range.

Another approach is to use join:

$ join -o 1.1 1.2 1.3 1.4 1.1 2.2 file2 file1 | awk '$6 >= $2 && $6 <= $3'
1 19 23 A 1 19
1 19 23 A 1 21
1 20 28 A 1 21
1 20 28 A 1 24
4 42 45 A 4 45

join -o generated the expected output format. The awk statement is filtering the lines that are in range.

Upvotes: 1

RavinderSingh13
RavinderSingh13

Reputation: 133458

Following may also help you here.

while read first second
do
  awk -v fir="$first" -v sec="$second" '$1==fir && ($2<=sec && $3>=sec){print $0,fir,sec}' file2
done < "file1"

Upvotes: 1

KamilCuk
KamilCuk

Reputation: 140970

Using join + awk:

join file2.txt file1.txt | awk '{if ($2 <= $5 && $5 <= $3) { print $1,$2,$3,$4,$1,$5 } }'

First two files are joined on the first column, then the columns are compared and output printed (with the first column printed twice, as join hides it).

Upvotes: 1

Related Questions