Login or Register to Ask a Question and Join Our Community


Shell Programming and Scripting

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

Login to Discuss or Reply to this Discussion in Our Community

 
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