Login or Register to Ask a Question and Join Our Community


Shell Programming and Scripting

AWK or KSH : Sort, Group and extract from 3 files

Login to Discuss or Reply to this Discussion in Our Community

 
Thread Tools Search this Thread
Top Forums Shell Programming and Scripting AWK or KSH : Sort, Group and extract from 3 files
# 1  
Old 08-19-2009
AWK or KSH : Sort, Group and extract from 3 files
Hi,
I've the following two CSV files:

Code:
File1.csv                                      File2.csv
 
Class,Student#                             Student#,Marks
1001,6001                                   6002,50
1001,6002                                   6001,60
1002,7000                                   6003,20
1002,7001                                   8000,10
1002,7002                                   7000,30
1002,7003                                   7002,90
1003,8000                                   7001,50
                                            7003,20

Now, I need to extract the Students who got max marks in each Class and store it in File3.csv. For example, File3.csv should contain Class,Student# who got max marks, Marks.

Also, if there are more than 3 students in a class, then the second highest mark should be taken.

So, the final output should be

Code:
File3.csv:
  
Class,Student#,Marks
1001,6001,60    --> Max mark
1002,7001,50    --> Second highest
1003,8000,10

I am using AIX 5.3 version. The file sizes are huge. Please suggest on how to achieve this in either awk or ksh or sed. Thanks.
# 2  
Old 08-19-2009
This looks suspiciously like a homework which is against the rules.
Please show your own effort and indicate where you're having difficulties.
# 3  
Old 08-19-2009
The following code gives the max marks... I need to know on how to get second highest...

Code:
sort -t',' -k2 File1.csv -o File1.csv

sort -t',' -k1 File2.csv |\
join -t',' -e 'ZZZZ' -1 2 -2 1 -o 1.1 2.2 1.2 File1.csv - | sort |\
awk -F',' 'BEGIN {g=$1;v=$3} {if($1!=g){print g,v;} g=$1;v=$3} END {print g,v}'

Last edited by Matrix2682; 08-21-2009 at 06:00 PM..
# 4  
Old 08-21-2009
The code gives me max marks and its corresponding student#. But I am not sure on how to get the second highest mark, if there are more than 3 students in a class...

Code:
sort -t',' -k2 File1.csv -o File1.csv

sort -t',' -k1 File2.csv |\
join -t',' -e 'ZZZZ' -1 2 -2 1 -o 1.1 2.2 1.2 File1.csv - | sort |\
awk -F',' 'BEGIN {g=$1;v=$3} {if($1!=g){print g,v;} g=$1;v=$3} END {print g,v}'

Login or Register to Ask a Question

Previous Thread | Next Thread

10 More Discussions You Might Find Interesting

1. Shell Programming and Scripting

awk help or any other tool to sort between two files

Experts Good day, I want to sort two files f1 & f2 to matching with f1's first field with f2's 3rd field like to get in a result file : I tried with join but getting wrong result, I think there must be something with awk or other unix tool: cat f1 MYQCI63 srvcmi12 D7QDI ... (4 Replies)
Discussion started by: rveri
4 Replies

2. Shell Programming and Scripting

How to pick a group of data using awk/ksh

Hi gurus, I have data coming in as shown below. And in each case, I need to pick the data in the last group as shown below. Data Set 1: DC | 18161621 LA | 15730880 NY | 16143237 DC | 18161621 LA | 17316397 NY | 17915905 DC | 18161621 LA | 17993534 NY | 18161621 DC | 18161621... (11 Replies)
Discussion started by: calredd
11 Replies

3. Shell Programming and Scripting

Sort and extract based on two files

Hi, I am having trouble sorting one file based on another file. I tried the grep -f function and failed. Basically what I have is two files that look like this: File 1 (the list) gh aba for hmm File 2 ( the file that needs to be sorted) aba 2 4 6 7 for 2 4 7 4... (4 Replies)
Discussion started by: phil_heath
4 Replies

4. Shell Programming and Scripting

Extract data with awk and write to several files

Hi! I have one file with data that looks like this: 1 data data data data 2 data data data data 3 data data data data . . . 1 data data data data 2 data data data data 3 data data data data . . . I would like to have awk to write each block to a separate file, like this: 1... (3 Replies)
Discussion started by: LinWin
3 Replies

5. Shell Programming and Scripting

Sort and extract based on two files

Hi, I am having trouble sorting one file based on another file. I tried the grep -f function and failed. Basically what I have is two files that look like this: File 1 (the list) gh aba for hmm File 2 ( the file that needs to be sorted) aba 2 4 6 7 for 2 4 7 4 hmm 1 ... (3 Replies)
Discussion started by: phil_heath
3 Replies

6. Shell Programming and Scripting

Sort the file contents in each group....print the group title as well

I've this file and need to sort the data in each group File would look like this ... cat file1.txt Reason : ABC 12345-0023 32123-5400 32442-5333 Reason : DEF 42523-3453 23345-3311 Reason : HIJ 454553-0001 I would like to sort each group on the last 4 fileds and print them... (11 Replies)
Discussion started by: prash184u
11 Replies

7. Shell Programming and Scripting

Need awk to extract lines and sort

Hi, My data looks like this. CHR SNP BP A1 TEST NMISS OR STAT P 0 SNP_A-8282315 0 2 ADD 1530 1.074 0.7707 0.4409 0 SNP_A-8282315 0 2... (11 Replies)
Discussion started by: genehunter
11 Replies

8. Shell Programming and Scripting

awk - sort, then print the high value for each group

Hi @ all I'm trying to achive to this problem, I've a 2-column composed file as the following: 192.168.1.2 2 192.168.1.3 12 192.168.1.2 4 192.168.1.4 3 cpc1-swan1-2-3-cust123.swan.cable.ntl.com 4 192.168.1.3 5 192.168.1.2 10 192.168.1.4 8... (8 Replies)
Discussion started by: m4rco-
8 Replies

9. Shell Programming and Scripting

How to extract a column from two different files in AWK?

Hi guys, I need help in extracting one column of numbers from two different files and display it in a output file. In specific, I want to extrac the column no.2 ($2) from each file, file1.txt, file2.txt. Then place both extracted columns in a one file, out.txt. the line command I use to... (7 Replies)
Discussion started by: solracq
7 Replies

10. Shell Programming and Scripting

awk/sed/ksh script to cleanup /etc/group file

Many of my servers' /etc/group file have many userid's that does not exist in /etc/passwd file and they need to be deleted. This happened due to manual manipulation of /etc/passwd files. I need to do this for 40 servers. Can anyone help me in achieving this? Even reducing a step or two will be... (6 Replies)
Discussion started by: pdtak
6 Replies
Login or Register to Ask a Question