"How can I unpivot or transpose my tabular data so that there's only one record per row?"
I see this question a lot and I thought it was worth a quick Friday blog post.
Data often aren’t quite in the format that you want. We usually provide CSV / XLS access to our data in “pivoted” or “normalized” form so they look like this:
But for a lot analyses and applications, particularly data visualisation tools like D3, ggplot2, Tableau, it’s more convenient to have your data “unpivoted” or “denormalized” so it looks like this:
Although this is less space efficient, space is cheap, and it means there’s always only one record per row, so you can use simple functions to access and filter data.
Here are three ways to “unpivot” or “denormalize” your data - in effect, to transpose columns to rows and have one complete record per row.