Jump to content
Sign in to follow this  
LWC

[Solved] Rearrange array columns based on first row's values

Recommended Posts

I import CSV files which have various headers.
Is there a way to simply keep only the ones I need and in the order I need them in?
For example, if the array's first row's values are Foo Bar Test This, then I want to turn them to Foo This Bar.
Here's how I do it "manually" with _ArrayColDelete and _ArraySwap, but I want to use something smarter like rearrange($aArray, ["Foo", "This", "Bar"]):

#include <Array.au3>

Local $aArray[4][4] = [["Foo", "Bar", "Test", "This"], [1, 2, 3, 4], [5, 6, 7, 8], [9, 10, 11, 12]]
_ArrayDisplay($aArray, "Original")
_ArrayColDelete($aArray, 2) ;2 stands for Test, but next time Test might not be 2, but it will still be called "Test"
_ArraySwap($aArray, 1, 2, true) ;1 and 2 might be different next time, but their names will remain
_ArrayDisplay($aArray, "Modified")

If it helps, I do the same thing in VBA based on Sub Reorganize_columns  (the second/alternative example in that page).

Edited by LWC
Solved

Share this post


Link to post
Share on other sites

Hasn't been thoroughly tested but maybe something like this:

#include <Array.au3>

Local $aArray[4][4] = [["Foo", "Bar", "Test", "This"], [1, 2, 3, 4], [5, 6, 7, 8], [9, 10, 11, 12]]
_ArrayDisplay($aArray, "Original")

Local $aRange[3] = ["Foo", "This", "Bar"]
$aArray = _ReArrange($aArray,  $aRange)

Func _ReArrange($aSearch, $aRange)
    Local $iSearch
    For $i = UBound($aSearch, 2) - 1 To 0 Step - 1
        $iSearch = _ArraySearch($aRange, $aSearch[0][$i])
        If $iSearch > -1 Then
            If $i <> $iSearch Then _ArraySwap($aSearch, $iSearch, $i, True)
        EndIf
        If $iSearch = -1 Then
            _ArrayColDelete($aSearch, $i)
            ContinueLoop
        EndIf
    Next
    Return $aSearch
EndFunc

_ArrayDisplay($aArray, "Modified")

 

Share this post


Link to post
Share on other sites

another one:

#include <Array.au3>

Local $aArray[5][4] = [["Foo", "Bar", "Test", "This"],[1, 2, 3, 4],[5, 6, 7, 8],[9, 10, 11, 12],[13, 14, 15, 16]]
_ArrayDisplay($aArray, "Original")

Local $aWanted[] = ["Foo", "This", "Bar"]
Local $aModified = rearrange($aArray, $aWanted)

_ArrayDisplay($aModified, "Modified")


Func rearrange(ByRef $aArray, ByRef $aIndex)

    Local $aResult[UBound($aArray)][UBound($aIndex)]
    Local $aNdx[UBound($aIndex)]

    For $i = 0 To UBound($aIndex) - 1
        For $x = 0 To UBound($aArray, 2) - 1
            If $aArray[0][$x] = $aIndex[$i] Then
                $aNdx[$i] = $x
                ExitLoop
            EndIf
        Next
    Next

    For $i = 0 To UBound($aArray) - 1
        For $x = 0 To UBound($aIndex) - 1
            $aResult[$i][$x] = $aArray[$i][$aNdx[$x]]
        Next
    Next
    Return $aResult
EndFunc   ;==>rearrange

 

Edited by Chimp
little debug

small minds discuss people average minds discuss events great minds discuss ideas.... and use AutoIt....

Share this post


Link to post
Share on other sites

@Chimp A perfectly good method which answers the question, but I think that passing the desired numeric sequence of columns would be better than passing an array of headers. The reason is that some headers may be duplicated, or omitted. Just a passing comment: in case it might be relevant to LWC (or anyone else).

Edited by czardas

Share this post


Link to post
Share on other sites

Hi @czardas, I agree with you,

in post #3 I've verbatim answered to the OP's request.

@LWC If you mind to pass the numbers of the desired columns instead of their names, the function is even simpler (and bulletproof ...)

#include <Array.au3> ; just for _ArrayDisplay

Local $aArray[5][4] = [["Foo", "Bar", "Test", "This"],[1, 2, 3, 4],[5, 6, 7, 8],[9, 10, 11, 12],[13, 14, 15, 16]]
_ArrayDisplay($aArray, "Original")

; pass columns number instead of names
Local $aWanted[] = [0, 3, 2] ; first column is number 0 (zero based)
Local $aModified = rearrange($aArray, $aWanted)

_ArrayDisplay($aModified, "Modified")

