Jump to content
LWC

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

Recommended Posts

LWC

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
Subz

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
Chimp

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
czardas

@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
Chimp

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

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
LWC

@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
Chimp

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
LWC

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

  • Similar Content

    • 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  
    • Dzenan03
      By Dzenan03
      I want to make a while loop, that creates variables based on a array. For thist I created the array $iDsO with the number and the name of folders in an other folder. Every folder has a different name an I want to create variables(arrays) for each folder that show me all the files in that folder. For example: I have the Folder \Folder1. In it there are the Folders \1, \2, \3. In 1, 2 and 3 there are some files(.png). The array for Folder1 is $iDsO and now I want to crate the arrays $iDsO1, $iDsO2 and $iDsO3 with the files in them can I make something like this:
      While $iDs > 0 ;$iDs is the number of files in Folder1>> $iDsO[0] $iDs#here should come the Foldername for example '1'# = _FileListtoArray(@ProgramFilesDir&"\Folder1\"&$iDsO[$iDs]) $iDs = $iDs - 1 Wend So that in the End I have three variabels ($iDs1, $iDs2 and $iDs3)
       
      Is this posible or if not what could I do instead ( I don´t know the number of folders in Folder1 in the begining).
    • liagason
      By liagason
      Hello everyone,
      How can I display in ascending  sequence some numbers stored in a string variable?
      $str = "18,03,48,23" MsgBox(0,"test",$str) I would like it to display "03,18,23,48"
×