Looking for a pre-concocked array filter func

[sshrum]

Please note: I already know about SQLite...what I am aiming for is built-in array filtering support.

I've been writting apps in AutoIt for some time now, created many arrays, and defined way too many loops for filtering them.

I'd love to find a function that would allow for array filtering, something with search engine-ish style facilities. Ultimately, it should be able to handle single and 2-dim arrays with at least the following features/support:

+ Full array / column specific searches

+ conditionals (+,-,&,|)

+ case sensitivity / insensitivity flag

Something like...

Func _ArrayFilter($aArray, $sSearch, $iColumn = -1, $bCaseSensitive = false)

For starters, the array returned will be a duplicate of the original array but with only those rows that meet the filter params. Results where no rows were foudn will return an array with $aArray[0] = 0

Now I know to make one properly will take some time and may need a deeper set of options (feel free to chime in on additional options), so before I sit down and crank one out, I figured I'd post to see if someone has already started/finished one.

[JBeef]

freudian? concocted. ~Jap

[randallc]

freudian? concocted. ~Jap

Hi,

going to be a long job; do you consider "ADOR" built-in, as that would be the easiest way to get up a set of funcs, i think.

i have done a func for 1 and 2D arrays for sorting; wouldn't be too hard to modify.

Best, randall

[sshrum]

Ok...this is what I made.

I've set up the evals to bomb out as soon as possible to speed things up.

Tested with a 30487 x 41 dimensioned array filled with 10 megs of data (a dump of the WMP metatag data from my music lib). Should work just fine with single dim'ed as well.

On my system a full array search of 2 terms is ~10 seconds

Column specific search obviously go way faster: ~1 - 2 seconds

Search terms can use '-' and '+' for NOT and AND conditionals: "+BT -Chicane" Search terms without conditionals are treated as OR conditions

Feel free to try it up and let me know if you find any issues:

expandcollapse popup

Func _ArrayFilter($aSearch, $sTerms, $iBase=1, $iCol=-1, $iCase=2)
    Consolewrite("(_ArrayFilter) ")
    $iTimer = TimerInit()

; get the search array dimensions
    $iRows = ubound($aSearch) - $iBase
    if $iRows = 0 then return $aSearch
    $iCols = ubound($aSearch,2)

; create a empty storage array that matches the dimensions of the search array  
    if $iCols = 1 then
        Dim $aStorage[$iRows+1]
        $aStorage[0] = 0
        ConsoleWrite("[ " & $iRows & " ] ")
    Else
        Dim $aStorage[$iRows+1][$iCols]
        $aStorage[0][0] = 0
        ConsoleWrite("[ " & $iRows & " x " & $iCols & " ] ")
    EndIf

; error out and return an empty array if the requested column does not exist
    if $iCol > $iCols then 
        seterror(1)
        return $aStorage
    EndIf

; split up the search string and seperate into conditional sets (remove '-'/'+' from front of words)
    Consolewrite("-> Looking for '" & $sTerms & "' ")
    Dim $aMinus[1]
    Dim $aPlus[1]
    Dim $aOr[1]
    $aTerms = StringSplit($sTerms, " ")
    for $i = 1 to $aTerms[0]
        if stringleft($aTerms[$i],1) = "-" Then
            _ArrayAdd($aMinus, stringtrimleft($aTerms[$i],1))
        elseif stringleft($aTerms[$i],1) = "+" Then
            _ArrayAdd($aPlus, stringtrimleft($aTerms[$i],1))
        else
            _ArrayAdd($aOr, $aTerms[$i])
        EndIf
    Next
    $aMinus[0] = ubound($aMinus) - 1
    $aPlus[0] = ubound($aPlus) - 1
    $aOr[0] = ubound($aOr) - 1
    
; output some debug info
    If $iCol > -1 Then
        ConsoleWrite("in column " & $iCol & "...")
    Else
        ConsoleWrite("in all fields...")
    EndIf

; initialize our keeper count
    $iKeep = 0

; process the search array a row at a time
    For $i = $iBase to $iRows
        
; reset the keeper flag and evalstring
        $bKeep = False
        $sKeep = "F"
        $sEvalString = ""
        
