4
#foo.csv
No, Fruit, Quality, Units, Location
1, Orange, New, 10, Village
2, Orange, Fresh, 20, City
3, Apple, New, 15, Village
4, Grapes, Fresh, 25, City
5, Apple, Fresh, 30, City
6, Apple, Fresh, 35, City

With this data I want output in following way:
Fruit | Location | Quality | Units

I am not able to further group (sub-group?) data, and what I came with is below, but its not what I need:

Import-Csv "E:\foo.csv"  | 
Group-Object { $_.Fruit } | 
Select-Object -Property Count, 
    @{ Name = 'Fruits'; Expression = { $_.Name } }, 
    @{ Name = 'Location'; Expression = { ($_.Group | Measure-Object -Property Units -Sum).Sum } } | 
Export-Csv new.csv  -NoTypeInformation

I tried further:

Import-Csv "E:\foo.csv"  | 
Group-Object { $_.Fruit } | 
Select-Object -Property Count, 
    @{ Name = 'Fruits'; Expression = { $_.Name } }, 
    @{ Name = 'Location'; Expression = { $_.Location } },
    @{ Name = 'Quality'; Expression = { $_.Quality } },
    @{ Name = 'Units'; Expression = { ($_.Group | Measure-Object -Property Units -Sum).Sum } } | Sort-Object -Property Count | Format-Table -Auto

But data is blank.

msinfo
  • 1,147
  • 6
  • 21
  • 39
  • You want to group by (`Fruit`,`Location`,`Quality`) and sum `Units`? – user4003407 Feb 27 '15 at 12:28
  • Yes, right, first group by Fruit, then location, then Quality and then show there respective sum in Units. Kind of pivot table. But would like to see variations, as I am learning PowerShell. – msinfo Feb 27 '15 at 12:51

1 Answers1

8

You can group by multiple properties in the same time:

Import-Csv "E:\foo.csv"|
Group-Object Fruit,Location,Quality|
Select-Object @{Name='Fruit'   ;Expression={$_.Values[0]}},
              @{Name='Location';Expression={$_.Values[1]}},
              @{Name='Quality' ;Expression={$_.Values[2]}},
              @{Name='Units'   ;Expression={
                  ($_.Group|Measure-Object Units -Sum).Sum
              }}
user4003407
  • 21,204
  • 4
  • 50
  • 60
  • First I thought, I need to put on some nesting, but this is really great. Thanks for explanation. – msinfo Feb 27 '15 at 13:01