Login or Register to Ask a Question and Join Our Community


Shell Programming and Scripting

awk questions

Login to Discuss or Reply to this Discussion in Our Community

 
Thread Tools Search this Thread
Top Forums Shell Programming and Scripting awk questions
# 1  
Old 03-25-2013
awk questions
Hi All,
I have a file in the format shown below. I need to pick up only those lines which have the highest value in column 4 , based on column1.

The lines marked as bold should be the output. Any help is appreciated!

Input file :
Code:
-10.5|6|L|32|MPP||R||012009|E
-10.5|6|L|43|MPF|B|R||012009|E
-10.5|6|L|44|MPF||R||012009|E
-10.5|6|L|54|MR|V|R||012009|E
-10.5|6|L|55|MR|B|R||012009|E
-10.5|6|L|56|MR||R||012009|E
-10.5|6|P|08|MPP||R|F|012009|E
-10.5|6|P|13|MPF|B|R|F|012009|E
-10.5|6|P|14|MPF||R|F|012009|E
-10.5|6|P|22|MR|V|R|F|012009|E
-10.5|6|P|23|MR|B|R|F|012009|E
-10.5|6|P|24|MR||R|F|012009|E
-10.75|6|L|16|||P||012008|E
-10.75|6|L|18|||U||012008|E
-11.3|6|L|51|MR|V|P||012009|E
-11.3|6|P|19|MR|V|P|G|012009|E
-11.4|6|L|35|MR|B|R||IN2010|E
-11.85|6|L|41|MPF|B|P||012009|E
-11.85|6|L|42|MPF||P||012009|E
-11.85|6|L|52|MR|B|P||012009|E
-11.85|6|P|11|MPF|B|P|G|012009|E
-11.85|6|P|12|MPF||P|G|012009|E
-11.85|6|P|20|MR|B|P|G|012009|E
-11.95|6|L|53|MR||P||012009|E
-11.95|6|P|21|MR||P|G|012009|E
-11|6|L|17|||R||012008|E
-12.4|6|L|22|MR|V|R||IN2012|E
-12.4|6|L|34|MR|V|R||IN2010|E
-12.4|6|L|50|MR|V|R||IN2013|E
-12.7|6|L|23|MR|B|R||IN2011|E
-13.67|6|L|16|||P||012007|E
-13.67|6|L|17|||R||012007|E
-13.67|6|L|18|||U||012007|E
-14.92|7|P|09|MPP||P|I|012009|E
-14.93|5|L|25|MPP||P||012009|E
-14.9|5|L|19|MR|B|R||IN2012|E
-14.9|6|L|22|MR|V|R||IN2011|E
-14|6|L|24|MR||R||IN2012|E
-14|6|L|51|MR||R||IN2013|E
-15.14|6|L|16|||P||012006|E
-15.14|6|L|17|||R||012006|E
-15.14|6|L|18|||U||012006|E
-15.18|6|L|33|MPP|V|U||012009|E
-15.18|6|L|34|MPP|E|U||012009|E
-15.18|6|L|35|MPP|V|X||012009|E
-15.18|6|L|36|MPP|E|X||012009|E
-15.18|6|L|45|MPF|B|U||012009|E
-15.18|6|L|46|MPF||U||012009|E
-15.18|6|L|47|MPF|B|X||012009|E
-15.18|6|L|48|MPF||X||012009|E
-15.18|6|L|57|MR|V|U||012009|E

# 2  
Old 03-25-2013
What have you tried?
# 3  
Old 03-25-2013
Hi, Try this,
Code:
awk -F'\|' 'a[$1]>$4{a[$1]=$4;v[$1]=$0;}END{for(i in v){print v[i];}}' file

If your file is large then, use sort cmd to sort the file based on col1 and col4 and then, print the last match. Cheers, Ranga:-)
# 4  
Old 03-25-2013
Code:
awk -F\| ' {
                if ( $1 in A )
                {
                        split (A[$1], R, "|")
                        if ( R[4] < $4 )
                                A[$1] = $0
                }
                else
                        A[$1] = $0
} END {
        for ( i in A )
                print A[i]
} ' file | sort -nr

