[Help] Sorting 2 Dimensional array using 2 Columns

[geocine]

I have this array sorted using column 1 and here is how it looks like:

I want to sort the columns based on this priority

->Column 1 Ascending then Column 2 Descending

Here is how it should be:

[0]|antique lids|2.01|104

[1]|antique lidt|2.01|27

[2]|pot with lid|2.33|284

[3]|pot and lid|2.52|27

[4]|bears grease pot lid|2.55|155

[5]|lids pots|2.79|27

[6]|lid pot|2.88|237

[7]|ceramic pot with lid|2.92|22

[8]|ceramic pot lid|3.01|27

[9]|pot lids for sale|3.30|43

[UEZ]

I wrote this to sort multi dim arrays:

expandcollapse popup

#include <Array.au3>
Dim $array [10][3] = [["antique lids", 2.01, 104], _
             ["antique lidt", 2.01, 27], _
             ["pot with lid", 2.01, 284], _
             ["pot and lid", 2.52, 27], _
             ["bears grease pot lid", 2.55, 155], _
             ["lids pots", 2.79, 27], _
             ["lid pot", 2.88, 237], _
             ["ceramic pot with lid", 2.92, 22], _
             ["ceramic pot lid", 3.01, 27], _
             ["pot lids for sale", 3.30, 43]]

Dim $aI[3]
$aI[0] = 1 ;sort column 2 then
$aI[1] = 2 ;sort column 3 and last
$aI[2] = 0 ;sort column 1


_ArraySort_MultiColumn($array, $aI, 0)
_ArrayDisplay($array, 'Nachher:')
For $i = 0 To UBound($array) - 1
    ConsoleWrite($array[$i][0] & "|" & $array[$i][1] & "|" & $array[$i][2] & @CRLF)
Next

; #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
;                   $dir - sort direction - if set to 1, sort descendingly
; Autor .........:  UEZ
; Version .......:  v0.60 build 2010-07-04 Beta
; =========================================================================================
Func _ArraySort_MultiColumn(ByRef $aSort, ByRef $aIndices, $oDir = 0, $iDir = 0)
    Local $1st, $2nd
    If Not IsArray($aIndices) Or Not IsArray($aSort) Then ;checks if $aIndices is an array
        Return SetError(1, 0, 0) ;no array
    EndIf
    If UBound($aIndices) > UBound($aSort, 2) Then ;check if $aIndices array is greater the $aSort array
        Return SetError(2, 0, 0) ;$aIndices array is greater
    EndIf
    Local $x
    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
        EndIf
    Next
    If UBound($aIndices) = 1 Then ;check if only one index is given
        Return _ArraySort($aSort, $oDir, 0, 0, $aIndices[0])
    EndIf
    Local $j, $k, $l = 0
    _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, $oDir, $j, $k, $2nd)
        $l += 1
    Until $l = UBound($aIndices) - 1
    Return SetError(0, 0, 1)
EndFunc

Maybe it is usable for you, too.

BR,

UEZ

Edited October 23, 2010 by UEZ

[Melba23]

geocine,

You need to look for matching entries in the first column and then sort just those values on the second column. Here is a script showing how to do that - I changed your array so it has another matching but unsorted pair at the end for demonstration purposes:

expandcollapse popup

#include <Array.au3>

Global $aArray[10][3] = [ _
["antique lids",2.01,104], _
["antique lids",2.01,27], _
["pot with lid",2.33,284], _
["pot and lid",2.52,27], _
["bears grease pot lid",2.55,155], _
["lids pots",2.79,27], _
["lid pot",2.88,237], _
["ceramic pot with lid",2.92,22], _
["ceramic pot lid",3.30,127], _
["pot lids for sale",3.30,43]]

_ArrayDisplay($aArray, "Before")

_Second_Sort($aArray)

_ArrayDisplay($aArray, "After")

Func _Second_Sort(ByRef $aArray)

    Local $aTemp

    ; Work through array
    For $i = 0 To UBound($aArray) - 1
        ; Get already sorted value of Col 1
        $iValue = $aArray[$i][1]
        ; Now see if there are any other entries with the same value
        For $j = $i To UBound($aArray) - 1
            If $aArray[$j][1] <> $iValue Then ExitLoop
        Next
        ; If there are then sort the array for those entries only
        If $j - $i > 1 Then
            _ArraySort($aArray, 0, $i, $j - 1, 2)
            ; Move the loop index to the next entry
            $i = $j
        EndIf
    Next

EndFunc

I hope that helps.

M23

Edit:

UEZ,

Apologies - I had not seen your post. Awesome display by the Turniermannschaft this afternoon by the way.

Edited July 3, 2010 by Melba23

[geocine]

@UEZ

Where can I specify sorting direction. Is it possible to specify different sorting directions for different columns.

I want the first index to be sorted ASC and the second to be sorted DESC.

Thanks,

Edit:

I tried your function using this code and I get this result for three identical rows on column 1:

Dim $aI[2]
        $aI[0] = 1
        $aI[1] = 2
        _ArraySort_MultiColumn($KEYWORDS, $aI)

