Login or Register to Ask a Question and Join Our Community


UNIX for Dummies Questions & Answers

Compare values of fields from same column with awk

 
Thread Tools Search this Thread
Top Forums UNIX for Dummies Questions & Answers Compare values of fields from same column with awk
# 1  
Old 06-15-2012
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:

Code:
blue|red
blue|red
blue|green
blue|red

output:

Code:
blue|red,green

Hope I am clear enough...

Thanks guys !
# 2  
Old 06-15-2012
Try this:

Code:
awk -F\| 'BEGIN{OFS=FS} {start=match(a[$1],$2);if(start && substr(a[$1],(start+RLENGTH),1) ~ /^[,]*$/) next; if(a[$1]) a[$1]=a[$1]","$2; else a[$1]=$2} END{for(i in a) print i,a[i]}' file

Last edited by elixir_sinari; 06-15-2012 at 06:30 AM..
This User Gave Thanks to elixir_sinari For This Post:
lucasvs
# 3  
Old 06-19-2012
Thanks elixir_sinari !

Can you tell me what a[$1] stands for ?

Does it mean "the first column"?
# 4  
Old 06-19-2012
awk
Hi,
You can shortend a bit..
Try this one,
Code:
awk 'BEGIN{FS=OFS="|";}{if(a[$1] !~ $2&&a[$1]){a[$1]=a[$1]","$2;}if(!a[$1]){a[$1]=$2;}}END{for(i in a){print i,a[i];}}' file

a[$1] - its an array and using the first column as an index..
Cheers,
Ranga:-)
Last edited by rangarasan; 06-19-2012 at 05:57 AM..
# 5  
Old 06-19-2012
Hi rangarasan !

Your code returns:

Code:
b
blue|red,red,green,red

Without the ";", it suppresses the first "b" but only list the second column:

Code:
blue|red,red,green,red

That's the role of

Code:
if(start && substr(a[$1],(start+RLENGTH),1) ~ /^[,]*$/) next

to skip the duplicates
Last edited by lucasvs; 06-19-2012 at 03:05 AM..
# 6  
Old 06-19-2012
I have updated in my previous post. Pls check.
# 7  
Old 06-19-2012
It leaves the first ",":

Code:
blue|,red,green

---------- Post updated at 01:54 AM ---------- Previous update was at 01:43 AM ----------

However, elixir_sinari's code works for a file with 2 columns.
If more than 2 columns, the code will skip the columns >2.

As I don't know how many columns a file can contain, is there a way to do the same independently of the number of columns?

I mean, one single code to treat different intputs like (I changed the words by numbers in this example to be simpler):

* file1.tab:
Code:
1|2|3|4
1|1|3|4
1|2|3|3

output1.tab:
Code:
1|1,2|3|3,4

* file2.tab:
Code:
1|2
1|3
7|2

output2.tab:
Code:
1,7|2,3

* file3.tab:
Code:
1|2|7|9|5|8
1|2|3|9|5|6

output.3tab
Code:
1|2|3,7|9|5|6,8

(NB: the order of numbers separated by "," doesn't matter)
 
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. 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

3. Shell Programming and Scripting

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 - comp275_c0_seq2 73... (7 Replies)
Discussion started by: utritala
7 Replies

4. Shell Programming and Scripting

awk - compare 1st 15 fields of record with 20 fields

I'm trying to compare 2 files for differences in a selct number of fields. When differnces are found it will write the whole record of the second file including appending '|C' out to a delta file. Each record will have 20 fields, but only want to do comparison of 1st 15 fields. The 1st field of... (7 Replies)
Discussion started by: sljnk
7 Replies

5. 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

6. Shell Programming and Scripting

Compare two files based on values of fields.

Hi All, I have two files and data looks like this: File1 Contents #Field1,Field2 Dist_Center_file1.txt;21 Dist_Center_file3.txt;20 Dist_Center_file2.txt;20 File2 Contents (*** No Header ***) Dist_Center_file1.txt;23 Dist_Center_file2.txt;20 Dist_Center_file3.txt;20 I have... (4 Replies)
Discussion started by: Hangman2
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

How to read and compare multiple fields in a column at the same time

Hi, Currently I am coding up a nasty way of reading file input using *cat* rather than *read*. My text input looks like TextA 100 TextB 110 TextC 120 Currently I am using cat |while read line to read the first column and second column fields. cat foo.txt|while read line do ... (1 Reply)
Discussion started by: ahjiefreak
1 Replies
Login or Register to Ask a Question