Jump to content
Sign in to follow this  

(Yet another) Loop through array identify unique elements / sum columns

Recommended Posts



I have a problem I'm trying to overcome and I've hit a brick wall with a certain aspect of it.  I have an array where one column contains many duplicates and then two other collums that I need summing IF column x is identical.

An example of this array would be...


So as an output I want to be able to basically merge the duplicate data (from column 2) whilst summing columns 3 and 4 (column 1 can be ignored)...


I don't know what the contents of column 2 are going to be.  I don't have any code to paste as an example as I haven't found any functions that will even get me started on the right path without losing data form columns 3 & 4.

Any pointers would be very welcome at this point in time.


Share this post

Link to post
Share on other sites

sort the input array ( _ArraySort ) on column 2. Iterate the  input array making a check to see if column 2 changes and summing columns 3 and 4 since the last time column 2 changed, when column 2 changes write column 2 and your total counts for columns 3 and 4 to an output array.

Post a running replicator of the problem for more help if needed.


Problem solving step 1: Write a simple, self-contained, running, replicator of your problem.

Share this post

Link to post
Share on other sites

Many thanks, SlackerAI.  That very much pointed me in the right direction.

Here is my semi-final code...

; Now sort the array by Application Name
_ArraySort ( $ArraySoftwareUsage, 0, 0, 0, 1)

$ArraySize2 = UBound($ArraySoftwareUsage)
$RunningTotalRuns = 0
$RunningTotalAverageTime = 0

Global $ArraySoftwareUsage2[1][4]

For $i = 0 to ($ArraySize2 - 1)

    ; Get current Application Name
    $ReadApplicationName = $ArraySoftwareUsage[$i][1]
    ; Read Application Name from previous line (If line No. > 0)
    If ($i > 0) Then
        $PreviousAppName = $ArraySoftwareUsage[($i -1)][1]
        $PreviousAppName = $ReadApplicationName
    $RunningTotalRuns = $RunningTotalRuns + $ArraySoftwareUsage[$i][2]
    $RunningTotalAverageTime = $RunningTotalAverageTime + $ArraySoftwareUsage[$i][3]
    ; When Application name changes write values to new array
    If $ReadApplicationName <> $PreviousAppName Then
        $ArrayNumber2 = $ArrayNumber2 + 1
        ReDim $ArraySoftwareUsage2[($ArrayNumber2 + 1)][4]
        ; Total up the App usage and write to new array
        $ArraySoftwareUsage2[$ArrayNumber2][0] = $Image
        $ArraySoftwareUsage2[$ArrayNumber2][1] = $ReadApplicationName
        $ArraySoftwareUsage2[$ArrayNumber2][2] = $RunningTotalRuns
        $ArraySoftwareUsage2[$ArrayNumber2][3] = $RunningTotalAverageTime
        ; Reset counting variables
        $RunningTotalRuns = 0
        $RunningTotalAverageTime = 0





Share this post

Link to post
Share on other sites

... a way without sorting the array

#include <array.au3>

Local $aMyArray = [ _
        ['COMPUTERNAME1', 'APPLICATION1', 2, 4], _
        ['COMPUTERNAME1', 'APPLICATION2', 3, 3], _
        ['COMPUTERNAME2', 'APPLICATION1', 7, 1], _
        ['COMPUTERNAME2', 'APPLICATION2', 2, 5]]
Local $iQueryColumn = 1 ; column to query for duplicates

; find all unique elements from column1 (remove duplicates)
Local $aUniques = _ArrayUnique($aMyArray, $iQueryColumn, 0, 0, $ARRAYUNIQUE_NOCOUNT)

Local $iRows = UBound($aUniques, 1) ; number of rows for the final array (without duplicates)
Local $iCols = UBound($aMyArray, 2) ; same number of columns as the original array
Local $aResult[$iRows][$iCols] ; create the final array
Local $aAddends ; temporary array (rows with same value in column 2 to be summed)

For $i = 0 To $iRows - 1 ; for each unique value
    ; find indexes of all rows with same value in column 1 ...
    $aAddends = _ArrayFindAll($aMyArray, $aUniques[$i], 0, 0, 0, 0, $iQueryColumn)
    $iSum1 = 0
    $iSum2 = 0
    ; ... and sum values from column 2 and column 3
    For $iAddend = 0 To UBound($aAddends) - 1
        $iSum1 += $aMyArray[$aAddends[$iAddend]][2]
        $iSum2 += $aMyArray[$aAddends[$iAddend]][3]
    ; copy data from one of the duplicate rows of the original array
    For $x = 0 To $iCols - 1
        $aResult[$i][$x] = $aMyArray[$aAddends[0]][$x]
    ; and store sums to column 2 and 3
    $aResult[$i][2] = $iSum1
    $aResult[$i][3] = $iSum2
Next ; repeat for each of the unique values


found a bug and fixed:

changed line 30 from
$aResult[$i][$x] = $aMyArray[$aAddends[  $i  ]][$x]
$aResult[$i][$x] = $aMyArray[$aAddends[  0  ]][$x]

Edited by Chimp
debugged code

small minds discuss people average minds discuss events great minds discuss ideas.... and use AutoIt....

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
Sign in to follow this