Sorting file by a field, and then by another field.


 
Thread Tools Search this Thread
Top Forums Shell Programming and Scripting Sorting file by a field, and then by another field.
# 1  
Old 11-19-2009
Sorting file by a field, and then by another field.

Hi all,
Sorry the title is a mess, but did not find a better description at the time.
So here is my problem:
I have an input file:
HTML Code:
8:Mass40s --        0
48:Mass40s --        0
67:Mass40s --        0
86:Mass40s --        0
105:Mass40s --        0
9:Mass --        1
49:Mass --       86
68:Mass --       78
87:Mass --        0
106:Mass --        0
10:Mass Promo --      324
50:Mass Promo --     1077
69:Mass Promo --      871
88:Mass Promo --        0
107:Mass Promo --       19
How can I sort it first by name and then by number (like one does in excel for example).
I tried with
Code:
sort -t : -k2,1 input_file

and other combinations (k1,2;k2,2...etc) but it sorts either the first field (numeric) or the second one.

Note: I know the input file is exactly like I´m asking it to be, but this is a very small part of the input file where all the data are scrambled. So please take this in account.


Also if I have a input file like:
HTML Code:
10:Mass Promo --      324
50:Mass Promo --     1077
69:Mass Promo --      871
88:Mass Promo --        0
107:Mass Promo --       19
10:Mass Promo --      299
50:Mass Promo --     1008
69:Mass Promo --      733
88:Mass Promo --        0
107:Mass Promo --       29
Why the sort -n input_file command sorts by number and produce an output (for example):
HTML Code:
10:Mass Promo --      299
10:Mass Promo --      324
50:Mass Promo --     1008
50:Mass Promo --     1077
69:Mass Promo --      733
69:Mass Promo --      871
88:Mass Promo --        0
88:Mass Promo --        0
107:Mass Promo --       19
107:Mass Promo --       29
If you notice, you can see that it sorts by the first field and the third also, and I don't want that, I just one the first field sorted so that the upper line stays on top and not on below or bottom according to its value.

For this I tried
Code:
sort -n -t : k1,1 input_file

and
Code:
sort -t : k1,1 -n input_file

but none of them work.


So I hope you guys can shed some light on this issue as I need to make this easy to read and copy for my weekly reports.

I´m open to any suggestions whether in sort or awk commands.

Thanks and I hope I was clear enough.
# 2  
Old 11-19-2009
Hi, for your first question it depends on whether the numbers in the last column are part of the description. If they are this should work:
Code:
sort -t: -k2 -k1n infile

otherwise you would need something like this:
Code:
sed 's/:/ /' infile | sort -k2,3 -k1n |sed 's/ /:/' infile

For the second question that needs the original value intact for the other fields, this is called a stable sort. For GNU sort the option is -s:
Code:
sort -sn infile


Last edited by Scrutinizer; 11-19-2009 at 06:39 PM..
# 3  
Old 11-19-2009
Is this what you are looking for: -

Code:
sed 's/:/ /' infile | sort -k2,3 -k1n | sed 's/\(^[0-9]*\) /\1:/'

Output: -

Code:
9:Mass --        1
49:Mass --       86
68:Mass --       78
87:Mass --        0
106:Mass --        0
8:Mass40s --        0
48:Mass40s --        0
67:Mass40s --        0
86:Mass40s --        0
105:Mass40s --        0
10:Mass Promo --      324
50:Mass Promo --     1077
69:Mass Promo --      871
88:Mass Promo --        0
107:Mass Promo --       19

# 4  
Old 11-20-2009
Hi there,
Thanks for the answers provided, and it did helped me with the first question, so thanks Scrutinizer and Steadyonabix.
But for the second part of my questions, the stable sort option is an valid option on my server linux version, I think....
I tried:
Code:
sort -t: -k2 -k1n infile |sort -sn

and
Code:
sort -sn infile

But it does not sort the 1st field independently of the other....
If you know another way I would be grateful.
Anyway thanks for your help so far.
# 5  
Old 11-20-2009
So you wanted to sort the first column independently:
Code:
cut -d: -f1 infile |sort -n |paste -d: - infile |sed 's/:[^:]*//'

gawk 'BEGIN{OFS=FS=":"}{a[NR]=$1;b[NR]=$2}END{asort(a);for(i=1;i in a;++i)print a[i],b[i]}' infile

# 6  
Old 11-22-2009
With regard to the stable sort the simplest approach is to append a numeric key for the sort then remove it: -

Code:
[[ -a tmpfile ]] && rm tmpfile 

nawk -F":" ' { print $0"|"++_[$1] >> "tmpfile" } ' infileb 

sort -t"|" -k1n -k2n tmpfile | cut -d"|" -f1 > outfile 

