Adding together duplicate values in an array

Tagged: 

This topic contains 4 replies, has 4 voices, and was last updated by Profile photo of random commandline random commandline 6 months, 1 week ago.

  • Author
    Posts
  • #40786
    Profile photo of L-Bo
    L-Bo
    Participant

    Hello Powershell.org. I have been searching google all day for an answer to this and I am just not getting what I need so hopefully somebody here can offer some assistance. The short explanation is that I have 2 arrays that each contain some duplicate info. See below
    $ObjColl

    Name TotalUsers TotalInbox
    —- ———- ———-
    IT 3 3
    Accounting 4 2
    Marketing 2 1

    And the second one....
    $ObjColl1

    Name TotalUsers TotalInbox
    —- ———- ———-
    Accounting 2 1
    Marketing 2 1

    So when I add them together:

    $Final += $Objcoll
    $Final += $Objcoll1
    

    I get the following output:
    $Final

    Name TotalUsers TotalInbox
    —- ———- ———-
    IT 3 3
    Accounting 4 2
    Marketing 2 1
    Accounting 2 1
    Marketing 2 1

    What I actually want to do is aggregate the totals rather than doing a literal "combining" of the arrays. How can this be accomplished? Example of desired output below:

    $Final

    Name TotalUsers TotalInbox
    —- ———- ———-
    IT 3 3
    Accounting 6 3
    Marketing 4 2

    Thanks in advance for any assistance!

  • #40965
    Profile photo of Edmond Yee
    Edmond Yee
    Participant

    You would have to compare the actual values in the array. It may or may not be the most efficient way, but you could use a nested foreach loop such as:

    Foreach ($name in $ObjColl)
    {
          Foreach ($name1 in $ObjColl1)
          {
                If ($name.Name -eq $name1.Name)
                {
                        $row = new-object PSObject -Property @{
                            Name = $name.Name;
                            TotalUsers = ($name.TotalUsers + $name1.TotalUsers);
                            TotalInbox = ($name.TotalInbox + $name1.TotalInbox)
                        }
    
                        $Final += $row
                }
           }
    }

    This is assuming that the TotalUsers and TotalInbox columns are formatted as integers and not strings.

    $Final

    Name TotalUsers TotalInbox
    —- ———- ———-
    Accounting 6 3
    Marketing 4 2

    • This reply was modified 6 months, 1 week ago by Profile photo of Edmond Yee Edmond Yee.
  • #40968
    Profile photo of Dan Potter
    Dan Potter
    Participant

    You don't need the first two custom objects as you already have them but I needed to build them for example.

    
    $one = 'IT','Accounting','Marketing' | % {
    
    [pscustomobject]@{
    
    name = $_
    totalusers = (1..9) |Get-Random
    totalinbox = (1..9) |Get-Random
    
    }
    
    }
    
    
    
    $two = 'IT','Accounting','Marketing' | % {
    
    [pscustomobject]@{
    
    name = $_
    totalusers = (1..9) |Get-Random
    totalinbox = (1..9) |Get-Random
    
    
    }
    
    }
    
    
    
    $total = $one + $two
    
    $total | group name | %{
    
    $n = $_.name
    $group = $total | ? {$_.name -eq $n}
    
    [pscustomobject]@{
    
    name = $n
    totalusers = ($group.totalusers | Measure-Object -Sum).sum
    totalinbox = ($group.totalinbox | Measure-Object -Sum).sum
    
    
    
    }
    
    }
    
    
    • This reply was modified 6 months, 1 week ago by Profile photo of Dan Potter Dan Potter.
  • #40990
    Profile photo of Edmond Yee
    Edmond Yee
    Participant

    Sorry I misunderstood the question, I thought you wanted ONLY duplicates, do what Dan said 🙂

  • #41016
    Profile photo of random commandline
    random commandline
    Participant
    $ObjCol = "
    Name TotalUsers TotalInbox
    IT 3 3
    Accounting 4 2
    Marketing 2 1" -split "`n"
    
    $ObjCol2 = "
    Name TotalUsers TotalInbox
    Accounting 2 1
    Marketing 2 1 " -split "`n"
    
    $groups = $ObjCol,$ObjCol2 | ForEach-Object {
        ConvertFrom-Csv -InputObject $_ -Delimiter ' '} | 
        Group-Object -Property name
    
    $groups | ForEach-Object {
        $name = $_ | Select-Object -ExpandProperty Name
        $measure = $_.Group | Measure-Object -Property totalusers,totalinbox -Sum
        [PSCustomObject]@{
            Name = $name
            TotalUsers = $measure[0].sum
            TotalInbox = $measure[1].Sum
            }} | Format-Table -AutoSize
    
    # Results
    # Name       TotalUsers TotalInbox
    # ----       ---------- ----------
    # IT                  3          3
    # Accounting          6          3
    # Marketing           4          2
    

You must be logged in to reply to this topic.