Delete row from array where a column matches a value

[Deon]

I have a CSV file which contains eg 1000 rows.

The file is split into two types, as aexample I've replaced the data with fruit items in the attachment.

I want to split the array into two separate arrays (and then write them to files)... so one array will contain all the 'Apple' items, and one array will contain all the 'Orange' items.

I tried using one of the few scripts I found which delete blank rows but then change the expression to match 'Apple' and 'Orange' but it didn't work, nothing was altered in the array.

 

Appreciate any help you can offer

 

[InunoTaishou]

I can understand the topic title but not the topic description.

If you're just wanting to delete a row you can use _ArrayDelete (check the help file for an example on deleting a row from a 2D array)

As for splitting the array to two separate ones, you can create an array with a default size and use ReDim to make the array grow smaller/larger. You can also use _ArrayAdd and _ArrayInsert to add more rows to an existing array.

Maybe post some code on what you're trying to accomplish and we can help you better.

[kylomas]

Deon,

This might get you started...

expandcollapse popup

#include <array.au3>

; generate test file --------------------------------------------------------------------

Local $st = TimerInit()
Local $iNumRecs = 5000
Local $sTestCSV = @ScriptDir & '\test.csv'
Local $sOut

For $i = 1 To $iNumRecs
    $sOut &= 'Food,Fruit,' & (Random(0, 1, 1) ? 'Apple' : 'Orange') & ',$1.00' & @CRLF
Next

FileDelete($sTestCSV)
FileWrite($sTestCSV, $sOut)
ConsoleWrite('Time to gen file = ' & Round(TimerDiff($st) / 1000, 3) & ' seconds' & @CRLF)

; create 2D array from test file --------------------------------------------------------

$st = timerinit()
Local $aTmp1 = StringSplit(FileRead($sTestCSV), @CRLF, $STR_NOCOUNT + $STR_ENTIRESPLIT)
local $aCSV10[ubound($aTmp1)][4], $aTmp2

for $i = 0 to ubound($aTmp1) - 1
    $aTmp2 = stringsplit($aTmp1[$i],',', $STR_NOCOUNT)
    for $j = 0 to ubound($aTmp2) - 1
        $aCSV10[$i][$j] = $aTmp2[$j]
    Next
Next
ConsoleWrite('Time to gen 2D array = ' & Round(TimerDiff($st) / 1000, 3) & ' seconds' & @CRLF)

; split array based on value in col 3 ---------------------------------------------------

$st = timerinit()
local $aApples[ubound($aCSV10)][4], $aOranges[ubound($aCSV10)][4], $iApples = 0, $iOranges = 0

for $i = 0 to ubound($aCSV10) - 1
    if $aCSV10[$i][2] = 'Apple' then
        for $j = 0 to ubound($aCSV10,2) - 1
            $aApples[$iApples][$j] = $aCSV10[$i][$j]
        next
        $iApples += 1
    endif
    if $aCSV10[$i][2] = 'Orange' then
        for $j = 0 to ubound($aCSV10,2) - 1
            $aOranges[$iOranges][$j] = $aCSV10[$i][$j]
        next
        $iOranges += 1
    endif
Next

redim $aApples[$iApples][4]
redim $aOranges[$iOranges][4]

ConsoleWrite('Time to split arrays = ' & Round(TimerDiff($st) / 1000, 3) & ' seconds' & @CRLF)

_arraydisplay($aApples,'APPLES (' & $iApples & ')' )
_arraydisplay($aOranges,'ORANGES (' & $iOranges & ')')

InunoTaishou  is right, your title does NOT match your description.  You will receive better assistance by posting whatever code you've tried and a sample of your file.

kylomas

[Malkey]

Here is an alternative method not using arrays.  

; Create "Fruita.csv" file
If FileExists("Fruita.csv") Then FileDelete("Fruita.csv")
Local $sOut
For $i = 1 To 5000
    $sOut &= 'Food,Fruit,' & (Random(0, 1, 1) ? 'Apple' : 'Orange') & ',$' &$i & '.00' & @CRLF
Next
FileWrite("Fruita.csv", $sOut)

