Login or Register to Ask a Question and Join Our Community


Shell Programming and Scripting

[Solved] Compare column data in all the rows

Login to Discuss or Reply to this Discussion in Our Community

 
Thread Tools Search this Thread
Top Forums Shell Programming and Scripting [Solved] Compare column data in all the rows
# 1  
Old 10-09-2013
[Solved] Compare column data in all the rows
Hi..

In the below sorted input file.. I am comparing the first 3 columns of data one by one row and it is a pipeline delimitter file..
Code:
AA|BB|CC|line1
AA|BB|CC|ine4
AA|BB|CC|line2
BB|CC|DD|line3
BB|CC|DD|line5

If first 3 columns of data matches with any record in the file the expected
output will be

Code:
AA|BB|CC|line1,line4
AA|BB|CC|line2
BB|CC|DD|line3,line5

I know it will be possible through awk command.. please help on this

---------- Post updated at 06:20 PM ---------- Previous update was at 04:46 PM ----------

Hi..


I have just tried and it got worked the below script.. But i need in one line of command by using "awk"..

Code:
rm opfile
inputfile=ip-file
cut -d'|' -f1-3 $inputfile | sort | uniq  > temp
t=1
NumOfRec=`wc -l < temp`
while [ $t -le $NumOfRec ]
do
 rec=`head -$t temp | tail -1`
 grep "$rec" $inputfile | awk -F'|' '{print $4}' | tr -s  '\n' ',' | read lastcol
 
 echo "$rec|$lastcol" >> opfile
 
 t=`expr $t \+ 1`
done

Last edited by vbe; 10-09-2013 at 10:17 AM.. Reason: corrected faulty code to icode tags
# 2  
Old 10-09-2013
Your original requested output doesn't match your requirement.

It should be:
Code:
awk -F\| '{
  X=$1$2$3; A[X]?A[X]=A[X]","$NF:A[X]=$0
}
END {
  for( a in A )
    print A[a]
}' file
BB|CC|DD|line3,line5
AA|BB|CC|line1,ine4,line2

This User Gave Thanks to Scott For This Post:
RavinderSingh13
# 3  
Old 10-09-2013
Hats off to you Scott it's really a nice code.

Thanks,
R. Singh
# 4  
Old 10-09-2013
One more awk approach you may try

Code:
$ cat test
AA|BB|CC|line1
AA|BB|CC|ine4
AA|BB|CC|line2
BB|CC|DD|line3
BB|CC|DD|line5

Code:
$ awk -F'[|]' '{++i;printf(!_[$1$2$3]++)?$0 ",":((++i)<3)?($1=$2=$3="")$4 "," : $4 RS;i--}' test

resulting
Code:
AA|BB|CC|line1,ine4,line2
BB|CC|DD|line3,line5

Last edited by Akshay Hegde; 10-09-2013 at 10:54 AM.. Reason: tag
# 5  
Old 10-09-2013
Thanks a lot Scott.
Ya I noticed the orginal output and i wrongly typed into that.
Could you explain the command bit detail.. so it will useful if i get these type of scenarios to work next time..

Thanks..
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 Dummies Questions & Answers

Compare data - Match first column and compare second

Hi guys, looking for some help with a way to compare data in two files but with some conditions. example, File 1 consists of site1,10.1.1.1 site2,20.2.2.2 site3,30.3.3.3 File 2 contains site1,l0.1.1.1 site2,50.1.1.1 site3,30.3.3.3 site4,40.1.1.1 I want to be able to match the... (1 Reply)
Discussion started by: mutley2202
1 Replies

3. Shell Programming and Scripting

How to separate rows of data into another column?

I have data such as below where the value in second field is the same as that in the row after. 123456,22222,John,0,xyz 234567,22222,John1,1,cde 43212,3333,Jean,3,pip 84324,3333,Abel,2,cat I'd like to rearrange the output like below to put such records beside each other and separated with... (5 Replies)
Discussion started by: james2009
5 Replies

4. Shell Programming and Scripting

Data rearranging from rows to column

Hello Everyone, I have a input file looks like -0.005-0.004-0.003-0.002-0.00100.0010.0020.0030.0040.005My desired output should look like -0.005 -0.004 -0.003 -0.002 -0.001 0 0.001 0.002 0.003 0.004 0.005I had some success in getting the desired output. But i face a problem when i... (15 Replies)
Discussion started by: dinesh.n
15 Replies

5. Shell Programming and Scripting

Transpose Column of Data to Rows

I can no longer find my commands, but I use to be able to transpose data with common fields from a single column to rows using a command line. My data is separated as follows: NAME=BOB ADDRESS=COLORADO PET=CAT NAME=SUSAN ADDRESS=TEXAS PET=BIRD NAME=TOM ADDRESS=UTAH PET=DOG I would... (7 Replies)
Discussion started by: docdave78
7 Replies

6. UNIX for Dummies Questions & Answers

[Solved] Deleting all rows where the first column equals the second column

Hi, I have a tab delimited text file where the first two columns equal numbers. I want to delete all rows where the value in the first column equals the second column. How do I go about doing that? Thanks! Input: 1 1 ABC DEF 2 2 IJK LMN 1 2 ZYX OPW Output: 1 2 ZYX OPW (2 Replies)
Discussion started by: evelibertine
2 Replies

7. Shell Programming and Scripting

Compare 2 files and match column data and align data from 3 column

Hello experts, Please help me in achieving this in an easier way possible. I have 2 csv files with following data: File1 08/23/2012 12:35:47,JOB_5330 08/23/2012 12:35:47,JOB_5330 08/23/2012 12:36:09,JOB_5340 08/23/2012 12:36:14,JOB_5340 08/23/2012 12:36:22,JOB_5350 08/23/2012... (5 Replies)
Discussion started by: asnandhakumar
5 Replies

8. UNIX for Dummies Questions & Answers

[SOLVED] splitting a single column(with spaces) into multiple rows

Hi All, My requisite is to split a single column of phonemes seperated by spaces into multiple rows. my input file is: a dh u th a qn ch A v U r k my o/p should be like: adhu a dh u (3 Replies)
Discussion started by: girlofgenuine
3 Replies

9. Shell Programming and Scripting

column data to rows every n line

Hi every one, I am trying to organise an input text file like: input 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 into an output as following: output file 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 (5 Replies)
Discussion started by: nxp
5 Replies

10. UNIX for Dummies Questions & Answers

Find different column numbers among rows in data

I want to find the different column numbers among rows in a file. For example: A001 a b c d e ... N A002 a b c d e ... N A003 a b c d e ... N+1 A004 a b c d e ... N A005 a b c d e ... N+2 : : For most of the lines I will have N columns (say 1000 rows) in each line except the line 3... (5 Replies)
Discussion started by: AMBER
5 Replies
Login or Register to Ask a Question