How to compare the values of a column in a same file using awk?


 
Thread Tools Search this Thread
Top Forums Shell Programming and Scripting How to compare the values of a column in a same file using awk?
# 1  
Old 02-14-2014
How to compare the values of a column in a same file using awk?

Dear Unix experts,

I have got a file where I would like to compare the values of second column if first column is same in such a way that the difference between the values is >50. If not, I would like to discard both values.

For example, my input file looks like -

Code:
comp275_c0_seq2 73
comp275_c0_seq2 76
comp275_c0_seq2 85
comp275_c0_seq2 105
comp569_c0_seq1 117
comp569_c0_seq1 208
comp569_c0_seq1 328

where, for column 2, difference between row 'two and one' and 'three and two' and 'four and three' is less than 50. So, I would like to discard those entries.
For column 1, difference between rows 'six and seven' and 'eight and seven; is greater than 50 and hence keep them. So, my desired output will be
Code:
comp569_c0_seq1 117
comp569_c0_seq1 208
comp569_c0_seq1 328

Any help you can provide is highly appreciated.

Many thanks
# 2  
Old 02-14-2014
Is this a homework assignment?

What have you tried?
# 3  
Old 02-14-2014
Sorry I didn't post my code earlier.

Code:
awk 'NR % 2 != 0 {x=$1; y=$2} NR % 2 == 0 {if ($2 - y > 50){print x,y}}' test1

where test1 is the input file I described above.

It only gives me

Code:
comp569_c0_seq1 117

as output.
# 4  
Old 02-14-2014
I repeat: Is this a homework assignment?
# 5  
Old 02-14-2014
No, its not. I am trying to analyse some large dataset and really naive to shell programming
# 6  
Old 02-14-2014
If I understand your requirements correctly, you could try something like:
Code:
awk '
{       if($1 != l1 || $2 - l2 > 50) {
                if(lp) print l1, l2
                lp = 1
        } else  lp = 0
        l1 = $1 
        l2 = $2 
}
END {   if(lp) print l1, l2
}' input

If you want to run this on a Solaris/SunOS system use /usr/xpg4/bin/awk, /usr/xpg6/bin/awk, or nawk instead of the default /usr/bin/awk.

Your description and sample data didn't indicate what should happen if there is only one line for a given field 1 value. This script will print those lines because there is no other line with that field 1 value that has a value in field 2 that is within 50 points of it. For example, with the input file:
Code:
comp275_c0_seq2 73
comp275_c0_seq2 76
comp275_c0_seq2 85
comp275_c0_seq2 105
comp569_c0_seq1 117
comp569_c0_seq1 208
comp569_c0_seq1 328
added_set_1 1
added_set_1 100 
added_set_1 150 
added_set_1 200 
added_set_1 251
added_set_1 302 
added_set_1 322 
added_single 1

the output produced is:
Code:
comp569_c0_seq1 117
comp569_c0_seq1 208
comp569_c0_seq1 328
added_set_1 1
added_set_1 251
added_single 1

# 7  
Old 02-17-2014
Thank you for your help.
Login or Register to Ask a Question

Previous Thread | Next Thread

10 More Discussions You Might Find Interesting

1. UNIX for Beginners Questions & Answers

Compare values in multiple rows in one column using awk

I would like to compare values in column 8, and grep the ones where the different is > 1, columns 1 and 2 are the key for array. Every 4 rows the records values in columns 1 and 2 changed. Then, the comparison in the column 8 need to be done for the 4 rows everytime columns 1 and 2 changed ... (4 Replies)
Discussion started by: jiam912
4 Replies

2. UNIX for Beginners Questions & Answers

Compare Values between column in the same file