; build the search string from the row data
        if $iCols = 1 Then
            $sEvalString = $aSearch[$i]         
        elseif $iCol > -1 then
            $sEvalString = $aSearch[$i][$iCol]
        Else
            for $j = 0 to ($iCols - 1)
                $sEvalString &= $aSearch[$i][$j] & " -|- "
            Next
        EndIf
        
; if ANY minus value found, skip out to the next row (2) - no need to process any more
        if $aMinus[0] > 0 Then
            for $j = 1 to $aMinus[0]
                if stringinstr($sEvalString, $aMinus[$j], $iCase) then ContinueLoop(2)
            Next
        EndIf
        
; if ANY plus value NOT found, skip out to the next row (2) - no need to process anymore; otherwise set the keep flag to true
        if $aPlus[0] > 0 Then
            for $j = 1 to $aPlus[0]
                if not stringinstr($sEvalString, $aPlus[$j], $iCase) then ContinueLoop(2)
            Next
            $bKeep = true   
        EndIf
        
; if ANY or value found, flag and skip out (1)
        if $aOr[0] > 0 then
            for $j = 1 to $aOr[0]
                if stringinstr($sEvalString, $aOr[$j], $iCase) then 
                    $bKeep = True
                    ExitLoop
                EndIf
            Next
        EndIf
        
; row passed all eval tests, add it to the storage array
        if $bKeep Then
            $iKeep += 1
            if $iCols = 1 Then
                $aStorage[$iKeep] = $aSearch[$i]
            Else
                for $j = 0 to $iCols - 1
                    $aStorage[$iKeep][$j] = $aSearch[$i][$j]
                Next
            EndIf
        EndIf
    Next
    
; update the row count at the top of the array and remove any unused rows from the storage array
    if $iCols = 1 Then
        $aStorage[0] = $iKeep
        Redim $aStorage[$iKeep+1]
    Else
        $aStorage[0][0] = $iKeep
        Redim $aStorage[$iKeep+1][$iCols]
    EndIf
    ConsoleWrite($iKeep & " records found; complete [" & Round(TimerDiff($iTimer)/1000,2) & " secs]" & @CRLF)
    return $aStorage
EndFunc

EDIT: Single dim column evals...1 instead of 0

Edited September 26, 2007 by sshrum

[sshrum]

freudian? concocted. ~Jap

Why yes...I do have a liking of poultry.

[weaponx]

Why aren't you just using RegEx against each array element?

[sshrum]

Why aren't you just using RegEx against each array element?

No particular reason...I can see an advantage to using that instead but I'm not fluent with creating RegEx strings.