Func rearrange(ByRef $aArray, ByRef $aIndex)

    Local $aResult[UBound($aArray)][UBound($aIndex)] ; build the output array

    For $i = 0 To UBound($aArray) - 1 ; fill it with desired data
        For $x = 0 To UBound($aIndex) - 1
            $aResult[$i][$x] = $aArray[$i][$aIndex[$x]]
        Next
    Next

    Return $aResult
EndFunc   ;==>rearrange

 

Edited by Chimp
specified script's addressee

small minds discuss people average minds discuss events great minds discuss ideas.... and use AutoIt....

Share this post


Link to post
Share on other sites

@Chimp's right that I asked for header names, as they're less dynamic then column locations.

But no one addressed @Subz' code.

Assuming the consensus favors Chimp's code, here's a variation that supports both non 0-based arrays and quoted/chr(34) headers (due to the linked function that imports CSV files in my OP):

#include <Array.au3>

Local $aArray[5][4] = [["Foo", "Bar", "Test", "This"],[1, 2, 3, 4],[5, 6, 7, 8],[9, 10, 11, 12],[13, 14, 15, 16]]
_ArrayDisplay($aArray, "Original 0-based")
Local $aWanted[] = ["Foo", "This", "Bar"]
$aArray = rearrange($aArray, $aWanted)
_ArrayDisplay($aArray, "Modified 0-based")

_ArrayInsert($aArray, 0, ubound($aArray)) ; Turns this array into non 0-based
_ArrayDisplay($aArray, "Original non 0-based")
$aArray = rearrange($aArray, $aWanted, false)
_ArrayDisplay($aArray, "Modified non 0-based")

Func rearrange(ByRef $aArray, ByRef $aIndex, $0_based=true)

    local $count
    if not $0_based Then
        $count = $aArray[0][0]
        _ArrayDelete($aArray, 0)
    EndIf
    Local $aResult[UBound($aArray)][UBound($aIndex)]
    Local $aNdx[UBound($aIndex)]

    For $i = 0 To UBound($aIndex) - 1
        For $x = 0 To UBound($aArray, 2) - 1
            If $aArray[0][$x] = $aIndex[$i] or $aArray[0][$x] = chr(34) & $aIndex[$i] & chr(34) Then
                $aNdx[$i] = $x
                ExitLoop
            EndIf
        Next
    Next

    For $i = 0 To UBound($aArray) - 1
        For $x = 0 To UBound($aIndex) - 1
            $aResult[$i][$x] = $aArray[$i][$aNdx[$x]]
        Next
    Next

    if not $0_based Then
        _ArrayInsert($aResult, 0, $count)
    EndIf

    Return $aResult
EndFunc   ;==>rearrange

My last question is, how can I add something like:

Local $aWanted[] = ["Foo", "This", "", "Bar"]

And have the function add a blank column when it sees ""?

Share this post


Link to post
Share on other sites

the function will add a blank column when it sees "" (an empty string) or it doesn't found the wanted column name:

#include <Array.au3>

Local $aArray[5][4] = [["Foo", "Bar", "Test", "This"],[1, 2, 3, 4],[5, 6, 7, 8],[9, 10, 11, 12],[13, 14, 15, 16]]
_ArrayDisplay($aArray, "Original 0-based")
Local $aWanted[] = ["Foo", "Dummy", "This", "", "Bar"]
$aModified = rearrange($aArray, $aWanted)
_ArrayDisplay($aModified, "Modified 0-based")

_ArrayInsert($aArray, 0, UBound($aArray)) ; Turns this array into non 0-based
_ArrayDisplay($aArray, "Original non 0-based")
$aModified = rearrange($aArray, $aWanted, False)
_ArrayDisplay($aModified, "Modified non 0-based")

Func rearrange(ByRef $aArray, ByRef $aIndex, $0_based = True)

    Local $count

    If Not $0_based Then
        $count = $aArray[0][0]
        _ArrayDelete($aArray, 0)
    EndIf


    Local $aResult[UBound($aArray)][UBound($aIndex)]
    Local $aNdx[UBound($aIndex)]

    For $i = 0 To UBound($aIndex) - 1
        For $x = 0 To UBound($aArray, 2) - 1
            If $aArray[0][$x] = $aIndex[$i] Then
                $aNdx[$i] = $x
                ExitLoop
            ElseIf $aIndex[$i] = "" Then
                $aNdx[$i] = Null ; an empty column wanted
                ExitLoop
            ElseIf $x = UBound($aArray, 2) - 1 Then
                $aNdx[$i] = Null ; wanted string not found (leave an empty column)
            EndIf
        Next
    Next

    For $i = 0 To UBound($aArray) - 1
        For $x = 0 To UBound($aIndex) - 1
            If $aNdx[$x] <> Null Then
                $aResult[$i][$x] = $aArray[$i][$aNdx[$x]]
            EndIf
        Next
    Next

    If Not $0_based Then
        _ArrayInsert($aResult, 0, $count)
    EndIf

    Return $aResult
