sort arrays on multiple columns

[gcue]

hello world

i am using UEZ's function to sort arrays on multiple columns.  one thing i think it is lacking however is the option to choose at which index to start at.  sometimes arrays start at 0 but sometimes they start at 1.  headings are sometimes at 0 which throws the sort off quite a bit.  any chance this function can be modified to add the option to select which index to start at?  hope its easy.

expandcollapse popup

#include <array.au3>
$max = 50
$s = ""
Dim $array[$max][4]
for $i = 0 to $max - 1
    $array[$i][0] = Chr(Random(65, 67, 1)) & Chr(Random(65, 67, 1)) & Chr(Random(65, 67, 1)) & Chr(Random(65, 67, 1))
    For $j = 0 To 2
        If Random(0, 1, 1) Then $s &= Chr(Random(48, 57, 1))
    Next
    If $s = "" Then $s = Chr(Random(65, 67, 1))
    $array[$i][1] =  $s
    $array[$i][2] = Random(1, 10, 1)
    $m = Random(1, 12, 1)
    $d = Random(1, 31, 1)
    If $m < 10 Then $m = "0" & $m
    If $d < 10 Then $d = "0" & $d
    $array[$i][3] = Random(1900, 2010, 1) &  "-" & $m & "-" & $d
    $s = ""
Next
_ArrayDisplay($array, 'Before:')
Dim $aI[3]
;order of sorting rows - here row 0,3,2
$aI[0] = 0
$aI[1] = 3
$aI[2] = 2

_ArraySort_MultiColumn($array, $aI)
_ArrayDisplay($array, 'After:')

; #FUNCTION# =============================================================================
; Name.............:    _ArraySort_MultiColumn
; Description ...:      sorts an array at given colums (multi colum sort)
; Syntax...........:    _ArraySort_MultiColumn(ByRef $aSort, ByRef $aIndices)
; Parameters ...:       $aSort - array to sort
;                       $aIndices - array with colum indices which should be sorted in specified order - zero based
;                       $oDir/$iDir - sort direction - if set to 1, sort descendingly else ascendingly
; Author .........:     UEZ
; Version ........:     v0.70 build 2013-11-20 Beta
; =========================================================================================
Func _ArraySort_MultiColumn(ByRef $aSort, ByRef $aIndices, $oDir = 0, $iDir = 0)
    If Not IsArray($aIndices) Or Not IsArray($aSort) Then Return SetError(1, 0, 0) ;checks if $aIndices is an array
    If UBound($aIndices) > UBound($aSort, 2) Then Return SetError(2, 0, 0) ;check if $aIndices array is greater the $aSort array
    Local $1st, $2nd, $x, $j, $k, $l = 0
    For $x = 0 To UBound($aIndices) - 1 ;check if array content makes sense
        If Not IsInt($aIndices[$x]) Then Return SetError(3, 0, 0) ;array content is not numeric
    Next
    If UBound($aIndices) = 1 Then Return _ArraySort($aSort, $oDir, 0, 0, $aIndices[0]) ;check if only one index is given
    _ArraySort($aSort, $oDir, 0, 0, $aIndices[0])
    Do
        $1st = $aIndices[$l]
        $2nd = $aIndices[$l + 1]
        $j = 0
        $k = 1
        While $k < UBound($aSort)
            If $aSort[$j][$1st] <> $aSort[$k][$1st] Then
                If $k - $j > 1  Then
                    _ArraySort($aSort, $iDir , $j, $k - 1, $2nd)
                    $j = $k
                Else
                    $j = $k
                EndIf
            EndIf
            $k += 1
        WEnd
        If $k - $j > 1 Then _ArraySort($aSort, $iDir, $j, $k, $2nd)
        $l += 1
    Until $l = UBound($aIndices) - 1
    Return 1
EndFunc

 thanks for your help 

[Subz]

Maybe something like

Func _ArraySort_MultiColumn(ByRef $aSort, ByRef $aIndices, $oDir = 0, $iDir = 0, $iBase = 0)
    If $iBase > 0 Then _ArrayDelete($aSort, 0)
    ....
    ...
    ..
    .
    If $iBase > 0 Then _ArrayInsert($aSort, 0, UBound($aSort) - 1)
    Return 1
EndFunc

[Nine]

You could simple save row 0 then

