Jump to content

Recommended Posts

ternal
Posted (edited)

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?

#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 by ternal

Share this post


Link to post
Share on other sites
jchd

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


This wonderful site allows debugging and testing regular expressions (many flavors available). An absolute must have in your bookmarks.
Another excellent RegExp tutorial. Don't forget downloading your copy of up-to-date pcretest.exe and pcregrep.exe here
RegExp tutorial: enough to get started
PCRE v8.33 regexp documentation latest available release and currently implemented in AutoIt beta.

SQLitespeed is another feature-rich premier SQLite manager (includes import/export). Well worth a try.
SQLite Expert (freeware Personal Edition or payware Pro version) is a very useful SQLite database manager.
An excellent eBook covering almost every aspect of SQLite3: a must-read for anyone doing serious work.
SQL tutorial (covers "generic" SQL, but most of it applies to SQLite as well)
A work-in-progress SQLite3 tutorial. Don't miss other LxyzTHW pages!
SQLite official website with full documentation (may be newer than the SQLite library that comes standard with AutoIt)

Share this post


Link to post
Share on other sites
Melba23

ternal,

You might find this thread of interest:

M23


Any of my own code posted anywhere on the forum is available for use by others without any restriction of any kind._______My UDFs:

Spoiler

ArrayMultiColSort ---- Sort arrays on multiple columns
ChooseFileFolder ---- Single and multiple selections from specified path treeview listing
Date_Time_Convert -- Easily convert date/time formats, including the language used
ExtMsgBox --------- A highly customisable replacement for MsgBox
GUIExtender -------- Extend and retract multiple sections within a GUI
GUIFrame ---------- Subdivide GUIs into many adjustable frames
GUIListViewEx ------- Insert, delete, move, drag, sort, edit and colour ListView items
GUITreeViewEx ------ Check/clear parent and child checkboxes in a TreeView
Marquee ----------- Scrolling tickertape GUIs
NoFocusLines ------- Remove the dotted focus lines from buttons, sliders, radios and checkboxes
Notify ------------- Small notifications on the edge of the display
Scrollbars ----------Automatically sized scrollbars with a single command
StringSize ---------- Automatically size controls to fit text
Toast -------------- Small GUIs which pop out of the notification area

 

Share this post


Link to post
Share on other sites
pixelsearch
Posted (edited)

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 :

5b6eb9ff4cce5_doublearraysort.jpg.7fb35a8ba54132cf39c4539d0de14ce3.jpg
 

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 by pixelsearch
  • Like 1

Share this post


Link to post
Share on other sites
ternal
Posted (edited)

 

@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 by ternal

Share this post


Link to post
Share on other sites
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

Share this post


Link to post
Share on other sites
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.


This wonderful site allows debugging and testing regular expressions (many flavors available). An absolute must have in your bookmarks.
Another excellent RegExp tutorial. Don't forget downloading your copy of up-to-date pcretest.exe and pcregrep.exe here
RegExp tutorial: enough to get started
PCRE v8.33 regexp documentation latest available release and currently implemented in AutoIt beta.

SQLitespeed is another feature-rich premier SQLite manager (includes import/export). Well worth a try.
SQLite Expert (freeware Personal Edition or payware Pro version) is a very useful SQLite database manager.
An excellent eBook covering almost every aspect of SQLite3: a must-read for anyone doing serious work.
SQL tutorial (covers "generic" SQL, but most of it applies to SQLite as well)
A work-in-progress SQLite3 tutorial. Don't miss other LxyzTHW pages!
SQLite official website with full documentation (may be newer than the SQLite library that comes standard with AutoIt)

Share this post