Last edited by Yoda; 03-25-2013 at 04:23 PM.. Reason: Corrected the index for array
# 5  
Old 03-25-2013
Thank you all for the replies. But the problem is that your code does not eliminate unwanted entries.

I can use reverse sort command to sort the entries based on 1st and 4th column , but then I will have to print only the first occurence of the string.

Code:
sort -ur  -k1,4 NPBR2.XTR > tst1

Code:
0||P|30|MR||R|S|IN2010|E
0||P|29|MR||R|K|IN2010|E
0||P|28|MR|B|R|S|IN2010|E
0||P|27|MR|B|R|K|IN2010|E
0||P|26|MR|V|R|S|IN2010|E
0||P|25|MR|V|R|K|IN2010|E
0||P|24|MR||P|T|IN2010|E
0||P|23|MR||P|L|IN2010|E
0||P|22|MR|B|P|T|IN2010|E
0||P|21|MR|B|P|L|IN2010|E
0||P|20|MR|V|P|T|IN2010|E
0||P|19|MR|V|P|L|IN2010|E
0||P|15|PDP||X|S|IN2010|E
0||P|14|PDP||X|K|IN2010|E
0||P|13|PDP||R|S|IN2010|E
0||P|12|PDP||R|K|IN2010|E
0||P|11|PDP||P|S|IN2010|E
0||P|10|PDP||P|K|IN2010|E
0|7|P|18|MR||R|H|IN2010|E
0|7|P|17|MR|B|R|H|IN2010|E
0|7|P|16|MR|V|R|H|IN2010|E
0|7|P|15|MR||P|I|IN2010|E
0|7|P|14|MR|B|P|I|IN2010|E
0|7|P|13|MR|V|P|I|IN2010|E
0|7|P|09|PDP||X|H|IN2010|E
0|7|P|08|PDP||R|H|IN2010|E
0|7|P|07|PDP||P|H|IN2010|E
0|7|L|56|MR||A||IN2013|E

# 6  
Old 03-25-2013
try also:
Code:
awk '
!col1[$1] {col1[$1]=$1; cnt++; mx[$1]=$4}
{if ($4>=mx[$4]) {mx[$1]=$4; out[cnt-1]=$0}}
END {for (i=0; i<cnt; i++) print out[i]}' FS="|" infile

Last edited by rdrtx1; 03-25-2013 at 03:39 PM..
# 7  
Old 03-25-2013
Code:
sort -t\| +0 -1 +3 -4 -r yourfile | awk -F\| '!a[$1]++'

+m Start at the first character of the m+1th field.
-n End at the last character of the nth field (if -N omitted, assume the end of the line).

Just to clarify, if you consider using keys (-k options) and if you consider the line :

-10.5|6|L|32|MPP||R||012009|E

using -k1,4 will use the following key for sorting :
-10.5|6|L|32|MPP||R||012009|E

using -k4 will use the following key for sorting :
-10.5|6|L|32|MPP||R||012009|E

using -k4,4 will use the following key for sorting :
-10.5|6|L|32|MPP||R||012009|E

using -k1,1 will use the following key for sorting :
-10.5|6|L|32|MPP||R||012009|E

using -k1 will use the following key for sorting :
-10.5|6|L|32|MPP||R||012009|E

If no end position is specified the end of line is assumed
so using key :

Code:
sort -t\| -k1,1 -k4,4 -r yourfile | awk -F\| '!a[$1]++'

Last edited by ctsgnb; 03-25-2013 at 05:19 PM..
Login or Register to Ask a Question

Previous Thread | Next Thread

10 More Discussions You Might Find Interesting

1. Homework & Coursework Questions

awk questions using sort and grep

