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

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.

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.

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.

Back to Seattle on Sunday morning.

Wash Park on Flickr

Some great photos in the urban decay theme:

Ghosts of Shopping Past

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.

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.