Converting a wide structured table into a long format using R

Why do we want to transform a table from wide to long?

In the article published yesterday I explained how to fetch statistics from GENESIS using the statistics on death causes as an example. After downloading all the data and glueing the tables together you are finally left with one huge monster table.

36 columns for 2 genders and 18 age groups – 2480 rows for 31 years and 80 causes of death. Unless you are an idiot savant you are probably going to feel overwhelmed.

This type of table is called a “cross table” because the values gain meaning through rows and columns crossing each other. The structure is also referred to as “wide“, for obvious reasons. Now luckily there is a handy tool called “pivot table” present in all major spreadsheet applications that allows us to look at such a table from all sorts of different perspectives.  Let’s say we want to know how many women and men aged from 20 to 34 years died per year of any type of “malignant neoplasm” from 1980 to 2010. Extracting the the necessary data manually from such a huge cross table would be quite a drag – aided by a pivoting tool it becomes a chinch. How pivoting is done practically I will explain in a separate article.

The thing is though, that table keeping the data needs to be available in a specific structure, so the spreadsheet application knows how to deal with it and apply the pivoting mechanisms. This very structure is referred to as “long“. The picture illustrates how these types relate to each other.

To achieve this transformation we are going to use the melt function of the reshape package for R. In case you’re new to R check out this wikibook for more information. To make a long story short – if you’re (halfway) seriously interested in statistical computing, there is no way around this tool.

Awesome! So, how is it done now?

Let’s keep it simple for now and assume we have cross table that looks like this in a spreadsheet: 

First we need to export the wide formatted table as a CSV into a text file ‘wide.csv’. It is important that also the first column has a name in the cell above it – let’s say a good name for the a,b column is T, then the content of the CSV file would look like this for the above example:

From there we go with the command line in R.

And here’s the resulting content of ‘long.csv’:

Now after reimporting it into Excel, Calc or whatever, the merry pivoting can start! I am going to come back to pivoting as a technology several times on this blog. One ofthe next articles will be about how to use the pivoting feature in LibreOffice‘s spreadsheet program.

Dealing with four dimensional cross tables

Maybe you noticed that the table on death causes is not two but four dimensional. As a matter of fact there are just few modifications needed to the above script to handle that case as well. This is going to be covered in a soon-to-follow, separate article.

(original article published on

2 thoughts on “Converting a wide structured table into a long format using R

  1. you’re welcome. sorry, you don’t provide enough information to say something about what is going on. try to start out with a data frame that does not use factors ( if an error or warning says something about factors things get tricky in my experience – even though the concept behind it is straight forward and sensible.



  2. Hi

    Thanks for this. This is really useful for me. I had an slight issue.

    Warning message:
    [<-.factor(*tmp*, ri, value = c(36.42849731, 33.28438187,  :
    invalid factor level, NAs generated

    It only happened for 1 column, I checked that column and the format looks fine. Can you advise?

Leave a Reply

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