Created
January 21, 2012 07:10
-
-
Save andyoakley/1651859 to your computer and use it in GitHub Desktop.
Simple PivotTable in Powershell
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
| # Rotates a vertical set similar to an Excel PivotTable | |
| # | |
| # Given $data in the format: | |
| # | |
| # Category Activity Duration | |
| # ------------ ------------ -------- | |
| # Management Email 1 | |
| # Management Slides 4 | |
| # Project A Email 2 | |
| # Project A Research 1 | |
| # Project B Research 3 | |
| # | |
| # with $keep = "Category", $rotate = "Activity", $value = "Duration" | |
| # | |
| # Return | |
| # | |
| # Category Email Slides Research | |
| # ---------- ----- ------ -------- | |
| # Management 1 4 | |
| # Project A 2 1 | |
| # Project B 3 | |
| $rotate = "Activity" | |
| $keep = "Category" | |
| $value = "Duration" | |
| $pivots = $data | | |
| select -unique $rotate | | |
| foreach { $_.Activity} $data | | |
| group $keep | | |
| foreach { | |
| $group = $_.Group | |
| $row = new-object psobject $row | add-member NoteProperty $keep $_.Name | |
| foreach ($pivot in $pivots) { $row | add-member NoteProperty $pivot ($group | where { $_.$rotate -eq $pivot } | measure -sum $value).Sum } | |
| $row | |
| } |
Converted the script to an Advanced Function
function Invoke-Pivot {
param (
$data,
[string]$rotate, # Bits along the top
[string]$keep, # Those along the side
[string]$value # What to total
)
# Create variable to store the output
$NewRows = @()
# Fields of interest
# $rotate = "Activity" # Bits along the top
# $keep = "Category" # Those along the side
# $value = "Duration" # What to total
# Find the unique "Rotate" [top row of the pivot] values and sort ascending
$pivots = $data | select -unique $rotate | foreach { $_.$rotate} | Sort-Object
# Step through the original data...
# for each of the "Keep" [left hand side] find the Sum of the "Value" for each "Rotate"
$data |
group $keep |
foreach {
$group = $_.Group
# Create the data row and name it as per the "Keep"
$row = new-object psobject
$row | add-member NoteProperty $keep $_.Name
foreach ($pivot in $pivots) { # Cycle through the unique "Rotate" values and get the sum
$row | add-member NoteProperty $pivot ($group | where { $_.$rotate -eq $pivot } | measure -sum $value).Sum
}
# Add the total to the row
$row | add-member NoteProperty Total ($group | measure -sum $value).Sum
# Add the row to the collection
$NewRows += $row
}
# Do something with the pivot rows
return $NewRows
<#
.SYNOPSIS
Transforms rows and columns of a PSObject or other named object.
.DESCRIPTION
Long description
.EXAMPLE
PS C:>
Explanation of what the example does
.INPUTS
Inputs (if any)
.OUTPUTS
Output (if any)
.NOTES
#############################################################################
Rotates a vertical set similar to an Excel PivotTable
Given $data in the format:
Category Activity Duration
------------ ------------ --------
Management Email 1
Management Slides 4
Project A Email 2
Project A Research 1
Project B Research 3
with $keep = "Category", $rotate = "Activity", $value = "Duration"
Return
Category Email Slides Total
---------- ----- ------ -----
Management 1 4 5
Project A 2 1 3
Project B 3 3
#############################################################################
Changes
========#
Who When Details
------------ ---------- -----------------------------------------------
dennis 2019-10-22 Converted to advanced script
aphalon 2015-07-23 1. Added $NewRows variable to hold the output
2. Unique values were hardcoded for the example
-> changed to be the variable fields
3. Sorted $pivots
4. Added a Total column
5. Commented it a little :)
#############################################################################
#>
}
I think i found another version that was enhanced from above, it was intended to support multiple fields but i found several problems. My final version supports multiple row and column fields, handles null values and also supports pipeline input. Unfortunately i can't credit the original author as i can't find that original post which I enhanced.
function Get-ConcatProperties
{
<#
.SYNOPSIS
Conatenates the properties of an object
.PARAMETER obj
Object to process
.PARAMETER Property
Properties to concatenate
.PARAMETER delim
Delimiter to use when concatenating properties
.OUTPUTS
concatenate string using the value of the provided properties
e.g. if Property=@('a','b') and input obj is [PSCustomObject]@{a=3,b=4} the delimited output would be 3,4
e.g. if Property=@('a','c','d') and input obj is [PSCustomObject]@{a=3,b=4,d=6} the delimited output would be 3,,6
#>
param(
[Parameter(ValueFromPipeline=$true,Mandatory=$true)]
[psobject[]]$obj,
[Parameter(Mandatory=$false)]
[string]$delim = ",",
[Parameter(Mandatory=$true)]
[string[]]$Property = ",",
[string]$NullValue
)
process {
foreach ($o in $obj)
{
$o | ForEach-Object {
$members = @();
foreach ($p in $Property)
{
if ($null -eq $obj.$p )
{
if ($NullValue) {
$members += $nullValue
} else {
$members += ""
}
} elseif ("" -eq $obj.$p) {
if ($NullValue) {
$members += $nullValue
} else {
$members += ""
}
} else {
$members += $obj.$p
}
write-output ([string]::Join(",",$members))
}
}
}
}
}#function Get-ConcatProperties
function Get-PivotedData
{
[CmdletBinding(PositionalBinding=$false)]
<#
.SYNOPSIS
Pivots the data given a row and column field
.PARAMETER data
The data to pivot
.PARAMETER rowfield
Which object property to use as the label/grouping of each row in the output table
.PARAMETER colfield
Which object property to use as the label/grouping of each column in the output table
.PARAMETER colfieldNullLabel
.PARAMETER totalfield
Which object property to use for each cell in the output table, this field will be totaled using the method specified in TotalMethod
.PARAMETER TotalMethod
How should the cell be populated using the totalField (e.g. Sum)
.PARAMETER RowcolDelim
If multiple proeprties are specified for row/column, how should they be concatenated in the output table
e.g. if colfield=@('a','b') and input obj is [PSCustomObject]@{a=3,b=4} the delimited output would be 3,4
.PARAMETER ShowRowTotal
If true it will show the "totals" column at the end of each "row"
.PARAMETER ShowColumnTotal
If true it will show the "totals" row at the end of each "column"
.EXAMPLE
@(
[pscustomobject]@{a='a_data';b='b_data';c=1; d='d_data';e='e_data'}
,[pscustomobject]@{a='a_data';b='b_data';c=2; d=''}
,[pscustomobject]@{a='a_data';b='';c=3; d=''}
,[pscustomobject]@{a='a_data';b='';c=3; d='d_data2'}
) | Get-PivotedData -rowfield a,b -colfield d,e -colfieldNullLabel "NoData" -totalfield c -totalmethod Sum -ShowRowTotal -ShowColumnTotal
Result:
a b NoData,NoData d_data,e_data d_data2,NoData Total Sum
- - ------------- ------------- -------------- ---------
a_data b_data 2 1 3
a_data 3 3 6
Total Sum Total Sum 5 1 3 9
#>
param(
[Parameter(Mandatory=$true,Position=0,ValueFromPipeline=$true)]
[psobject[]]$data,
[Parameter(Mandatory=$true)]
[string[]]$rowfield,
[Parameter(Mandatory=$true)]
[string[]]$colfield,
[string]$colfieldNullLabel,
[Parameter(Mandatory=$false)]
[string]$RowcolDelim=',',
[Parameter(Mandatory=$true)]
[string]$totalfield,
[Parameter(Mandatory=$false)]
[ValidateSet("Sum","Average","Maximum","Minimum","Count")]
[string]$totalmethod="sum",
[switch]$ShowColumnTotal,
[switch]$ShowRowTotal
)
begin {
$rows = @()
# Fields of interest
#$colfield # Bits along the top
#$rowfield # Those along the side
#$totalfield # What to total
$dataToProc = @()
}
process {
foreach ($d in $data)
{
$dataToProc += $d
}
}
end {
if (-not $colfieldNullLabel) {
$colfieldNullLabel = "(null $colfield)"
}
#get unique list of properties in the colfield list that actually exist in the data
$pivots = $dataToProc | Sort-Object -Property $colfield -Unique | select -Property $colfield | select *, @{n='__!__Label';e={$_| Get-ConcatProperties -Property $colfield -delim $RowcolDelim -NullValue $colfieldNullLabel | select -last 1}}
$MeasureParms = @{
Property=$totalfield
}
if ($totalmethod -ne 'Count') {
$MeasureParms[$totalmethod]=$true
}
# Step through the original data...
$dataToProc |
# Group by the rowfield properties
Group-Object $rowfield |
# for each of the "rowfield" groups [left hand side] find the Sum (or other totalmethod) of the "totalfield" for each "rowfield" grouping
Foreach-Object {
$group = $_.Group
# Create the data "row" and name it as per the "rowfield" set
#$row = new-object psobject
#the heading is the list of grouping row property names, the value is the unique 'group' in the data itself
$row = $_.group | Select-Object -Property $rowfield -First 1
# Cycle through the unique "rowfield" property groups values and get the total
for ($i = 0; $i -lt $pivots.count; $i++)
{
$pivot=$pivots[$i]
$pivotLabel = $pivots[$i]."__!__Label"
$Values = $group | Where-Object {
foreach ($f in $colfield) {
if (-not ($_.$f -eq $pivot.$f)) {
return $false;
}
} return $true;
}
$row | add-member -MemberType NoteProperty -Name $pivotLabel -Value ($Values | Measure-Object @MeasureParms).$totalmethod
}
# Add the total to the row
if ($ShowRowTotal)
{
$row | add-member NoteProperty "Total $totalmethod" ($group | Measure-Object @MeasureParms).$totalmethod
}
# Add the row to the collection
$rows += $row
}#each row
#if total for each column is desired
if ($ShowColumnTotal)
{
$ColTotalHash = [ordered]@{}
foreach ($f in $rowfield) {
$ColTotalHash[$f]="Total $totalmethod"
}
$ColTotalRow=[pscustomobject]$ColTotalHash
#$ColTotalRow | add-member NoteProperty ([string]::Join($RowcolDelim, $rowfield)) "Total $totalmethod"
$MeasureParms.Remove("property") #remove the property, we don't need it for the total
foreach ($pivot in $pivots | select -expandProperty "__!__Label")
{
#$ColTotalRow | add-member NoteProperty ([string]::Join($RowcolDelim, $keep)) "_Total $totalmethod"
$ColTotalRow | add-member NoteProperty -Name $pivot -Value ($rows.$pivot | Measure-Object @MeasureParms ).$totalmethod
# Add the total to the row
}
#show the grand total of both row & column
if ($ShowRowTotal)
{
$ColTotalRow | add-member NoteProperty -Name "Total $totalmethod" -Value ($rows."Total $totalmethod" | Measure-Object @MeasureParms).$totalmethod
}
$rows += $ColTotalRow
}
Write-Output $rows
}#end block
}#function Get-PivotedData
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment
There are a few things that didn't quite work... here is an amended version