PowerShell: Sorting Within Hashtable Value Collections

Question

I have a test input csv file, as follows:

ID;Product;Price;Discount;Level
1;Alpha;23.00;0.03;A
2;Bravo;17.00;0.01;A
3;Charlie;11.00;0.05;A
4;Delta;17.00;0.05;A
5;Echo;29.00;0.07;A
6;Foxtrot;11.00;0.01;A
7;Golf;11.00;0.01;A
1;Hotel;53.00;0.11;B
2;India;53.00;0.13;B
3;Juliet;61.00;0.11;B
1;Kilo;79.00;0.23;C
2;Lima;89.00;0.23;C
3;Mike;97.00;0.29;C
4;November;83.00;0.17;C
5;Oscar;79.00;0.11;C

and I would like to generate the following output file:

ID;Product;Price;Discount;Level
1;Alpha;23.00;0.03;A
5;Echo;29.00;0.07;A
2;India;53.00;0.13;B
3;Juliet;61.00;0.11;B
2;Lima;89.00;0.23;C
3;Mike;97.00;0.29;C

That is, for each level I want to select the top two rows sorted by price and then discount. For example, for level B, I want Juliet and India not Juliet and Hotel.

I have the following code snippet which does not quite deliver!

$input = '.\TestInput.csv'
$products = @(Import-CSV -Path $input -Delimiter ";")
$levels = $products | 
    Group-Object -Property Level -AsHashTable 

$sales = $levels.GetEnumerator() | 
    Sort-Object -Property @{ Expression = { [int]($_.Price) } ; Descending = $true },
                          @{ Expression = { [int]($_.Discount) } ; Descending = $true } | 
    Select-Object -first 2

$output = '.\TestOutput.csv'
$sales | Export-Csv -Path $output -Delimiter ";" -NoTypeInformation

What am I missing?

Answer 1

Use Group-Object directly on the Import-Csv output:

Import-Csv '.\TestInput.csv' -Delimiter ';' | 
  Group-Object Level | 
    ForEach-Object { 
      $_.Group |
        Sort-Object { [int] $_.Price }, { [int] $_.Discount } |
          Select-Object -Last 2
    } | 
        Export-Csv -Path '.\TestOutput.csv' -Delimiter ";" -NoTypeInformation

^{Note: In PowerShell [Core] v6+, you could replace | Select-Object Last 2 with -Bottom 2, given that Sort-Object now supports -Top and -Bottom parameters.}

---

As for what you tried:

• While Group-Object normally sorts the resulting groups by the specified grouping criterion(s) (Level, in this case), this sorting is no longer guaranteed if you use -AsHashtable, given that hashtable entries are inherently unordered.

  • To prevent that, either use the default output (no -AsHashtable) - which yields individual group objects - as shown above, or add a final Sort-Object call that sorts by Level.

• $levels.GetEnumerator() sends key-value pairs (System.Collections.DictionaryEntry instances) through the pipeline, whose .Key property is the grouping criterion (.Level) and whose .Value property is the associated group.

  • However, you should be sending just the entry values - i.e., the group objects - through the pipeline, not key-value pairs; this can be achieved by simply accessing the hashtable's .Values property
    • However, since you need to process each group individually, to find the maximum values in each, you need an intermediate ForEach-Object call inside of which to perform the group-specific processing.

• You're using calculated properties - hashtable-based dynamic property definitions - to specify the criteria for Sort-Object; however, with Sort-Object that is never necessary, because you'll never see the name of such a property; therefore, using the expression script block directly (as shown) above is enough.

• Since your sorting is in descending order, Select-Object -First 2 will show the two highest values, but in descending order, whereas your desired output requests them in ascending order.

  • To get the latter, sort ascending, then select the last 2 objects.

To put it all together (but note that the solution at the top is both conceptually simpler and more efficient):

$sales = 
  $levels.Values | ForEach-Object { 
      $_ |  # process the group at hand
        Sort-Object -Property { [int] $_.Price }, { [int] $_.Discount } | 
          Select-Object -Last 2
    } | Sort-Object Level

Answer 2

$sales = ForEach ($Level in $levels.Keys | Sort-Object) { $levels.$Level | Sort-Object -Property  price,discount | Select-Object -last 2 }