Shell – how to sort by the day of the week

dateshellsorttext processing

I have two files.

file1:

Dave 734.838.9800  
Bob 313.123.4567  
Carol 248.344.5576  
Mary 313.449.1390  
Ted 248.496.2204  
Alice 616.556.4458

file2:

Bob Tuesday  
Carol Monday  
Ted Sunday   
Alice Wednesday  
Dave Thursday    
Mary Saturday

I merged the two files.

file3 should look like this:

Name      On-Call     Phone  
Carol     MONDAY      248.344.5576  
Bob       TUESDAY     313.123.4567  
Alice     WEDNESDAY   616.556.4458  
Dave      THURSDAY    734.838.9800  
Nobody    FRIDAY      634.296.3356  
Mary      SATURDAY    313.449.1390  
Ted       SUNDAY      248.496.2204

But I cannot get the weekdays to be in order. How do I go about doing that?

Best Answer

Here's another way to do it (short version, no temporary files): 

{ printf %s\\n "Name On-Call Phone"; 
join -a1 -j2 -o 1.1 2.1 1.2 2.3 -e "Nobody" \
<(printf %s\\n '5 Friday' '1 Monday' '6 Saturday' '7 Sunday' '4 Thursday' '2 Tuesday' '3 Wednesday') \
<(join <(sort file2) <(sort file1) | sort -k2) | sort -k2n | sort -k1n | \
cut -d' ' -f 2-; } | column -t

If you absolutely need the names of the days in caps then:

{ printf %s\\n "Name On-Call Phone";  join -a1 -j2 -o 1.1 2.1 1.3 2.3 -e "Nobody" <(cat <<IN
5 Friday FRIDAY
1 Monday MONDAY
6 Saturday SATURDAY
7 Sunday SUNDAY
4 Thursday THURSDAY
2 Tuesday TUESDAY
3 Wednesday WEDNESDAY
IN
) <(join <(sort file2) <(sort file1) | sort -k2) | sort -k2n | sort -k1n | cut -d' ' -f 2-; } | column -t

Long version:
Let's say we have two files, file1:

Dave 734.838.9800
Bob 313.123.4567
Carol 248.344.5576
Mary 313.449.1390
Ted 248.496.2204
Alice 616.556.4458
Jimmy 324.555.8867
Harry 422.858.2354
Lou 788.907.6859

and file2:

Bob Tuesday
Carol Monday
Jimmy Wednesday
Ted Sunday
Alice Wednesday
Dave Thursday
Harry Monday
Mary Saturday
Lou Sunday

We create file3 with the following content:

1 Monday MONDAY
2 Tuesday TUESDAY
3 Wednesday WEDNESDAY
4 Thursday THURSDAY
5 Friday FRIDAY
6 Saturday SATURDAY
7 Sunday SUNDAY

and then run:

{ printf %s\\n "Name On-Call Phone"; \
join <(sort file2) <(sort file1) | sort -k2 | \
join -a1 -j2 -o 1.1 2.1 1.3 2.3 -e "Nobody" <(sort -k2 file3) - \
| sort -k1n | cut -d' ' -f 2-; } | column -t

or, in one line:

{ printf %s\\n "Name On-Call Phone"; join <(sort file2) <(sort file1) | sort -k2 | join -a1 -j2 -o 1.1 2.1 1.3 2.3 -e "Nobody" <(sort -k2 file3) - | sort -k1n | cut -d' ' -f 2-; } | column -t

Output:

Name    On-Call    Phone
Carol   MONDAY     248.344.5576
Harry   MONDAY     422.858.2354
Bob     TUESDAY    313.123.4567
Alice   WEDNESDAY  616.556.4458
Jimmy   WEDNESDAY  324.555.8867
Dave    THURSDAY   734.838.9800
Nobody  FRIDAY     Nobody
Mary    SATURDAY   313.449.1390
Lou     SUNDAY     788.907.6859
Ted     SUNDAY     248.496.2204

How it works:
join <(sort file2) <(sort file1) | sort -k2 - the first two files are joined based on second field then the output is sorted by second column:

Carol Monday 248.344.5576
Harry Monday 422.858.2354
Mary Saturday 313.449.1390
Ted Sunday 248.496.2204
Lou Sunday 788.907.6859
Dave Thursday 734.838.9800
Bob Tuesday 313.123.4567
Jimmy Wednesday 324.555.8867
Alice Wednesday 616.556.4458

this is piped to join -a1 -j2 -o 1.1 2.1 1.3 2.3 -e "Nobody" <(sort -k2 file3) - to join it with file3 based on second field; -a1 adds unmatched lines from file3 to the output and -e "Nobody" replaces missing output fields with "Nobody":

5 Nobody FRIDAY Nobody
1 Carol MONDAY 248.344.5576
1 Harry MONDAY 422.858.2354
6 Mary SATURDAY 313.449.1390
7 Ted SUNDAY 248.496.2204
7 Lou SUNDAY 788.907.6859
4 Dave THURSDAY 734.838.9800
2 Bob TUESDAY 313.123.4567
3 Jimmy WEDNESDAY 324.555.8867
3 Alice WEDNESDAY 616.556.4458

the result is piped again to sort -k1n | cut -d' ' -f 2- to numerically sort the output on 1st field and then remove the 1st field:

Carol MONDAY 248.344.5576
Harry MONDAY 422.858.2354
Bob TUESDAY 313.123.4567
Alice WEDNESDAY 616.556.4458
Jimmy WEDNESDAY 324.555.8867
Dave THURSDAY 734.838.9800
Nobody FRIDAY Nobody
Mary SATURDAY 313.449.1390
Lou SUNDAY 788.907.6859
Ted SUNDAY 248.496.2204

since this was grouped {...} with printf %s\\n "Name On-Call Phone" which prints the header, the entire output is then piped to column -t to prettify it.

You could skip one sort if file3 is already sorted on 2nd column (e.g. this time with a simple two-column file3):

5 Friday
1 Monday
6 Saturday
7 Sunday
4 Thursday
2 Tuesday
3 Wednesday

and also assign a phone number to "Nobody" e.g. sed 's/Nobody/888.000.8888/2':

{ printf %s\\n "Name On-Call Phone"; join <(sort file2) <(sort file1) | \
sort -k2 | join -a1 -j2 -o 1.1 2.1 1.2 2.3 -e "Nobody" file3 - | sort -k1n | \
cut -d' ' -f 2-; } | sed 's/Nobody/888.000.8888/2' | column -t

output:

Name    On-Call    Phone
Carol   Monday     248.344.5576
Harry   Monday     422.858.2354
Bob     Tuesday    313.123.4567
Alice   Wednesday  616.556.4458
Jimmy   Wednesday  324.555.8867
Dave    Thursday   734.838.9800
Nobody  Friday     888.000.8888
Mary    Saturday   313.449.1390
Lou     Sunday     788.907.6859
Ted     Sunday     248.496.2204
Related Question