Input File:- COLUMN1 COLUMN2 COLUMN3 COLUMN4 COLUMN5 COLUMN6 SMS Email AO Mail Post N Cell VEGE Potato E W 396 12 0 384 0 0 0 0 0 VEGE Onion S W 17 0 17 0 0 0 0 0 0 FRUIT APPLE N W 549 61 0 0 0 0 0 488 0 FRUIT APPLE SE W 291 14 239 38 0 10 0 0 0 FRUIT APPLE EAMS W 397 32 309 56 309 309 0... (27 Replies)
Discussion started by: Nina2910
27 Replies

3. Shell Programming and Scripting

Compare two files column values using awk

Judi # cat File1 judi /export/home 76 judi /usr 83 judi # judi # cat File2 judi /export/home 79 judi /usr 82 judi # if COLUMN3 of File2 is greater that COLUMN3 of File1, then print File2's lines juid /export/home 79 Code tags please (2 Replies)
Discussion started by: judi
2 Replies

4. Shell Programming and Scripting

Compare the second column of a file with the second column of another in awk

Hi, I know that this topic has been discussed in the past and I've tried to follow all the guidelines. Anyhow, I following describe my problem. I have a file (file1 , no. records = 67) containing pairs of IP addresses as follows (with single space as delimiter between the fields): example... (5 Replies)
Discussion started by: amarn
5 Replies

5. UNIX for Dummies Questions & Answers

Compare values of fields from same column with awk

Hi all ! If there is only one single value in a column (e.g. column 1 below), then return this value in the same output column. If there are several values in the same column (e.g. column 2 below), then return the different values separated by "," in the output. pipe-separated input: ... (11 Replies)
Discussion started by: lucasvs
11 Replies

6. Shell Programming and Scripting

How to compare the values of a column in awk in a same file and consecutive lines..

I would like to compare the values of 2nd column of consecutive lines of same file in such a way so that if the difference between first value and second value is more than 100 it should print complete line else ignore line. Input File ========== PDB 2500 RTDB 123 RTDB-EAGLE 122 VSCCP 2565... (4 Replies)
Discussion started by: manuswami
4 Replies

7. Shell Programming and Scripting

Compare two files using awk or sed, add values in a column if their previous fields are same

Hi All, I have two files file1: abc,def,ghi,5,jkl,mno pqr,stu,ghi,10,vwx,xyz cba,ust,ihg,4,cdu,oqw file2: ravi,def,kishore ramu,ust,krishna joseph,stu,mike I need two output files as follows In my above example, each row in file1 has 6 fields and each row in file2 has 3... (3 Replies)
Discussion started by: yerruhari
3 Replies

8. UNIX for Dummies Questions & Answers

Compare two files using awk or sed, add values in a column if their previous fields are same

Hi All, I have two files file1: abc,def,ghi,5,jkl,mno pqr,stu,ghi,10,vwx,xyz cba,ust,ihg,4,cdu,oqw file2: ravi,def,kishore ramu,ust,krishna joseph,stu,mike I need two output files as follows In my above example, each row in file1 has 6 fields and each row in file2 has 3... (1 Reply)
Discussion started by: yerruhari
1 Replies

9. UNIX for Advanced & Expert Users

Compare two files using awk or sed, add values in a column if their previous fields are same

Hi All, I have two files file1: abc,def,ghi,5,jkl,mno pqr,stu,ghi,10,vwx,xyz cba,ust,ihg,4,cdu,oqw file2: ravi,def,kishore ramu,ust,krishna joseph,stu,mike I need two output files as follows In my above example, each row in file1 has 6 fields and each row in file2 has 3... (1 Reply)
Discussion started by: yerruhari
1 Replies

10. Shell Programming and Scripting

I need to extract last column of a file and compare the values

Hi, I am new to unix and I need help in solving below mentioned issue, really appreciate ur help. I have a file sam, john, 2324, 07142007 tom, thomson, 2343, 07142007 john, scott, 2478, 07142007 its a comma delimited file, I need to extract the last column from each line and this... (4 Replies)
Discussion started by: vukkusila
4 Replies
Login or Register to Ask a Question

Featured Tech Videos