ArrayMultiColSort -New Release 06 April 2019

[jchd]

To sort numeric data you need to store data in numeric format in the column. Collation as text yields "12" < "9" just because of lexicographic order used ("1" < "9"), but gives 12 > 9 since the comparison is now made in integers (also works for negative values).

The syntax shows correctly after page refresh.

[Melba23]

Funtime60,

What jchd said!

So either store the elements as numbers, or if they are read in from a file as strings, you will need to loop through them using Number to convert them:

#include <Array.au3>
#include <ArrayMultiColSort.au3>

Global $testfilearray1[3][3] = [["123", "321", "-1"], ["123", "-31", "13"], ["123", "-321", "-12"]]

For $i = 0 To UBound($testfilearray1, 1) - 1
    For $j = 0 To UBound($testfilearray1, 2) - 1
        $testfilearray1[$i][$j] = Number($testfilearray1[$i][$j])
    Next
Next

Global $aSortData[][] = [[1, 0], [2, 0]]

_ArrayMultiColSort($testfilearray1, $aSortData)

_ArrayDisplay($testfilearray1, "", Default, 8)

M23

[Funtime60]

My apologies, I failed to notice that my posts were the last ones on the page so my responses were delayed.

Thank you for the clarification jchd, I suspected as much.

At least my hours spent generating my padding thing (I'm still unsure of the proper name) aren't entirely in vain as It has effectively set up a method of looping through only the columns to be sorted.

[Funtime60]

Melba23

Not to criticize, but wouldn't it be more efficient to only make the columns that are to be sorted into number format? repurposing this section of code from my padding thing should do just that. Is there anything inherently wrong doing it this way that  should Know about?

This has the same requirements as my original padding thing on the previous page.

#Region Init
Local $testfilearray1[3][3] = [["123", "321", "-1"], ["123", "-31", "13"], ["123", "-321", "-12"]]
Local $testsize = UBound($testfilearray1) - 1
Global $aSortData[][] = [[1, 0], [2, 0]]
#EndRegion Init


#Region Make Numb
Local $sorttotalcount = UBound($aSortData)-1
For $sortcount = 0 To $sorttotalcount Step 1
    Local $colnum  = $aSortData[$sortcount][0]
    For $testfilerownum2 = 0 To $testsize Step 1
        $testfilearray1[$testfilerownum2][$colnum] = Number($testfilearray1[$testfilerownum2][$colnum])
    Next
Next
_ArrayDisplay($testfilearray1, "$testfilearray1")
#EndRegion

[Melba23]

Funtime60,

Quote

wouldn't it be more efficient to only make the columns that are to be sorted into number format?

Of course - in fact if you try to convert text string elements into numbers you will destroy them. I only looped through the whole array in my example because all columns in your original example were numeric.

M23

[Adele]

I have simply an array like that:

2 2
2 1
2 3
1 2
1 1
1 3

This is what i want:

1 1
1 2
1 3
2 1
2 2
2 3

I tried lots of codes above, nothing worked.

What my sort data should be? @Melba23

Edited December 29, 2018 by Adele

[Melba23]

Adele,

The secret is to get the sort data array correctly formatted:

#include "ArrayMultiColSort.au3"

Global $aArray[][] = [[2, 2], [2, 1], [2, 3], [1, 2], [1, 1], [1, 3]]

; Here is the original array
_ArrayDisplay($aArray, "Original", Default, 8)

; Set the order in which the columns are to be sorted and how to sort them
; First we sort col 0 in ascending order and then col 1 also in ascending order
Global $aSortData[][] = [[0, 0], [1, 0]]

; Sort the array
_ArrayMultiColSort($aArray, $aSortData)

; And here is the sorted one
_ArrayDisplay($aArray, "Sorted", Default, 8)

All clear now?

M23

[maniootek]

No condition to check if column index stored in $aSortData is out of the array to be sorted range?

[Melba23]

maniootek,

No - neither do I check if the user brushed their teeth last night or is wearing clean(ish) underwear.

There is a fine line between making UDFs idiot-proof (which is evidently impossible) by adding significant error-checking code, which both inflates and complicates the UDF, and leaving some things to the intelligence of the user. In this case I felt that as the user required specific columns to be sorted it was likely that they would correctly enter the column numbers in the array - which I take it you did not and so got an error. I will take a look and see how difficult it would be to check the indices in $aSortData - but do not hold your breath.

M23

[Melba23]

maniootek,

An amended UDF with a quick sanity check of the columns to be sorted:

Please let me know what you think.

M23

Edited May 10, 2020 by Melba23
Beta code removed

[maniootek]

Now it's easier to debug the script if somehow I pass wrong column index (which happened to me for the first time I used your UDF)

Thank you @Melba23

Edited April 5, 2019 by maniootek

[Melba23]

maniootek,

Great - I will release a new version this weekend.

M23

[Melba23]

[New Release] - 06 April 2019

Added: Error-checking for sensible column numbers in the $aSortData array, with an additional error status.

New code and zip in the first post.

M23

[Thopaga]

Hi Melba23.
Good work.
I also made one long ago but updated it recently just for fun.
I guess there are many more versions in the forum.
Have a nice day.

 

 

 

 

 

[pixelsearch]

@Melba23: that's a useful UDF and it's fast too 

I just tested it to sort a column containing digits & letters, maybe I should have done it differently but it worked :

expandcollapse popup

#include "ArrayMultiColSort.au3" ; Melba23's UDF
#include <Array.au3>
#include <File.au3>
#include <MsgBoxConstants.au3>

Local $aArray, $aArrayRegExp
$sFilePath = @ScriptDir & "\ArrayMultiColSort - personal example.csv"
_FileReadToArray($sFilePath, $aArray, $FRTA_NOCOUNT, ",")
If @error Then
    MsgBox($MB_TOPMOST, "_FileReadToArray", "@error = " & @error)
    Exit
EndIf

_ArrayDisplay($aArray, "1 = before multisort")

Local $iDim_Rows = UBound($aArray, $UBOUND_ROWS)
Local $iDim_Cols = UBound($aArray, $UBOUND_COLUMNS)
Redim $aArray[$iDim_Rows][$iDim_Cols + 2] ; add 2 temporary columns from the right

For $iRow = 0 To $iDim_Rows - 1
    $aArrayRegExp = StringRegExp($aArray[$iRow][0], '^\d+', $STR_REGEXPARRAYGLOBALMATCH)
    If Not @error Then ; $aArrayRegExp is valid
        $aArray[$iRow][$iDim_Cols] = Number($aArrayRegExp[0])
        $aArray[$iRow][$iDim_Cols + 1] = StringMid($aArray[$iRow][0], StringLen($aArrayRegExp[0]) + 1)
    Else
        $aArray[$iRow][$iDim_Cols] = "A"
        $aArray[$iRow][$iDim_Cols + 1] = $aArray[$iRow][0]
    EndIf
Next

; Sort on both temporary columns, ascending
Local $aSortData[][] = [ _
    [$iDim_Cols, 0], _
    [$iDim_Cols + 1, 0]]
_ArrayMultiColSort($aArray, $aSortData, 1) ; 3rd par = 1 (index to start from) as index 0 = header

Redim $aArray[$iDim_Rows][$iDim_Cols] ; delete 2 temporary columns from the right

_ArrayDisplay($aArray, "2 = after multisort")

As you can see in the script, 2 temporary columns were added from the right of the Array :
* First temporary column : populated with the digits part (or an "A" in case no digits are found)
* Second temporary column : contains the rest of the string.

Then your UDF sorts these 2 temporary columns (ascending) before they got deleted, they did the job
We notice that 51, 3526, 6001 are correctly sorted and they are followed by their corresponding string part (which is also correctly sorted compared to the original). Also "JANETTE" and "FAWN" which had no digits at all are now correctly placed at the end of the Array (due to the "A" while sorting)

In fact, I need to do this in a Listview when the user needs to sort this kind of "mixed" column. The problem is that it seems more complicated to sort that way in a listview which is already populated, because it requires to use GUICtrlListView_RegisterSortCallBack() and its 2nd parameter which is very slow for sorting when equal to 2 (Use Windows API StrCmpLogical : better for "x1y" < "x10y")

I wonder if there is another alternative for using your UDF in listview to sort this kind of column, because I can't delete all "native created" items one by one (it will take too much time) just to reuse their contents in an Array, do exactly what we saw in the script above, then repopulate the listview... it will be too much time consuming, depending on the number of items/columns.

Edited January 5, 2020 by pixelsearch
Changed '\d+' to '^\d+'

[Melba23]

pixelsearch,

If you use my GUIListViewEx UDF to manage the ListView then you can use the _GUIListViewEx_UserSort function to allow columns to be sorted using any function you require. That might well be a way to do what you wish - although it does mean that you have to use the GUIListViewEx UDF to do all ListView manipulation that exists within your script, which may mean too much of a rewrite for just this one advantage.

M23

[pixelsearch]

Thanks Melba23, I will consider all options.
Anyway, I'm glad that I discovered your solid ArrayMultiColSort UDF, it is useful and will be re-used at 1st occasion

Edit : why isn't ArrayMultiColSort.au3 included into Array.au3 ?
I mean, who decides to add (or not) an UDF to the next official AutoIt release ?
imho, it should be added because it is useful, reliable, fast & small (8kb)

Edited December 27, 2019 by pixelsearch

[pixelsearch]

Hi Melba23,
I achieved my goal by adding your function to the "CSV file editor" script. Natural sort uses it and it works fine now, with good speed. Using your function, I even could do what follows :

We notice that a subitem had focus before the sort... and it's still focused after the sort. It may be not an extraordinary feature but it could be handy, especially on LV having plenty of rows (or if you simply would like the cell to keep focus).

I tried to script it first by using $iParam ("a value to associate with the item") now that my items are not any more "native created". But I couldn't do it that way, then I tried with your ArrayMultiColSort() function and could do it, while keeping your function untouched. So thanks for that too

Edit1: Just checked the help file, topics _GUICtrlListView_*
It seems that it also could be done this way :

Local $iItemID = _GUICtrlListView_MapIndexToID($g_idListview, $g_iItem)
ConsoleWrite("$iItemID = " & $iItemID & @lf) ; selected cell in this item (before sort)

; sort function here...

Local $iItemAfterSort = _GUICtrlListView_MapIDToIndex($g_idListview, $iItemID)
ConsoleWrite("$iItemAfterSort = " & $iItemAfterSort & @lf) ; item to redraw (after sort)

Gonna confirm it now, though it already seems very promising.
Thank you, help file

Edit2: tests made and both ways are needed to have the same cell selected when the sort is done.
1) Numeric & String sorts :
MapIndexToID & MapIDToIndex are ok because both Numeric & String sorts send an LVM_SORTITEMSEX message, which takes care of "changing the index of each item to reflect the new sequence". In this case MapIndexToID & MapIDToIndex are reliable.

