Order columns in CSV file

Question

I am trying to order all columns by column name in a CSV file. What I have is something like this:

name  ,adress  ,mobile-number
Ane   ,USA     ,12121212
Joane ,England ,234234

and the output I need is

adress ,name  ,mobile-number
USA    ,Ane   ,12121212
England,Joane ,234234

The problem is that I have more than three columns, and I don't know the order they come in, but I need to reorder them in ascending order.

Answer 1

Using Miller (mlr) to clean up the excessive whitespace in each field (including in the header), and then to reorder the fields:

$ mlr --csv clean-whitespace then reorder -f adress,name,mobile-number file
adress,name,mobile-number
USA,Ane,12121212
England,Joane,234234

(note that using reorder -f adress would move only the adress field to the start of the fields, which in this case would have the same effect.)

Or, if you want to order the fields in ascending order according to the way the headings sort:

$ mlr --csv clean-whitespace then sort-within-records file
adress,mobile-number,name
USA,12121212,Ane
England,234234,Joane

Answer 2

What we can do is transpose the columns to rows using the rs utility originating from BSD Unix, then just sort the rows (thus, effectively, sorting the columns of the original data), and then transpose again.

Firstly, note by the way that, in alphabetical order, it's: address, mobile-number, name. M is before N!

Step by step:

$ cat data
name  ,adress  ,mobile-number
Ane   ,USA     ,12121212
Joane ,England ,234234

$ rs -s, -C, -T < data
name  ,Ane   ,Joane ,
adress  ,USA     ,England ,
mobile-number,12121212,234234,

$ rs -s, -C, -T < data | sort
adress  ,USA     ,England ,
mobile-number,12121212,234234,
name  ,Ane   ,Joane ,

$ rs -s, -C, -T < data | sort | rs -s, -C, -T
adress  ,mobile-number,name  ,
USA     ,12121212,Ane   ,
England ,234234,Joane ,

$ rs -s, -C, -T < data | sort | rs -s, -S' ' -T
adress         mobile-number  name  
USA            12121212       Ane   
England        234234         Joane 

Finally:

$ rs -s, -C, -T < data | sort | rs -s, -S' ' -T | sed -e 's/ \([^ ]\)/,\1/g'
adress        ,mobile-number ,name  
USA           ,12121212      ,Ane   
England       ,234234        ,Joane 

I did this on Ubuntu GNU/Linux, and first had to sudo apt-get install rs.

Answer 3

Using Raku (formerly known as Perl_6)

~$ raku -MText::CSV -e 'my @dta = csv(in => $*IN, sep => ",", strict => True)>>.trim;   \
         my $index = @dta.[0].pairs.sort(*.value).map: *.key;           \
         @dta.=map( *.[$index.cache]); csv(in => @dta, out => $*OUT);'  file

Raku is a programming language in the Perl family that features high-level Unicode support. Above, the language is used at the command line in conjunction with Raku's Text::CSV module.

Sample Input:

name  ,adress  ,mobile-number
Ane   ,USA     ,12121212
Joane ,England ,234234

Sample Output:

adress,mobile-number,name
USA,12121212,Ane
England,234234,Joane

Briefly, data is read-in using the csv(...) method/function, whitespace is removed around entries using trim, and stored in @dta array. An $index is generated from the [0] first row of @dta by converting entries to numbered pairs, sorting by value, then getting the sorted numeric keys. Then @dta is .= over-written with $index re-ordered columns. Finally, a csv file is output on $*OUT stdout.

Note: removing the call to >>.trim will return double-quoted column-values where entries contain trailing whitespace, in accordance with RFC 4180. Reverse the column sort order by adding .reverse to the indexing code, as in [$index.cache.reverse].

Sample Output without >>.trim and with .reverse:

"name  ",mobile-number,"adress  "
"Ane   ",12121212,"USA     "
"Joane ",234234,"England "

https://github.com/Tux/CSV/blob/master/doc/Text-CSV.md
https://docs.raku.org
https://raku.org