use _ArrayDelete (...) to remove the row.

do your multi cols sort.

and use _ArrayInsert (...) to put back row 0 

[jchd]

We definitely need an _ArrayQSort taking a collation function as parameter, just like qsort() in C.
I'll make one some day if it doesn't exist yet somewhere.

[gcue]

55 minutes ago, Subz said:

Maybe something like

Func _ArraySort_MultiColumn(ByRef $aSort, ByRef $aIndices, $oDir = 0, $iDir = 0, $iBase = 0)
    If $iBase > 0 Then _ArrayDelete($aSort, 0)
    ....
    ...
    ..
    .
    If $iBase > 0 Then _ArrayInsert($aSort, 0, UBound($aSort) - 1)
    Return 1
EndFunc

thanks that worked!

however there's something weird going on - when specifying a base I would have to include specifying the other options before it

$oDir = 0, $iDir = 0

when i specify this it doesnt work

Quote

 

Local $sort_by_columns[2] = [1, 0]

_ArraySort_MultiColumn($post_array, $sort_by_columns, 0, 0, 0)

 

 however when i specify like this it works

Quote

Local $sort_by_columns[2] = [1, 0]

 _ArraySort_MultiColumn($post_array, $sort_by_columns, 0)

 

Edited January 26, 2019 by gcue

[gcue]

33 minutes ago, jchd said:

We definitely need an _ArrayQSort taking a collation function as parameter, just like qsort() in C.
I'll make one some day if it doesn't exist yet somewhere.

yea surprised it doesnt exist already

[UEZ]

Can you test this please?

expandcollapse popup

#include-once
#include <Array.au3>

; #FUNCTION# =============================================================================
; Name.............:    _ArraySort_MultiColumn
; Description ...:      sorts a 2D array at given columns (multi column sort)
; Syntax...........:    _ArraySort_MultiColumn(ByRef $aSort, ByRef $aIndices)
; Parameters ...:       $aSort - array to sort
;                       $aIndices - array with column indices which should be sorted in specified order - zero based
;                       $oDir/$iDir - sort direction - if set to 1, sort descending else ascending. $oDir is for the 1st column,
;                                                     $iDir for the remaining columns
;                       $iStart - where to start to sort - 0 = 0 based array, 1 = 1 based array
; Return values.:       Success: 1
;                       Failure: 0 and sets @error:
;                       1 - $aSort or $aIndices is not an array
;                       2 - $aIndices has more entries than $aSort has columns
;                       3 - $aIndices contains a non-integer value
;                       4 - $aSort is not a 2D array
;                       5 - $aIndices contains an out-of-range column index
;                       6 - $aIndices contains duplicate column indices
;                       7 - internal _ArraySort call failed (@extended holds its @error)
; Author .........:     UEZ
; Version ........:     v0.90 build 2026-07-18 Beta
; =========================================================================================
Func _ArraySort_MultiColumn(ByRef $aSort, ByRef $aIndices, $oDir = 0, $iDir = 0, $iStart = 0)
    If Not IsArray($aIndices) Or Not IsArray($aSort) Then Return SetError(1, 0, 0) ;checks if $aSort / $aIndices are arrays
    Local $iCols = UBound($aSort, 2)
    If $iCols = 0 Then Return SetError(4, 0, 0) ;array is 1D only
    Local $iKeys = UBound($aIndices)
    If $iKeys > $iCols Then Return SetError(2, 0, 0) ;check if $aIndices array is greater than $aSort column count
    Local $x, $m, $j, $k, $l, $bNewGroup
    Local $aIdx[$iKeys] ;local integer copy - caller's $aIndices stays untouched
    For $x = 0 To $iKeys - 1 ;validate array content
        If IsNumber($aIndices[$x]) Then
            If $aIndices[$x] <> Int($aIndices[$x]) Then Return SetError(3, 0, 0) ;fractional number
        ElseIf Not StringRegExp($aIndices[$x] & "", "^[-+]?\d+$") Then
            Return SetError(3, 0, 0) ;not an integer value
        EndIf
        $aIdx[$x] = Int($aIndices[$x])
        If $aIdx[$x] < 0 Or $aIdx[$x] > $iCols - 1 Then Return SetError(5, 0, 0) ;column index out of range
        For $m = 0 To $x - 1
            If $aIdx[$m] = $aIdx[$x] Then Return SetError(6, 0, 0) ;duplicate column index
        Next
    Next
    $iStart = Int($iStart) < 0 ? 0 : Int($iStart) > 1 ? 1 : Int($iStart)
    _ArraySort($aSort, $oDir, $iStart, 0, $aIdx[0]) ;initial sort by first key column
    If @error Then Return SetError(7, @error, 0)
    If $iKeys = 1 Then Return 1 ;only one index given -> done
    For $l = 0 To $iKeys - 2 ;one pass per additional key column
        $j = $iStart
        $k = $iStart + 1
        While $k < UBound($aSort)
            $bNewGroup = False
            For $m = 0 To $l ;compare ALL previous key columns, not just the current one
                If $aSort[$j][$aIdx[$m]] <> $aSort[$k][$aIdx[$m]] Then
                    $bNewGroup = True
                    ExitLoop
                EndIf
            Next
            If $bNewGroup Then
                If $k - $j > 1 Then
                    _ArraySort($aSort, $iDir, $j, $k - 1, $aIdx[$l + 1])
                    If @error Then Return SetError(7, @error, 0)
                EndIf
                $j = $k
            EndIf
            $k += 1
        WEnd
        If $k - $j > 1 Then ;last group at the end of the array
            _ArraySort($aSort, $iDir, $j, $k - 1, $aIdx[$l + 1])
            If @error Then Return SetError(7, @error, 0)
        EndIf
    Next
    Return 1
