Ordering array inside a cell

[aborruso]

Hi,
I have this CSV

a,b
1,"1,12B,12,12A,3"
2,"1,2,10,1A"

and I would like to order the arrays in b to obtain

a,b
1,"1,3,12,12A,12B"
2,"1,1A,2,10"

These arrays are both text and number. And to do it I run this command:

<input.txt mlr --csv nest --explode --values --across-records -f b --nested-fs "," then \
 put -S '$bN=int(regextract($b,"[0-9]+"));$bT=regextract($b,"[a-zA-Z]+")' then \
 unsparsify then \
 sort -f a -n bN -f bT then \
 cut -x -f bN,bT then nest --implode --values --across-records -f b --nested-fs ","

Now I know that in Miller 6 we have arrays and we have the great DSL. And I would like to learn how to do it using array functions.
I have read this documentation page, but I'm not able to create a right script.
I would apply regextract function in a sorting for loop.

Do you have some tips for me?

Thank you

[aborruso]

@johnkerl is there a way to apply "natural sorting"?

If I have

15
1
2/AX
22
1/C
1/A
1/BA
2
3

I can run <input.txt sort -V to have

1
1/A
1/BA
1/C
2
2/AX
3
15
22

It would be great to have natural sort option both in sort verb and in DSL array function.

[aborruso]

Using the shell, and starting from

a,b
1,"15,1,2/AX,22,1/C,1/A,1/BA,2,3"

I can use the great system function and run

<input.txt mlr --c2p --barred cat  then put -S '$toto=system("echo ".$b." | tr , \"\n\" | sort -V | paste -sd, -")'

to have

+---+-------------------------------+-------------------------------+
| a | b                             | toto                          |
+---+-------------------------------+-------------------------------+
| 1 | 15,1,2/AX,22,1/C,1/A,1/BA,2,3 | 1,1/A,1/BA,1/C,2,2/AX,3,15,22 |
+---+-------------------------------+-------------------------------+

[johnkerl]

@aborruso if I understand correctly there are two separate questions here.

First issue

The first is how to sort within a field -- https://miller.readthedocs.io/en/latest/sorting/#custom-sorting-of-arrays-within-records is indeed the right place -- perhaps it could be more clear. The examples there I showed were multi-line, but you could also do this on one line like this:

$ cat input.csv
a,b
1,"1,12B,12,12A,3"
2,"1,2,10,1A"

$ mlr --csv --from input.csv put '$b=joinv(sort(splita($b, ",")),",")'
a,b
1,"1,3,12,12A,12B"
2,"1,2,10,1A"

That is, I think, much simpler than nest with explode and implode. Note that this uses Miller's default sorting rule which is (among other things) numbers before strings. https://miller.readthedocs.io/en/latest/sorting/#custom-sorting-of-arrays-within-records could be more clear about this rule.

Second issue

The second issue is how to specify the rule by which Miller does its sorting. To be honest, I'm embarrassed to admit that this is the first I've heard of "natural sort order" -- I had to google it just now. Miller absolutely does not (yet) do this. However, now that I know about it, I like it very much and it should be Miller's default. It looks like there are good resources for this & it should be pretty quick to code up.

Thanks for teaching me about this! :)

[aborruso]

First issue

The first is how to sort within a field -- https://miller.readthedocs.io/en/latest/sorting/#custom-sorting-of-arrays-within-records is indeed the right place -- perhaps it could be more clear. The examples there I showed were multi-line, but you could also do this on one line like this:

$ cat input.csv
a,b
1,"1,12B,12,12A,3"
2,"1,2,10,1A"

$ mlr --csv --from input.csv put '$b=joinv(sort(splita($b, ",")),",")'
a,b
1,"1,3,12,12A,12B"
2,"1,2,10,1A"

That is, I think, much simpler than nest with explode and implode. Note that this uses Miller's default sorting rule which is (among other things) numbers before strings. https://miller.readthedocs.io/en/latest/sorting/#custom-sorting-of-arrays-within-records could be more clear about this rule.

It's much simpler than nest with explode and implode, but I do in this way to be able to sort differently numbers and text: first I explode, then split in two columns numbers and text, then I apply the sorting and then I implode it again.
Because I do not want this order 1,2,10,1A, but this one 1,1A,2,10 (1A must be the second).

Second issue

The second issue is how to specify the rule by which Miller does its sorting. To be honest, I'm embarrassed to admit that this is the first I've heard of "natural sort order" -- I had to google it just now. Miller absolutely does not (yet) do this. However, now that I know about it, I like it very much and it should be Miller's default. It looks like there are good resources for this & it should be pretty quick to code up.

Thanks for teaching me about this! :)

Thank you, it would be great to have it in Miller.

Also I only discovered yesterday of the natural sorting, but as soon as I saw it exists in Linux sort, I thought that then it has already existed for many years and I felt stupid :)

Linux sort is really interesting because it has several kinds of sort:

• echo "15,1r,1/AR,2/AX,22,1/C,1/A,1/BA,2,3,a,SNC" | tr , "\n" | sort -V | paste -sd, - gives to me

1r,1/A,1/AR,1/BA,1/C,2,2/AX,3,15,22,SNC,a

• echo "15,1r,1/AR,2/AX,22,1/C,1/A,1/BA,2,3,a,SNC" | tr , "\n" | sort -n | paste -sd, - gives to me

a,SNC,1/A,1/AR,1/BA,1/C,1r,2,2/AX,3,15,22

• echo "15,1r,1/AR,2/AX,22,1/C,1/A,1/BA,2,3,a,SNC" | tr , "\n" | sort -h | paste -sd, - gives to me

a,SNC,1/A,1/AR,1/BA,1/C,1r,2,2/AX,3,15,22

[aborruso]

@johnkerl I'm adding two URLs:

• https://www.gnu.org/software/coreutils/manual/coreutils.html#Version-sort-ordering (search "natural sort");
• a great python module and a cli to run natural sorting https://natsort.readthedocs.io/en/master/index.html

[aborruso]

Using natsort cli, the "locale" option (a sort of human and non ASCII sorting):

echo "SNC-1-10-1r-1/A-2/10-10/1A2-10/A-1000/C1-a" | tr - "\n" | natsort -l  | paste -sd- -

gives me

1-1/A-1r-2/10-10-10/1A2-10/A-1000/C1-a-SNC