Author Posts

January 1, 2012 at 12:00 am

by Remigiusz at 2013-03-08 12:04:46

Hi
I have object Report like this below
Name value count
——————————————-
Alan low 8
John high 3
Alan low 5
Andrew low 2
Alan high 1
Andrew high 2

could you help me to group items in this object to get group for each change in Name and each change in value, count should be summed so I should get something like below
Alan low 13
John high 3
Andrew low 2
Alan high 1
Andrew high 2

For every change in column Name check column value and when you find change group Name, value and make sum of column count where ex. Name is Alan and value low, next Name Alan value high etc.

How can I do this ?

by DexterPOSH at 2013-03-08 21:48:36

Hi Remigiusz,

Can you explain a bit more. What you want to achieve?

by mjolinor at 2013-03-10 09:24:39

One way is to accumulate the counts using a hash table with the Name and Value as the keys, and then create a new set of objects from the hash table:

$ht = @{}
$objects |
foreach {$ht["$($_.Name) $($_.Value)"] += [int]$_.count}

$ht.keys |
sort |
foreach {
New-Object psobject -Property @{
Name = $_.split()[0]
Value = $_.split()[1]
Count = $ht.$_
}
} |
select Name,Value,Count|
Format-Table -AutoSize

Name Value Count
---- ----- -----
Alan high 1
Alan low 13
Andrew high 2
Andrew low 2
John high 3

by mikefrobbins at 2013-03-10 12:45:28

I copy and pasted your data into notepad and saved it as a csv (meaning it uses a space as a delimiter).

Import-Csv U:\tmp\data.csv -Delimiter ' ' |
Group-Object -Property name, value |
foreach {"$($_.Name), $(($_.Group | measure-object -property count -sum).sum)"}

The output of the above one-liner looks like this:
Alan, low, 13
John, high, 3
Andrew, low, 2
Alan, high, 1
Andrew, high, 2