Sponsored Content
Top Forums Shell Programming and Scripting AWK or KSH : Sort, Group and extract from 3 files Post 302345503 by Matrix2682 on Wednesday 19th of August 2009 11:43:20 AM
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.
 

10 More Discussions You Might Find Interesting

1. 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

2. 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

3. 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

4. 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

5. 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

6. 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

7. 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

8. 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

9. 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

10. 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
LIBXO(3)						   BSD Library Functions Manual 						  LIBXO(3)

NAME
xo_emit -- emit formatted output based on format string and arguments LIBRARY
library ``libxo'' SYNOPSIS
#include <libxo/xo.h> LIBXO(3) BSD Library Functions Manual LIBXO(3) NAME
xo_open_list xo_open_list_h xo_open_list_hd xo_open_list_d xo_open_instance xo_open_instance_h xo_open_instance_hd xo_open_instance_d xo_close_instance xo_close_instance_h xo_close_instance_hd xo_close_instance_d xo_close_list xo_close_list_h xo_close_list_hd xo_close_list_d -- open and close lists and instances LIBRARY
library ``libxo'' SYNOPSIS
int xo_open_list_h(xo_handle_t *xop, const char *name); int xo_open_list(const char *name); int xo_open_list_hd(xo_handle_t *xop, const char *name); int xo_open_list_d(const char *name); int xo_open_instance_h(xo_handle_t *xop, const char *name); int xo_open_instance(const char *name); int xo_open_instance_hd(xo_handle_t *xop, const char *name); int xo_open_instance_d(const char *name); int xo_close_instance_h(xo_handle_t *xop, const char *name); int xo_close_instance(const char *name); int xo_close_instance_hd(xo_handle_t *xop); int xo_close_instance_d(void); int xo_close_list_h(xo_handle_t *xop, const char *name); int xo_close_list(const char *name); int xo_close_list_hd(xo_handle_t *xop); int xo_close_list_d(void); DESCRIPTION
Lists are sequences of instances of homogeneous data objects. Two distinct levels of calls are needed to represent them in our output styles. Calls must be made to open and close a list, and for each instance of data in that list, calls must be make to open and close that instance. The name given to all calls must be identical, and it is strongly suggested that the name be singular, not plural, as a matter of style and usage expectations. A list is a set of one or more instances that appear under the same parent. The instances contain details about a specific object. One can think of instances as objects or records. A call is needed to open and close the list, while a distinct call is needed to open and close each instance of the list: xo_open_list("item"); for (ip = list; ip->i_title; ip++) { xo_open_instance("item"); xo_emit("{L:Item} '{:name/%s}':0, ip->i_title); xo_close_instance("item"); } xo_close_list("item"); Getting the list and instance calls correct is critical to the proper generation of XML and JSON data. EXAMPLE: xo_open_list("user"); for (i = 0; i < num_users; i++) { xo_open_instance("user"); xo_emit("{k:name}:{:uid/%u}:{:gid/%u}:{:home}0, pw[i].pw_name, pw[i].pw_uid, pw[i].pw_gid, pw[i].pw_dir); xo_close_instance("user"); } xo_close_list("user"); TEXT: phil:1001:1001:/home/phil pallavi:1002:1002:/home/pallavi XML: <user> <name>phil</name> <uid>1001</uid> <gid>1001</gid> <home>/home/phil</home> </user> <user> <name>pallavi</name> <uid>1002</uid> <gid>1002</gid> <home>/home/pallavi</home> </user> JSON: user: [ { "name": "phil", "uid": 1001, "gid": 1001, "home": "/home/phil", }, { "name": "pallavi", "uid": 1002, "gid": 1002, "home": "/home/pallavi", } ] LEAF LISTS
In contrast to a list of instances, a "leaf list" is list of simple values. To emit a leaf list, call the xo_emit() function using the ""l"" modifier: for (ip = list; ip->i_title; ip++) { xo_emit("{Lwc:Item}{l:item}0, ip->i_title); } The name of the field must match the name of the leaf list. In JSON, leaf lists are rendered as arrays of values. In XML, they are rendered as multiple leaf elements. JSON: "item": "hammer", "nail" XML: <item>hammer</item> <item>nail</item> ADDITIONAL DOCUMENTATION
Complete documentation can be found on github: http://juniper.github.io/libxo/libxo-manual.html libxo lives on github as: https://github.com/Juniper/libxo The latest release of libxo is available at: https://github.com/Juniper/libxo/releases SEE ALSO
xo_emit(3) HISTORY
The libxo library was added in FreeBSD 11.0. AUTHOR
Phil Shafer BSD
December 4, 2014 BSD
All times are GMT -4. The time now is 02:45 AM.
Unix & Linux Forums Content Copyright 1993-2022. All Rights Reserved.
Privacy Policy