Login or Register to Ask a Question and Join Our Community


Shell Programming and Scripting

Use awk to count and rearrange entries

Login to Discuss or Reply to this Discussion in Our Community

 
Thread Tools Search this Thread
Top Forums Shell Programming and Scripting Use awk to count and rearrange entries
# 1  
Old 01-21-2014
Use awk to count and rearrange entries
How can I use awk to count the occurrence of field 2 and rearrange the output like below:

Input:

Code:
OA1 FM AA
OA0 FM CC
ON0 FM CC
FN1 FN BB
OY1 FN BB
OY2 FN CC
OY3 FN CC
YT0 FM AA
KW1 FN CC
KW3 FM BB
YT4 FM AA
FN2 FT BB
OA3 FT AA
ON7 FM BB


output:

Code:
FM(7)
AA=OA1,YT0,YT4
CC=OA0,ON0
BB=KW3,ON7

FN(5)
BB=FN1,OY1
CC=OY2,OY3,KW1

FT(2)
BB=FN2
AA=OA3

# 2  
Old 01-21-2014
If order doesn't matter then this would be fine

Code:
$ awk '{
          A[$2]++
          B[$2 FS $3] = B[$2 FS $3] ? B[$2 FS $3] "," $1 : $1
       }
    END{   
            for(i in A){
                                   print i"("A[i]")"
                    for(j in B){
                                   split(j,X)
                                   if(X[1]==i)
                                   print X[2]"="B[j]              
                               }
                                   print ""
                       }
       }' file

Code:
FM(7)
AA=OA1,YT0,YT4
BB=KW3,ON7
CC=OA0,ON0

FN(5)
BB=FN1,OY1
CC=OY2,OY3,KW1

FT(2)
AA=OA3
BB=FN2

Last edited by Akshay Hegde; 01-21-2014 at 09:31 AM..
This User Gave Thanks to Akshay Hegde For This Post:
MadeInGermany
# 3  
Old 01-21-2014
Thank you for your effort, but I am still not getting the desired output.
# 4  
Old 01-21-2014
Hai aydi I just updated did you try ?
# 5  
Old 01-21-2014
Hello,

Following may also help.

Code:
awk '/FM/ {i++};
        /FN/ {j++};
        /FT/ {k++};
        {
                if($3 ~ f && $2 =="FM")
                {a=a","$1}
                 {f="AA"; g="BB"; h="CC"}
                if($3 ~ g && $2 =="FM")
                 {b=b","$1}
                 {f="AA"; g="BB"; h="CC"}
                if($3 ~ h && $2 =="FM")
                {c=c","$1}
                 {f="AA"; g="BB"; h="CC"}

                if($3 ~ f && $2 =="FN")
                {a1=a1","$1}
                 {f="AA"; g="BB"; h="CC"}
                if($3 ~ g && $2 =="FN")
                 {b1=b1","$1}
                 {f="AA"; g="BB"; h="CC"}
                if($3 ~ h && $2 =="FN")
                {c1=c1","$1}
                 {f="AA"; g="BB"; h="CC"}

                if($3 ~ f && $2 =="FT")
                {a2=a2","$1}
                 {f="AA"; g="BB"; h="CC"}
                if($3 ~ g && $2 =="FT")
                 {b2=b2","$1}
                 {f="AA"; g="BB"; h="CC"}
                if($3 ~ h && $2 =="FT")
                {c2=c2","$1}
                 {f="AA"; g="BB"; h="CC"}
 
        }
END { print"FM= "i"\nAA= " a"\nBB= "b"\nCC= "c"\nFN= "j"\nAA= " a1"\nBB= "b1"\nCC= "c1"\nFT= "k"\nAA= " a2"\nBB= "b2"\nCC= "c2}' check_data_range_details | sed 's/\= \,/\=/g'

Output will be as follows.


Code:
FM= 7
AA=OA1,YT0,YT4
BB=KW3,ON7
CC=OA0,ON0
FN= 6
AA=
BB=FN1,OY1
CC=OY2,OY3,KW1
FT= 2
AA=OA3
BB=FN2
CC=



Thanks,
R. Singh
# 6  
Old 01-21-2014
Yeah, I used the edited version
# 7  
Old 01-21-2014
Okay..
Login or Register to Ask a Question

Previous Thread | Next Thread

10 More Discussions You Might Find Interesting

1. Shell Programming and Scripting

