CODEWITHSUNDEEP
linux
lightsnail
lightsnail

Reputation: 788

Select rows in one file based on specific values in the second file (Linux)

I have two files:

One is "total.txt". It has two columns: the first column is natural numbers (indicator) ranging from 1 to 20, the second column contains random numbers.

1 321
1 423
1 2342
1 7542
2 789
2 809
2 5332
2 6762
2 8976
3 42
3 545
... ...
20 432
20 758

The other one is "index.txt". It has three columns:(1.indicator, 2:low value, 3: high value)

1 400 5000
2 600 800
11 300 4000

I want to output the rows of "total.txt" file with first column matches with the first column of "index.txt" file. And at the same time, the second column of output results must be larger than (>) the second column of the "index.txt" and smaller than (<) the third column of the "index.txt".

The expected result is as follows:

1 423
1 2342
2 809
2 5332
2 6762
11 ...
11 ...

I have tried this:

awk '$1==(awk 'print($1)' index.txt) && $2 > (awk 'print($2)' index.txt) && $1 < (awk 'print($2)' index.txt)'  total.txt > result.txt

But it failed!

Can you help me with this? Thank you!

Upvotes: 0

Views: 1436

Answers (2)

Barmar
Barmar

Reputation: 782785

You need to read both files in the same awk script. When you read index.txt, store the other columns in an array.

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

FNR == NR is the common awk idiom to detect when you're processing the first file.

Upvotes: 1

fivetentaylor
fivetentaylor

Reputation: 1297

Use join like Barmar said:

# To join on the first columns
join -11 -21 total.txt index.txt

And if the files aren't sorted in lexical order by the first column then:

join -11 -21 <(sort -k1,1 total.txt) <(sort -k1,1 index.txt)

Upvotes: 0

Related Questions