EndFunc   ;==>rearrange

 


small minds discuss people average minds discuss events great minds discuss ideas.... and use AutoIt....

Share this post


Link to post
Share on other sites

Here's again support for quoted headers + plus adding unfound column names instead of using blanks:

#include <Array.au3>

Local $aArray[5][4] = [["""Foo""", "Bar", "Test", "This"],[1, 2, 3, 4],[5, 6, 7, 8],[9, 10, 11, 12],[13, 14, 15, 16]]
_ArrayDisplay($aArray, "Original 0-based")
Local $aWanted[] = ["Foo", "Dummy", "This", "", "Bar"]
$aModified = rearrange($aArray, $aWanted)
_ArrayDisplay($aModified, "Modified 0-based")

_ArrayInsert($aArray, 0, UBound($aArray)) ; Turns this array into non 0-based
_ArrayDisplay($aArray, "Original non 0-based")
$aModified = rearrange($aArray, $aWanted, False)
_ArrayDisplay($aModified, "Modified non 0-based")

Func rearrange(ByRef $aArray, ByRef $aIndex, $0_based = True)

    Local $count

    If Not $0_based Then
        $count = $aArray[0][0]
        _ArrayDelete($aArray, 0)
    EndIf

    Local $aResult[UBound($aArray)][UBound($aIndex)]
    Local $aNdx[UBound($aIndex)]

    For $i = 0 To UBound($aIndex) - 1
        For $x = 0 To UBound($aArray, 2) - 1
            If $aArray[0][$x] = $aIndex[$i] or $aArray[0][$x] = chr(34) & $aIndex[$i] & chr(34) Then
                $aNdx[$i] = $x
                ExitLoop
            ElseIf $aIndex[$i] = "" Then
                $aNdx[$i] = Null ; an empty column wanted
                ExitLoop
            ElseIf $x = UBound($aArray, 2) - 1 Then
                $aNdx[$i] = Null ; wanted string not found (leave an empty column)
            EndIf
        Next
    Next

    For $i = 0 To UBound($aArray) - 1
        For $x = 0 To UBound($aIndex) - 1
            If $aNdx[$x] <> Null Then
                $aResult[$i][$x] = $aArray[$i][$aNdx[$x]]
            elseif $i=0 and $aIndex[$x]<>"" then ; if wanted string not found, use it as a title for the blank column
                $aResult[$i][$x]=$aIndex[$x]
            EndIf
        Next
    Next

    If Not $0_based Then
        _ArrayInsert($aResult, 0, $count)
    EndIf

    Return $aResult
EndFunc   ;==>rearrange

 

