During the past few years, interest in high-frequency price data has grown steadily. Recent major economic events - including the food crisis and the energy price surge – have increased the need for timely high-frequency data, openly available to all users. Standard survey methods lag behind in meeting this demand, due to the high cost of collecting detailed sub-national data, the time delay usually associated with publishing the results, and the limitations to publishing detailed data. For example, although national consumer price indices (CPIs) are published on a monthly basis in most countries, national statistical offices do not release the underlying price data.
"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.