Awk: Print count for column in a file using awk

Hi, I have the following input in a file & need output as mentioned below(need counter of every occurance of field which is to be increased by 1). Input: 919143110065 919143110065 919143110052 918648846132 919143110012 918648873782 919143110152 919143110152 919143110152... (2 Replies)
Discussion started by: siramitsharma
2 Replies

2. Shell Programming and Scripting

Rearrange Lines with awk

I need to rearrange the lines in the input file in the example below: Input: LG1 R500 A-170 F1:81 F1:22 F2:32 F1:71 LG1 R700 A-203 F2:17 E2:18 LG1 R700 B-224 E1:9 LG2 R500 C-235 E2:9 F2:17 Output: LG1 R500 A-170 F1:81 LG1 R500 A-170 F1:22 LG1 R500 A-170 F2:32 LG1 R500 A-170... (2 Replies)
Discussion started by: aydj
2 Replies

3. Shell Programming and Scripting

Using awk to rearrange fields

Hi, I am required to arrange columns of a file i.e make the 15th column into the 1st column. I am doing awk 'begin {fs=ofs=","} {print $15,$1,$2,$3,$4,$5,$6,$7,$8,$9,$10,$11,$12,$13,$14}' ad.data>ad.csv the problem is that column 15 gets to column 1 but it is not comma separated with the... (10 Replies)
Discussion started by: seddoubt
10 Replies

4. Shell Programming and Scripting

awk - count character count of fields

Hello All, I got a requirement when I was working with a file. Say the file has unloads of data from a table in the form 1|121|asda|434|thesi|2012|05|24| 1|343|unit|09|best|2012|11|5| I was put into a scenario where I need the field count in all the lines in that file. It was simply... (6 Replies)
Discussion started by: PikK45
6 Replies

5. Shell Programming and Scripting

how to rearrange a matrix with awk

Hi, every one. I have two files ,one is in matrix like this, one is a list with the same data as the matrix. AB AE AC AD AA AF SA 3 4 5 6 4 6 SC 5 7 2 8 4 3 SD 4 6 5 3 8 3 SE 45 ... (5 Replies)
Discussion started by: xshang
5 Replies

6. Shell Programming and Scripting

Using awk to rearrange data according to date

Hi, I have a large data frame as shown below, where data is separated into years. 10 May 2011 Created: 10 May 11 15:05 GMT Scale: SIO-2005 and others GC-MD, Cape Grim, Tasmania, Lat.: 40.68S, Lon.: 144.69E, Alt: 94m above sea level You can use the following format in Fortran to read data... (4 Replies)
Discussion started by: gd9629
4 Replies

7. UNIX for Dummies Questions & Answers

Rearrange columns and rows with awk

Hello, I have the following problem I have two columns with numbers arranged as follows: x1 y1 x2 y2 .... .... x250 y250 Now I need them arranged as follows: "string a" x1 y1 x1 y2 "string b" "string a" x1 y2 x2 y2 (3 Replies)
Discussion started by: Tom46
3 Replies

8. Shell Programming and Scripting

awk sed cut? to rearrange random number of fields into 3 fields

I'm working on formatting some attendance data to meet a vendors requirements to upload to their system. With some help on the forums here, I have the data close. But they've since changed what they want. The vendor wants me to submit three fields to them. Field 1 is the studentid field,... (4 Replies)
Discussion started by: axo959
4 Replies

9. Shell Programming and Scripting

rearrange a file

hi! in awk, i have a file like this: Trace1: WRIT,Trace2: BLAN,Trace3: BLAN, -47.2120018005371,,,39815.4809027778 -46.3009986877441,,,39815.4809027778 -46.277000427246,,,39815.4809143519 -46.7389984130859,,,39815.4809259259 -46.3460006713867,,,39815.4809259259... (10 Replies)
Discussion started by: riderman
10 Replies

10. Shell Programming and Scripting

Need help in AWK;Search String and rearrange columns

Hi AWK Experts, file1.txt contains: 29b11b820ddcc:-|OHad.perWrk|spn_id=AH111|spn_ordtyp=MY_REQ|msg_typ=ah.ntf.out|spn_ordid=928176|spn_nid=3|msg_strt=1175615334703|msg_que=oput|diff=371|17:48:55,074|17:48:55,084|10 file2.txt contains:... (2 Replies)
Discussion started by: spring_buck
2 Replies
Login or Register to Ask a Question