0

I have a table

A 1
A 1
A 1
A 1
A 1
A 1
A 2
B 1
B 1
B 1
B 2
B 1
B 1
B 1

I want to print column 1 of lines with values of column 2 at least 2 times larger than the 3rd step above AND 3rd step below in the same column. However, only consider the lines with the same name in column 1.

Therefore, output should be

B

I want to modify upon this script which is written by Stéphane Chazelas, to meet the additional requirement in bold above.

awk -v key=1 -v value=2 '
  NR > 6 {
    x = saved_value[NR%6]; y = saved_value[(NR - 3) % 6]; z = $value
    if (y >= 2*x && y >= 2*z) print saved_key[(NR - 3) % 6]
  }
  {saved_key[NR % 6] = $key; saved_value[NR % 6] = $value}'  < file

(It is actually a post continued from here. Since there is a more complicated situation, I want to illustrate better it here.)

. . .

20171010 update :

I am now modifying the script written by Stéphane Chazelas but now I am selecting the lines with values that are at least 2 times smaller than the 3rd value above and 3rd value below. Previously I simplified the example and so I could understand and modify the script by myself to v2 <= v1/2 && v2 <= v3/2, but again, failed... To make the matter more direct, I am now providing the real file as follows, where the values in the 2nd column are useless and the values in the 3rd columns are to be compared :

K00188:14:H2LMFBBXX:6:1101:27440:1668   1   2
K00188:14:H2LMFBBXX:6:1101:27440:1668   2   2
K00188:14:H2LMFBBXX:6:1101:27440:1668   3   2
K00188:14:H2LMFBBXX:6:1101:27440:1668   4   1
K00188:14:H2LMFBBXX:6:1101:27440:1668   5   1
K00188:14:H2LMFBBXX:6:1101:27440:1668   6   1
K00188:14:H2LMFBBXX:6:1101:27440:1668   7   1
K00188:14:H2LMFBBXX:6:1101:27440:1668   8   1
K00188:14:H2LMFBBXX:6:1101:27440:1668   9   1
K00188:14:H2LMFBBXX:6:1101:27440:1668   10  1
K00188:14:H2LMFBBXX:6:1101:6501:1686    1   2
K00188:14:H2LMFBBXX:6:1101:6501:1686    2   2
K00188:14:H2LMFBBXX:6:1101:6501:1686    3   2
K00188:14:H2LMFBBXX:6:1101:6501:1686    4   1
K00188:14:H2LMFBBXX:6:1101:6501:1686    5   1
K00188:14:H2LMFBBXX:6:1101:6501:1686    6   1
K00188:14:H2LMFBBXX:6:1101:6501:1686    7   2
K00188:14:H2LMFBBXX:6:1101:6501:1686    8   2
K00188:14:H2LMFBBXX:6:1101:6501:1686    9   2
K00188:14:H2LMFBBXX:6:1101:6501:1686    10  2

If whole lines are printed, expected output is :

K00188:14:H2LMFBBXX:6:1101:6501:1686    4   1
K00188:14:H2LMFBBXX:6:1101:6501:1686    5   1
K00188:14:H2LMFBBXX:6:1101:6501:1686    6   1

This is my failed modification :

awk -v key=1 -v value=3 '
  NR > 6 {
    k1 = saved_key[NR%6];   k2 = saved_key[(NR - 3) % 6];   k3 = $key
    v1 = saved_value[NR%6]; v2 = saved_value[(NR - 3) % 6]; v3 = $value
    if (k1 == k2 && k2 == k3 && v2 <= v1/2 && v2 <= v3/2) print $0
  }
  {saved_key[NR % 6] = $key; saved_value[NR % 6] = $value}' < test

How could I correct it?

. . .

20171011 update :

How could I add an additional key so I could compare the values in column 3 with 3rd values above and below in column 4 (i.e. a different column)? Please refer to 20171011 update. Thanks again!

K00188:14:H2LMFBBXX:6:1101:27440:1668   1   0   2
K00188:14:H2LMFBBXX:6:1101:27440:1668   2   0   2
K00188:14:H2LMFBBXX:6:1101:27440:1668   3   0   2
K00188:14:H2LMFBBXX:6:1101:27440:1668   4   1   0
K00188:14:H2LMFBBXX:6:1101:27440:1668   5   1   0
K00188:14:H2LMFBBXX:6:1101:27440:1668   6   1   0
K00188:14:H2LMFBBXX:6:1101:27440:1668   7   1   0
K00188:14:H2LMFBBXX:6:1101:27440:1668   8   1   0
K00188:14:H2LMFBBXX:6:1101:27440:1668   9   1   0
K00188:14:H2LMFBBXX:6:1101:27440:1668   10  1   0
K00188:14:H2LMFBBXX:6:1101:6501:1686    1   0   2
K00188:14:H2LMFBBXX:6:1101:6501:1686    2   0   2
K00188:14:H2LMFBBXX:6:1101:6501:1686    3   0   2
K00188:14:H2LMFBBXX:6:1101:6501:1686    4   1   0
K00188:14:H2LMFBBXX:6:1101:6501:1686    5   1   0
K00188:14:H2LMFBBXX:6:1101:6501:1686    6   1   0
K00188:14:H2LMFBBXX:6:1101:6501:1686    7   0   2
K00188:14:H2LMFBBXX:6:1101:6501:1686    8   0   2
K00188:14:H2LMFBBXX:6:1101:6501:1686    9   0   2
K00188:14:H2LMFBBXX:6:1101:6501:1686    10  0   2