; Create "Fruit_Orange.csv" file
If FileExists("Fruit_Orange.csv") Then FileDelete("Fruit_Orange.csv")
$st = TimerInit()
Local $sFruitFile = FileRead("Fruita.csv")
FileWrite("Fruit_Orange.csv", StringRegExpReplace($sFruitFile, "(.*Apple.*\R*)", ""))
ConsoleWrite('"Fruit_Orange.csv" generated in ' & Round(TimerDiff($st) / 1000, 3) & ' secs :- ' & @LF & FileRead("Fruit_Orange.csv") & @LF)

; Create "Fruit_Apple.csv" file
If FileExists("Fruit_Apple.csv") Then FileDelete("Fruit_Apple.csv")
$st2 = TimerInit()
FileWrite("Fruit_Apple.csv", StringRegExpReplace($sFruitFile, "(.*Orange.*\R*)", ""))
ConsoleWrite("--------------" & @LF)
ConsoleWrite('"Fruit_Apple.csv" generated in ' & Round(TimerDiff($st2) / 1000, 3) & ' secs :- ' & @LF & FileRead("Fruit_Apple.csv") & @LF)

; Cleanup files
FileDelete("Fruita.csv")
FileDelete("Fruit_Orange.csv")
FileDelete("Fruit_Apple.csv")

The above script deletes the fruit not wanted in the new file. i.e. If Oranges are needed, then Apples are deleted.

Whereas, the following script keeps the fruit that is wanted in the new file. i.e.  If Oranges are needed, then "not-Oranges" are deleted.

; https://www.autoitscript.com/forum/topic/180240-delete-row-from-array-where-a-column-matches-a-value/?do=findComment&comment=1293819
; Create "Fruita.csv" file
If FileExists("Fruita.csv") Then FileDelete("Fruita.csv")
Local $sOut
For $i = 1 To 6000
    $sOut &= 'Food,Fruit,' & (Random(0, 2, 1) ? (Random(0, 1, 1) ? 'Apple' : "Pear") : 'Orange') & ',$' &$i & '.00' & @CRLF
Next
FileWrite("Fruita.csv", $sOut)

; Create "Fruit_Orange.csv" file
If FileExists("Fruit_Orange.csv") Then FileDelete("Fruit_Orange.csv")
$st = TimerInit()
Local $sFruitFile = FileRead("Fruita.csv")
;FileWrite("Fruit_Orange.csv", StringRegExpReplace($sFruitFile, "(.*Apple.*\R*)", ""))
FileWrite("Fruit_Orange.csv", StringRegExpReplace($sFruitFile, "Food,Fruit,(?!Orange).*\R?", ""))
ConsoleWrite('"Fruit_Orange.csv" generated in ' & Round(TimerDiff($st) / 1000, 3) & ' secs :- ' & @LF & FileRead("Fruit_Orange.csv") & @LF)

; Create "Fruit_Apple.csv" file
If FileExists("Fruit_ApplePear.csv") Then FileDelete("Fruit_ApplePear.csv")
$st2 = TimerInit()
;FileWrite("Fruit_Apple.csv", StringRegExpReplace($sFruitFile, "(.*Orange.*\R*)", ""))
FileWrite("Fruit_ApplePear.csv", StringRegExpReplace($sFruitFile, "Food,Fruit,(?!Apple|Pear).*\R?", ""))
ConsoleWrite("--------------" & @LF)
ConsoleWrite('"Fruit_Apple.csv" generated in ' & Round(TimerDiff($st2) / 1000, 3) & ' secs :- ' & @LF & FileRead("Fruit_ApplePear.csv") & @LF)

; Cleanup files
FileDelete("Fruita.csv")
FileDelete("Fruit_Orange.csv")
FileDelete("Fruit_ApplePear.csv")

 

Edited January 30, 2016 by Malkey

[Deon]

Thanks guys!

 

On 1/28/2016 at 5:08 PM, kylomas said:

InunoTaishou  is right, your title does NOT match your description.  You will receive better assistance by posting whatever code you've tried and a sample of your file.

Sorry, I had two trains of thought in my head at once. My initial idea was to duplicate the file, then remove the matching ones from one and the other to create two separate files with the correct value. Re-reading my title/post, this was not very clear... I apologise!

 

On 1/28/2016 at 10:48 PM, Malkey said:

Here is an alternative method not using arrays. 

 

This works perfectly! Only takes about 5 seconds to run through 10,000 lines of data and split them into the correct files.. thanks Malkey!