Powershell Group By Multiple Properties

Question

I am trying to determine if there's an easier way to write a Powershell function that groups an array by multiple properties and sums specified properties in the group, similar to the following:

#Ungrouped data
ID ID2 Value
-- --- -----
A  A1    100
A  A2    200
A  A2    300
B  B1    400
B  B1    500
B  B1    600
B  B3    700
C  C1    800
C  C2    900
C  C1   1000

#Grouped data
ID ID2 Sum Value
-- --- ---------
A  A1        100
A  A2        500
B  B1       1500
B  B3        700
C  C1       1800
C  C2        900

Here's what I have so far:

$grouped = $ungrouped | group ID, ID2
$grouped | foreach {
    [pscustomobject] @{
        ID = $_.group | select -unique -expand ID
        ID2 = $_.group | select -unique -expand ID2
        'Sum Value' = ($_.group | measure value -sum).sum
    }
}

This works for me, but I just get the feeling I'm overdoing it and there may be a more concise way to write this, especially if I was grouping by more properties and wanted to sum up or aggregate more grouped values.

Thanks in advance for the help!

What's your problem? As that's a fairly common way to do that (check out JPBlanc's answer if you want to make a hash table) - otherwise you should move this to code review.SE — LinkBerest - SO sold our work
– LinkBerest - SO sold our work, Commented Jun 11, 2015 at 3:50

JPBlanc · Accepted Answer · 2015-06-11 05:00:19Z

11

I have the same, perhaps a bit simpler, I remove two |

$grouped = $ungrouped | group ID, ID2
$grouped | foreach {
    $b= $_.name -split ', '
    [pscustomobject] @{
         ID = $b[0];ID2 = $b[1]
        'Sum Value' = ($_.group | measure value -sum).sum
    }
}

One liner :

Import-Csv 'YourFile.csv' | Group-Object -Property ID,ID2 | % {$b=$_.name -split ', ';$c=($_.group | Measure-Object -Property value -Sum).Sum;[PScustomobject]@{ID=$b[0];ID2=$b[1];Sum=$c}}

answered Jun 11, 2015 at 5:00

JPBlanc

73.1k17 gold badges137 silver badges180 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

user2864740 Over a year ago

$b= $_.name -split ', ' -> $id, $id2 = $_.name -split ', ' (then no need for $b[n])

Collectives™ on Stack Overflow

Powershell Group By Multiple Properties

1 Answer 1

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related