Powershell group by multiple properties
I am trying to determine if there is an easier way to write a Powershell function that groups an array by multiple properties and sums the specified properties in a 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 feel like I'm overdoing it and there might be a more concise way to write this, especially if I were grouping more properties and wanted to summarize or combine more grouped values.
Thanks in advance for your help!
+4
source to share
1 answer
I have the same, maybe a little simpler, I delete 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}}
+5
source to share