Pivot Tables and Medians in R

Pivot Tables are a useful way of aggregating data into the format that you’re after. In this example, I’m going to be using R to pivot some data and calculate medians for me. This is useful because Excel can calculate medians (the =MEDIAN(values)) function, but what it can’t do is calculate medians for Pivot Tables. I assume that it can’t do this because calculating the median of large groups of aggregated data can be very computationally intensive, and may take longer than you would expect.

The good news, however, is that R can do this with problems. Say that you have run an experiment and are left with the following:

participant condition score
1 1 95
1 1 90
1 2 105
1 2 110
2 1 64
2 1 80
2 2 90
etc.

But that’s now what you want – instead, say that you want the following:

Participant condition_1 condition_2
1 median of score median of score
2 median of score median of score
etc.

Here’s the code I used to sort this out:

datafile = read.table(file.choose(), header= TRUE)

median_output <- tapply(as.numeric(datafile$score),  list(datafile$participant,  datafile$condition), median)

write.table(median_output, file.choose())

Using file.choose() presents you with a pop-up window asking which file to load in to use as your datafile and also asks you, at the end of the script, where you want to save your pivoted data. At this point, you can call it a text file (e.g., “medians.txt”) and save it to wherever you want.

To Pivot more complex datasets, all you need to do is add more columns from your dataset to the list function. You’ll then get the fully pivoted data out instead.

Don’t forget that you can run this using funcitons other than the median (e.g., mean) – just replace median with whatever you need.

Note finally that I ran as.numeric() on the score column. This was done because, when reading in the raw data, R sometimes assumes that the column is a factor rather than a numeric column. If it’s assumed the wrong thing, you’ll probably get an error saying “Error in tapply…  arguments must have same length”. If this happens, make sure that all of your columns which should be a factor are a factor and all of your columns which should be numeric are numeric.

Leave a comment

7 Comments

  1. Suhel

     /  April 19, 2011

    Re. your note on using as.numeric(). Just a caution that if a column is stored as a factor, you should not use as.numeric() directly on it, but rather as.numeric(as.character(x)) — this is because of the way that factor encoding is done in R. See ?factor, and particularly the Details and Warning sections. For an illustration of what can go wrong:
    > as.numeric(factor(seq(0, 10, by=2)))

  2. Good point – I didn’t know that could make a difference, but thanks! I’ll make sure to cast as character first in future.

  3. I think Excel actually can do medians in a pivot table.

  4. You would think it should be able to, but sadly it can’t – I’ve just double-checked the 2010 version and it doesn’t allow you to do, though it does have a built-in median function you can use when not working with pivot tables.

  5. You would think so, but sadly that’s not the case. I just checked again in the 2010 version and it’s not there.

  1. Aggregate Function in R: Making your life easier, one mean at a time | Psychwire
  2. Data Aggregation in R: plyr, sqldf and data.table | Psychwire

Leave a Reply

Your email address will not be published.

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>