Array extract - removing rows that has a blank field

[jchd]

Current AutoIt regexp still uses the (now outdated) PCRE1 library which uses a stack. Current PCRE2 uses the heap and most limts PCRE1 had are now things of the past.

Lets hope PCRE2 will be part of some new AutoIt release.

[MrCheese]

I'm stuck a little.

I can extract column 0, and then column 3 (ID03) for example, but then I am not able to insert those two columns into a new temporary array to delete and export.

I'm not sure how to represent the $aRange.

$sFilePath = $filedir & $csvload

        _FileReadToArray($sFilePath, $aMaster, $FRTA_NOCOUNT, ",")

        $iRowsMaster = UBound($aMaster, $UBOUND_ROWS)


        Dim $aTemp_Base[$iRowsMaster][2]

        Local $extract1 = _ArrayExtract($aMaster, -1, -1, 0, 0) ; THIS WORKS

        

        _ArrayDisplay($extract1, "1D extract", Default, 8) ; THIS WORKS

        Local  $aRange[4] = [3, 3, 5, 9] ; THIS WAS A TEST, BUT DOESN'T SHOW ANYTHING

        Local $aTmp = $aTemp_Base
        
        _ArrayInsert($aTmp, $aRange, $extract1)

        _ArrayDisplay($aTmp, "2D array - inserted", Default, 8)

 

[MrCheese]

i ended up looping to delete columns that I don't want in the interim 

[MrCheese]

expandcollapse popup

For $i = 0 To $aIDTotal - 1
        
        $sFilePath = $filedir & $csvload

        _FileReadToArray($sFilePath, $aMaster, $FRTA_NOCOUNT, ",")

        $iRowsMaster = UBound($aMaster, $UBOUND_ROWS) - 1 ; counts rows
        $iColsMaster = UBound($aMaster, $UBOUND_COLUMNS) - 1 ; counts columns

        $sRow = 0
        $sSearch = $IDno ; id number to extract

        Local $iIndex = _ArraySearch($aMaster, $sSearch, 0, 0, 0, 1, 1, $sRow, True) ; searches for the column to keep

        $aTemp = $aMaster ; temp array

        For $j = $iColsMaster To 0 Step -1
            If $j = 0 Or $j = $iIndex Then
                ContinueLoop
            Else
            EndIf
            _ArrayColDelete($aTemp, $j)
        Next

        For $r = $iRowsMaster To 0 Step -1
            If $aTemp[$r][1] = "" Then
                _ArrayDelete($aTemp, $r)
            Else
            EndIf

        Next
        _ArrayDisplay($aTemp, "Array-RowDelete: " & $sSearch)

        $sFilePath = $filedir & "\export\"& $IDno & "-Export.csv"
        _FileWriteFromArray($sFilePath,$aTemp,Default,Default,",")

    Next

 

This loops through the IDs to export - but it literally takes forever to delete the rows especially on more sparse IDs.

Is there a better way?

Thanks for your help

[MrCheese]

I ended up using this:

but I would love to speed up the column delete by inserting columns instead

Func _DeleteEmptyRows($aArray)
    Local $Rows = UBound($aArray, 1)
    Local $Cols = UBound($aArray, 2)
    Local $aTemp[$Rows][$Cols]
    Local $not_empty
    Local $Count = 0

    ;Loop through rows
    For $Y = 0 To $Rows - 1
        $not_empty = 0

        ;Loop through columns
        For $X = 0 To $Cols - 1

            ;Copy all columns to temp array even if they are all empty
            $aTemp[$Count][$X] = $aArray[$Y][$X]

            ;if column 1 contains data then its kept, otherwise deleted
            If $aArray[$Y][1] <> "" Then $not_empty = BitOR($not_empty, 1)
        Next

        ;If the row has any data, increment, else keep overwriting last row until it contains something
        If $not_empty Then $Count += 1
    Next

    ReDim $aTemp[$Count][$Cols]
    Return $aTemp
EndFunc   ;==>_DeleteEmptyRows

 

Edited July 27, 2018 by MrCheese

[MrCheese]

and then... i ended up doing this - which compiles a new array by pulling the columns and skipping blank rows in the ID array.

its a lot quicker

expandcollapse popup

#include <Array.au3>

_FileReadToArray($sFilePath, $aIDMAP, $FRTA_NOCOUNT, ",") ; ID array

$iRowsIDMAP = UBound($aIDMAP, $UBOUND_ROWS) - 1

$sFilePath = $filedir & $csvload

_FileReadToArray($sFilePath, $aMaster, $FRTA_NOCOUNT, ",")

$iRowsMaster = UBound($aMaster, $UBOUND_ROWS) - 1 ; counts rows
$iColsMaster = UBound($aMaster, $UBOUND_COLUMNS) - 1 ; counts columns

