Sort 2D Array with Alpha and Numerical Columns

[millisys]

Hey folks,

I am trying to sort a 2D array and having a little difficulty. The first column is alpha and the second column is numeric. Here is the CSV file that I am reading from:

bob,12.4

jim,27.2
mary,2.6
susan,3.8

When I sort the array, the second column is sorting as

bob,12.4
mary,2.6
susan,3.8
jim,27.2

instead of

mary,2.6
susan,3.8

bob,12.4
jim,27.2

which I imagine is a result of the data being interpreted as a string value instead of a number value. I have tried using some ideas  such as stringsplit to create an array of each line, change the second column to number, and then reassemble it, but it doesn't seem to be working.

Here is the AutoIt code. Any help would be appreciated. Thank you.

#include <file.au3>
#include <array.au3>

Local $scores
_FileReadToArray("scores.csv", $scores, 0, ",")
_ArraySort($scores, 1, 0, 0, 1)
_ArrayDisplay($scores, "column 1 ascending")

[Melba23]

millisys,

Try my ArrayMultiColSort UDF:

#include <Array.au3>

#include "ArrayMultiColSort.au3"

Global $aArray[4][2] = [["bob", 12.4], ["jim", 27.2], ["mary", 2.6], ["susan", 3.8]]

Global $aSortData[1][2] = [[1, 0]] ; Sort col 1 as ascending numeric

_ArrayMultiColSort($aArray, $aSortData)

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

M23

[sahsanu]

Hello,

Try to convert to Number the second column before sort:

#include <file.au3>
#include <array.au3>

Local $scores
_FileReadToArray("scores.csv", $scores, 0, ",")

_ArraySort($scores, 1, 0, 0, 1)
_ArrayDisplay($scores, "column 1 ascending")

For $i = 0 To UBound($scores) - 1
    $scores[$i][1] = Number($scores[$i][1])
Next

_ArraySort($scores, 1, 0, 0, 1)
_ArrayDisplay($scores, "column 1 ascending")

Edit: I forgot to say that if you want to sort ascending you should change the first 1 in _ArraySort() function to 0 (0 means ascending and 1 descending).

Cheers,

sahsanu

Edited August 18, 2014 by sahsanu

[mikell]

An alternative way

#include <file.au3>
#include <array.au3>

Local $scores
_FileReadToArray("scores.csv", $scores, 0, ",")

$a = UBound($scores)
Local $temp[$a][2]
For $i = 0 to $a - 1
    $n = _ArrayMaxIndex($scores, 1, 0, 0, 1)
    $temp[$i][0] = $scores[$n][0]
    $temp[$i][1] = $scores[$n][1]
    $scores[$n][1] = ""
Next
_ArrayDisplay($temp)

Edit

sahsanu 

Edited August 18, 2014 by mikell

[millisys]

You guys are awesome! All of those techniques worked. Thank you so much Melba23, sahsanu and mikell.

[Malkey]

  On 8/18/2014 at 8:08 PM, millisys said:

 

Hey folks,

I am trying to sort a 2D array and having a little difficulty. The first column is alpha and the second column is numeric. Here is the CSV file that I am reading from:

bob,12.4

jim,27.2

mary,2.6

susan,3.8

When I sort the array, the second column is sorting as

bob,12.4

mary,2.6

susan,3.8

jim,27.2

instead of

mary,2.6

susan,3.8

bob,12.4

jim,27.2

which I imagine is a result of the data being interpreted as a string value instead of a number value. I have tried using some ideas  such as stringsplit to create an array of each line, change the second column to number, and then reassemble it, but it doesn't seem to be working.

Here is the AutoIt code. Any help would be appreciated. Thank you.

#include <file.au3>
#include <array.au3>

Local $scores
_FileReadToArray("scores.csv", $scores, 0, ",")
_ArraySort($scores, 1, 0, 0, 1)
_ArrayDisplay($scores, "column 1 ascending")

 

Your opening post says you use

_ArraySort($scores, 1, 0, 0, 1)

which returns

bob,12.4

mary,2.6

susan,3.8

jim,27.2

which correctly sorts ths second column (C0l1) in decending order.

You say you want

mary,2.6

susan,3.8

bob,12.4

jim,27.2

This has the second column (Col1) sorted in ascending order.

Therefore, you need to use

_ArraySort($scores, 0, 0, 0, 1)

It's in the help file under the _ArraySort function.

#include <file.au3>
#include <array.au3>

Local $scores
_FileReadToArray("scores.csv", $scores, 0, ",")
;_ArraySort($scores, 1, 0, 0, 1) ; Sorts second column (Col1) in decending order.
_ArraySort($scores, 0, 0, 0, 1)  ; Sorts second column (Col1) in ascending order.
_ArrayDisplay($scores, "column 1 ascending")