EndFunc   ;==>_ArraySort_MultiColumn

Seems to work but I'm not 100% sure...

Edited July 18 by UEZ
Code update

[gcue]

not quite.  i tried a base 1 index array (specified it to start at 1) and it started with 0 anyway.  my array has 6 columns - maybe thats why?

syntax used

Local $sort_by_columns[2] = [6, 1]

_ArraySort_MultiColumn($final_array, $sort_by_columns, 1, 1)

 

Edited January 26, 2019 by gcue

[UEZ]

The 5th parameter is for setting the search function to base 0 or base 1. In your example you will sort the array descending but not 1 based.

Try 

_ArraySort_MultiColumn($final_array, $sort_by_columns, 1, 1, 1)

[gcue]

works! 

the parameters in your description i think are confusing

; Parameters ...:       $aSort - array to sort
;                       $aIndices - array with colum indices which should be sorted in specified order - zero based
;                       $oDir/$iDir - sort direction - if set to 1, sort descendingly else ascendingly
;                       $iStart - where to start to sort - 0 = 0 based array, 1 = 1 based array

those are 4 parameters but the function is accepting 5

i think the confusion lies with 

$oDir/$iDir - sort direction - if set to 1, sort descendingly else ascendingly

is that 1 or 2 variables?  i think its 2 seperate ones from what the function is accepting anyway

 

thank you so much for your help!

[UEZ]

Yes, it is a little bit confusing. $oDir is for the 1st column and $iDir for the rest.

 

[Melba23]

gcue,

Or you could use my ArrayMultiColSort UDF, which has the start and finish position of the sort already built-in:

#include "ArrayMultiColSort.au3"

$max = 50
$s = ""
Dim $array[$max][4]
for $i = 0 to $max - 1
    $array[$i][0] = Chr(Random(65, 67, 1)) & Chr(Random(65, 67, 1)) & Chr(Random(65, 67, 1)) & Chr(Random(65, 67, 1))
    For $j = 0 To 2
        If Random(0, 1, 1) Then $s &= Chr(Random(48, 57, 1))
    Next
    If $s = "" Then $s = Chr(Random(65, 67, 1))
    $array[$i][1] =  $s
    $array[$i][2] = Random(1, 10, 1)
    $m = Random(1, 12, 1)
    $d = Random(1, 31, 1)
    If $m < 10 Then $m = "0" & $m
    If $d < 10 Then $d = "0" & $d
    $array[$i][3] = Random(1900, 2010, 1) &  "-" & $m & "-" & $d
    $s = ""
Next
_ArrayDisplay($array, 'Before:')

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

; Sort the array, starting at row 1
_ArrayMultiColSort($array, $aSortData, 1)

_ArrayDisplay($array, 'After:')

M23

[gcue]

cool thank you very much!