Selecting specific 'id's from lines and columns using 'SED' or 'AWK'

Shell Programming and Scripting
1

Hello experts,
I am new to this group and to 'SED' and 'AWK'. I have data (text file) with 5 columns (C_1-5) and 100s of lines (only 10 lines are shown below as an example). I have to find or select only the id numbers (C-1) of specific lines with '90' in the same line (of C_3) AND with '20' in their previous line (of C_3) AND '40' in their next line (of C_3). That is, the id-list should represent all three conditions altogether.

My Data File:

C_1 C_2 C_3 C_4 C_5
1 1 90 0 406
2 0 20 -1 1500
3 1 90 0 377
4 0 60 -1 1500
5 4 90 1 275
6 0 40 -1 1500
7 4 90 1 228
8 0 80 -1 1500
9 1 90 0 414
10 0 60 -1 1500
-- - -- -- ---

Any 'SED' or ''AWK' command(s) could do this task..?
I would greatly appreciate your help..!

Thanking you in advance...!
Kamu

2

Hi, welcome to the Unix forum. Your sample file does not contain such a pattern.
What have you tried so far?

3

Hello Scrutinizer,
Thank you for your quick response, and for welcoming me!
Sorry for the sample that did not contain the pattern.
Actually, other portions of the file have the pattern.
I am appending some more lines that have the pattern for your kind notice.

I was searching for similar threads from this group. Nothing worked.
Here is the data (sample) with additional lines:

C_1 C_2 C_3 C_4 C_5
1 1 90 0 406
2 0 20 -1 1500
3 1 90 0 377
4 0 60 -1 1500
5 4 90 1 275
6 0 40 -1 1500
7 4 90 1 228
8 0 80 -1 1500
9 1 90 0 414
10 0 60 -1 1500
11 1 90 0 406
12 0 20 -1 1500
13 1 90 0 377
14 0 40 -1 1500
15 4 90 1 275
16 0 20 -1 1500
17 4 90 1 228
18 0 40 -1 1500
19 1 90 0 414
20 0 60 -1 1500
21 1 90 0 406
22 0 40 -1 1500
23 1 90 0 377
24 0 20 -1 1500
25 4 90 1 275
26 0 40 -1 1500
27 4 90 1 228
28 0 20 -1 1500
29 1 90 0 414
30 0 40 -1 1500
-- - -- -- ---
I expect that the command will give the output like this: (13, 17, 25, 29); because these line-ids satisfy all 3 conditions (based on values in C_3): 1) '90' in the same line, 2) '20' in the previous line, and 3) '40' in the next line. However, I could not arrive at a command (lines) that yields correct solution (although I searched for similar threads that may give clues to this problem).

It would be great if any one solves this...!
Thanks again,
Kamu

4

Hi Kamkamu, try this

awk 'q==20&&p==90&&$3==40{print id}{q=p;id=$1;p=$3}' infile
5

Hello Scrutinizer,
What a superb expert you are...! It worked perfectly..!
Within a flash of a moment, you have provided the solution!
Thanks a ton, for your help..!

Sincerely,
Kamu

6 
awk '{a[NR]=$3} a[NR-2]==20&&a[NR-1]==90&&a[NR]==40 {print NR-2}' infile
7

Hello rdcwayx,
Thank you so much for your help.
When I tried with my long sample (as in my earlier post), it gave the output like this:
12
16
24
28
This id-list is one id-number short of the actual id's that satisfy those 3 conditions. The correct id-list should be as follows:
13
17
25
29
I will try to modify your code slightly and see whether it works perfectly.

Thanks again,
Kamu

---------- Post updated at 12:28 PM ---------- Previous update was at 12:07 PM ----------

Hello rdcwayx,
When I did a minor change in your code (from {print NR-2} to {print NR-1}, it worked perfectly. Now, your code looks like this:

awk '{a[NR]=$3} a[NR-2]==20&&a[NR-1]==90&&a[NR]==40 {print NR-1}' infile

Do you agree with me..?
Thanks for your expert help.

Sincerely,
Kamu

---------- Post updated at 01:06 PM ---------- Previous update was at 12:28 PM ----------

Hello rdcwayx,
On a recount, when I tried your code with my data-file with column titles on the first line, it gives the correct line-id. With data alone (without header), I had to modify your code.

awk '{a[NR]=$3} a[NR-2]==20&&a[NR-1]==90&&a[NR]==40 {print NR-2}' infile

On the other hand, the code provided by 'Scrutinizer' (as below) worked even when the id-lines were jumbled up.

awk 'q==20&&p==90&&$3==40{print id}{id=$1;q=p;p=$3}' infile

Thank you so much both of you...again!

Kamu