Using AWK to format output based on key field


 
Thread Tools Search this Thread
Top Forums Shell Programming and Scripting Using AWK to format output based on key field
# 1  
Old 04-22-2011
MySQL Using AWK to format output based on key field

I have file which contains gene lines something like this

Code:
Transcript Name          GO
POPTR_0016s06290.1      98654
POPTR_2158s00200.1      11324
POPTR_0004s22390.1      12897
POPTR_0001s11490.1
POPTR_0016s13950.1      14532
POPTR_0015s05840.1      13455
POPTR_0013s06470.1      12344
POPTR_0013s06470.1      13248
POPTR_0013s06470.1      14565
POPTR_0013s06470.1      16817

I want following output

Code:
Transcript Name          GO
POPTR_0016s06290.1      98654
POPTR_2158s00200.1      11324
POPTR_0004s22390.1      12897
POPTR_0001s11490.1
POPTR_0016s13950.1      14532
POPTR_0015s05840.1      13455
POPTR_0013s06470.1      12344||13248||13248||14565||16817

please help me to get this output
# 2  
Old 04-22-2011
Code:
nawk 'FNR==1{h=$0;next} {a[$1]=($1 in a)?a[$1] "||" $2:$2}END{print h;for(i in a) print i,a[i]}' OFS='\t' myFile

This User Gave Thanks to vgersh99 For This Post:
# 3  
Old 04-22-2011
Try:
Code:
awk 'NR==1{h=$0;next}{a[$1]=a[$1]"||"$2}END{print h;for (i in a){sub("^\\|\\|","",a[i]);print i,a[i]}}' file

This User Gave Thanks to bartus11 For This Post:
# 4  
Old 04-22-2011
Quote:
Originally Posted by vgersh99
Code:
nawk 'FNR==1{h=$0;next} {a[$1]=($1 in a)?a[$1] "||" $2:$2}END{print h;for(i in a) print i,a[i]}' OFS='\t' myFile

thank you for your help,but I got this out put output
Code:
Transcript Name          GO
POPTR_0016s06290.1      ||98654
POPTR_2158s00200.1      ||11324
POPTR_0004s22390.1      ||12897
POPTR_0001s11490.1      || 
POPTR_0016s13950.1      ||14532
POPTR_0015s05840.1      ||13455
POPTR_0013s06470.1      ||12344||13248||13248||14565||16817

I need this output

Code:
Transcript Name          GO
POPTR_0016s06290.1      98654
POPTR_2158s00200.1      11324
POPTR_0004s22390.1      12897
POPTR_0001s11490.1       
POPTR_0016s13950.1      14532
POPTR_0015s05840.1      13455
POPTR_0013s06470.1      12344||13248||13248||14565||16817

# 5  
Old 04-22-2011
Strange. given your sample input, I got:
Code:
Transcript Name          GO
POPTR_0004s22390.1      12897
POPTR_0015s05840.1      13455
POPTR_0001s11490.1
POPTR_0016s06290.1      98654
POPTR_0013s06470.1      12344||13248||14565||16817
POPTR_2158s00200.1      11324
POPTR_0016s13950.1      14532

This User Gave Thanks to vgersh99 For This Post:
# 6  
Old 04-22-2011
Quote:
Originally Posted by bartus11
Try:
Code:
awk 'NR==1{h=$0;next}{a[$1]=a[$1]"||"$2}END{print h;for (i in a){sub("^\\|\\|","",a[i]);print i,a[i]}}' file

Perfect thank you.It works fine

---------- Post updated at 02:33 PM ---------- Previous update was at 02:30 PM ----------

Quote:
Originally Posted by vgersh99
Strange. given your sample input, I got:
Code:
Transcript Name          GO
POPTR_0004s22390.1      12897
POPTR_0015s05840.1      13455
POPTR_0001s11490.1
POPTR_0016s06290.1      98654
POPTR_0013s06470.1      12344||13248||14565||16817
POPTR_2158s00200.1      11324
POPTR_0016s13950.1      14532

strange I checked it again here is the result
Code:
~/newdata/phytozome_perlfiles# nawk 'FNR==1{h=$0;next} {a[$1]=($1 in a)?a[$1] "||" $2:$2}END{print h;for(i in a) print i,a[i]}' OFS='\t' tmp
Transcript Name          GO
POPTR_0013s06470.1      ||12344||13248||14565||16817
POPTR_0016s06290.1      ||98654
POPTR_0004s22390.1      ||12897
POPTR_2158s00200.1      ||11324
POPTR_0001s11490.1      ||
POPTR_0015s05840.1      ||13455
POPTR_0016s13950.1      ||14532