Edited July 3, 2010 by geocine

[UEZ]

...

UEZ,

Apologies - I had not seen your post. Awesome display by the Turniermannschaft this afternoon by the way.

Why you are apologies? Many roads lead to Rome...

Yep, I hope Germany will win Currently best "Turniermannschaft" in this tournament!

@UEZ

Where can I specify sorting direction. Is it possible to specify different sorting directions for different columns.

I want the first index to be sorted ASC and the second to be sorted DESC.

Thanks,

I updated the code with your values! No, it is not implemented yet to sort the indicies seperately in different directions!

BR,

UEZ

[geocine]

@UEZ

Hi, I updated my post. I am trying to sort for column 1[ASC] and 2[DESC] in order. Please see the result above whenever I have 3 rows with the same column value for column 1.

[UEZ]

Please check post#2 again!

I used strings instead of float/int! That's the reason for the sort results!

BR,

UEZ

[geocine]

@UEZ

Hi for some reason I still get the same result for column three on post number #4.

104

27

284

Should be

284

104

27

Thanks,

[UEZ]

As I mentioned before it is not implemented yet to sort the indicies seperately in different directions!

Here the result for :

Dim $aI[3]

$aI[0] = 1 ;sort column 2 then

$aI[1] = 2 ;sort column 3 and last

$aI[2] = 0 ;sort column 1

antique lidt|2.01|27

antique lids|2.01|104

pot with lid|2.01|284

pot and lid|2.52|27

bears grease pot lid|2.55|155

lids pots|2.79|27

lid pot|2.88|237

ceramic pot with lid|2.92|22

ceramic pot lid|3.01|27

pot lids for sale|3.3|43

Other direction:

pot lids for sale|3.3|43

ceramic pot lid|3.01|27

ceramic pot with lid|2.92|22

lid pot|2.88|237

lids pots|2.79|27

bears grease pot lid|2.55|155

pot and lid|2.52|27

pot with lid|2.01|284

antique lids|2.01|104

antique lidt|2.01|27

BR,

UEZ

Edited July 3, 2010 by UEZ

[Melba23]

geocine,

Did you see my post a while ago? You have to sort the array twice, but it does get you what you said you wanted!

M23

[geocine]

Hi Melba23

I think your code is right, could you check what is wrong with my code. Because, every time I enter the array values manually on your code it works perfectly.

Your Code [Modified With My Values | Manually Inputted Array]

expandcollapse popup

#include <Array.au3>

Global $aArray[10][3] = [ _
        ["ceramic pot with lid", 2.92, 22], _
        ["lid pot", 2.88, 237], _
        ["bears grease pot lid", 2.55, 155], _
        ["ceramic pot lid", 3.01, 27], _
        ["pot lids for sale", 3.30, 43], _
        ["antique lidt", 2.01, 27], _
        ["pot with lid", 2.01, 284], _
        ["lids pots", 2.79, 27], _
        ["antique lids", 2.01, 104], _
        ["pot and lid", 2.52, 27]]

_ArraySort($aArray, 0, 0, 0, 1)

_ArrayDisplay($aArray, "Before")

_Second_Sort($aArray)

_ArrayDisplay($aArray, "After")


Func _Second_Sort(ByRef $aArray)

    Local $aTemp

    ; Work through array
    For $i = 0 To UBound($aArray) - 1
        ; Get already sorted value of Col 1
        $iValue = $aArray[$i][1]
        ; Now see if there are any other entries with the same value
        For $j = $i To UBound($aArray) - 1
            If $aArray[$j][1] <> $iValue Then ExitLoop
        Next
        ; If there are then sort the array for those entries only
        If $j - $i > 1 Then
            ;Change to Descending
            _ArraySort($aArray, 1, $i, $j - 1, 2)
            ; Move the loop index to the next entry
            $i = $j
        EndIf
    Next
EndFunc   ;==>_Second_Sort

Your Code [using My Generated Array]

expandcollapse popup

#include <Array.au3>
#include <String.au3>
#include <Inet.au3>
$domain = "amazon.com"
$keyword = "pot lids"
$api_key = "7ce8efe1e5a0685b941918a6909456ab"

