2D Array sum up data

[Harmageddon]

Hi guys,

I'm just started scripting and i got into kinda a strugle where I can't figure it out by myself how to get the data I need from the array.

The example of Array i have:

Item ID	Item Name	Currency	Units	Client ID
1	Name1	USD	10	5656565
2	Name2	EUR	15	56461314
3	Name3	EUR	6	8465142
4	Name4	USD	10	5456421
4	Name4	USD	10	213248
5	Name5	EUR	10	5481231
5	Name5	EUR	-2	121554686

From this Array I need to make new array to look like this:

Item ID	Item Name	Currency	Units
1	Name1	USD	10
2	Name 2	EUR	15
3	Name3	EUR	6
4	Name4	USD	20
5	Name5	EUR	8

 

I've tried to search the forums for examples to make this work, however, lack of knowledge is not helping it  Maybe someone has had similar case where they needed to sum up Array.

 

Thanks in advance.

[AutoBert]

Try to adapt _ArraySum2DCol from this script:

expandcollapse popup

#include <File.au3>
#include <array.au3>


Global $aFile

_FileReadToArray("2dSum.txt", $aFile)
Global $a2D[$aFile[0]][2]
_ArrayDelete($aFile, 0)
For $i = 0 To UBound($aFile) - 1
    $aSplit = StringSplit($aFile[$i], " ", 2)
    $a2D[$i][0] = $aSplit[0]
    $a2D[$i][1] = $aSplit[1]
Next

;Summierung
$aResRow = _ArraySum2DRow($a2D) ;Result in one Row
_ArrayDisplay($aResRow, "Summen")
$aResCol = _ArraySum2DCol($a2D, 0, 1)
_ArrayDisplay($aResCol, "Summen")

Func _ArraySum2DRow(ByRef $avArray)
    Local $aTmp = _ArrayUnique($avArray)
    _ArrayDelete($aTmp, 0)
    _ArraySort($aTmp)
    Local $aRes[2][UBound($aTmp)]
    For $i = 0 To UBound($aTmp) - 1
        $aRes[0][$i] = $aTmp[$i]
    Next
    For $i = 0 To UBound($avArray) - 1
        For $j = 0 To UBound($aRes, 2) - 1
            ;ConsoleWrite($avArray[$i][0] & @TAB & $avArray[$i][1] & @CRLF)
            If $aRes[0][$j] = $avArray[$i][0] Then $aRes[1][$j] += $avArray[$i][1]
        Next
    Next
    Return $aRes
EndFunc   ;==>_ArraySum2DRow

Func _ArraySum2DCol(ByRef $avArray, $iUnique = 0, $iResult = 1)
    Local $aTmp = _ArrayUnique($avArray, $iUnique)
    _ArrayDelete($aTmp, 0)
    _ArraySort($aTmp)
    Local $aRes[UBound($aTmp, 1)][2]
    For $i = 0 To UBound($aTmp) - 1
        $aRes[$i][$iUnique] = $aTmp[$i]
        For $j = 0 To UBound($avArray) - 1
            If $aRes[$i][$iUnique] = $avArray[$j][$iUnique] Then
                ;ConsoleWrite($aRes[$i][$iUnique] & @TAB & $avArray[$j][$iUnique] & @CRLF)
                $aRes[$i][1] += $avArray[$j][$iResult]
            EndIf
        Next
    Next
    Return $aRes
EndFunc   ;==>_ArraySum2DCol

Tested with 2dsum.txt

[Malkey]

Try this.

#include <Array.au3>

Local $aArray[8][5] = [ _
        ["Item ID", "Item Name", "Currency", "Units", "Client ID"], _
        [1, "Name1", "USD", 10, 5656565], _
        [2, "Name2", "EUR", 15, 56461314], _
        [3, "Name3", "EUR", 6, 8465142], _
        [4, "Name4", "USD", 10, 5456421], _
        [4, "Name4", "USD", 10, 213248], _
        [5, "Name5", "EUR", 10, 5481231], _
        [5, "Name5", "EUR", -2, 121554686]]
_ArrayDisplay($aArray, "Original Array")

