Sorting elements of a CSV file

Question

I have a csv file with seven numbers per line like this:

1083,20,28,42,23,10,43
1084,20,5,29,59,40,33
1085,39,50,21,12,40,55
1086,45,4,6,23,10,2
1087,36,46,28,32,3,20

I want to keep the first number in place (column 1) and sort columns 2 to 7, making the file like

1083,10,20,23,28,42,43
1084,5,20,29,33,40,59
1085,12,21,39,40,50,55
1086,2,4,6,10,45,23
1087,3,20,28,32,36,46

How can I do that with awk, sed or whatever?

Thanks

Answer 1

The problem you are facing is you have files with MAC line endings (\r) whereas the commands you are running are assuming Unix line endings (\n).

Use Perl:

$ perl -l015 -F, -0015 -pae '$_ = join ",", shift @F, sort { $a <=> $b } @F' input.csv

Options:

• -l sets ORS to \r = octal 15.
• -0 sets RS to \r = octal 15.
• -p will loop thru your csv file on a per record basis. A record is split from the input file by the RS, which is \r as set by the -0 option.
• -F will set the field separator to comma for each record as it is read.
• -a will split each record as it is being read into fields and stored in array @F.
• Note: the order of supplying the options is important.

So the output that you will get shall also have MAC line endings. That is why you are NOT seeing your expected output, rather all your output records appear squashed into a single line based on all the solutions given here.

Answer 2

With perl:

perl -F, -lape '$_ = join ",", shift @F, sort {$a <=> $b} @F' < input.csv

With GNU awk:

gawk -F, '
  {
    split($0, a)
    printf "%s", a[1]
    delete a[1]
    n = asort(a)
    for (i = 1; i <= n ; i++) printf "%s", FS a[i]
    print ""
  }' < input.csv

Or using the join.awk library (thanks @WeijunZhou)

gawk -i join -F, -v OFS=, '
  {
    split($0, a)
    first = a[1]
    delete a[1]
    n = asort(a)
    print first, join(a, 1, n, OFS)
  }'

Answer 3

Tried with below method combination of awk and sed

j=awk '{print NR}' filename | sort -nr| sed -n '1p'

for ((i=1;i<=$j;i++)); do k=`awk -F "," -v  i="$i" 'NR==i {print $1}' i.txt` ;echo $k;awk -F "," -v i="$i" 'NR==i {$1=" ";print $0}' i.txt| sed -r "s/^\s+//g"| perl -pne "s/ /\n/g"| sort -n| perl -pne "s/\n/ /g";echo " "| sed "s/.*/&\n/g";done|sed '/^$/d'| sed "N;s/\n/ /g"| sed "s/ /,/g"| sed "s/\,*$//g"

output

1083,10,20,23,28,42,43
1084,5,20,29,33,40,59
1085,12,21,39,40,50,55
1086,2,4,6,10,23,45
1087,3,20,28,32,36,46

Answer 4

For simple csv without quoting, try this:

while IFS= read -r l; do
    col1=$(printf '%s' "$l" | cut -d, -f1)
    printf '%s,' "$col1"
    printf '%s' "$l" | cut -d, -f2- | grep -o "[0-9]*" | sort -n | paste -sd,
done < file