Pages
Categories
- Axlett
- http://news.mrdwab.com mrdwab
- Edmundchen
- http://news.mrdwab.com mrdwab
- Mauricio
- http://news.mrdwab.com mrdwab
- http://blog.hiremebecauseimsmart.com recommendation engineer
- http://news.mrdwab.com mrdwab
Browsing on a mobile?
The Grumpy Writer’s Grumps- Other things worth reading
Using the reshape package in R for pivot-table-like functionality
A little more than a week ago, I wrote about creating pivot tables in Microsoft Excel and OpenOffice.org. I also mentioned that I would explain how to do similar calculations by using R. This post will explain how to achieve similar results in R by using the reshape package.
I had initially started experimenting with the reshape package several months ago when I was trying to figure out how to reshape data from wide to long formats. However, once I started experimenting with it, I realized I had misunderstood what the reshape package was designed to do. Now that I finally have a grasp of what can be done using the package, I thought I would share what I’ve found using a few examples.
Part 1: Contingency tables using built-in functions
There are a lot of different ways to tabulate data in R. In this post, I’ll start by demonstrating how to use
table,xtabs, andftablebefore making things more interesting and using the reshape package to flip data around.We’ll start with R’s built-in functions. First, we’ll get some data into R. We’ll start with a basic table from Wikipedia that was used to demonstrate cross-tabulation. I’ve already gone and put the data in a CSV file that can be loaded using the code below.
Did you see how easy it was to tabulate frequencies of the data? Unfortunately, not all data is that easy to work with. Now, we’ll add a couple of columns to the data.
We can use the table function again to see what our data look like. In the following line, we are using favorite color (
fav.col) as our rows,Genderas our columns, and separate tables for left-handed and right-handed respondents.This is OK, but the code is somewhat cumbersome. The
xtabsfunction will give you the same result, but with some simpler code. Notice that we don’t need to use the “$” notation in the following code since the last element in our command identifies the data.The
ftable(flat table) function is even more flexible. Instead of having two separate tables, for instance, you can have a single table with the same information. With theftablefunction, you mention the data, and then identify what data you want to use for rows, and what data you want to use for your columns. In our data, we have four columns named “Gender“, “Handedness“, “fav.col“, and “fav.shape“. If we wanted a flat table version of the previous output, we want the third column as our rows, and our columns are determined by the first and third columns. Here are three examples and their output.Part 2: Advanced results using the reshape package
These flat frequency tables are informative, but they may not provide you all the information you actually want. For instance, what if you wanted more pivot-table-like results, where you were interested in not just frequencies, but maybe also sums and averages. If that’s the case, then you really need to figure out how to use the reshape package. Here are some examples.
First, we’ll load the package, load some data, and preview the rows in our data. (We’ll use the same data from the Pivot Tables in Excel and OpenOffice.org Calc post.)
In order to use the reshape package, you need to “
melt” the data. The melting function asks for id variables (id.vars) and measured variables (measured). In the case of the book sales dataset, the measured variables are “Sales“, “Margin“, and “Quantity” (columns 6 through 8). Using that information, we melt the data using the following code.Now, it’s time to play around and see what we can do with reshape. The counterpart to “
melt” is “cast“. We first mention what dataset we are working with (m.book.sales) and then the reshape function that we want to use. We use the “~” symbol to define relationships. The “~” is usually read as “is described by”, but it’s best understood with some experimentation.Well, that’s not really useful. But the notice provided, “
Aggregation requires fun.aggregate: length used as default” is helpful. What that means is that we need to tell R what functions we want to use, for instance, “sum” or “mean“. Let’s try this again.Much better. But, can we make more complicated tables? Here’s one sorted first by region, then by representative.
Notice that the above code is a convenient flat table. Also, the above table works well because each representative only sells in one region. But what if we had used “
Region + Representative” instead?Notice that now things are not so tidy. Books from a given publisher are sold in multiple districts. By sticking another “
~” into the code after “variable“, we can get some separated tables. Compare the following table with the previous one.What if we wanted totals? To get that, we need to add “
margins” to our code.And, what if we wanted both sum and mean? We can set up multiple functions as follows.
Of course, we can also subset our data so that we just get information on selected variables. You may remember from the original book sales dataset that the “
variables” are “Sales“, “Margin“, and “Quantity“. We can use that information to cast different summary tables. Here are two more examples.Who needs pivot tables in Excel?
Related posts (possibly):