Converting a 4-dimensional wide table into long format

In my last article about converting a wide table into a long table using reshape’s melt function (recommend reading it first), I promised to soon cover the 4-dimensional case – here you go. ¬†Originally I was faced with this problem when checking out the official statistics on death causes in Germany. The problem is that you cannot apply the pivoting tools of spreadsheet programs like Excel or Calc to a (wide) cross table. Also other tools like reshape’s cast function expect a long structured data table.

We will again use the melt function of R’s reshape package. As a matter of fact I am unsure if the solution I chose is the most elegant way to do this – also it is depending on having a wide-formatted dimension with few different values – like gender in this case. In case of too many values for the wide dimensions one could have the script loop through them. If somebody knows a nifty one step solution with melt – I’d be happy to hear about it.

Again we first need to give the first two long columns a name right above them in the second row – let’s say “year” and “type”. After exporting it as a CSV into wide.csv we end up with this (note how the first two columns in the first row are empty):

And the R command sequence or script could look like this:

And here is what the result file long.csv should contain.

And that’s about it already. Now we can reimport the data and start pivoting. How pivoting works in LibreOffice’s Calc I am going to explain in one of the next articles.

Leave a Reply

Your email address will not be published. Required fields are marked *