;Local $aArr = $aArray
;ReDim $aArr[UBound($aArr)][4] ; Remove the last "Client ID" column.
; Or
Local $aArr = _ArrayExtract($aArray, -1, -1, -1, UBound($aArray, 2) - 2)

_ArraySort($aArr, 0, 1) ; Use only if array is not sorted.

_ArrayDisplay($aArr, "4 columns")

; Sum the 4th columns of adjacent rows, if columns 1 to 3 of the adjacent rows are equal. And, remove the duplicate row.
For $i = UBound($aArr) - 1 To 2 Step -1
    If $aArr[$i][0] = $aArr[$i - 1][0] And $aArr[$i][1] = $aArr[$i - 1][1] And $aArr[$i][2] = $aArr[$i - 1][2] Then
        $aArr[$i - 1][3] += $aArr[$i][3]
        _ArrayDelete($aArr, $i)
    EndIf
Next

_ArrayDisplay($aArr, "Sum Identical data")

 

[pixelsearch]

@AutoBert: I found that 9 lines from your example could be replaced by 2 only, with exactly the same result :

;~ Global $aFile
;~ _FileReadToArray("2dSum.txt", $aFile)
;~ Global $a2D[$aFile[0]][2]
;~ _ArrayDelete($aFile, 0)
;~ For $i = 0 To UBound($aFile) - 1
;~     $aSplit = StringSplit($aFile[$i], " ", 2)
;~     $a2D[$i][0] = $aSplit[0]
;~     $a2D[$i][1] = $aSplit[1]
;~ Next

Global $a2D[0][2]
_FileReadToArray("2dSum.txt", $a2D, $FRTA_NOCOUNT," ")

A confirmation with _ArrayDisplay($a2D) shows that the Array is the same in both cases. Hope I'm not mistaken 
 

[Harmageddon]

35 minutes ago, Malkey said:

Try this.

#include <Array.au3>

Local $aArray[8][5] = [ _
        ["Item ID", "Item Name", "Currency", "Units", "Client ID"], _
        [1, "Name1", "USD", 10, 5656565], _
        [2, "Name2", "EUR", 15, 56461314], _
        [3, "Name3", "EUR", 6, 8465142], _
        [4, "Name4", "USD", 10, 5456421], _
        [4, "Name4", "USD", 10, 213248], _
        [5, "Name5", "EUR", 10, 5481231], _
        [5, "Name5", "EUR", -2, 121554686]]
_ArrayDisplay($aArray, "Original Array")

;Local $aArr = $aArray
;ReDim $aArr[UBound($aArr)][4] ; Remove the last "Client ID" column.
; Or
Local $aArr = _ArrayExtract($aArray, -1, -1, -1, UBound($aArray, 2) - 2)

_ArraySort($aArr, 0, 1) ; Use only if array is not sorted.

_ArrayDisplay($aArr, "4 columns")

; Sum the 4th columns of adjacent rows, if columns 1 to 3 of the adjacent rows are equal. And, remove the duplicate row.
For $i = UBound($aArr) - 1 To 2 Step -1
    If $aArr[$i][0] = $aArr[$i - 1][0] And $aArr[$i][1] = $aArr[$i - 1][1] And $aArr[$i][2] = $aArr[$i - 1][2] Then
        $aArr[$i - 1][3] += $aArr[$i][3]
        _ArrayDelete($aArr, $i)
    EndIf
Next

_ArrayDisplay($aArr, "Sum Identical data")

 

Thanks Malkey, it works almost perfect. The only thing is that when I add it to the main script with more data as an example from the sample data:

1	Name1	USD	10
1	Name1	USD	5
1	Name1	USD	15
2	Name2	EUR	1
2	Name2	EUR	2
2	Name2	EUR	3
2	Name2	EUR	-2

 I get the result 

1	Name1	USD	10
1	Name1	USD	20
2	Name2	EUR	4

 

All other data past row 1 and 2 are added correctly as far as I have checked with the data I have.

[Malkey]

If your are not having column header titles in the array, then change 

For $i = UBound($aArr) - 1 To 2 Step -1

to
 

For $i = UBound($aArr) - 1 To 1 Step -1

This will include the first row of data  that used to be the excluded column headers row.