1

I've looked up examples with awk but I'm struggling to use arrays properly when comparing multiple conditionals. I have two files and I want to return information when two conditions are met:

  1. Column 1 of file2 (ex. ScYwTfa_25) matches column 4 of file 1 (i.e. Y == Y)
  2. Column 2 of file2 is greater than or equal to column 5 of file 1 but less than or equal to column 6 of file 1 (i.e. Ymin < Y1 < Ymax)

Columns 5 and 6 in file1 are a range of positional values, and if the position from file 2 is in that range, I want to map it to the corresponding range of values in columns 2 and 3 of file 1 and return the new position, along with the first column of file1. In file 1, the range of columns 5 and 6 are the same length as the range of columns 2 and 3, but for different coordinate systems named in columns 4 and 1.

Here are example files:

file 1

X  Xmin  Xmax  Y             Ymin     Ymax
10 27124 27153 ScYwTfa_25    11382070 11382099
10 41731 41779 ScYwTfa_10450 20433584 20433632
10 41780 41819 ScYwTfa_10450 20433544 20433583
10 41886 41916 ScYwTfa_10450 20433447 20433477
10 41917 41943 ScYwTfa_10450 20433420 20433446

file 2

Y             Y1
ScYwTfa_25    11382075
ScYwTfa_10450 20433425
ScYwTfa_10450 20433430
ScYwTfa_10450 99999999

The desired result would be:

10 27129
10 41922
10 41927

The first row of the result is produced because ScYwTfa_25 of file 2 matches ScYwTfa_25 in file 1 AND 11382075 (file 2) is in the range of 11382070 to 11382099 (file 1). Thus, column 1 of file 1 is printed (i.e. 10) and 27129 is based on finding the position of 11382075 in the new range in file 1 from columns 2 and 3 (i.e. Xmin + (Y1 - Ymin) = 27124 + (11382075 - 11382070) = 27129).

Likewise, rows 2 and 3 of file 2 correspond to the last row of file 1 and therefore produce output. However, the last row in file 2 does not produce any output because even though ScYwTfa_10450 has multiple matches in file 1, the position 99999999 is not within the bounds of any of the Ymin to Ymax for that Y.

Hopefully the example explains it well enough. I haven't used awk much for relating information between different files, though I've been trying to use NR == FNR method.

Thanks in advance,

matt

2
awk '
  FNR==1{ next }                          # skip header on file2, file1
  FNR==NR{                                # if file2...
    y[$1]=(y[$1]=="" ? "" : y[$1] FS) $2  # append Y1-values FS-separated to array `y`
    next                                  # continue with next record
  }
                                          # if file1...
  ($4 in y){                              # col4 matches col1 of file2
    n=split(y[$4], y1)                    # split Y1-values into array `y1`
                                          # of length n (using FS as separator)
    for (i=1;i<=n;i++)                    # loop over y1 values
      if ($5<=y1[i] && y1[i]<=$6)         # Y1 in range?
        print $1, ($2 + (y1[i] - $5))     # print result
  }
' file2 file1
1
  • Thank you for the response with the annotations...definitely helps me get further along with my understanding of how to do more complex actions with awk
    – Mgdesaix
    Dec 3 '20 at 4:29

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.