Let me ask you this...can I pass a search string with conditionals in it like "+BT -Chicane" and have RegEx parse that without me having to seperate out the conditional types (like I'm doing now)? Looks like it can.

I'll play around with RegEx over the next couple of days and if I feel comfortable with it, I'll switch out the stringinstr() funcs

[randallc]

Hi,

I have not converted a full UDF, but using my RegExp func, to search for all lines with 1 search term, I get 23x as fast searching the whole line ;

\Just an example to get you going?

Array2D in my signature

(6Mb file 6 secs/ 0.3 secs)

; _ArrayFilterex.au3
#AutoIt3Wrapper_UseAnsi=y ;RegExp faster if large
#include-once
#include<Array2D.au3>
#include<file.au3>
#include<_ArrayFiltersshrum.au3>
#include<_FindLinesRegExpDetail.au3>
local $s_File=FileOpenDialog("Select", @ScriptDir & "", "Text (*.txt)", 1 + 4 ),$ar1_Array_Row1[1]
;~ local $s_File=@ScriptDir &"\DELL9150IndexJul07Recent_S_.txt",$ar1_Array_Row1[1]
$iTimerT = TimerInit()
_FileReadToArray($s_File,$ar1_Array_Row1)
if $ar1_Array_Row1[UBound($ar1_Array_Row1)-1]="" then _ArrayDelete($ar1_Array_Row1,UBound($ar1_Array_Row1)-1)
$ar1_Array_Row1[0]=$ar1_Array_Row1[UBound($ar1_Array_Row1)-1]
_ArrayDelete($ar1_Array_Row1,UBound($ar1_Array_Row1)-1)
ConsoleWrite("UBound($aSearch)="&UBound($ar1_Array_Row1)&@LF)
;~ _ArrayDisplay($ar1_Array_Row1,"",0)
local $aSearch=_Array2DCreateFromArrayst($ar1_Array_Row1)
ConsoleWrite("UBound($aSearch)="&UBound($aSearch)&@LF)
$sTerms="search"
$aSearch=_ArrayFilter($aSearch, $sTerms)
ConsoleWrite("UBound($aSearch)="&UBound($aSearch)&@LF)
local $i_Time1=TimerDiff($iTimerT)/1000
ConsoleWrite(" records found; complete [" & Round(TimerDiff($iTimerT)/1000,2) & " secs]" & @CRLF)
;~ _ArrayDisplay($aSearch)

;~ Exit
$iTimer = TimerInit()
$aSearch=_FindLinesRegExpDetail( $s_file,  $s_AnswerFile, $sTerms,0,0,1); last param = 0 would search only first column ;18x as fast instaed of 23x
;~ _FindLinesRegExpDetail(ByRef $s_file, ByRef $s_AnswerFile, $s_Searches, $i_Append = 0, $i_Case = 0, $i_NotFoldersF = 0, $i_InRecentF = -1)
ConsoleWrite("UBound($aSearch)="&UBound($aSearch)&@LF)
local $i_Time2=TimerDiff($iTimer)/1000
ConsoleWrite(" records found; complete [" & Round(TimerDiff($iTimer)/1000,2) & " secs]" & @CRLF)
ConsoleWrite("Time Ratio [" & Round($i_Time1/$i_Time2,2) & " x as fast], to include reading file to array" & @CRLF)
_ArrayDisplay($aSearch)

Obviously, I have not done anything other than look for those lines; but I think all your criterioa can be performed with RegExp on the whole file this way...

best, randall

UBound($aSearch)=35814

UBound($aSearch)=35814

(_ArrayFilter) [ 35813 x 6 ] -> Looking for 'search' in all fields...10 records found; complete [3.96 secs]

UBound($aSearch)=11

records found; complete [6.44 secs]

pattern=(?m)(^[^\|]*(?i)search[^\|]*\|.*$)

UBound($aSearch)=10

records found; complete [0.28 secs]

Time Ratio [23.29 x as fast], to include reading file to array

[flyingboz]

Wow, Sean!

I was just sitting down to write this functionality when I decided I would waste a few minutes on the forum!

I've got a paying project I've got to get out the door tonight, but I'll look at what you've got tomorrow -- I'm very intrigued,

and I'm sure the work lives up to your usual standard of excellence.

All the best.

[sshrum]

Damn...it takes me a good second per meg to load my datafile into array format (10 megs = 10 seconds)...your way takes .78 sec

(bowing to the master after trying to snatch the Global Const from the master's keyboard)

Only one problem, when I run the _Find search on my data file, I'm getting nothing back...my _ArrayFilter is returning back 261 entries.

Most likely an issue with the RegEx search parameters but I don't know enuff about those to really troubleshoot it. This is what the _Find sez it is looking for:

pattern=(?m)(^[^\|]*(?i)BT[^\|]*\|.*$)

Here are two records from my pipe-delimited datafile:

7/24/2007 7:22:14 PM|BT||128016||217.066|3499161|wma|False|False|audio||1/2/2004 12:01:00 AM|3|\\nas-2\volume_1\music\Bt\Bt-Superfabulous (Scott Humphtey Radio Mix).wma||Superfabulous [scott Humphtey Radio Mix]|50||0|0|0|0|0|0|0|50|BT|Technology [EP]|BT|8/22/2004 1:40:44

AM|Trance|AMG|Electronica|Nettwerk|4|{3171C0FC-48B6-40AE-B04B-36FA514A3CAA}|{3171C0FC-48B6-40AE-B04B-36FA514A3CAA}|{928851BF-A3C4-467A-9E03-6D5AD5D05924}||2004

7/24/2007 7:36:09 PM|Chicane||192000||412.219|9893784|mp3|False|False|audio|||0|\\nas-2\volume_1\music\Ch\Chicane-In Praise Of The Sun.mp3||In Praise of the Sun|50||0|0|0|0|0|0|0|50||Easy to Assemble CD PROMO||2/21/2006 3:13:42 AM|Trance|||||||||

I'll keep playing with it.

[flyingboz]

You may wish to consider downloading a binary grep utility for testing your regular expressions.

You can quickly test regular expressions against your data file for hits / misses, and then

convert them to au3 expressions when you're comfortable you've got them right.

jgsoft also has a powergrep utility that i've actually been tempted to shell out cash for , lots of neat features, like converting regular expression syntax between several popular languages --perl, php, etc.

my grep --version yields: grep (GNU grep) 2.4.2

[randallc]

pattern=(?m)(^[^\|]*(?i)BT[^\|]*\|.*$)

Woops,

$aSearch=_FindLinesRegExpDetail( $s_file,  $s_AnswerFile, $sTerms,0,0,0); last param = 1 would search only first column ;18x as fast instaed of 23x

Had param wrong..

This is not case-sensitive, of course, as it stands; this works for me, only finds first one of your example..

Best, randall

UBound($aSearch)=3

UBound($aSearch)=3

(_ArrayFilter) [ 2 x 41 ] -> Looking for 'BT' in all fields...1 records found; complete [0.01 secs]

UBound($aSearch)=2

records found; complete [0.08 secs]

pattern=(?m)(^(?i).*BT.*$)

UBound($aSearch)=1

records found; complete [0 secs]

Time Ratio [45.78 x as fast], to include reading file to array

+>16:17:54 AutoIT3.exe ended.rc:0

PS - only an example; not sure how you would use this for your arrays etc... Edited September 27, 2007 by randallc

[sshrum]

I sorta see what your doing...a line by line search before array-ing the data.

While that is faster, I need more control over what is being evaluated, in my case, specific column search capability.

Can RegEx handle queries that look for the delimiter and search on the text between a start and end delimiter? Say like:

Find lines where text contains 'BT' between the 11th and 12th delimiter where the delimiter is '|'.

Also, is it possible to include multiple search sepcifications into 1 regex expression? Based on what I'm reading round the net, it looks like it can.

TIA

[randallc]

Hi,

I'm sure it's possible; [my methods just show it is possible; there must be better syntax to do it all once you know how *****]

check 2nd subitem, case-sens (no"(?i)"), not containing"BT" ("[^BT]*") [ or is this "[^B^T]*" ...?

(?m)(^[^\|]*\|[^BT]*\|.*$)

but it might be awkward.

I am no expert, but have found itt so fast that I have used it a little in some situations..

check 3rd subitem, case-sens (no"(?i)"), containing exactly "BT"

(?m)(^[^\|]*\|[^\|]*\|BT\|.*$)

but there must be better ways to count the number of delimiters; I have just used a literal approach due to lack of expertise

check either ("|")

4th subitem, case-sens (no"(?i)"), containing exactly "BT" OR

4th subitem, case-sens (no"(?i)"), containing exactly "Chicane"

(?m)(^[^\|]*\|[^\|]*\|[^\|]*\|BT\|.*$|^[^\|]*\|[^\|]*\|[^\|]*\|Chicane\|.*$)

There must be a bracketed way as well... randall Edited September 27, 2007 by randallc

[randallc]

OK,

Here s an attempt at a UDF for this;

expandcollapse popup

; _FindLinesRegExpDetail.au3
;~ #AutoIt3Wrapper_UseAnsi=y
#include-once
#include<array.au3>
Func _FindLinesRegExp($s_FileF, $s_Searches, $i_Column = 0, $i_Case = 0, $i_NoWildCards = 1)
    If $i_NoWildCards Then
        $s_Searches = StringReplace(StringReplace($s_Searches, "\", "\\"), ".", "\.");~     $s_Searches = StringReplace(StringReplace($s_Searches, "(", "\("), ")", "\)")
        $s_Searches = StringReplace(StringReplace($s_Searches, "?", "\?"), ".", "*", "\*");~    $s_Searches = StringReplace(StringReplace($s_Searches, "(", "\("), ")", "\)")
    EndIf
    ;=====================================Extra OR needed
    $s_Searches = StringReplace($s_Searches, "|", "+")
    $s_Searches = StringReplace(StringReplace($s_Searches, "+", "|+"), "-", "|^")
    $s_Searches = StringReplace(StringReplace($s_Searches, "=", "|="), " ", "")
    $s_Searches = StringReplace($s_Searches, "&", ".*"); no "or" extra needed
    Local $ar_SearchSplit = StringSplit($s_Searches, "|"), $c = _ArrayDelete($ar_SearchSplit, 0), $arExcludes[1], $ar_SearchSplit2[1], $d = ConsoleWrite("$s_Searches=" & $s_Searches & @LF)
    For $i = 0 To UBound($ar_SearchSplit) - 1
        If $ar_SearchSplit[$i] == "" Then ContinueLoop;_ArrayDelete($ar_SearchSplit, $i)
        If Not StringInStr("+^=&", StringLeft($ar_SearchSplit[$i], 1)) Then _ArrayAdd($ar_SearchSplit2, "[^\|]*" & $ar_SearchSplit[$i] & "[^\|]*")
        If StringLeft($ar_SearchSplit[$i], 1) == "+" Then _ArrayAdd($ar_SearchSplit2, "[^\|]*" & StringTrimLeft($ar_SearchSplit[$i], 1) & "[^\|]*")
        If StringLeft($ar_SearchSplit[$i], 1) == "=" Then _ArrayAdd($ar_SearchSplit2, StringTrimLeft($ar_SearchSplit[$i], 1))
        If StringLeft($ar_SearchSplit[$i], 1) = "^" Then _ArrayAdd($arExcludes, StringTrimLeft($ar_SearchSplit[$i], 1));poost-process extra Excludes in array loop
    Next
    $s_Searches = _ArrayToString($ar_SearchSplit2, "|", 1)
    If FileExists($s_FileF) Then
        Local $h_file = FileOpen($s_FileF, 0), $s_FileRead = StringStripCR(FileRead($h_file)), $a = FileClose($h_file), $i_EndDelim = 1,$s_FirstLine,$i_NoDelim
        If StringInStr($s_FileRead, @LF) Then $s_FirstLine=StringLeft($s_FileRead, StringInStr($s_FileRead, @LF) - 1)
        If not StringInStr($s_FirstLine, "|") Then $i_NoDelim=1 ; need to change first part of search string wildcard in 1D array lines
        If StringInStr($s_FirstLine, "|") Then StringReplace($s_FirstLine, "|", "|")
        Local $i_NumCols = @extended , $sStart = '(?:[^\|]*\|)', $sEnd = $sStart, $d = ConsoleWrite("$s_Searches=" & $s_Searches & @LF)
        If $i_Column = $i_NumCols Then $i_EndDelim = 0
        If StringRight($s_Searches, 6) <> "[^\|]*" Then $sEnd = '\|'
        If StringRight($s_Searches, 6) == "[^\|]*" Then $s_Searches = StringTrimRight($s_Searches, 6)
        $s_Searches &= $sEnd & '{' & $i_EndDelim & '}' & ')' & '.*$)';$c = ConsoleWrite("$s_Searches=" & $s_Searches & @LF)
        If $i_Case = 2 Or Not $i_Case Then $sStart = '(?i)' & $sStart
        if $i_NoDelim then $s_Searches=StringReplace($s_Searches,"[^\|]*",".*"); need to change first part of search string wildcard in 1D array lines
        Local $patternReg = '(?m)(^' & $sStart & '{' & $i_Column & '}(?:' & $s_Searches, $c = ConsoleWrite("pattern=" & $patternReg & @LF);
        Local $ar_Result = StringRegExp($s_FileRead, $patternReg, 3)
        If UBound($arExcludes) > 1 Then
            For $i = 1 To UBound($arExcludes) - 1
                For $j = 0 To UBound($ar_Result) - 1
                    Local $patternReg = $sStart & '{' & $i_Column & '}(?:' & "[^\|]*" & $arExcludes[$i] & $sEnd & '{' & $i_EndDelim & '}' & ')' & '.*', $c = ConsoleWrite("pattern=" & $patternReg & @LF);
                    if $i_NoDelim then $patternReg=StringReplace($patternReg,"[^\|]*",".*"); need to change first part of search string wildcard in 1D array lines
                    If StringRegExp($ar_Result[$j], $patternReg, 0) And ($ar_Result[$j] <> "") Then $ar_Result[$j] = ""
                Next
            Next
        EndIf
        Local $s_Result = StringReplace(StringReplace(_ArrayToString($ar_Result, @LF), @LF & @LF, @LF), @LF & @LF, @LF)
        $ar_Result = StringSplit($s_Result, @LF)
        _ArrayDelete($ar_Result, 0)
        if $ar_Result[UBound($ar_Result) - 1] ="" then _ArrayDelete($ar_Result, UBound($ar_Result) - 1)
        Return $ar_Result
    Else
        SetError(1)
    EndIf
EndFunc   ;==>_FindLinesRegExpoÝ÷ ØLZ^jëh×6; _ArrayFilterex.au3
#AutoIt3Wrapper_UseAnsi=y ;RegExp faster if large
#include-once
#include<Array2D.au3>
#include<file.au3>
#include<_ArrayFiltersshrum.au3>
#include<_FindLinesRegExp.au3>
Local $sString = "Column1|Column2|Column3|Column4|Column5" & @LF & _
        "Column1a|Column2a|Column3a|Column4a|Column5a" & @LF & _
        "Column1b|Column2b|Column3b|Column4b|Column5b" & @LF & _
        "Column1c|Column2c|Column3c|Column4c|Column5c" & @LF & _
        "Column1d|Column2d|Column3d|Column4d|Column5d"
;~ $sString = StringReplace($sString,"|",chr(128))
Local $s_File = @ScriptDir & "\testregexp3.txt", $ar1_Array_Row1[1]
Local $c = FileDelete($s_File), $c = FileWrite($s_File, $sString),$ar_Array2Result[1]
;~ local $s_File=FileOpenDialog("Select", @ScriptDir & "", "Text (*.txt)", 1 + 4 ),$ar1_Array_Row1[1]
;~ local $s_File=@ScriptDir &"\DELL9150IndexJul07Recent_S_.txt",$ar1_Array_Row1[1]
;~ Local $s_File = @ScriptDir & "\ACERIndexJul07Recent_C_.txt", $ar1_Array_Row1[1]
Local $iTimer = TimerInit(), $iCol=0, $i_case=2,$sTerms = "c-2b-d";,$sTerms = "Column1c"
$aSearch = _FindLinesRegExp ($s_File, $sTerms, $iCol, $i_case);_FindLinesRegExp($s_FileF, $s_Searches, $i_Column = 0, $i_NumCols = 1, $i_Case = 0)
ConsoleWrite("UBound($aSearch)=" & UBound($aSearch) & @LF&" records found; complete [" & Round(TimerDiff($iTimer) / 1000, 2) & " secs]" & @CRLF)
;~ _ArrayDisplay($aSearch, "_FindLinesRegpDetail")
if IsArray($aSearch) then $ar_Array2Result = _Array2DCreateFromArrayst ($aSearch)
_ArrayDisplay($ar_Array2Result, "_FindLinesRegpDetail")

Best, Randall

[sshrum]

Trying out the example...mod'ed it to open my file; available here: http://www.shrum.net/uploads/metadata.arr (9+ megs)

Changed $sTerms="bt" but got 0 back. Was expecting like 100+

; _ArrayFilterex.au3
#AutoIt3Wrapper_UseAnsi=y;RegExp faster if large
#include-once
#include<Array2D.au3>
#include<file.au3>
#include<_ArrayFiltersshrum.au3>
#include<_FindLinesRegExp.au3>
Local $iTimer = TimerInit(), $iCol=0, $i_case=2,$sTerms = "c-2b-d";,$sTerms = "Column1c"
local $s_File="f:\code\mmm\source\v.1\metadata.arr", $ar1_Array_Row1[1]
$sTerms = "bt"
$aSearch = _FindLinesRegExp ($s_File, $sTerms, $iCol, $i_case);_FindLinesRegExp($s_FileF, $s_Searches, $i_Column = 0, $i_NumCols = 1, $i_Case = 0)
ConsoleWrite("UBound($aSearch)=" & UBound($aSearch) & @LF&" records found; complete [" & Round(TimerDiff($iTimer) / 1000, 2) & " secs]" & @CRLF)
if IsArray($aSearch) then $ar_Array2Result = _Array2DCreateFromArrayst ($aSearch)
_ArrayDisplay($ar_Array2Result, "_FindLinesRegpDetail")

Is there something special I have to do the the Terms entry?

Edited October 15, 2007 by sshrum

[randallc]

Hi,

works with "$iCol=1" instead of "$iCol=0"

Best, randall