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 Answers 2

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.

1
  • 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. Aug 1, 2017 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

You must log in to answer this question.

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