Skip to main content

More PivotTables in PowerShell

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.

[sourcecode language="powershell"]

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

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

} [/sourcecode]

• −    − •    − • •    − • − −     − − −    • −    • − •    • − • •    •    − • − −

Recent trip to Colorado

As a mini pre-Christmas vacation, Amy and I travelled to Colorado for a few days last week.

We left Seattle early on Wednesday and were in Denver by noon. I called in to several meetings and continued to work through the afternoon and early evening from our hotel room to finish up some items from the week.

Thursday was an entirely different experience. We were up early and headed to a nondescript warehouse in the suburbs of Denver to witness the filming of a Food Network Challenge starring our very own Eve Samonsky. It was great seeing the behind-the-scenes set up; the coordination effort, number of people involved, logistics and sheer volume of recorded video was surprising to me, overshadowed only by the impressive creations of the competing pastry chefs. While the competitors worked non-stop for some 10+ hours, we broke up the day with a good breakfast at Lucile's and a walk around Wash Park. We returned for a tense ending and to admire the result of a day of hard effort but you'll have to wait until the show airs some time in 2010 to see it all.

Oakley-9902

Oakley-9917

On Friday morning we headed up to Winter Park for a couple of days of skiing. There had been about 6” of snow the day before but continues were dry, clear and cold by the time we arrived shortly after lunch. The first few runs of the season are always a bit of a challenge but it came back quickly. We enjoyed a good few hours on the slopes surrounding by great views of the snow-covered Rockies before retiring to a ski-in condo at the Iron Horse Resort (bargain found online just the night before!). More skiing on Saturday, followed by a good soak in the hot tub. Aching bones.

The views from the slopes are something you can't really capture with a camera. The bright clear sunshine at 9,000+ ft, crisp 15 degrees F and brilliant blue skies are all around.

Mary Jane run, Winter Park Colorado

Back to Seattle on Sunday morning.

Wash Park on Flickr

• −    − •    − • •    − • − −     − − −    • −    • − •    • − • •    •    − • − −

5 Best Data Visualization Projects of the Year 2009 | FlowingData

It was a huge year for data. There's no denying it. Data is about to explode. Applications sprung up left and right that help you understand your data - your Web traffic, your finances, and your life. There are now online marketplaces that sell data as files or via API. Data.gov launched to provide the public with usable, machine-readable data on a national scale. State and local governments followed, and data availability expands every day. At the same time, there are now tons of tools that you can use to visualize your data. It's not just Excel anymore, and a lot of it is browser-based. Some of the tools even have aesthetics to boot. It's exciting times for data, indeed. Data has been declared sexy, and the rise of the data scientist is here.

via 5 Best Data Visualization Projects of the Year – 2009 | FlowingData.

• −    − •    − • •    − • − −     − − −    • −    • − •    • − • •    •    − • − −
• −    − •    − • •    − • − −     − − −    • −    • − •    • − • •    •    − • − −

Optimizing consumption by bring supply nearer to the need

The cafeterias at work recently changed from centralized distribution of napkins (i.e. big pileswith the flatware) to per-table dispensers.

On the face of it, this would seem to be more costly in both the one-time cost of dispensers (not huge) and ongoing cost of having a personkeep them all stocked. At the same time, having a disposable resource so close to hand would seem to encourage more wasteful consumption through sheer convenience.

My suspicion is that the numbers tell a different tale. Once the habits are changed, I suspect patterns shift to a more just-in-time consumption model where napkins are pulled as needed and not in bulk 'just in case'. Waste through claimed but unused napkins likely drops as a result.

Obviously this post isn't really about napkins.

• −    − •    − • •    − • − −     − − −    • −    • − •    • − • •    •    − • − −

Lighthouses and regulation

A while back I came across the guide to the Foreign Service Office Selection Program. The sample questions are fun to try at home and one in particular caught my eye:

All of the following are examples of United States products that would typically fail to be produced to optimal output without government intervention EXCEPT:

A. national defense products B. light provided by lighthouses C. new automobiles D. new highways

The correct answer is C, which makes sense when you look at the competition in that marketplace. (How this question applies to GM/Chrysler is perhaps a slightly different matter these days...)