1. The problem statement, all variables and given/known data: So i'll probably get told off for this but I have a few problems and rather than clog up the whole forum I'll post them here. Please bare in mind I am a complete novice when it comes to all this and so if you help please treat me like a... (4 Replies)
Discussion started by: jamesb18
4 Replies

2. Shell Programming and Scripting

Couple of easy questions for experts on awk/sed

Hello Experts.. I have 3-4 C codes with Oracle SQL statements embedded. All the SQL statements starts with EXEC SQL keyword and ends with ;. I want to extract all the SQL statements out of these codes. I did awk '/^EXEC SQL/,/\;/' inputFile (I use this on all of the codes individually). That... (2 Replies)
Discussion started by: juzz4fun
2 Replies

3. AIX

Simple AWK cleanup/questions AIX

I have an unfortunate need to redo a bunch of disk settings on a VIOS on AIX, so I was putting together a quick script to scrub everything it has, make the changes, and then put the mappings back. It works, I just am trying to get my awk a bit more up-to-snuff and wanted to know the proper way to... (2 Replies)
Discussion started by: Vryali
2 Replies

4. Shell Programming and Scripting

Awk/Nawk Questions

Hi Guys, This is the Input: <xn:MeContext id="XXX012"> <xn:ManagedElement id="1"> <xn:attributes> <xn:userLabel>XXX012</xn:userLabel> <xn:swVersion>R58E68</xn:swVersion> </xn:attributes> </xn:ManagedElement> </xn:MeContext>... (4 Replies)
Discussion started by: smarones
4 Replies

5. Shell Programming and Scripting

basic awk questions

I find an script with awk sitting around. I went through some online manuals, but I can't figure out exactly how it works. I can't post the whole program. Not allowed. This is the line that is confusing me. I get when else is in the script grep -v "^REM " $1| grep -v "JUNK;" | awk -F" "... (2 Replies)
Discussion started by: guessingo
2 Replies

6. Homework & Coursework Questions

Print questions from a questions folder in a sequential order

1.) I am to write scripts that will be phasetest folder in the home directory. 2.) The folder should have a set-up,phase and display files I have written a small script which i used to check for the existing users and their password. What I need help with: I have a set of questions in a... (19 Replies)
Discussion started by: moraks007
19 Replies

7. Shell Programming and Scripting

Some questions about grep/awk

Hi guys. I need to filter some values from a number of log files. One of the files is: Interconnect Utilisation Results: Achieved Maximum Number of Concurrent Connections: 17 Statistics for Average Number of Concurrent Connections: Point Estimation: Confidence Interval: ... (2 Replies)
Discussion started by: Faaz0
2 Replies

8. Shell Programming and Scripting

...yet another string of awk/sed questions from a RegExp-Challenged luser %-\

Greetings all, ...here is yet another string of awk/sed questions from a RegExp-Challenged luser :eek: I'm looking to have sed/awk do some clean-up on routing tables and to that end, I would like to do the following: 1.) If a line contains the word "masks" or "subnets" prepend CR/LF to... (16 Replies)
Discussion started by: SteveB-in-LV
16 Replies

9. UNIX for Dummies Questions & Answers

Questions on AWK

Hi, Could someone tell me how to use a varible in the one of the sections of the awk command below which adjusts the spacing (i.e. "%-5s%-10s%-15s")? echo "1 2 3" | awk ' { printf("%-5s%-10s%-15s",$1,$2,$3); printf("\n"); } ' e.g. I have a variable named LENGTH which has the value 8 and I... (2 Replies)
Discussion started by: stevefox
2 Replies

10. UNIX for Dummies Questions & Answers

awk questions

I have the following script: set DAY=`date +%y%m%d` set H=`date +%H` set M=`date +%M` awk 'NR==1 {printf "MY HEADER WITH TIME STAMP $H \n"} {print $0}' data1> data2 my result is: MY HEADER WITH TIME STAMP $H ......data information.... ......data information.... How can I get... (1 Reply)
Discussion started by: bobo
1 Replies
Login or Register to Ask a Question