Sort a nested hash table by value

Question

I have a hash array as such:

$weeklyStats[$RecipientName][$weekNr][$total]

Which is created in a loop as such:

$weeklyStats = @{}
$weekNr = get-date -UFormat %V

ForEach ($RecipientName in $MailTraffic.keys)  
{  

    $weeklyStats[$RecipientName] = @{}
    $weeklyStats[$RecipientName][$weekNr] = @{}
    $weeklyStats[$RecipientName][$weekNr]['Total'] = 0
    $weeklyStats[$RecipientName][$weekNr]['Sent'] = 0
    $weeklyStats[$RecipientName][$weekNr]['Received'] = 0

    foreach($item in $MailTraffic[$RecipientName].keys)  
    {  
        weeklyStats[$RecipientName][$weekNr]['Total'] =+ 1
        if $MailTraffic[$RecipientName]['transaction'] == "Sent"
        {
            $weeklyStats[$RecipientName][$weekNr]['Sent'] =+ 1
        }
        else
        {
            $weeklyStats[$RecipientName][$weekNr]['Received'] =+ 1
        }
    }
}

I don't know how to 'dump' a variable in Powershell but here is the contents in json:

{
    "mike":  {
                           "11":  {
                                      "Total":   411,
                                      "Sent":     21,
                                      "Received":390,
                                  }
              },
    "peter":  {
                           "11":  {
                                      "Total":   751,
                                      "Sent":     51,
                                      "Received":700,
                                  }
              },
    "frank":  {
                           "11":  {
                                      "Total":   620,
                                      "Sent":     20,
                                      "Received":600,
                                  }
              },
}

I want to print out the keys and values in descending order of the $total. I can only find examples how to do it if the hash table is only one level deep.

The intended output would be:

Name        Total       Received    Sent
-----       -----       -----       -----
peter       751         700         51
frank       620         600         20
mike        411         390         21

That is included in my question? $stats is the name of the hash table, $name is a string, weeknr and total are integers. — Lunacy, Mar 14 '20 at 21:49
To get your output, I envision `$stats` being created like `$stats = @{'Frank' = @{10 = @{ 500 = 'Frank , 10, 500'}}},@{'Mike' = @{10 = @{ 673 = 'Mike , 10, 673}}}`. I will bet you that is not correct. So I'd like to see how it is really created. — AdminOfThings, Mar 14 '20 at 22:05
For you to output `Frank , 10, 500` from `$stats[$name][$weekNr][$total]`, you must have a string with all of the values statically set because your two examples aren't consistent with spacing for it to be an interpolated output. — AdminOfThings, Mar 14 '20 at 22:15
Please show us the intended output as well. It's not clear whether you want to sort both levels, or what — Mathias R. Jessen, Mar 15 '20 at 12:23

score 0 · Answer 1 · answered Mar 14 '20 at 21:36

0

Sort by referencing the Keys property of the inner hashtable, then assign to a new [ordered] dictionary:

$sorted = [ordered]@{}

$stats.GetEnumerator() |Sort-Object {
  # Sort by first key from each inner hashtable
  $_.Value.Keys |Select -First 1
} -Descending |ForEach-Object {
  # re-assign to our ordered dictionary
  $sorted[$_.Key] = $_.Value
}

$sorted now contains your new sorted dictionary

answered Mar 14 '20 at 21:36

Mathias R. Jessen

157,619
12
148
206

Tnx, unfortunately my example was wrong :| and I still fail to apply your example when an additional level is added. – Lunacy Mar 14 '20 at 21:48
@Lunacy Could you perhaps update your question to show how you created this nested hashtable to begin with? – Mathias R. Jessen Mar 14 '20 at 23:18

iRon · Answer 2 · 2020-03-15T10:39:38.763

Most PowerShell cmdlets are intended to handle (stream!) [PSObject] type (which includes a [PScustomerObject] type) lists for input and output.
^{(To understand the difference see e.g. Difference between PSObject, Hashtable, and PSCustomObject)}.
Nested hash tables are difficult to maintain and handle in PowerShell (see also: Powershell Multidimensional Arrays) because PowerShell is optimized for streaming which is rather difficult with cascaded objects, therefore I recommend you convert you nested hashtable in a (rather flat) [PScustomerObject] list, something like:

$PSStats =
    ForEach ($name in $Stats.Keys) {
        ForEach ($weekNr in $_.Keys) {
            ForEach ($total in $_.Keys) {
                [pscustomobject]@{name = $name; weekNr = $weekNr; total = $total}
            }
        }
    }

Once you have converted it into PSCustomObject list, you can easily sort it and display the results:

$PSStats | Sort-Object Total

Hmh, I'm an idiot, since there is some private data involved I simplified the example, unfortunately that was not representative and a flat list won't do. I corrected that now. — Lunacy, Mar 15 '20 at 13:24

score 0 · Answer 3 · answered Mar 15 '20 at 15:07

I would just create a custom object from your hash tables and then sort on the Title property:

# Creation of $mailtraffic
$mailtraffic = @{'Mike' = @{'Total' = 411; 'Sent' = 21; 'Received' = 390};'Peter' = @{'Total' = 751; 'Sent' = 51; 'Received' = 700};'Frank' = @{'Total' = 620; 'Sent' = 20; 'Received' = 600}}

# Sorting Code
$mailtraffic.GetEnumerator() |
    Select @{n='Name';e={$_.Key}},@{n='Total';e={$_.Value.Total}},@{n='Received';e={$_.Value.Received}},@{n='Sent';e={$_.Value.Sent}} |
        Sort Total -Descending

Sort a nested hash table by value

3 Answers3