# 7  
Old 04-22-2011
Try this awk script...
Code:
nawk '{if(NR>1){r[$1]=r[$1]?r[$1]"||"$2:$2}else s=$0}END{print s;for(i in r)print i"\t"r[i]}' file

Login or Register to Ask a Question

Previous Thread | Next Thread

10 More Discussions You Might Find Interesting

1. UNIX for Beginners Questions & Answers

Problem with getting awk to multiply a field by a value set based on condition of another field

Hi, So awk is driving me crazy on this one. I have searched everywhere and read man, docs and every related post Google can find and still no luck. The actual files I need to run this on are sensitive in nature, but it is the same thing as if I needed to calculate weighted grades for multiple... (15 Replies)
Discussion started by: cotilloe
15 Replies

2. Shell Programming and Scripting

Update a specific field in file with Variable value based on other Key Word

I have an input file with A=xyz B=pqr I would want the value in Second Field (xyz or pqr) updated with a value present in Shell Variable based on the value passed in the first field. (A or B ) while read line do NEW_VALUE = `some functionality done on $line` If $line=First Field-... (1 Reply)
Discussion started by: infernalhell
1 Replies

3. Shell Programming and Scripting

Using awk to print output based on first field.

Hi Folks, I have one requirement, There is one file, which contains two fields. Based on first field, I need to print an output. Example will be more suitable. Input file like this. abc 5 abc 10 xyz 6 xyz 9 xyz 10 mnp 10 mnp 12 mnp 6 (2 Replies)
Discussion started by: Raza Ali
2 Replies

4. Shell Programming and Scripting

Files summary using awk based on index key

Hello , I have several files which are looking similar to : file01.txt keyA001 350 X string001 value001 keyA001 450 X string002 value007 keyA001 454 X string002 value004 keyA001 500 X string003 value005 keyA001 255 X string004 value006 keyA001 388 X string005 value008 keyA001 1278 X... (4 Replies)
Discussion started by: alex2005
4 Replies

5. Shell Programming and Scripting

awk to adjust coordinates in field based on sequential numbers in another field

I am trying to output a tab-delimited result that uses the data from a tab-delimited file to combine and subtract specific lines. If $4 matches in each line then the first matching sequential $6 value is added to $2, unless the value is 1, then the original $2 is used (like in the case of line... (3 Replies)
Discussion started by: cmccabe
3 Replies

6. Shell Programming and Scripting

awk to update value in field based on another field

In the tab-delimeted input file below I am trying to use awk to update the value in $2 if TYPE=ins in bold, by adding the value of HRUN= in italics. In the below since in line 1 TYPE=ins the 117282541 value in $2 has 6 added because that is the value of HRUN=. Hopefully the awk is a start but I... (2 Replies)
Discussion started by: cmccabe
2 Replies

7. Shell Programming and Scripting

awk - Merge two files based on one key

Hi, I am struggling with the an awk command to merge two files based on a common key. I want to append the value from File2 ($2) onto the end of File1 where $1 from each file matches - If no match then nothing is apended File1 COL1|COL2|COL3|COL4|COL5|COL6|COL7... (3 Replies)
Discussion started by: Ads89
3 Replies

8. Shell Programming and Scripting

awk or sed? change field conditional on key match

Hi. I'd appreciate if I can get some direction in this issue to get me going. Datafile1: -About 4000 records, I have to update field#4 in selected records based on a match in the key field (Field#1). -Field #1 is the key field (servername) . # of Fields may vary # comment server1 bbb ccc... (2 Replies)
Discussion started by: RascalHoudi
2 Replies

9. Shell Programming and Scripting

Gawk / Awk Merge Lines based on Key

Hi Guys, After windows died on my netbook I installed Lubuntu and discovered Gawk about a month ago. After using Excel for 10+ years I'm amazed how quick and easily Gawk can process data but I'm stuck with a little problem merging data from multiple lines. I'm an SEO Consultant and provide... (9 Replies)
Discussion started by: Jamesfirst
9 Replies

10. UNIX for Dummies Questions & Answers

awk - Summing a field based on another field

So, I need to do some summing. I have an Apache log file with the following as a typical line: 127.0.0.1 - frank "GET /apache_pb.gif HTTP/1.0" 200 2326 Now, what I'd like to do is a per-minute sum. So, I can have awk tell me the individual minutes, preserving the dates(since this is a... (7 Replies)
Discussion started by: treesloth
7 Replies
Login or Register to Ask a Question