1

File1 has 8 columns. The column 3 and 4 make a pairing like rs|860 rs|756 and the values associated with the pair is at column 8.

bb  yy  rs|860  rs|756  xx  aa  0.7 2234
bb  yy  rs|310  rs|260  xx  aa  0.3 9838
bb  yy  rs|110  rs|77   xx  aa  0.5 2291
bb  yy  rs|756  rs|860  xx  aa  0.4 2269
bb  yy  rs|110  rs|77   xx  aa  0.9 1112
bb  yy  rs|756  rs|860  xx  aa  0.8 3269
bb  yy  rs|233  rs|79   xx  aa  0.4 1397
bb  yy  rs|79   rs|233  xx  aa  0.7 1397

The pair of column3 and 4 or 4 and 3 should be treated same for instance, rs|860 rs|756 == rs|756 rs|860. Next, there might be repetition in the pairs in row one is also present in row4 and row6 (inversely). Simply here AB = BA = BA. First I want to sort the col3 and col4 row-wise which can make data like AB = AB = AB. Next, in all the occurrence of the pairs only one should retain with maximum value at column8. FOr example, rs|860 rs|756 has maximum value at row6 (i.e., 3269) so the row1 and row4 should be deleted. Likewise in case of rs|110 rs|77 row5 should be deleted. Next, in case of pairs with similar value at column8, one with higher value at column7 should be retained. For example, in case of rs|233 rs|79 row7 with lower value at column7 (0.4) should be deleted. For any pair, if both the column8 and column7 have no higher/lower difference any one can be deleted.

The desired output File2

bb  yy  rs|260  rs|310  xx  aa  0.3 9838
bb  yy  rs|77   rs|110  xx  aa  0.5 2291
bb  yy  rs|756  rs|860  xx  aa  0.8 3269
bb  yy  rs|79   rs|233  xx  aa  0.7 1397
2

Here's a non-elegant awk solution:

{
    split($3, a, "|")
    split($4, b, "|")
    if (a[2] > b[2]){
        $3=b[1]"|"b[2]
        $4=a[1]"|"a[2]
    }
    split(arr[$3" "$4], c, " ")
    if ($8 > c[8]){
        arr[$3" "$4] = $0
    }
}
END{
    for (item in arr){
        print(arr[item])
    }
}

Run with

awk -f script.awk input

It does not retain the spacing and the ordering is random.

| improve this answer | |
  • This doesn’t handle the $7 rule.  The OP’s sample output shows that the rs|79   rs|233 line should have $7 = 0.7, but your script gives 0.4. – Scott Aug 1 '17 at 4:35
0

Building on pfnuesel’s answer,

{
    split($3, a, "|")
    split($4, b, "|")
    if (a[2] > b[2]){
        $3=b[1]"|"b[2]
        $4=a[1]"|"a[2]
    }
    key=$3" "$4
    split(arr[key], c, " ")
    if ($8 > c[8]  ||  ($8 == c[8] && $7 > c[7])){
        arr[key] = $0
    }
}
END{
    for (item in arr){
        print(arr[item])
    }
}

This assumes, as shown (but not explicitly stated) in the question, that the values in the third and fourth columns are of the form

some_string | number

where spaces are for illustration only, and the string does not contain any | characters.  These tokens are to be sorted based on the values of the numbers; the string prefixes are not compared.

As in pfnuesel’s answer, usage is

awk -f script.awk file1

The exact spacing of the input file is lost, but a readable column spacing can be (re)created by piping through column -t; e.g.,

awk -f script.awk file1 | column -t > file2
| improve this answer | |

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.