double arraysort

[ternal]

Hi,

Recently I have had the need to do a sort and then do a second sort while the item of the first sort stays the same ( double sorting , first on column x then while column x is the same sort column y).

I did not put much efffort into error checking but so far I did not need it.

For my applications so far it works perfectly however if someone is willing I want to test this extensivly.

If anyone has big lists of random stuff to sort could you try this out please?

expandcollapse popup

#include <Array.au3>



; #FUNCTION# ====================================================================================================================
; Name ..........: _ArraySort_Double 
; Description ...: 
; Syntax ........: _ArraySort_Double (Byref $array[, $first_index = Default[, $second_index = Default[, $ascending = Default]]])
; Parameters ....: $array               - 2d array to sort.
;                  $first_index         - [optional] first column to sort. Default is 0.
;                  $second_index        - [optional] second column to sort. Default is 1.
;                  $ascending           - [optional] ascending/descending. Default is 1.
; Return values .: 1 if no errors occured , -1 if errors occured
; Author ........: Ternal
; Remarks .......:  Needs excessive testing.
; Related .......: _arraysort()
; ===============================================================================================================================
Func _ArraySort_Double (byref $array, $first_index = Default, $second_index = Default, $ascending = Default)
    Local $temp_value
    Local $counter = 1
    If UBound($array, $UBOUND_DIMENSIONS) <> 2 Then 
        MsgBox(0, "error", "error")
        return -1
    EndIf
    If $first_index = Default Then $first_index = 0
    If $second_index = Default Then $second_index = 1
    If $ascending = Default Then $ascending = 1
    _ArraySort($array, $ascending, 0, 0, $first_index); you can alter settings of primary sort here
    If @error Then 
        MsgBox(0, "error", @error)
        return -1
    EndIf
    $temp_value = $array[0][$first_index]
    For $x = 1 to UBound($array, 1) - 1
        If Mod( $x, 10000) = 0 Then ConsoleWrite("at " & $x & " of a total : " &  UBound($array, 1) & @CRLF)
        If $array[$x][$first_index] = $temp_value Then
            $counter+= 1
            If $x = UBound($array, 1) - 1 Then; do last line here(if last line is not a new item)
                _ArraySort($array, $ascending, $x - $counter, $x, $second_index);you can alter settings of secondary sort here(don't forget to place line 34 the exact same)
                If @error Then 
                    MsgBox(0, "error", @error)
                    return -1
                EndIf
            EndIf
        Else
            If $counter > 0 Then ;at least 2  of the same
                _ArraySort($array, $ascending, $x - $counter, $x - 1, $second_index);you can alter settings of secondary sort here(don't forget to place line 29 the exact same)
                If @error Then 
                    MsgBox(0, "error", @error)
                    return -1
                EndIf
                $counter = 1
            EndIf
        EndIf
        $temp_value =  $array[$x][$first_index]
    Next
    Return 1
EndFunc

Kind regards, Ternal

Edited August 9, 2018 by ternal

[jchd]

What you call "double sort" is in fact a sort based on collation invoking a compound comparison (several nested criterions).

[Melba23]

ternal,

You might find this thread of interest:

M23

[pixelsearch]

Hi Ternal,
For the record, I tested your function on... 10 rows. It worked fine, just a couple of things :

* All the sort went descending instead of ascending, probably due to the line :
If $ascending = Default Then $ascending = 1
But 1 means descending in _ArraySort(), not ascending. Anyway, no big deal.

* If $counter > 0 Then ;at least 2  of the same
    ...
  EndIf
Why this condition when $counter is always > 0 anywhere in the script ?
So I kept what was inside the If... EndIf, deleting these 2 If... Endif lines. Here  we go :
 

#include <Array.au3>
#include <MsgBoxConstants.au3>

Global $aArray[10][2] = _
  [[3, 33] , _
   [4, 43] , _
   [4, 41] , _
   [1, 11] , _
   [3, 31] , _
   [2, 22] , _
   [4, 44] , _
   [3, 32] , _
   [4, 42] , _
   [2, 21]]

_ArrayDisplay($aArray, "Before Sort")
MsgBox($MB_SYSTEMMODAL, "Result", _ArraySort_Double($aArray))

Here are the results, after adding a couple of _ArrayDisplay in your function :

 

The "Finished" _ArrayDisplay shows how your double sorting script worked fine, bravo.
Hope you'll find a tester with an Array of thousand of lines, as your line If Mod( $x, 10000) is waiting to be executed.

Good luck 
 

Edited August 11, 2018 by pixelsearch

[ternal]

 

@pixelsearch  the counter DOES go up.. $counter += 1

I have tested this on arrays of 50k rows .. the mod function works fine. 

$counter should be resetted to 0 I will look into this more

@melba I will look into your script later today. It seems so much more proffesional

 

I added a .rar file with a 10k+ csv file.

 

Something I forgot to mention this is specifically meant to run on big lists only (50K to 150k)

I am scared I did not manage the secondary sort properly but this can place 1 line completly out of place and searching through the entire array is my issue xD

 

However guys I will look into melba's script first

user.rar

Edited August 10, 2018 by ternal

[junkew]

If you have doubt on sorting with 2 columns just add a column with concatenated values and sort on that one.

[ternal]

@junkew ok but does that work when one column are integers and the other alphanumerical? I did a similar thing with 4 columns of int's but I thought when mixing columns with alfanumerical values it would not work. Never tested it out tbh

[jchd]

@junkew

Concatenating columns doesn't work in the general case, even with text-only type. It's even worse when any column is numerical type.
For instance consider these few rows, to be sorted on Col1 ascending then Col2 ascending:

Col1    Col2    Col1&Col2   Rank (Col1&Col2)    Rank (expected)
===============================================================
abcx    def     abcxdef         1                   4
abc     yz      abcyz           2                   3
a       c       ac              4                   1
ab      z       abz             3                   2

Concatenation only works with fixed-size text columns, i.e. a very small subset of real-world cases.

[junkew]

So you could pad your numeric fields and text fields to a fixed right aligned value. I am not saying a perfect solution but if you cannot use udf or .net solutions its a tradeoff between speed complexity maintainability understandability ...