Data pivots for Kindergartners

Hjalmar Gislason
Dec 11, 2016 · 5 min read

One of the first things many of us are likely to do with data is a Kindergarten or first grade exercise in data gathering that will result in something like this:

For many, the concept of “what data is” won’t go much beyond this level of sophistication: Data is a two-dimensional table that I can easily type into a grid in Excel. Talk about dimensions, measures, multi-dimensional data and data pivots and many will zone out thinking this is something too complex for them. The fact is that these are concepts most people already understand, they just don’t know it yet. Let me fix that.

Multi-dimensional data? That’s crazy talk

I was in my first (and only — another story) year in Computer Science when I first heard of multi-dimensional databases. This intrigued me. I could envision the third dimension as if the table would have cells on a third dimension forming a sort of a cube. Beyond three and the brain started to bend. My hallucinations of rows of cubes and planes of cubes and cubes of cubes as more dimensions were added are probably best kept to myself before you all stop reading this!

It might therefore come as a surprise to many of you that you confidently work with multi-dimensional data all the time.

In the example above, the dimensions are indeed only two both literally and in the data representation. The class might have labeled one: “Student”. The other one (with age, height and weight) would typically go unlabeled, but they are the measures*. Not such a scary word in this context. That’s what they are.

Let’s return to Dan and his class. Next year, they repeat the experiment. The kids are a year older and Dan may have to stay away from the doughnuts:

This is now three dimensional data, as we’ve added year to the mix.

If the class continues to do this experiment annually they will quickly figure out that repeating the original table over and over again is not good for studying the trends in the data, so they change the layout of the data:

The class has now pivoted the data. This wasn’t hard at all. In fact you will see that there is at least one more way that makes sense to layout the data. Something that looks more like an individual score-card per student:

Additionally, you could flip each table so that the rows are by year, and the columns by measure. The same is true of the previous two options. All in all those would be 6 different pivotal representations or pivots of the same data.

You could also combine all of the data in a single table without confusing anyone:

For those in the US and others with SI-unit handicap it might be helpful to add imperial units into the mix:

What you’re looking at now is four dimensional data**, and it’s not scary at all, is it? This is what multi-dimensional data and “data pivots” are all about.

So, without your knowledge you’ve been working with multi-dimensional data, measures, dimensions and data pivots more or less since you learned how to read. You may be more of a data person than you thought.

Additional detail: Long form data

We data nerds like to structure the data a little differently so that it is easier for computers to understand (but harder for humans, as is often the case). We would lay the data out so that each row of the data file contains a unique combination of all the dimension values (“2015”, “Jane”, “Age” and “metric” are all dimension values) followed by the value itself:

Year,Student,Measure,System of measurement,Value
2015,Jane,Age,metric,5
2015,Jane,Age,imperial,5
2015,Jane,Height,metric,108
2015,Jane,Height,imperial,43
2015,Jane,Weight,metric,20
2015,Jane,Weight,imperial,44
2015,Joe,Age,metric,5
2015,Joe,Age,imperial,5
2015,Joe,Height,metric,110
2015,Joe,Height,imperial,43
2015,Joe,Weight,metric,19
2015,Joe,Weight,imperial,42
2015,Dan,Age,metric,35
2015,Dan,Age,imperial,35
2015,Dan,Height,metric,180
2015,Dan,Height,imperial,71
2015,Dan,Weight,metric,78
2015,Dan,Weight,imperial,172
2016,Jane,Age,metric,6
2016,Jane,Age,imperial,6
2016,Jane,Height,metric,115
2016,Jane,Height,imperial,45
2016,Jane,Weight,metric,22
2016,Jane,Weight,imperial,49
...

This is very verbose and makes the data hard to read for us humans, but it is non-ambiguous and easily understandable for a computer. Each line in a table like this would be called a key-value pair. The unique combination of the dimension values (e.g. “2015, Jane, Weight, metric”) is the key, and the value is — well — the value. The data is now said to be in long form, as opposed to wide form when more than one value is present in each row — which is essentially any other arrangement of the data.

Many software solutions require your data to be in long form to perform certain functions, some even in order for the data to load at all. So, understanding what long-form data is can be useful for those that dare to go further.

- — -

* Some software systems refer to metrics, facts or simply values rather than measures.

** The “System of measurement” dimension is imperfectly represented in the table as it is not stated for the age. This is only true because (thankfully!) both the metric and the imperial system measure time in the same units.

Hjalmar Gislason

Written by

Adventures in data. Founder and CEO of GRID (@grid_hq). Proud data nerd. Curious about everything. Founder of 5 software companies.

More From Medium

Also tagged Datatables

Also tagged Data Science

Welcome to a place where words matter. On Medium, smart voices and original ideas take center stage - with no ads in sight. Watch
Follow all the topics you care about, and we’ll deliver the best stories for you to your homepage and inbox. Explore
Get unlimited access to the best stories on Medium — and support writers while you’re at it. Just $5/month. Upgrade