0

I have two files A and B. File-A has 4 columns and 600,000 rows. File-B has 4 columns and 5000 rows. Example:

File-A:

ENSB1 1 12245 0.53 0.002
ENSB2 1 13400 0.27 0.0003
ENSB3 1 14780 0.13 0.00001
ENSB4 1 15201 0.33 0.9
ENSB5 2 56259 0.70 0.0002
ENSB6 2 57000 0.42 0.00004
ENSB7 2 58200 0.50 0.5

File-B:

1 12000 15000 G1
1 14000 16000 G2
2 56000 59000 G3

I want to pick the first row of File-B. Then, I want to identify multiple rows from File-A in a way that values in column 3 of File-A that fall within the range of values in column 2 and column 3 of File-B; and the corresponding value in column 2 of File-A also matches with value in column 1 of File-B. I expect multiple rows from File-A fulfilling the above criteria. Once multiple rows are identified through the above criteria then among these rows from File-A select the lowest value in column 5 and write it in a new column in the corresponding row in File-B. These steps repeat for all rows in File-B one by one.

Expected New File:

1 12000 15000 G1 0.00001
1 14000 16000 G2 0.00001
2 56000 59000 G3 0.00004

I am new in learning Linux commands, if somebody can help, it will be great.

6
  • Look into the Unix join command to find matching lines in files, remembering to sort the input files first. Not sure about the 2nd part yet. Commented May 22, 2022 at 15:24
  • 1
    AWK is the tool that the people who created Unix shell also created for shells to call to manipulate text. Get the book Effective AWK Programming, 5th Edition, by Arnold Robbins to learn how to write awk scripts.
    – Ed Morton
    Commented May 23, 2022 at 13:02
  • from File-A select the lowest value in column 4 and write it in a new column in the corresponding row in File-B is not consistent with the output where the 5th coumn is printed. Please edit your question to clarify which field to test (the 4th?) and print (the 5th?).
    – thanasisp
    Commented May 23, 2022 at 20:31
  • @thanasisp, Yeah, sorry for that. It was supposed to be column 5. Corrected it now. Commented May 24, 2022 at 1:49
  • 1
    @EdMorton , Thank you to suggest the book. Got it. Seems a good one. Commented May 24, 2022 at 1:57

2 Answers 2

1

Try

awk 'NR==FNR   {Line[FNR] = $0           # first file processing
                Cat[FNR]  = $1
                Min[FNR]  = $2
                Max[FNR]  = $3
                Low[FNR]  = 1E10         # initialize to high number so first value from file2 will be lower
                ML        = FNR
                next
               }
                                         # second file processing; if same Category and $3 between Min and Max, and $5 lower than before one, keep $5

               {for (i=1; i<=ML; i++)  if ($2 == Cat[i] &&
                                           $3 >= Min[i] &&
                                           $3 <= Max[i] &&
                                           $5 <  Low[i]) Low[i] = $5
               }
END                     {for (i=1; i<=ML; i++) print Line[i], Low[i]
                        }
' File-B File-A
1 12000 15000 G1 0.00001
1 14000 16000 G2 0.00001
2 56000 59000 G3 0.00004

There's not much explanation needed: from the first file (NR == FNR), it keeps the category, and range / boundary values, and sets the Low array element so that the first read real value will be lower. Processing the second file, it loops across the above recorded data sets. If the new read record has the same Category as an entry, and $3 is between Min and Max, and $5 lower than the one obtained before, keep $5 in the Low array. In the END section, print the recorded lines and their obtained Low value to stdout.

7
  • Hi RudiC, Thank you for sharing commands. I have tried to run it, but it produces new file with no contents in it. Can you please re-advise. Commented May 23, 2022 at 6:25
  • Run it with the samples given. It produces the output presented, on stdout. No extra files created. Do the samples you gave represent EXACTLY the data you're working on? What about the field separators?
    – RudiC
    Commented May 23, 2022 at 8:02
  • Regarding Low[FNR] = 1E10 - always initialize min and max variables to the first value to be processed rather than some number that you hope will be less than/greater than the min/max in the input.
    – Ed Morton
    Commented May 23, 2022 at 14:26
  • 1
    Rats! The ML got lost when transferring the code into the post. Corrected in post. Sorry!
    – RudiC
    Commented May 23, 2022 at 18:27
  • @RudiC , Thank lot. It is working now. But in the resultant file it is puts the lowest value from $5 in File-B as a new row. I expect it in new column that would be 5th column in File-B. Also, Can you please guide how to get the corresponding values of the column 4 of File-A as well. Commented May 24, 2022 at 1:59
1

Using any awk and any sort:

$ cat tst.sh
#!/usr/bin/env bash

sort -k5,5n -- "$1" |
awk '
NR==FNR {
    if ( !($3 in map) ) {
        keys[++numKeys] = $3
        map[$3] = $5
    }
    next
}
{
    for ( k=1; k<=numKeys; k++ ) {
        key = keys[k]
        if ( ($2 <= key) && (key <= $3) ) {
            print $0, map[key]
            next
        }
    }
}
' - "$2"

$ ./tst.sh 'File-A' 'File-B'
1 12000 15000 G1 0.00001
1 14000 16000 G2 0.00001
2 56000 59000 G3 0.00004

The above will correctly use the lowest associated $5 when duplicate $3 values appear File-A.

2
  • Thank you much. A bit difficult to understand at my level, but working well. Thank you. Also, thanks for recommending boofk. Cheers, Commented May 24, 2022 at 7:49
  • Thanks but it actually didn't do what you want, it just appeared to do so due to the specific sample input you had provided. It's fixed now and I also tweaked it to be able to handle duplicate $3 values in case you can have those (I don't see why you couldn't). If this doesn't do what you want, let us know in what way it's failing and edit your question to include such failure case(s) in your sample input/output.
    – Ed Morton
    Commented May 24, 2022 at 14:12

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .