Jump to content
Harmageddon

2D Array sum up data

Recommended Posts

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.

Share this post


Link to post
Share on other sites
AutoBert

Try to adapt _ArraySum2DCol from this script:

#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

Share this post


Link to post
Share on other sites
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")

 

Share this post


Link to post
Share on other sites
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 :)
 

Share this post


Link to post
Share on other sites
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 Malkeyit 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.

Share this post


Link to post
Share on other sites
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.

Share this post


Link to post
Share on other sites

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now

×