0

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 four columns:(1.indicator, 2:low value, 3: high value, 4:Region name)

1 400 5000 R1
2 600 800 R2
2 4000 8000 R3
11 300 4000 R4

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". And the region name also should be attached.

The expected result is as follows:

1 423 R1
1 2342 R1
2 809 R2
2 5332 R3
2 6762 R3
11 ... R4
11 ... R4

I have tried this:

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

The result comes out just as follows (with region R2 missing)

1 423 R1
1 2342 R1
2 5332 R3
2 6762 R3
11 ... R4
11 ... R4

Can you help me to explain this and tell me how to handle this? Thank you!

Actually, if the results can be output according to different regions is better, it means that in this example, it's better to produce 4 text files relating different regions (R1.txt, R2.txt, R3.txt and R4.txt). Thanks!

  • You have two "2" indexes for R2 and R3 region – Costas Sep 9 '16 at 19:12
  • @Costas That's the issue -- he needs to be able to remember all the regions for each index, and find the one that matches the data in total.txt. – Barmar Sep 9 '16 at 19:13
  • 1
    This would be better done in a language with multidimensional arrays, like perl or php. Each indicator points to an array of regions, each with its own high and low values. Then you can loop through the array looking for the region where the number is in range. – Barmar Sep 9 '16 at 19:15
  • @Costas Yes. That's where I get stuck. Although R2 and R3 are different regions, they have the same indicator number. I don't know how to figure it out. – lightsnail Sep 9 '16 at 19:16
  • @Barmar For such case OP should build array on region but not on index – Costas Sep 9 '16 at 19:17
3

The indicators in your both input files (the first column) are non-unique, so it's hard to use those as keys. But if the region ID's are unique, we can use those:

 $ awk 'FNR == NR { reg=$4; low[reg]=$2; high[reg]=$3; ind[reg]=$1; next} 
  { for (reg in ind) { 
      if ($1 == ind[reg] && $2 >= low[reg] && $2 <= high[reg]) {
         print $0,reg
      } } }' index.txt total.txt
1 423 R1
1 2342 R1
2 789 R2
2 5332 R3
2 6762 R3

This is basically your code, with the for loop added to map the regions to their indicators, and check which of the lines in the second file match.

(as for 2 789 R2 vs 2 809 R2, I'm not sure why 809 should be printed if the limits are 600 and 800.)

Change the print command to print $0,reg > reg ".txt" to get output to files named by the regions.

  • Thank you so much! @ilkkachu Since the expected result is edited by my own, so that's a mistake. You are right. Thanks again! – lightsnail Sep 9 '16 at 19:31

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.