Andy Oakley

Similar to the Simple PivotTables in Excel script I wrote a while back, today I needed to do a different type of pivot by hierarchy. I had a flat table of rows which I wanted to group by several columns recursively.

# Given data such as
#
# A       B           C
# ------- ----------- ----
# red     lisbon      cat
# blue    venice      dog
# green   paris       cat
# blue    london      dog
# green   lisbon      fish
# green   paris       cat
#
# Output the following
#
#  cat
#     red
#         lisbon           1
#     green
#         paris            2
#  dog
#     blue
#         venice           1
#         london           1
#  fish
#     green
#         lisbon           1
#
# $list | output-hierarchy "C","A","B"

function output-hierarchy
{
    param($hierarchy,$depth=0)
    if ($depth -gt 5) { return }

    $groups = $input | group $hierarchy[0]
    foreach ($group in $groups)
    {
        if ($hierarchy.length -gt 1)
        {
            # Emit title at this level
            "`t"*$depth + $group.name

            $group.group | output-hierarchy $hierarchy[1..($hierarchy.length-1)] ($depth+1)
        }
        else
        {
            # Figure out how many spaces to right align count
            $spacer=(60-$group.name.length-$depth-$group.group.count.tostring().length)

            # End of the road, count the remaining items that fall into this category
            "`t"*$depth + $group.name + " "*$spacer + $group.group.count
        }
    }
}

Quick script for PivotTable-like functionality in PowerShell. I find myself using this a lot.

# 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
	}

I have too much time on my hands this morning. The following script makes it easy to get and set Twitter status. I’ve seen similar ones elsewhere but they all seemed to have external dependencies, this one does not.

#twitter.ps1:
$script:username = "foo"
$script:password = "bar"
function get-twitter
{
 $wc = new-object System.Net.Webclient
 $wc.Credentials = new-object System.Net.NetworkCredential $script:username,$script:password 
 $rest = $wc.DownloadString("<a href="http://twitter.com/statuses/friends_timeline.xml">http://twitter.com/statuses/friends_timeline.xml</a>")
 $xml = [xml]$rest
 $xml.statuses.status
}
function set-twitter
{
 param($status)
 $wc = new-object System.Net.Webclient
 $wc.Credentials = new-object System.Net.NetworkCredential $script:username,$script:password 
 [System.Reflection.Assembly]::LoadWithPartialName("System.Web") | out-null
 $encodedstatus = [System.Web.HttpUtility]::UrlEncode($status)
 $postdata = "status=$encodedstatus"
 $postbytes = [System.Text.Encoding]::ASCII.GetBytes($postdata)
 $wc.Headers.Add("Content-Type", "application/x-www-form-urlencoded")
 [System.Text.Encoding]::ASCII.GetString($wc.UploadData("<a href="http://twitter.com/statuses/update.xml">http://twitter.com/statuses/update.xml</a>", $postbytes))
}