Link to post
Share on other sites

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now

  • Similar Content

    • careca
      By careca
      Hi, i have this array of numbers in the form of strings, and i assumed _arraysort would sort them out, but theres no option to do it numerically, so i had to do it another way, seems clumsy, what am i missing here? There's for sure a better way to sort this out numerically right? _arraysort is doing a mess with the numbers, it goes like this: 35,45,50,42,48,54
      #include<Array.au3> Local $ID, $AM Local $FO = Fileopen('mult.txt') Local $FR = FileReadToArray($FO) FileClose($FO) _ArrayDisplay($FR) For $d = 0 To UBound($FR) $AM = _ArrayMin($FR, 1) ConsoleWrite($AM &@CRLF) $ID = _ArraySearch($FR, $AM) _ArrayDelete($FR, $ID) Next So this way i retrieve the lowest number and then delete it from the array, what would you do?
      mult.txt
    • Skeletor
      By Skeletor
      Hi Virtual People,
      My array works perfectly fine. However, what is the best practice if the line in the array doesn't have the correct amount of columns and if I can add a placeholder?

       
      For $count = 1 To _FileCountLines($FileRead1) Step 1 $string = FileReadLine($FileRead1, $count) $input = StringSplit($string, ",", 1) $value1 = $input[1] $value2 = $input[2] $value3 = $input[3] _Excel_RangeWrite($oWorkbook, $oWorkbook.Activesheet, $value2, "A1") _Excel_RangeWrite($oWorkbook, $oWorkbook.Activesheet, $value1, "B1") _Excel_RangeWrite($oWorkbook, $oWorkbook.Activesheet, $value3, "C1") Next  
    • MrCheese
      By MrCheese
      hi all,
      reviewing the forum, this thread is applicable: 
       
       
      I wanted to know if there is now a better way to do this?
      In essence, I load a tab delimited txt file into an array (works well). I used tab, as some fields in the original csv contains commas.
      However, I needed autoit to manipulate this array, and output it as a csv.
      IF my array contains items with a comma, without double quotes around the field, then how best do I get a csv out of this?
      My current workaround is to filewritefromarray tab delimited, then open it in excel and save as a csv. I will need to check this to see how the address fields behave that contain a comma.
       
      Any thoughts would be appreciated.
       
    • Skeletor
      By Skeletor
      Hi All,

      I would like to know how you would take a FileLineRead and insert it into an array which then inserts it into Excel?
      One thing to know is the files content is broken up, so I only use half of the content within $FileRead1.
      So its imperative that the $value1, $value2, etc variables be used. 
      Code below:
      $FileRead1 = FileReadLine("C:\temp\sample.txt",1) For $count = 1 To _FileCountLines($FileRead1) Step 1 $string = FileReadLine($FileRead1, $count) $input = StringSplit($string, ",", 1) $value1 = $input[1] $value2 = $input[2] $value3 = $input[3] $value4 = $input[4] _Excel_RangeWrite($oWorkbook, $oWorkbook.Activesheet, $value1, "A1") _Excel_RangeWrite($oWorkbook, $oWorkbook.Activesheet, $value2, "B1") _Excel_RangeWrite($oWorkbook, $oWorkbook.Activesheet, $value3, "C1") _Excel_RangeWrite($oWorkbook, $oWorkbook.Activesheet, $value4, "D1") Next  
    • AnonymousX
      By AnonymousX
      Hello,
      I'm trying to write a script that moves copies excel cells into an array. I'll than manipulate the values and send array into another program. 
      I don't want range to be specific to a workbook, or sheet, or set of cells.
      I want user to be able to highlight desired cells and to copy either normally ("Ctrl+C") or by a hotkey ("Alt+C"). 
      Could someone help me with this?
      Thank you,
      I've tried to write the framework: (edited)
      #include <MsgBoxConstants.au3> #include <Array.au3> #include <Excel.au3> HotKeySet("!v", "Pastedata") While True Sleep(1000) WEnd func Makearray() local $bArray ;User has cells already copied ;Convert clipboard into an array ;I don;t know how excel stores data to clipboard so don;t know how to bring it into array _Arraydisplay($bArray) MsgBox(0,0,$bArray) return $bArray endfunc func Pastedata() Local $aArray MsgBox(0,0,"wait",1) ;make array based on assumption user has already copied a range to clipboard $aArray = Makearray() ;paste code ;don;t worry about this I got the rest endfunc  
×