Arrays - Transpose

wutzke · November 28, 2006

I have an array

Dec '04       146
Nov '04         78
Oct '04          53
Sep '04         38
Aug '04         48
Jul '04           54
Jun '04          27
May '04         19
Apr '04          68
Mar '04          45
Feb '04          46
Jan '04          38

That I want to transpose to

Dec '04 Nov '04 Oct '04 Sep '04 Aug '04 Jul '04 Jun '04 May '04 Apr '04 Mar '04 Feb '04 Jan '04
146     78        53        38       48       54         27       19          68         45      46      38

Using Excel is easy enough to using transpose via Paste Special

But I have a comma separated file with thousands of lines and think it might be quicker to use AU to pre-processor

I'm reading the array this way

$inc = 0
; Read in lines of text until the EOF is reached
While 1
    $line = FileReadLine($file)
    $inc = $inc + 1
    If @error = -1 Then ExitLoop
$info = StringSplit ($line, ",")
$myArray[$inc][1]=$info[1]
$myArray[$inc][2]=$info[2]
   MsgBox(0, $myArray[$inc][1], $myArray[$inc][2])
Wend

Edited November 28, 2006 by wutzke

/dev/null · November 28, 2006

Try this. WARNING: NO ERROR CHECKING IS DONE! I leave that up to you!!

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

dim $lines
$input_filename = "c:\test.txt"
$output_filename = "c:\test_transp.txt"

_FileReadToArray($input_filename,$lines)

$outline1 = ""
$outline2 = ""

for $n = 1 to $lines[0]
    $splitted = StringSplit($lines[$n],",")
    $outline1 &= $splitted[1] & ","
    $outline2 &= $splitted[2] & ","
Next

FileWriteLine($output_filename,$outline1)
FileWriteLine($output_filename,$outline2)

test.txt looks like this:

Dec '04,146

Nov '04,78

Oct '04,53

Cheers

Kurt

dabus · November 28, 2006

Have a look at Ubound & ArrayAdd.

You could do a For/Next-loop to create a new array using [Re]Dim and Ubound.

wutzke · November 28, 2006

maybe

for $i = 0 to $arraycount
    $newArray1 = $array[0,$i]
    $newArray2 = $array[1,$i]
Next

where $arraycount equalling the columns

then write that to a csv

Edited November 28, 2006 by wutzke

SmOke_N · November 28, 2006

$sFile = @DesktopDir & '\sretest.txt'
MsgBox(0, '', _IDONTKNOWWHATTOCALLTHISFUNCTION($sFile))

Func _IDONTKNOWWHATTOCALLTHISFUNCTION($hFile)
    If Not FileExists($hFile) Then Return SetError(1, 0, 0)
    Local $sString = FileRead($hFile), $sHold = '', $sHoldNum
    Local $aDates = StringRegExp($sString, "(\w{3}\s+\'\d{2})\s", 3)
    Local $aNum = StringRegExp($sString, "\w{3}\s+\'\d{2}\s*(\d+)", 3)
    If Not IsArray($aDates) Or Not IsArray($aNum) Then Return SetError(2, 0, 0)
    For $iCC = 0 To UBound($aDates) - 1
        $sHold &= $aDates[$iCC] & @TAB
        $sHoldNum &= $aNum[$iCC] & _SpaceConvert(StringLen($aDates[$iCC]), StringLen($aNum[$iCC]))
    Next
    $sHold = StringTrimRight($sHold, StringLen(@TAB))
    $sHoldNum = StringTrimRight($sHoldNum, StringLen(@TAB))
    Return $sHold & @CRLF & $sHoldNum
EndFunc

Func _SpaceConvert($sLen1, $sLen2)
    Local $sHold = ''
    For $iCC = 1 To $sLen1 - $sLen2
        $sHold &= Chr(32)
    Next
    Return $sHold & @TAB
EndFunc

wutzke · November 28, 2006

That looks great

I'll see if that works with the entire data chuck.

2300-04-CLO          CLOTHING
     Sep '06      459.0000   Dec '05      127.0000   Mar '05      184.0000   Jun '04       27.0000   
     Aug '06       68.0000   Nov '05       58.0000   Feb '05       50.0000   May '04       19.0000   
     Jul '06       74.0000   Oct '05       50.0000   Jan '05       30.0000   Apr '04       68.0000   
     Jun '06       51.0000   Sep '05      530.0000   Dec '04      146.0000   Mar '04       45.0000   
     May '06       40.0000   Aug '05       83.0000   Nov '04       78.0000   Feb '04       46.0000  
     Apr '06       34.0000   Jul '05       42.0000   Oct '04       53.0000   Jan '04       38.0000   
     Mar '06       44.0000   Jun '05       67.0000   Sep '04       38.0000                           
     Feb '06       32.0000   May '05       53.0000   Aug '04       48.0000                              
     Jan '06       38.0000   Apr '05       57.0000   Jul '04       54.0000

SmOke_N · November 29, 2006

That looks great

I'll see if that works with the entire data chuck.