If whole lines are printed, expected output is :

K00188:14:H2LMFBBXX:6:1101:6501:1686    4   1   0
K00188:14:H2LMFBBXX:6:1101:6501:1686    5   1   0
K00188:14:H2LMFBBXX:6:1101:6501:1686    6   1   0

It is my trial :

awk -v key1=1 -v key2=2 -v value1=3 -v value2=4 '
    {
    k1 = saved_key1[NR%6];   k2 = saved_key1[(NR - 3) % 6];   k3 = $key1
    k4 = saved_key2[NR%6];   k5 = saved_key2[(NR - 3) % 6];   k6 = $key2
    v1 = saved_value1[NR%6]; v2 = saved_value1[(NR - 3) % 6]; v3 = $value1
    v4 = saved_value2[NR%6]; v5 = saved_value2[(NR - 3) % 6]; v6 = $value2
    if (k1 == k2 && k2 == k3 && v2 <= v4/2 && v2 <= v6/2) print saved_record[(NR-3)%6]
  }
  {saved_key1[NR % 6] = $key1; saved_value1[NR % 6] = $value1}' < file
  • 1
    Re: your latest edit. You're printing $0, $0 is the current record, so the record for the 3rd line below the one you're checking. So you'd need to print k2, or also save the full record instead of just key and value and print saved_record[(NR-3)%6] if you want the whole record. – Stéphane Chazelas Oct 10 '17 at 16:20
  • How could I add an additional key so I could compare the values in column 3 with 3rd values above and below in column 4 (i.e. a different column)? Please refer to 20171011 update. Thanks again! – Johnny Tam Oct 11 '17 at 10:18
  • you should be able to figure that out. – Stéphane Chazelas Oct 11 '17 at 11:39
  • I tried to modify the script but failed... as it is written above. – Johnny Tam Oct 11 '17 at 13:31
  • You're never storing anything in the saved_key2, saved_value2, saved_record. You could instead save the whole record and re-split upon checking as shown in the second example in my answer. – Stéphane Chazelas Oct 11 '17 at 13:40
1

Then it would be:

awk -v key=1 -v value=2 '
  NR > 6 { # for 7th record and over only
    k1 = saved_key[NR%6];   k2 = saved_key[(NR - 3) % 6];   k3 = $key
    v1 = saved_value[NR%6]; v2 = saved_value[(NR - 3) % 6]; v3 = $value
    if (k1 == k2 && k2 == k3 && v2 >= 2*v1 && v2 >= 2*v3) print k2
  }
  # for every record, save key and value in ring buffers:
  {saved_key[NR % 6] = $key; saved_value[NR % 6] = $value}'

Note that the k1 == k2 and k2 == k3 comparisons will be numerical if the values look like numbers (so 00 will be considered the same as 0) and textual otherwise. Change to k1 "" == k2 to force a textual comparison.

Or save the whole records and re-split when checking. Like for your 20171010 update:

awk -v key=1 -v value=3 '
  NR > 6 {
    # "above" is an array with the fields of 6th last record
    split(saved_record[NR%6], above)
    # "text" is the 3rd last record and the one we will be looking at
    text = saved_record[(NR - 3) % 6]
    # "text" fields split into the "here" array.
    split(text, here)
    # $0 contains the current record (the one 3 lines below "here")
    # and $1, $2, $3... the fields of that record.
    if (above[key] == here[key] && here[key] == $key && \
        here[value] <= above[value] / 2 && here[value] <= $value / 2)
      print text
  }
  {saved_record[NR % 6] = $0}'
  • I met some problems when I tried to modify the script, would you mind taking a look of an update down below… Thank you very much! – Johnny Tam Oct 10 '17 at 13:39
  • I move the comment to here : "Change the value=2 to value=3 if you want to consider the value in the 3rd column instead of second." Yes, I have changed value = 3, and also this v2 <= v1/2 && v2 <= v3/2, but failed. I am curious if I should change something more. – Johnny Tam Oct 10 '17 at 16:03
  • Please refer to the original post to see my version of modification. Thanks. – Johnny Tam Oct 10 '17 at 16:09
0

Relatively short GNU datamash + awk solution:

datamash -W -g1 count 2 collapse 2 <file | awk '$2==7{ split($3,a,","); k=a[4]; 
             delete a[4]; if(k>=a[7]*2) print $1 }'
  • The above solution is too hard for me to fix... I tried datamash and the output seems to be more easily understood. If it is possible, would you mind taking a look at the 20171011 update, that how about comparing the values of a different column? How could I modify the command using datamash? Thank you so much. – Johnny Tam Oct 14 '17 at 2:21

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.