1

I have the following column headers:

EntryDate,HH_ID,HH_type,ID#,Age,First,Last,Gender,Race,Ethnicity,CaseWorkerName

There is about 2000 rows of data that much match up.

I would like to sort by CaseWorkerName and have all the values in the other columns line up.

What is the best way to achieve this?

2
  • 1
    sort is usually the best option to sort columnated input... Jan 13 '17 at 21:40
  • Does the file contain the header on the first line? Should that be part of the output? Is the data numeric (integer or floats?), or text?
    – Kusalananda
    Jan 13 '17 at 21:45
6

The CaseWorkerName is column 11. The sort utility may be told what column to sort by, and what delimiter to use when specifying columns:

$ sort -t ',' -k11,11 data.in

This will tell sort to use commas as delimiters and sort in ascending lexicographical order based column 11 to 11 (i.e. only column 11).

The output is written to the console. If you want to store the output in another file, use

$ sort -t ',' -k11,11 -o data.out data.in

It is ok to specify data.in as output file if you use the -o flag to do it.

If you wish to avoid sorting the first line (it may contain the column headers), then we must first separate the header from the data:

$ head -n 1 data.in >data.header
$ sed '1d' data.in  >data.unsorted

Then sort and reunite the sorted data with the header:

$ sort -t ',' -k1,11 -o data.sorted data.unsorted
$ cat data.header data.sorted >data.out
$ rm data.sorted data.header data.unsorted

Or, shorter

$ sed '1d' data.in  | sort -t ',' -k11,11 -o data.sorted
$ head -n 1 data.in | cat - data.sorted >data.out
$ rm data.sorted

If you're on a Linux system using GNU coreutils, this may be made even shorter,

$ ( head -n 1; sort -t ',' -k11,11 ) <data.in >data.out

With the GNU coreutils implementation of head, the standard input to the subprocess ((...)) will be consumed first by head while any remaining data is given to sort. The output from the subprocess will be the output from head followed by the output from sort.

On other systems, head might consume more than expected from the standard input stream, which will give sort nothing (or at least not the rest of the file) to work with. This is the case on at least OpenBSD.

The result is in data.out after each example above.

Note, all of these approaches will fail if the data in any column contains a comma...

6
  • @don_crissti This works only with GNU coreutils. On my system, this will only return the header row. Will add it with a comment.
    – Kusalananda
    Jan 14 '17 at 10:19
  • @don_crissti I'll see what I can do and what thoughts the OpenBSD people have about this.
    – Kusalananda
    Jan 14 '17 at 12:20
  • @don_crissti There's something else that is wrong from what I first thought though. (head -n 1; tail -n 1) <file does the right thing, but (head -n 1; sort) <file does not...
    – Kusalananda
    Jan 14 '17 at 12:26
  • I'm just curious (as I'd like to ask a Q but I only have access to linux setups) if you don't mind (and if/when you have the time)... If you run the following on your BSD setup: printf %s\\n {1..4} >infile and then { grep -q 2; cat; } <infile does it print the last two lines (3 and 4) ? Jan 25 '17 at 13:47
  • 1
    Thank you ! And sorry for bothering you with such requests. I don't get any output with that second command either although per the standards I think I should ? I'll post a question about this in a few minutes... Jan 25 '17 at 13:53

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.