2300-04-CLO          CLOTHING
     Sep '06      459.0000   Dec '05      127.0000   Mar '05      184.0000   Jun '04       27.0000   
     Aug '06       68.0000   Nov '05       58.0000   Feb '05       50.0000   May '04       19.0000   
     Jul '06       74.0000   Oct '05       50.0000   Jan '05       30.0000   Apr '04       68.0000   
     Jun '06       51.0000   Sep '05      530.0000   Dec '04      146.0000   Mar '04       45.0000   
     May '06       40.0000   Aug '05       83.0000   Nov '04       78.0000   Feb '04       46.0000  
     Apr '06       34.0000   Jul '05       42.0000   Oct '04       53.0000   Jan '04       38.0000   
     Mar '06       44.0000   Jun '05       67.0000   Sep '04       38.0000                           
     Feb '06       32.0000   May '05       53.0000   Aug '04       48.0000                              
     Jan '06       38.0000   Apr '05       57.0000   Jul '04       54.0000

It worked here with the above... you can adjust the tabs and such to get more spacing.

wutzke · December 5, 2006

Thanks for your help here.

I started looking at this again in a different way.

The following code works right, except I get an error

called from the Array.au3

Array variable has incorrect number of subscripts or subcript dimension range exceeded

#include<Array.au3>
$message = "Choose a file."
$inFile = FileOpenDialog($message, "@AppDataDir", "DATA (*.csv;*.txt)", 1 + 4 )

If @error Then
    MsgBox(4096,"","No File(s) chosen")
EndIf

$file = FileOpen($inFile, 0)

; Check if file opened for reading OK
If $file = -1 Then
    MsgBox(0, "Error", "Unable to open file.")
    Exit
EndIf



Dim $myArray[60][4]  

$lineCount = 0
While 1
    $line = FileReadLine($file)
    If @error = -1 Then ExitLoop
    $lineLen = StringLen($line)
    $lineCount = $lineCount + 1
    $line = StringLeft($line, 107)
    $chunks = 0
    $inc = 0
    While $chunks < 4
        $chunkStart=($chunks*26)+1
        $strChunk=StringStripWS(StringMid($line,$chunkStart,27),3)
        $aDate=StringLeft($strChunk,7)
        $aNum=StringRight($strChunk,10)
        $inc = $inc +1      
        $myArray[$inc][1]=$aDate
        $myArray[$inc][2]=$aNum
;       MsgBox(0, $myArray[$inc][1], $myArray[$inc][2])
        $chunks = $chunks +1
    WEnd
Wend

FileClose($file)
_FileWriteFromArray("testout.txt",$myArray,1)

/dev/null · December 5, 2006

Thanks for your help here.
I started looking at this again in a different way.

The following code works right, except I get an error
called from the Array.au3
Array variable has incorrect number of subscripts or subcript dimension range exceeded
Dim $myArray[60][4]

as I don't know your input file, I see some possible error sources.

1.) You dim the array with [60][4]. If your file contains more than 60 lines, you will run into that error.

2.) I suspect an error in your logic. You increase the array index within the second while loop. $inc = $inc +1. That can lead to the error you described, as problem nr. 1.) could occur much faster.

3.) _FileWrtiteFromArray is NOT designed to write a 2-dim array to a file!

It would help if you could post your data source.

Cheers

Kurt

Edited December 5, 2006 by /dev/null

wutzke · December 5, 2006

Thanks,

Even if I do

;_ArrayDisplay ( $myArray, "results" )

instead of writing to data I get the error

Sep '06   459.0000   Dec '05      127.0000   Mar '05      184.0000   Jun '04       27.0000  
     Aug '06       68.0000   Nov '05       58.0000   Feb '05       50.0000   May '04       19.0000  
     Jul '06       74.0000   Oct '05       50.0000   Jan '05       30.0000   Apr '04       68.0000  
     Jun '06       51.0000   Sep '05      530.0000   Dec '04      146.0000   Mar '04       45.0000  
     May '06       40.0000   Aug '05       83.0000   Nov '04       78.0000   Feb '04       46.0000  
     Apr '06       34.0000   Jul '05       42.0000   Oct '04       53.0000   Jan '04       38.0000  
     Mar '06       44.0000   Jun '05       67.0000   Sep '04       38.0000                          
     Feb '06       32.0000   May '05       53.0000   Aug '04       48.0000                              
     Jan '06       38.0000   Apr '05       57.0000   Jul '04       54.0000

/dev/null · December 5, 2006

Thanks,
Even if I do
;_ArrayDisplay ( $myArray, "results" )
instead of writing to data I get the error

well... same reason. NOT designed for 2-dim arrays!

