Login or Register to Ask a Question and Join Our Community


Shell Programming and Scripting

Awk: group rows by id and simple conversion

Login to Discuss or Reply to this Discussion in Our Community

 
Thread Tools Search this Thread
Top Forums Shell Programming and Scripting Awk: group rows by id and simple conversion
# 1  
Old 03-13-2013
Awk: group rows by id and simple conversion
Hi all,

I am a newbie to awk and trying to learn by doing examples.
I got stuck at this relatively simple conversion.
The start file looks like:
Code:
1 2 "t1"
1 3 "h1"
2 1 "h1"
2 2  "h2"

and I want to convert it into
Code:
1 t1:2, h1:3;
2 h1:1, h2:2;

Thanks.
Last edited by Franklin52; 03-13-2013 at 06:15 AM.. Reason: Please use code tags for data and code samples
# 2  
Old 03-13-2013
Code:
$ awk '{gsub("\"","");A[$1]=A[$1]?A[$1]", "$3":"$2:$1" "$3":"$2}END{for(i in A){print A[i]";"}}' file

1 t1:2, h1:3;
2 h1:1, h2:2;

This User Gave Thanks to pamu For This Post:
eagle_fly
# 3  
Old 03-13-2013
pamu.

Thanks for your help.
Somehow I get the supposedly first line to be always at the end.
Any idea why that is?

E.g. for the above example
Code:
2 h1:1, h2:2;
1 t1:2, h1:3;

# 4  
Old 03-13-2013
Try with this..

Code:
awk '{gsub("\"","");A[$1]=A[$1]?A[$1]", "$3":"$2:$1" "$3":"$2;if(!B[$1]++){C[++a]=$1}}END{for(i=1;i<=a;i++){print A[C[i]]";"}}' file

This User Gave Thanks to pamu For This Post:
eagle_fly
# 5  
Old 03-13-2013
The reversal of lines is due to the fact that (i in A) supplies all array elements, but in undefined order. To keep the right sequence, try:
Code:
awk     '       {gsub (/"/,"")
                 if (X != $1) printf "%s%s", X!=""?";\n":"", $1
                 printf "%s %s:%s", X==$1?",":"", $3, $2
                 X = $1
                }
         END    {printf ";\n"
                }
        ' file
1 t1:2, h1:3;
2 h1:1, h2:2;

Last edited by RudiC; 03-13-2013 at 06:26 AM..
This User Gave Thanks to RudiC For This Post:
eagle_fly
# 6  
Old 03-13-2013
Thanks for great solutions!Smilie

I want to print the largest id, i.e. C[i] for the max i.
I can see how to run another for loop, but is there a better way?
# 7  
Old 03-13-2013
Quote:
Originally Posted by eagle_fly
Thanks for great solutions!Smilie

I want to print the largest id, i.e. C[i] for the max i.
I can see how to run another for loop, but is there a better way?
See i m not clear what you are looking for.

but for max i you can use a.
and print C[a]

Hope this helps

pamu
Login or Register to Ask a Question

Previous Thread | Next Thread

10 More Discussions You Might Find Interesting

1. Shell Programming and Scripting

Rearrange rows by group pairs

Hello gurus, I have two variable columns 1 and 2 , and their respective groups in 3 and 4 var1 var2 gr1 gr2 a b g h c d h g d f d h f g h g d r h d p q a b h y h g r t g h I want to rearrange the rows in such a way that all similarly grouped (var1 var2) pairs are together . The... (4 Replies)
Discussion started by: senhia83
4 Replies

2. Shell Programming and Scripting

Group by and translate rows to column

I've a comma separated file with data below: 61401370587,505,1;0402686146,123;2387936.0;20170812 61401370587,505,2;0401296221,34;3.0;20170811 61401370587,505,5;0431169322,123;2387936.0;20170812 My requirement is to group by using 1st,2nd column . And translate the 3rd column's row data... (1 Reply)
Discussion started by: bhagat-reena
1 Replies

3. UNIX for Advanced & Expert Users

Conversion of rows to columns using awk based om column value

HI, My Input file data is dn:adcfgeneral id:13343 Name:xxxxxx Password:iutyerwuitywue wpuwt tuiytruityrutyrwtyrwp dn:cdferwjyyyy id:3875 Name:yyyy Password :hgfdsjkfhdsfkdlshf dshfkldshfdklsfh interset:uiuiufj My output should be ... (6 Replies)
Discussion started by: dineshaila
6 Replies

4. Solaris

Rows to column conversion in Solaris

i am using the command pkginfo -l | /usr/xpg4/bin/grep -e 'VENDOR' -e 'NAME' -e 'VERSION' >> /tmp/test1.txt in order to get name,vendor and version of the applications installed on Solaris machine.The output stored in test1.txt in as shown below: NAME: Solaris Zones (Usr) VERSION: ... (4 Replies)
Discussion started by: omkar.jadhav
4 Replies

5. UNIX for Dummies Questions & Answers

Columns to rows conversion in unix

Hi All, My requirement is to convert the rows to columns, please help me how to do in one command. Ex: source file is having data like ABC,XYZ,123,987,KKK,XXX,666 Need output like ABC XYZ 987 KKK XXX 666 Regards, Pavan. (3 Replies)
Discussion started by: madsongtel
3 Replies

6. Shell Programming and Scripting

complicated row to rows conversion

I have a file containing rows with the following format. Field1|Field2|Field3|data1:data data2:data data3:"dataA:data dataB:data" data4:data:data (and so on) I need to format the above row into multiple rows that look like this: Field1|Field2|Field3|data1|data ... (2 Replies)
Discussion started by: newreverie
2 Replies

7. Shell Programming and Scripting

AWK - calculating simple correlation of rows

Is there any way to calculate a simple correlation of few selected rows with all the rows in input ? In the below example I selected Row01,02,03 and correlated with all the rows. I was trying to run in R. But the this big data matrix is too much to handle for R and eventually my system is... (3 Replies)
Discussion started by: quincyjones
3 Replies

8. UNIX for Dummies Questions & Answers

conversion and transpositions of columns and rows

Hi, I am wondering how to do this more efficiently than I am currently doing it: I have these 3 columns 3315 E14 1 397 E14 2 321 E14 3 27 E14 4 7 E14 5 2 E14 6 But I like them to be displayed like this: 1 2 ... (5 Replies)
Discussion started by: danieladna
5 Replies

9. Shell Programming and Scripting

Convert rows to columns group

Hi I have the input file following like this "AIX" "AIX 6.0" "AIX 7.0" "Redhat 8" "Redhat 9" "Redhat 5.0 Enterprise Linux" "Sun Solaris 9" "Sun Solaris 10", "Sun Microsystems" "Oracle" .................................Like this 2000 lines I need to convert this input into... (5 Replies)
Discussion started by: selvanraj
5 Replies

10. Shell Programming and Scripting

Sort, group rows

I wrote script in bash which generates this report: User1,admin,rep,User2,shell,path1,x1,r1 User2,admin,rep,User7,shell,path1,x1,r1 User3,admin,rep,User4,shell,path1,x1,r1 User4,admin,rep,User3,shell,path1,x1,r1 User5,admin,rep,User1,shell,path1,x1,r1 User6,admin,rep,User5,shell,path1,x1,r1... (6 Replies)
Discussion started by: patrykxes
6 Replies
Login or Register to Ask a Question