How to Group data to get the count

Tagged: 

This topic contains 6 replies, has 4 voices, and was last updated by Profile photo of Max Kozlov Max Kozlov 1 month ago.

  • Author
    Posts
  • #71362
    Profile photo of Avinash
    Avinash
    Participant

    I have a Csv file with following data with two cloumns

    Region Type
    —— ———–
    EuropeWest Operational
    EuropeWest Operational
    EuropeWest Operational
    EuropeWest Operational
    EuropeWest Operational
    EuropeWest Operational
    EuropeWest Operational
    EuropeWest Operational
    EuropeWest Operational
    EuropeWest Operational
    EuropeWest Operational
    EuropeWest Operational
    EuropeWest Operational
    EuropeWest Operational
    EuropeWest Operational
    EuropeWest Operational
    EuropeWest Operational
    EuropeWest Operational
    EuropeWest Operational
    EuropeWest Operational
    EuropeWest Operational
    EuropeWest Operational
    EuropeNorth MigrateSource
    EuropeNorth Operational
    USCentral Operational
    USCentral Operational
    AsiaEast MigrateSource
    AsiaEast Operational
    AsiaEast Operational
    AsiaEast Operational

    how can i get the count in this way in three columns and there count

    Region Operational MigrateSource
    —— ———– ———–
    EuropeWest 22 0
    EuropeNorth 1 1
    USCentral 2 0
    AsiaEast 3 1

  • #71371
    Profile photo of Sam Boutros
    Sam Boutros
    Participant

    The request demands an unnecessary level of complexity in the output. Here's the code the produces the desired output:

    $RawData  = Import-Csv .\data1.csv
    
    $Operational   = $RawData | ? { $_.Type -eq 'Operational' }   | 
        Group-Object -Property Region | Select Name,Count | sort Name
    $MigrateSource = $RawData | ? { $_.Type -eq 'MigrateSource' } | 
        Group-Object -Property Region | Select Name,Count | sort Name
    
    $myOutput = foreach ($Item in $Operational) {
        $FoundMatch = $false
        $MigrateSource | % {
            if ($Item.Name -eq $_.Name) {
                $FoundMatch = $true
                [PSCustomObject]@{
                    Region        = $Item.Name
                    Operational   = $Item.Count
                    MigrateSource = $_.Count
                }
            }
        }
        if (! $FoundMatch) {
            [PSCustomObject]@{
                Region        = $Item.Name
                Operational   = $Item.Count
                MigrateSource = 0
            }
        }
    } 
    
    foreach ($Item in $MigrateSource) {
        $FoundMatch = $false
        $Operational | % {
            if ($Item.Name -eq $_.Name) {
                $FoundMatch = $true
                [PSCustomObject]@{
                    Region        = $Item.Name
                    MigrateSource = $Item.Count
                    Operational   = $_.Count
                }
            }
        }
        if (! $FoundMatch) {
            [PSCustomObject]@{
                Region        = $Item.Name
                MigrateSource = $Item.Count
                Operational   = 0
            }
        }
        if ($_ -notin $myOutput) { $myOutput += $_ }
    } 
    
    $myOutput | FT -a 
    

    and here's how the output looks like:

    Region      Operational MigrateSource
    ------      ----------- -------------
    AsiaEast              3             1
    EuropeNorth           1             1
    EuropeWest           22             0
    USCentral             2             0
    
  • #71392
    Profile photo of Max Kozlov
    Max Kozlov
    Participant

    close-to-onliner 🙂

    $states = 'Operational','MigrateSource'; $csv = Import-Csv .\data1.csv;
     $csv | Group-Object -Property Region | ForEach-Object {
     $types = $_.Group | Group-Object Type -AsHashTable;
     $states | Foreach-Object -Begin { $o = [PSCustomObject]@{ Region = $_.Name } } -Process { $o | Add-Member -MemberType NoteProperty -Name $_ -Value ($types[$_]).Count } -End { $o }
    }
    
  • #71438
    Profile photo of Ron
    Ron
    Participant

    Dynamic version, creates a column for each value by name, in case the types expand. Columns are sorted alphabetically.

    $types = $csv |select -Unique -expand Type                                                                                                                          
    $regions = $csv |select -Unique -expand Region
    $s = [array]"Region" + ($types | Sort)
    $regions | %{
      $obj = New-Object –TypeName PSObject
      $r = $_
      $obj | Add-Member –MemberType NoteProperty –Name Region -value $r
      $types | %{
        $t = $_
        $obj | Add-Member –MemberType NoteProperty –Name ($t) -value ($csv|?{$_.Region -eq $r -and $_.Type -eq $t} | Measure-Object).Count
      }
      $obj
    } | Select $s
    
  • #71447
    Profile photo of Avinash
    Avinash
    Participant

    Thanks Sam,Max & Ron –It is working–That was a quick help !!!

    I would prefer Ron Code as it is taking less time in execution..

  • #71455
    Profile photo of Avinash
    Avinash
    Participant

    I have one more question
    Can we achieve the same output if we have two arrays and region column has no same values
    like

    $data1=

    Region Type
    —— ———–
    EuropeWest Operational
    EuropeWest Operational
    EuropeWest Operational
    EuropeNorth Operational
    USCentral Operational
    USCentral Operational
    AsiaEast Operational
    AsiaEast Operational
    AsiaEast Operational

    $data2=

    Region Type
    —— ———–
    EuropeWest MigrateSource
    EuropeWest MigrateSource
    EuropeNorth MigrateSource
    USCentral MigrateSource
    USCentral MigrateSource

    output should be :
    Region Operational MigrateSource
    —— ———– ———–
    EuropeWest 4 2
    EuropeNorth 1 1
    USCentral 2 0
    AsiaEast 3 1

  • #71476
    Profile photo of Max Kozlov
    Max Kozlov
    Participant

    What stop you to $data = $data1 + $data2 ??

You must be logged in to reply to this topic.