How to Group data to get the count

PowerShell Help
1

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

2

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
3

close-to-onliner :slight_smile:

$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 }
}
4

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
5

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…

6

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

7

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