Edited by LWC

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
Sign in to follow this  

  • Recently Browsing   0 members

    No registered users viewing this page.

  • Similar Content

    • By Jangal
      Hello friends
      This app is slow
      How to increase its speed?
       
      #include <Array.au3> #include <StringConstants.au3> #include <File.au3> #include <String.au3> Global $aWord[][2]  = [[1, "google"],[2,"hello"]]


        Global $sFileName = @ScriptDir & "\1.txt" ; 2MB Text File Local $sFileRead = FileRead($sFileName) Local $res = StringRegExp($sFileRead, "(*UCP)\b[\pL\d]{2,}", 3) _ArrayDisplay($res)   for $sWord in $res     $iIndex = _ArraySearch($aWord, $sWord, 0, 0, 0, 0, 1, 2)     ;MsgBox(0,0,$iIndex)     if $iIndex == -1 Then         Local $aFill = [[0,$sWord]]         _ArrayAdd($aWord,$aFill)        ;      Else         $aWord[$iIndex][0] +=1     EndIf   Next _ArrayDisplay($aWord)

      1.txt
    • By Colduction
      Hi dear friends!, i'm sorry for creating a new thread (a new problem), i have over than 9 lists that i want to combine them to be this (in this example, there are 3 test files):


      I've written a little code for splitting main information, but i really confused how to make results as "Output.txt", here is that code:
       
      $sRegex_1 = StringRegExp(FileRead("1.txt"), '(?s:(?<=\=\=\r\n)(.*?)(?=\r\n\=\=))', 3) $sRegex_2 = StringRegExp(FileRead("2.txt"), '(?s:(?<=\=\=\r\n)(.*?)(?=\r\n\=\=))', 3) $sRegex_3 = StringRegExp(FileRead("3.txt"), '(?s:(?<=\=\=\r\n)(.*?)(?=\r\n\=\=))', 3) For $i = 0 To UBound($sRegex_1) - 1 ConsoleWrite($sRegex_1[$i] & @CRLF) For $j = 0 To UBound($sRegex_2) - 1 ConsoleWrite($sRegex_2[$j] & @CRLF) For $k = 0 To UBound($sRegex_3) - 1 ConsoleWrite($sRegex_3[$k] & @CRLF) Next Next Next  
    • By nacerbaaziz
      hello evrybody
      here is an example about how to split your texts using a delimiter with the ability to select how much of delimiters shows in each colum  with $i_number
      e.g
      you have a long text and you want to split it in an array
      that evry colum have a number (n) of lines
      i made a function that do that for you
      just call it with a three params
      $s_text
      your text
      $i_number
      the number that you want to put in each col
      $s_siparator
      the siparator
      default is "|"
      here is the function with example
      i hope that it will be useful for you
       
      ****
       
      #include <Array.au3> $s_txt = "some text1some text2|some text3|some text4|some text5|some text6" $array = splitText($s_txt, 2) _ArrayDisplay($array) Func splitText($s_text, $i_number, $s_siparator = "|") Local $a_TXT = StringSplit($s_text, $s_siparator) Local $a_Return[$a_TXT[0] + 1] If ($a_TXT[0] <= $i_number) Or ($i_number <= 0) Then ReDim $a_Return[2] $a_Return[0] = 1 $a_Return[1] = $s_text Return $a_Return EndIf Local $i_Processed = 1, $i_arrayProcessed = 1 Do For $i = $i_Processed To ($i_Processed + $i_number) - 1 If ($a_TXT[0] < $i) Then ExitLoop If Not ($a_Return[$i_arrayProcessed]) Then $a_Return[$i_arrayProcessed] = $a_TXT[$i] Else $a_Return[$i_arrayProcessed] &= $s_siparator & $a_TXT[$i] EndIf $i_Processed += 1 Next $i_arrayProcessed += 1 Until ($a_TXT[0] < $i_Processed) ReDim $a_Return[$i_arrayProcessed] $a_Return[0] = $i_arrayProcessed - 1 Return $a_Return EndFunc ;==>splitText
      accept my greetings
      thanks to
      @Dan_555
      for his notes
       
    • By MesterPerfect
      good morning
      this is the first post here in the autoit forums
      i hope that you can help me in my problem
      i have a JSON encoded
      it a map of my forums
      where i want to make a treeview that have the same type of map
      e.g
      a system (as category)
      windows (as sub category)
      software (as an child item in the windows category)
      .....
      i don't know how to do that
      so, i know that i can do that using the json functions
      but i need your help about how we can do it as the type that i told you
      by the way i need to put the sub info for each item in an array that give me the ability to manage my items
      e.g
      can post thread
      can reply
      message cound ...
      you just give me a small example and i can continue.
      am sorry if this against the rules of the forum.
      but i realy searched a lot but i couldn't
      i hope some one give me the way.
      thank you very much in advance
       
      here is the link of json forum
      https://www.autoitscript.com/forum/topic/148114-a-non-strict-json-udf-jsmn/
      and here is my encoded json file
       
      { "tree_map": { "0": [ 1, 5, 6, 7 ], "1": [ 2 ], "2": [ 4 ], "5": [ 3 ], "6": [ 8 ], "8": [ 9, 10 ] }, "nodes": [ { "breadcrumbs": [], "description": "", "display_in_list": true, "display_order": 1, "node_id": 1, "node_name": null, "node_type_id": "Category", "parent_node_id": 0, "title": "Main category", "type_data": {} }, { "breadcrumbs": [ { "node_id": 1, "title": "Main category", "node_type_id": "Category" } ], "description": "", "display_in_list": true, "display_order": 1, "node_id": 2, "node_name": null, "node_type_id": "Forum", "parent_node_id": 1, "title": "Main forum", "type_data": { "allow_poll": true, "allow_posting": true, "can_create_thread": true, "can_upload_attachment": true, "discussion_count": 0, "last_post_date": 0, "last_post_id": 0, "last_post_username": "", "last_thread_id": 0, "last_thread_prefix_id": 0, "last_thread_title": "", "message_count": 0, "min_tags": 0, "require_prefix": false } }, { "breadcrumbs": [ { "node_id": 1, "title": "Main category", "node_type_id": "Category" }, { "node_id": 2, "title": "Main forum", "node_type_id": "Forum" } ], "description": "", "display_in_list": true, "display_order": 1, "node_id": 4, "node_name": null, "node_type_id": "Forum", "parent_node_id": 2, "title": "my forums1", "type_data": { "allow_poll": true, "allow_posting": true, "can_create_thread": true, "can_upload_attachment": true, "discussion_count": 0, "last_post_date": 0, "last_post_id": 0, "last_post_username": "", "last_thread_id": 0, "last_thread_prefix_id": 0, "last_thread_title": "", "message_count": 0, "min_tags": 0, "require_prefix": false } }, { "breadcrumbs": [], "description": "", "display_in_list": true, "display_order": 2, "node_id": 5, "node_name": null, "node_type_id": "Category", "parent_node_id": 0, "title": "Perfect", "type_data": {} }, { "breadcrumbs": [ { "node_id": 5, "title": "Perfect", "node_type_id": "Category" } ], "description": "", "display_in_list": true, "display_order": 2, "node_id": 3, "node_name": null, "node_type_id": "Forum", "parent_node_id": 5, "title": "ahmed", "type_data": { "allow_poll": true, "allow_posting": true, "can_create_thread": true, "can_upload_attachment": true, "discussion_count": 0, "last_post_date": 0, "last_post_id": 0, "last_post_username": "", "last_thread_id": 0, "last_thread_prefix_id": 0, "last_thread_title": "", "message_count": 0, "min_tags": 0, "require_prefix": false } }, { "breadcrumbs": [], "description": "", "display_in_list": true, "display_order": 3, "node_id": 6, "node_name": null, "node_type_id": "Forum", "parent_node_id": 0, "title": "autoit", "type_data": { "allow_poll": true, "allow_posting": true, "can_create_thread": true, "can_upload_attachment": true, "discussion_count": 0, "last_post_date": 0, "last_post_id": 0, "last_post_username": "", "last_thread_id": 0, "last_thread_prefix_id": 0, "last_thread_title": "", "message_count": 0, "min_tags": 0, "require_prefix": false } }, { "breadcrumbs": [ { "node_id": 6, "title": "autoit", "node_type_id": "Forum" } ], "description": "", "display_in_list": true, "display_order": 3, "node_id": 8, "node_name": null, "node_type_id": "Forum", "parent_node_id": 6, "title": "examples", "type_data": { "allow_poll": true, "allow_posting": true, "can_create_thread": true, "can_upload_attachment": true, "discussion_count": 0, "last_post_date": 0, "last_post_id": 0, "last_post_username": "", "last_thread_id": 0, "last_thread_prefix_id": 0, "last_thread_title": "", "message_count": 0, "min_tags": 0, "require_prefix": false } }, { "breadcrumbs": [ { "node_id": 6, "title": "autoit", "node_type_id": "Forum" }, { "node_id": 8, "title": "examples", "node_type_id": "Forum" } ], "description": "", "display_in_list": true, "display_order": 3, "node_id": 9, "node_name": null, "node_type_id": "Forum", "parent_node_id": 8, "title": "GUI", "type_data": { "allow_poll": true, "allow_posting": true, "can_create_thread": true, "can_upload_attachment": true, "discussion_count": 0, "last_post_date": 0, "last_post_id": 0, "last_post_username": "", "last_thread_id": 0, "last_thread_prefix_id": 0, "last_thread_title": "", "message_count": 0, "min_tags": 0, "require_prefix": false } }, { "breadcrumbs": [ { "node_id": 6, "title": "autoit", "node_type_id": "Forum" }, { "node_id": 8, "title": "examples", "node_type_id": "Forum" } ], "description": "", "display_in_list": true, "display_order": 31, "node_id": 10, "node_name": null, "node_type_id": "Forum", "parent_node_id": 8, "title": "windowEX", "type_data": { "allow_poll": true, "allow_posting": true, "can_create_thread": true, "can_upload_attachment": true, "discussion_count": 0, "last_post_date": 0, "last_post_id": 0, "last_post_username": "", "last_thread_id": 0, "last_thread_prefix_id": 0, "last_thread_title": "", "message_count": 0, "min_tags": 0, "require_prefix": false } }, { "breadcrumbs": [], "description": "", "display_in_list": true, "display_order": 4, "node_id": 7, "node_name": null, "node_type_id": "Category", "parent_node_id": 0, "title": "vbs", "type_data": {} } ] }  
    • By nooneclose
      I need to dynamically resize my 2d array while looping. 
      I know this code:
      ReDim $rArray[UBound($rArray) + 1] works for the rows, however, I also need to increase the columns. How would i go about increasing both rows and columns while looping? 
×
×
  • Create New...