The interesting part for me was the lighthouses. Who knew.

• −    − •    − • •    − • − −     − − −    • −    • − •    • − • •    •    − • − −

Regulation and construction quality

It's interesting to look at the impact of regulation and supply and demand on the construction industry. (As with most posts on these topics, this is mostly speculation which would need to be researched/measured to be confirmed but the conjectures are testable.)

As a simple example, one can observe that houses built more than a hundred years ago are still standing strong while those built as recently as the 90s are being torn down and replaced with higher quality construction. Of course this is a generalization and there are clearly exceptions at both ends, but what if we take a look at the factors in the marketplace?

Houses built a hundred years ago in the US were constructed in a time of zero regulation and yet many are still standing. Perhaps the quality of materials played a role here; you got more wood in your 2x4s back then (see 1920s construction for an example). Or maybe it was the manual effort involved; hand-cut blocks and studs, plaster walls and so on. Of course it's hard to count those that have already fallen down or been razed  so there is a bit of survivorship bias at work here.

The sudden surge in demand for houses after World War II (think G. I. Bill) presumably led to a general decrease in construction quality as it was much easier to make money slapping together cheap houses. Government regulation presumably followed which brought some balance back into the market, likely right around the time that the supply and demand were beginning to balance themselves once again.

What about the individual contractor, carpenter or plasterer? Could the introduction of powertools have a more significant impact than any of these other factors? There's no doubt that such modernization has dramatically increased the efficiency of construction. However, given the differences between hours to evenly plaster a wall or ceiling compared to the minutes needed with some sheetrock and a portable screwdriver, what would we expect to see happy to fit and finish quality?

• −    − •    − • •    − • − −     − − −    • −    • − •    • − • •    •    − • − −

Oakleys China-bound

Starting in mid January of next year, Amy and I are planning to be in Beijing, China for about five months. To make that happen there is much to be done in the next six weeks: from visas to travel plans and accommodation, also planning work projects and picking the right clothes, all while figuring out what to do with cars and other commitments here and how to spend our time while we're there.

Exciting times ahead!

• −    − •    − • •    − • − −     − − −    • −    • − •    • − • •    •    − • − −

Day after Thanksgiving in Seattle

Although the Macy's day-after-Thanksgiving parade was a little early for my tastes this particular Friday morning, the weather was so fine that I had to make to trip into Seattle for some photos, to catch the tree lighting and star illumination and for dinner and a movie.

I didn't really have a particular theme in mind when I got off the bus so I just starting taking some shots with my 30mm prime. The black and white was an afterthought in post-processing.

Seattle Greyhound bus station

Seattle United States Courthouse

Oakley_20091127_1326

Oakley_20091127_1348_1

Oakley_20091127_1360_1

The evening sun over Elliot Bay really made this one stand out.

Oakley_20091127_1370_1

Full set on Flickr

SYQ5JYWWQFR4

• −    − •    − • •    − • − −     − − −    • −    • − •    • − • •    •    − • − −

Thankful

Thanksgiving is probably my favorite time of year. It's an opportunity to slow down for a moment, reflect on how good things really are, and look forward to the times ahead.

Since I seem to think in bulleted lists these days, here are some of the things I'm grateful for:

  • A loving, caring, kind and thoughtful wife. I expect to be thankful for this one for a very long time to come.
  • Supportive family. With so many having made special trips this year to join us in Port Townsend, I'm reminded how important this is. Particularly also for my parents who also hosted a lovely reception for us in England. And of course the new set of people who I now also get to call family and their welcoming.
  • Friends. Life is much more fun with company and I'm reminded of the hospitality extended over the years to a young foreigner in a new city, especially over the holidays.
  • Employment. It may not always be easy but I enjoy what I do and know that it's not been an easy year employment-wise for many.
Today itself has been been rather great as well, including many of my favorite things.
  • Nespresso. Coffee continues to be a vital enabler.
  • Whole Foods. Holding the title of Mayor on Foursquare for the local store may be taking it a bit far but they just have so many tasty things.
  • Turkey, football and one of those entirely overwhelming, immensely satisfying nap after a delicious meal.
  • Feel good, family movies on TV.
Life is good.