Sep '06   459.0000   Dec '05      127.0000   Mar '05      184.0000   Jun '04       27.0000  
     Aug '06       68.0000   Nov '05       58.0000   Feb '05       50.0000   May '04       19.0000  
     Jul '06       74.0000   Oct '05       50.0000   Jan '05       30.0000   Apr '04       68.0000  
     Jun '06       51.0000   Sep '05      530.0000   Dec '04      146.0000   Mar '04       45.0000  
     May '06       40.0000   Aug '05       83.0000   Nov '04       78.0000   Feb '04       46.0000  
     Apr '06       34.0000   Jul '05       42.0000   Oct '04       53.0000   Jan '04       38.0000  
     Mar '06       44.0000   Jun '05       67.0000   Sep '04       38.0000                          
     Feb '06       32.0000   May '05       53.0000   Aug '04       48.0000                              
     Jan '06       38.0000   Apr '05       57.0000   Jul '04       54.0000

is this the format of your input file?

Cheers

Kurt

Edited December 5, 2006 by /dev/null

/dev/null · December 5, 2006

well... same reason. NOT designed for 2-dim arrays!
is this the format of your input file?

Cheers
Kurt

here is something to get you started. There are still some errors in your logic, but I'm sure you'll figure that out

#include<Array.au3>
#include<File.au3>

$message = "Choose a file."
$inFile = FileOpenDialog($message, "@AppDataDir", "DATA (*.csv;*.txt)", 1 + 4 )

If @error Then
    MsgBox(4096,"","No File(s) chosen")
EndIf

$file = FileOpen($inFile, 0)

; Check if file opened for reading OK
If $file = -1 Then
    MsgBox(0, "Error", "Unable to open file.")
    Exit
EndIf



Dim $myArray[1] ; <== CHANGED THIS

$lineCount = 0
While 1
    $line = FileReadLine($file)
    If @error = -1 Then ExitLoop
    $lineLen = StringLen($line)
    $lineCount = $lineCount + 1
    $line = StringLeft($line, 107)
    $chunks = 0
    $inc = 0
    While $chunks < 4
        $chunkStart=($chunks*26)+1
        $strChunk=StringStripWS(StringMid($line,$chunkStart,27),3)
        $aDate=StringLeft($strChunk,7)
        $aNum=StringRight($strChunk,10)
        ;$inc = $inc +1        
        ;$myArray[$inc][1]=$aDate
        ;$myArray[$inc][2]=$aNum
        _ArrayAdd($myArray,$aDate & " " & $aNum); <== ADDED THIS
        ;MsgBox(0, $myArray[$inc][1], $myArray[$inc][2])
        $chunks = $chunks +1
    WEnd
Wend

_ArrayDisplay($myArray,"Test"); <== ADDED THIS
FileClose($file)
_FileWriteFromArray("testout.txt",$myArray,1); <== CHANGED THIS

Cheers

Kurt

wutzke · December 5, 2006

yes it is

what is a better way to write a multi-dimensional array to a file?

Edited December 5, 2006 by wutzke

/dev/null · December 5, 2006

yes it is
what is a better way to write a multi-dimensional array to a file?

loop through the array and write each line of the outputfile with FileWriteLine() or FileOpen/FileWrite/FileClose. Why do you use a 2-dim array for the result anyway?

Cheers

Kurt

wutzke · December 5, 2006

I will be creating an Excel file later

#include<Array.au3>
#include<File.au3>
$message = "Choose a file."
$inFile = FileOpenDialog($message, "@AppDataDir", "DATA (*.csv;*.txt)", 1 + 4 )

If @error Then
    MsgBox(4096,"","No File(s) chosen")
EndIf

$file = FileOpen($inFile, 0)

; Check if file opened for reading OK
If $file = -1 Then
    MsgBox(0, "Error", "Unable to open file.")
    Exit
EndIf

; Read in lines of text until the EOF is reached

; msg "configure next stock item"
;time out in 2 seconds and go to Next

;format  
;mgf           |date     |date
;stock Number  |num sales|num sales
; auto group for excel 


Dim $myArray[41][4]  
    $inc = 0
$lineCount = 0
While 1
    $line = FileReadLine($file)
    If @error = -1 Then ExitLoop
    $lineLen = StringLen($line)
    $lineCount = $lineCount + 1
    $line = StringLeft($line, 107)
    $chunks = 0
    While $chunks < 4
        $chunkStart=($chunks*26)+1
        $strChunk=StringStripWS(StringMid($line,$chunkStart,27),3)
        $aDate=StringLeft($strChunk,7)
        $aNum=StringRight($strChunk,10)
        $inc = $inc +1      
        $myArray[$inc][1]=$aDate
        $myArray[$inc][2]=$aNum
        $chunks = $chunks +1
    WEnd
Wend

$rows = UBound($myArray)
$cols = UBound($myArray, 2)
$dims = UBound($myArray, 0)


FileClose($file)

    $outline1 =  ""
    $outline2 =  ""
$inc = 0
$output_filename = "testout.csv"
while $inc < $rows -1
    $inc = $inc +1  
    $outline1 =  $outline1 & $myArray[$inc][1] & "," 
    $outline2 =  $outline2 & $myArray[$inc][2] & "," 
wend    

    MsgBox(0,"",$outline1)
    FileWriteLine($output_filename,$outline1)
    FileWriteLine($output_filename,$outline2)

Arrays - Transpose

Recommended Posts

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Create an account or sign in to comment

Create an account

Sign in

Recently Browsing 0 members