2) Natural sort :
ArrayMultiColSort() does the job, because extracting the data from LV in an array (splitting 1 column in 2), then multi sorting this array on 2 columns, then updating each and every item/subitem text in LV doesn't involve Windows sort process at all, which means the index of each item didn't change (though LV is fully 'sorted') so MapIndexToID & MapIDToIndex aren't reliable to know where now is placed the item that had one cell selected before the sort...

Edited January 12, 2020 by pixelsearch

[mistersquirrle]

@Melba23, great work as always. Just wanted to say thanks, and share a small modification that I made, for of course my own uses.

 

I have a file list that is in a format like "prefix_filename.ext". I then have it in a 2D array like:

; [0][0] = File name
; [0][1] = Occurances of file in a search
Local $aFiles[][] = [ _
        ['pick_Rough.txt', 2], _
        ['pick_All.txt', 5], _
        ['pick_Fine.txt', 1] _
        ['choose_Rough.txt', 1] _
        ['choose_Smooth.txt', 1] _
        ['all_All.txt', 8] _
        ['all_Fine.txt', 1] _
        ]

I wanted to be able to sort by the occurrence count ([[1, 1]]), and then by a couple specific prefixes, such as the most common ('pick_'), then the most general ('all_') so that when I'm looping through the array later to find occurrences of the files, the most common ones are searched first. Your default for the string sort means that I would have had to put the full file name in an ordered list, which I didn't want to do.

I edited the __AMCS_SortChunk function line 164 from:

If $aArray[$i][$iColumn] = $aSortOrder[$j] Then

-->

If StringInStr($aArray[$i][$iColumn], $aSortOrder[$j]) > 0 Then

This way, the string sorting only needs to contain a part of what you pass in the aSortData to be sorted, so I could pass it like: 

$aSortData[][] = [[1, 1], [0, $sSort & ',all_']]

Where $sSort would be the most common/recent (like 'pick_'), followed up by the most general, like 'all_' for the file prefixes. This for me works great, and now I can sort by the most found, then by specific file prefixes.

 

Hope that all makes sense. I know that I could also do StringInStr = 1 to make sure that it only starts with the prefix as well, but any thoughts on this approach?

[Melba23]

mistersquirrle,

A sensible suggestion which could have a wider application. Please try this Beta version of the UDF and see if it meets with your requirements. You will need to use a new parameter which determines if a full or partial match is required when using strings to sort the column - the function header explains in detail:

 

I am still looking into the possibility of widening the "match" options to cater for the partial string at the beginning or end of the element - however this would mean using some RegExes to compare the strings, and they always make my brain bleed!

M23

Edited June 14, 2020 by Melba23
Beta code removed