**Psychwire » R**, and kindly contributed to R-bloggers)

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**for the author, please follow the link and comment on his blog:

**Psychwire » R**.

R-bloggers.com offers

**daily e-mail updates**about R news and tutorials on topics such as: visualization (ggplot2, Boxplots, maps, animation), programming (RStudio, Sweave, LaTeX, SQL, Eclipse, git, hadoop, Web Scraping) statistics (regression, PCA, time series, trading) and more...