For $i = 0 To $iRowsIDMAP - 1
    
    $sRow = 0
    $IDno = $iRowsIDMAP[$i][1]
    $sSearch = $IDno

    Local $iIndex = _ArraySearch($aMaster, $sSearch, 0, 0, 0, 1, 1, $sRow, True) ; search for the ID


    Local $extract1 = _ArrayExtract($aMaster, -1, -1, 0, 0) ; extracts the key array
    Local $extract2 = _ArrayExtract($aMaster, -1, -1, $iIndex, $iIndex) ; extracts the array containing the desired ID header


    local $aTemp[ubound($extract1)][2]
    local $row = 0
    for $j = 0 to UBound($extract1) - 1
        if $extract2[$j] = "" Then ; if field is empty in the ID field then it skips
    Else ; fills the row with the corresponding information
        $aTemp[$row][0] = $extract1[$j] ; without adjusting by using $row then you end up with gaps when a row is skipped
        $aTemp[$row][1] = $extract2[$j]
        $row = $row + 1
        EndIf
    Next
        
    $sFilePath = $filedir & "\export\"& $IDno & "-Export.csv"
    _FileWriteFromArray($sFilePath,$aTemp,Default,Default,",")

Next

 

Edited July 27, 2018 by MrCheese

[FrancescoDiMuro]

@MrCheese

You could try SQLite to make things even faster

[mikell]

So you're not seduced by my little regex monster ?  

Anyway to speed up your script while using arrays/loops you might as much as possible avoid the use of _Array* funcs

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

Local $a
_FileReadToArray("IDArray.csv", $a, $FRTA_NOCOUNT, ",")
_ArrayDisplay($a)

Local $rows = UBound($a)

For $col = 1 to 9
  Local $b[$rows][2] , $n = 0
  For $i = 0 to $rows-1
    If $a[$i][$col] <> "" Then 
          $b[$n][0] = $a[$i][0]
          $b[$n][1] = $a[$i][$col]
          $n += 1
    EndIf
  Next
  Redim $b[$n][2] 
 _ArrayDisplay($b)
Next

 

[FrancescoDiMuro]

@mikell
I'd like to learn more about StringRegExp(Replace), but I have never so much time, and every try I do, brings me unexpected results
From a such powerful tools, arise a lot of knowledge.
Gotta learn more about!

[jchd]

You really should, it's a excellent investment.

[FrancescoDiMuro]

I have seem almost every tutorial about ( on the Help file, here on the Forum ), but I think that I have not enough "material" to try with, in order to understand at my best the StringRegExp() function.
I think I need a big test file to try with!
 

[RTFC]

@MrCheese: This is a bit premature, because the current E4A version (4.4) does not yet have the direct write-out to CSV (already working, will be available in next release), but just to provide an alternative that will be size-unlimited (in x64: whatever fits into your virtual memory; max size in x86: below 2GB) in E4A v4.5 (provided you then convert the .csv directly to .mat file first with the matrix file converter, and load that matrix directly, avoiding use of AutoIt arrays altogether). This is just as demo:

#include "Eigen4AutoIt.au3"

_Eigen_StartUp()

Global $aIDMAP
_FileReadToArray("IDarray.csv", $aIDMAP, $FRTA_NOCOUNT, ",") ; ID array

$matIDs=_Eigen_CreateMatrix_FromArray($aIDMAP)
_matrixDisplay($matIDs,"matIDs")

For $cc=1 To 9
    If _Eigen_ConditAny_Col($matIDs,$cc,">",0)=False then ContinueLoop

    $mask=_Eigen_ConditMask_Col($matIDs,$cc,">",0)
    $matNew=_Eigen_CreateMatrix_FromArows_Mask($matIDs,$mask)

    If Not @error and $matNew>0 Then
        _Eigen_CreateArray_FromMatrix($matNew,$aIDMAP)
        _MatrixDisplay($MatNew,"rows with filled col " & $cc )

 ; new func available in E4A v4.5: _Eigen_WriteMatrix_ToTextFile($matNew,$outfile) directly (default format: CSV)
 ; for now, write via array to file
        $outfile="ID"&$cc&"array.csv"
        _FileWriteFromArray($outfile,$aIDMAP,Default,Default,",")
    EndIf
    _Eigen_ReleaseMatrix($matNew)
    _Eigen_ReleaseMatrix($mask)
Next

_Eigen_CleanUp()

 

Edited July 27, 2018 by RTFC

[jchd]

I didn't try to make the regexp help any kind of tutorial, just a handy digest of most of the available constructs.
I know there are a few errors or inaccuracies left, which I don't have much time to fix.
In case Jon someday decides to switch to the now-stable PCRE2 API n some future AutoIt release, functions and help will have to be seriously reworked.

Still, PCRE1 in current AutoIt is powerful enough for many purposes. There are a large number of good PCRE resources floating around, like this one: https://www.regular-expressions.info/index.html

Always practice while on the learning curve and this is one of the best regex workbench: https://regex101.com/
Leave the flavor as pcre(php) as its compatible with AutoIt PCRE. You can see an live explanation in plain english of the typed regex, you can test substitution (as in StringRegExpReplace) and also debug your expressions or see them work (or fail) step by step.