cat outfile

Output: -

Code:
10:Mass Promo --      324
10:Mass Promo --      299
50:Mass Promo --     1077
50:Mass Promo --     1008
69:Mass Promo --      871
69:Mass Promo --      733
88:Mass Promo --        0
88:Mass Promo --        0
107:Mass Promo --       19
107:Mass Promo --       29

Login or Register to Ask a Question

Previous Thread | Next Thread

10 More Discussions You Might Find Interesting

1. Shell Programming and Scripting

Need to replace last field in a file,if first field matches

Hi, Need to replace last field in a file(/etc/passwd) ,if first filed matches with particular username. Scenario: cat testfor1 deekshi:x:7082:7082::/home/deekshi:/bin/bash harini1:x:7083:7083::/home/harini1:/bin/bash Here,if first field contains "deekshi", then i should replace... (4 Replies)
Discussion started by: Sumanthsv
4 Replies

2. Shell Programming and Scripting

Sorting file with CRLF within field, RS=$

OK below is what my sample file looks like. I need to sort by the Primary Key ie: {1:F01SAESVAV0AXXX0466020126} in the first record. Record seperator is $. I tried sort, but it completely messes it up. I am thinking I will need to use something like awk which understands the record seperator... (6 Replies)
Discussion started by: alfredo123
6 Replies

3. Shell Programming and Scripting

Command/script to match a field and print the next field of each line in a file.

Hello, I have a text file in the below format: Source Destination State Lag Status CQA02W2K12pl:D:\CAQA ... (10 Replies)
Discussion started by: pocodot
10 Replies

4. Linux

How do I format a Date field of a .CSV file with multiple commas in a string field?

I have a .CSV file (file.csv) whose data are all enclosed in double quotes. Sample format of the file is as below: column1,column2,column3,column4,column5,column6, column7, Column8, Column9, Column10 "12","B000QRIGJ4","4432","string with quotes, and with a comma, and colon: in... (3 Replies)
Discussion started by: dhruuv369
3 Replies

5. Shell Programming and Scripting

Plz Help. Compare 2 files field by field and get the output in another file.

Hi Freinds, I have 2 files . one is source.txt and second one is target.txt. I want to keep source.txt as baseline and compare target.txt. please find the data in 2 files and Expected output. Source.txt 1|HYD|NAG|TRA|34.5|1234 2|CHE|ESW|DES|36.5|134 3|BAN|MEH|TRA|33.5|234... (5 Replies)
Discussion started by: i150371485
5 Replies

6. Shell Programming and Scripting

Compare two files Field by field and output the result in another file

Hi Friends, Need Help. I have file1.txt as File1.txt |123|A|7267|Hyder|Cross|Sell|7801 |995|A|7051|2008|Lunar|New|Year|Promotion|7801 |996|A|7022|Q108|Targ|Prospect|&|SSCC|Savings|Promo|7801 |997|A|7182|Q1|Feb-Apr|08|Credit|ITA|PA|SBA|Campaign|7801 File2.txt... (7 Replies)
Discussion started by: i150371485
7 Replies

7. UNIX for Dummies Questions & Answers

Sorting data in file based on field in another file

Hi, I have two files, one of which I would like to sort based on the order of the data in the second. I would like to do this using a simple unix statement. My two files as follows: File 1: 12345 1 2 2 2 0 0 12349 0 0 2 2 1 2 12350 1 2 1 2 2 2 . . . File2: 12350... (3 Replies)
Discussion started by: kasan0
3 Replies

8. Shell Programming and Scripting

Append 1st field from a file into 2nd field of another file

Hi, I've internally searched through forums for about 2+ hours. Unfortunately, with no luck. Although I've found some cases close to mine below, but didn't help so much. Actually, I'm in short with time. So I had to post my case. Hoping that you can help. I have 2 files, FILE1 ... (1 Reply)
Discussion started by: amurib
1 Replies

9. Shell Programming and Scripting

Sorting on two fields time field and number field

Hi, I have a file that has data in it that says 00:01:48.233 1212 00:01:56.233 345 00:09:01.221 5678 00:12:23.321 93444 The file has more line than this but i just wanted to put in a snippet to ask how I would get the highest number with time stamp into another file. So from the above... (2 Replies)
Discussion started by: pat4519
2 Replies

10. Shell Programming and Scripting

Sorting on last field

Hello I am trying to sort on the last field and it seems to have lost ideas on what to do. the file am sorting looks like this Tan\da\1223 hey\1234 two\three\think\4579 i want to sort on the last fields (1223, 1234 and 4579). thank you (2 Replies)
Discussion started by: ganiel24
2 Replies
Login or Register to Ask a Question