$objKeyArray = ObjCreate("Scripting.Dictionary")
$KEYWORD_PROPOSAL = _INetGetSource("http://us.api.semrush.com/?action=report&type=phrase_related&phrase=" & $keyword & "&key=" & $api_key & "&display_limit=10&export=api")
If (StringInStr($KEYWORD_PROPOSAL, "ERROR") < 1) Then
    $KEYWORD_PROPOSALS = _StringExplode($KEYWORD_PROPOSAL, @LF)
    Global $KEYWORD_PROPOSALS_EXPLODED[10][3]
    For $i = 1 To 10 Step 1
        $KEYWORD_PROPOSAL_ROW = _StringExplode($KEYWORD_PROPOSALS[$i], ";")
        ;For $j = 0 to 6 Step 1
        ;   $KEYWORD_PROPOSALS_EXPLODED[$i][$j] = $KEYWORD_PROPOSAL_ROW[$j]
        ;Next

        If $i = 6 Then
            $KEYWORD_PROPOSALS_EXPLODED[$i - 1][0] = "antique lidt"
        Else
            $KEYWORD_PROPOSALS_EXPLODED[$i - 1][0] = $KEYWORD_PROPOSAL_ROW[0]
        EndIf

        If $i = 6 Or $i = 7 Then
            $KEYWORD_PROPOSALS_EXPLODED[$i - 1][1] = 2.01
        Else
            $KEYWORD_PROPOSALS_EXPLODED[$i - 1][1] = $KEYWORD_PROPOSAL_ROW[3]
        EndIf

        $KEYWORD_PROPOSALS_EXPLODED[$i - 1][2] = $KEYWORD_PROPOSAL_ROW[5]
    Next
    Dim $aI[2]
    $aI[0] = 1
    $aI[1] = 2
    
    _ArraySort($KEYWORD_PROPOSALS_EXPLODED, 0, 0, 0, 1)]
    _ArrayDisplay($KEYWORD_PROPOSALS_EXPLODED,"Before")
    _Second_Sort($KEYWORD_PROPOSALS_EXPLODED)
    _ArrayDisplay($KEYWORD_PROPOSALS_EXPLODED,"After")
    $KeywordList = ObjCreate("System.Collections.ArrayList")
Else
    $KEYWORD_PROPOSAL = 0
EndIf

Func _Second_Sort(ByRef $aArray)

    Local $aTemp

    ; Work through array
    For $i = 0 To UBound($aArray) - 1
        ; Get already sorted value of Col 1
        $iValue = $aArray[$i][1]
        ; Now see if there are any other entries with the same value
        For $j = $i To UBound($aArray) - 1
            If $aArray[$j][1] <> $iValue Then ExitLoop
        Next
        ; If there are then sort the array for those entries only
        If $j - $i > 1 Then
            ;Change to Descending
            _ArraySort($aArray, 1, $i, $j - 1, 2)
            ; Move the loop index to the next entry
            $i = $j
        EndIf
    Next
EndFunc   ;==>_Second_Sort

I think there is an issue regarding references. Kindly take a quick look on my code.

Edited July 3, 2010 by geocine

[UEZ]

Replace line

$KEYWORD_PROPOSALS_EXPLODED[$i - 1][2] = $KEYWORD_PROPOSAL_ROW[5]

with

$KEYWORD_PROPOSALS_EXPLODED[$i - 1][2] = Int($KEYWORD_PROPOSAL_ROW[5])

and it should work!

Here the version with my function adjusted for your requirement

expandcollapse popup

#include <Array.au3>
#include <String.au3>
#include <Inet.au3>
$domain = "amazon.com"
$keyword = "pot lids"
$api_key = "7ce8efe1e5a0685b941918a6909456ab"

$objKeyArray = ObjCreate("Scripting.Dictionary")
$KEYWORD_PROPOSAL = _INetGetSource("http://us.api.semrush.com/?action=report&type=phrase_related&phrase=" & $keyword & "&key=" & $api_key & "&display_limit=10&export=api")
If (StringInStr($KEYWORD_PROPOSAL, "ERROR") < 1) Then
    $KEYWORD_PROPOSALS = _StringExplode($KEYWORD_PROPOSAL, @LF)
    Global $KEYWORD_PROPOSALS_EXPLODED[10][3]
    For $i = 1 To 10 Step 1
    $KEYWORD_PROPOSAL_ROW = _StringExplode($KEYWORD_PROPOSALS[$i], ";")
    ;For $j = 0 to 6 Step 1
    ; $KEYWORD_PROPOSALS_EXPLODED[$i][$j] = $KEYWORD_PROPOSAL_ROW[$j]
    ;Next

    If $i = 6 Then
    $KEYWORD_PROPOSALS_EXPLODED[$i - 1][0] = "antique lidt"
    Else
    $KEYWORD_PROPOSALS_EXPLODED[$i - 1][0] = $KEYWORD_PROPOSAL_ROW[0]
    EndIf

    If $i = 6 Or $i = 7 Then
    $KEYWORD_PROPOSALS_EXPLODED[$i - 1][1] = 2.01
    Else
    $KEYWORD_PROPOSALS_EXPLODED[$i - 1][1] = $KEYWORD_PROPOSAL_ROW[3]
    EndIf

    $KEYWORD_PROPOSALS_EXPLODED[$i - 1][2] = Int($KEYWORD_PROPOSAL_ROW[5])
    Next
    Dim $aI[2]
    $aI[0] = 1
    $aI[1] = 2
    _ArraySort_MultiColumn($KEYWORD_PROPOSALS_EXPLODED, $aI, 0)
    _ArrayDisplay($KEYWORD_PROPOSALS_EXPLODED,"After")
    $KeywordList = ObjCreate("System.Collections.ArrayList")
Else
    $KEYWORD_PROPOSAL = 0
EndIf

; #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

BR,

UEZ

Edited November 20, 2013 by UEZ

[geocine]

@UEZ

Wow thanks. Thank you for spontaneously answering my inquiries and almost spoon feeding me glad to have you here.

Thanks,

Aivan