{"id":813,"date":"2015-02-27T14:19:23","date_gmt":"2015-02-27T21:19:23","guid":{"rendered":"http:\/\/homepages.uc.edu\/~yaozo\/wordpress\/?p=813"},"modified":"2015-02-27T14:19:23","modified_gmt":"2015-02-27T21:19:23","slug":"pivot-tables-in-r-with-melt-and-cast","status":"publish","type":"post","link":"https:\/\/zhuoyao.net\/index.php\/2015\/02\/27\/pivot-tables-in-r-with-melt-and-cast\/","title":{"rendered":"Pivot Tables in R with melt and cast"},"content":{"rendered":"
\n
<\/i>Marco Ghislanzoni<\/a><\/span><\/span><\/div>\n<\/header>\n
\n
\"Untitled\"<\/div>\n
\n
<\/div>\n

[Total: 10 \u00a0 \u00a0Average: 2.5\/5]<\/span><\/div>\n

If you are making the transition from Excel to R and still can\u2019t figure out how to quickly obtain pivot tables like Excel has, this article is for you!<\/p>\n

Actually it is pretty easy to produce Pivot Tables in R. All you need is a package called\u00a0reshape<\/em> by\u00a0Hadley Wickham (yes, the same prolific author of\u00a0plyr<\/em> and\u00a0ggplot2<\/em>) and some understanding of how\u00a0reshape<\/em>\u00a0\u201cthinks\u201d and works.<\/p>\n

If you are interested in an alternative, easier but less powerful, method to create Pivot Tables in R using\u00a0aggregate<\/em>, you can read my other post here<\/a>.<\/p>\n

For this tutorial, we will be using a sample data set called Salespeople PivotTable report<\/em>,\u00a0which is used in many articles by Microsoft teaching the basics of Pivot Tables in Excel. The link to the sample data file can be found at the bottom of this article.<\/p>\n

We will first obtain each result in Excel and then reproduce it with R.\u00a0I recommend that you download the sample data file and follow along.<\/p>\n

1. Download the sample data file<\/h3>\n

Use the link at the bottom of the article and save the file somewhere on your hard drive. So far so good!<\/p>\n

2. Open the file in Excel and save a CSV copy of the first sheet<\/h3>\n

Double-click the file from the location where you saved it to open it inside Excel. The Excel worksheet has 3 tabs. We will be using the first one, containing the source data, and the last one, which contains a ready-made Pivot Table to play with.\u00a0If you do not know how to make Pivot Tables in Excel, I recommend you read the Microsoft tutorial. In this article we will focus on how to obtain the same results with R and we will use the ready-made pivot table on sheet 3.<\/p>\n

Once you are on the first sheet of the sample data set (the one with the source data), save a copy of it as CSV (Comma Separated Values) file. This will generate a file which is easier to import into R. R has packages which allow you to read directly Excel files without converting them to CSV, but for the sake of\u00a0simplicity\u00a0we will stick to CSV.<\/p>\n

Before saving as CSV, change the format of column E (Order Amount) to General so that the orders amounts appear as numbers with 2 decimals and not with a $ sign in front and comma separated thousands. If you export to CSV without doing this first, there will be extra steps required in R to convert the sales amounts to numeric (see the note at the end of the article).<\/p>\n

3. Load the CSV file in R<\/h3>\n

This is an easy step accomplished with the read.CSV<\/em> function as follows.<\/p>\n

\n
<\/div>\n
\n\n\n\n
\n
\n
1<\/div>\n<\/div>\n<\/td>\n
\n
\n
data<\/span> <<\/span>–<\/span> read<\/span>.<\/span>CSV<\/span>(<\/span>“file_name.txt”<\/span>,<\/span> header<\/span>=<\/span>TRUE<\/span>)<\/span><\/div>\n<\/div>\n<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n<\/div>\n<\/div>\n

file_name.txt is the name of the CSV file you created from Excel. If it is not in the current working directory in R, you can either change R\u2019s working directory with setwd<\/em> or move the file to the current working directory or add a path to the file before the file name.<\/p>\n

At this point you should have the same base data open in Excel and loaded into R. In R the first lines will look like this:<\/p>\n

\n
<\/div>\n
\n\n\n\n
\n
\n
1<\/div>\n
2<\/div>\n
3<\/div>\n
4<\/div>\n
5<\/div>\n
6<\/div>\n
7<\/div>\n<\/div>\n<\/td>\n
\n
\n
Country <\/span>Salesperson <\/span>Order<\/span>.<\/span>Date <\/span>OrderID <\/span>Order<\/span>.<\/span>Amount<\/span><\/div>\n
1<\/span>\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0<\/span>UK\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0<\/span>Suyama<\/span> 10<\/span>\/<\/span>07<\/span>\/<\/span>2003<\/span>\u00a0\u00a0 <\/span>10249<\/span>\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0<\/span>1863.40<\/span><\/div>\n
2<\/span>\u00a0\u00a0\u00a0\u00a0 <\/span>USA\u00a0\u00a0\u00a0\u00a0 <\/span>Peacock<\/span> 11<\/span>\/<\/span>07<\/span>\/<\/span>2003<\/span>\u00a0\u00a0 <\/span>10252<\/span>\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0<\/span>3597.90<\/span><\/div>\n
3<\/span>\u00a0\u00a0\u00a0\u00a0 <\/span>USA\u00a0\u00a0\u00a0\u00a0 <\/span>Peacock<\/span> 12<\/span>\/<\/span>07<\/span>\/<\/span>2003<\/span>\u00a0\u00a0 <\/span>10250<\/span>\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0<\/span>1552.60<\/span><\/div>\n
4<\/span>\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0<\/span>UK\u00a0\u00a0 <\/span>Dodsworth<\/span> 15<\/span>\/<\/span>07<\/span>\/<\/span>2003<\/span>\u00a0\u00a0 <\/span>10255<\/span>\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0<\/span>2490.50<\/span><\/div>\n
5<\/span>\u00a0\u00a0\u00a0\u00a0 <\/span>USA\u00a0\u00a0 <\/span>Leverling<\/span> 15<\/span>\/<\/span>07<\/span>\/<\/span>2003<\/span>\u00a0\u00a0 <\/span>10251<\/span>\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 <\/span>654.06<\/span><\/div>\n
6<\/span>\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0<\/span>UK\u00a0\u00a0\u00a0\u00a0<\/span>Buchanan<\/span> 16<\/span>\/<\/span>07<\/span>\/<\/span>2003<\/span>\u00a0\u00a0 <\/span>10248<\/span>\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 <\/span>440.00<\/span><\/div>\n<\/div>\n<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n<\/div>\n<\/div>\n

4. Your first Pivot Table in R with melt and cast<\/h3>\n

Here we go. This is what we have been waiting for, the rest was just preparation to get to this point. Ready?<\/p>\n

We will re-produce in R a pivot table like the default one that appears in the 3rd tab of the sample Excel file and which looks like this.<\/p>\n

\"Capture_1\"<\/a><\/p>\n

Basically it shows how much each sales person has sold in total across all orders and and countries\u00a0(note that the Country filter is set to All). It also shows a Grand Total which is the sum of all sales.<\/p>\n

To get the job done in R we will use the reshape<\/em> package. There are other packages available which can achieve the same results, but\u00a0reshape<\/em>is particularly versatile and easy to use once you have grasped some basic concepts. (And, hey, it has been written by\u00a0Hadley Wickham, so we _have_ to use it!)<\/p>\n

We can install (if not already installed) and load the\u00a0reshape<\/em> package with:<\/p>\n

\n
<\/div>\n
\n\n\n\n
\n
\n
1<\/div>\n
2<\/div>\n<\/div>\n<\/td>\n
\n
\n
install<\/span>.<\/span>packages<\/span>(<\/span>“reshape”<\/span>)<\/span><\/div>\n
library<\/span>(<\/span>reshape<\/span>)<\/span><\/div>\n<\/div>\n<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n<\/div>\n<\/div>\n

If you have\u00a0ggplot2<\/em> installed and loaded,\u00a0reshape<\/em> will already be available.<\/p>\n

The\u00a0reshape<\/em>\u00a0package contains two functions that are key to easily generate pivot tables in R. They are\u00a0melt<\/em>\u00a0and\u00a0cast<\/em>.<\/p>\n

melt<\/em> transforms a data frame from the original format to a so called long format, where all the observed variables (called measures) appear, together with their respective value, in two adjacent columns named variable<\/strong> and value<\/strong>. Each row of this new data format is identified by a unique combination of the id<\/strong> variables, also part of the original data frame.
\n<\/i><\/p>\n

An example will clarify how\u00a0melt<\/em> works. Let\u2019s take the first 6 rows of our data set.<\/p>\n

\n
<\/div>\n
\n\n\n\n
\n
\n
1<\/div>\n
2<\/div>\n
3<\/div>\n
4<\/div>\n
5<\/div>\n
6<\/div>\n
7<\/div>\n<\/div>\n<\/td>\n
\n
\n
Country <\/span>Salesperson <\/span>Order<\/span>.<\/span>Date <\/span>OrderID <\/span>Order<\/span>.<\/span>Amount<\/span><\/div>\n
1<\/span>\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0<\/span>UK\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0<\/span>Suyama<\/span> 10<\/span>\/<\/span>07<\/span>\/<\/span>2003<\/span>\u00a0\u00a0 <\/span>10249<\/span>\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0<\/span>1863.40<\/span><\/div>\n
2<\/span>\u00a0\u00a0\u00a0\u00a0 <\/span>USA\u00a0\u00a0\u00a0\u00a0 <\/span>Peacock<\/span> 11<\/span>\/<\/span>07<\/span>\/<\/span>2003<\/span>\u00a0\u00a0 <\/span>10252<\/span>\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0<\/span>3597.90<\/span><\/div>\n
3<\/span>\u00a0\u00a0\u00a0\u00a0 <\/span>USA\u00a0\u00a0\u00a0\u00a0 <\/span>Peacock<\/span> 12<\/span>\/<\/span>07<\/span>\/<\/span>2003<\/span>\u00a0\u00a0 <\/span>10250<\/span>\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0<\/span>1552.60<\/span><\/div>\n
4<\/span>\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0<\/span>UK\u00a0\u00a0 <\/span>Dodsworth<\/span> 15<\/span>\/<\/span>07<\/span>\/<\/span>2003<\/span>\u00a0\u00a0 <\/span>10255<\/span>\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0<\/span>2490.50<\/span><\/div>\n
5<\/span>\u00a0\u00a0\u00a0\u00a0 <\/span>USA\u00a0\u00a0 <\/span>Leverling<\/span> 15<\/span>\/<\/span>07<\/span>\/<\/span>2003<\/span>\u00a0\u00a0 <\/span>10251<\/span>\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 <\/span>654.06<\/span><\/div>\n
6<\/span>\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0<\/span>UK\u00a0\u00a0\u00a0\u00a0<\/span>Buchanan<\/span> 16<\/span>\/<\/span>07<\/span>\/<\/span>2003<\/span>\u00a0\u00a0 <\/span>10248<\/span>\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 <\/span>440.00<\/span><\/div>\n<\/div>\n<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n<\/div>\n<\/div>\n

It contains 5 columns. The first 4 identify each order by a combination of Country, Salesperson, Order.Date and OrderID. These are all non-numeric and there are no calculations we can do on them except, maybe, counting their frequency. Using\u00a0melt<\/em>\u2018s terminology, Country, Salesperson, Order.Date, OrderID are\u00a0id<\/strong> variables, while Order.Amount, which is a numeric and which is the one we would like to sum up in our pivot table, is a\u00a0measure<\/strong>.<\/p>\n

When melting your data, you can indicate multiple\u00a0id<\/strong> variables and also multiple\u00a0measure<\/strong> variables. The\u00a0id<\/strong> variable will appear as-is in the resulting melted format, while the\u00a0measure<\/strong> variable will be stacked in the\u00a0variable<\/strong> column with their respective value in the\u00a0value<\/strong> column.<\/p>\n

Note that melt<\/em> preserves all data. Nothing is lost or modified, just the way the data are collected within the data frame. It is\u00a0therefore possible to \u201cun-melt\u201d<\/em>\u00a0the melted data and go back to the original format at any time.<\/p>\n

Let\u2019s melt our sales data frame and see what the result looks like.<\/p>\n

\n
<\/div>\n
\n\n\n\n
\n
\n
1<\/div>\n<\/div>\n<\/td>\n
\n
\n
data<\/span>.<\/span>m<\/span> <<\/span>–<\/span> melt<\/span>(<\/span>data<\/span>,<\/span> id<\/span>=<\/span>c<\/span>(<\/span>1<\/span>:<\/span>4<\/span>)<\/span>,<\/span> measure<\/span>=<\/span>c<\/span>(<\/span>5<\/span>)<\/span>)<\/span><\/div>\n<\/div>\n<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n<\/div>\n<\/div>\n

Pretty simple. Instead of listing the column names we have used their numerical identifiers. We have indicated to\u00a0melt<\/em> that columns 1 to 4 (\u00a0Country, Salesperson, Order.Date, OrderID) are\u00a0id<\/strong> variables while column 5 (Order.Amount) is a\u00a0measure<\/strong> variable.
\n<\/em><\/p>\n

Here is how the first 6 rows of data.m look like at this point:<\/p>\n

\n
<\/div>\n
\n\n\n\n
\n
\n
1<\/div>\n
2<\/div>\n
3<\/div>\n
4<\/div>\n
5<\/div>\n
6<\/div>\n
7<\/div>\n<\/div>\n<\/td>\n
\n
\n
Country <\/span>Salesperson <\/span>Order<\/span>.<\/span>Date <\/span>OrderID\u00a0\u00a0\u00a0\u00a0 <\/span>variable\u00a0\u00a0 <\/span>value<\/span><\/div>\n
1<\/span>\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0<\/span>UK\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0<\/span>Suyama<\/span> 10<\/span>\/<\/span>07<\/span>\/<\/span>2003<\/span>\u00a0\u00a0 <\/span>10249<\/span> Order<\/span>.<\/span>Amount<\/span> 1863.40<\/span><\/div>\n
2<\/span>\u00a0\u00a0\u00a0\u00a0 <\/span>USA\u00a0\u00a0\u00a0\u00a0 <\/span>Peacock<\/span> 11<\/span>\/<\/span>07<\/span>\/<\/span>2003<\/span>\u00a0\u00a0 <\/span>10252<\/span> Order<\/span>.<\/span>Amount<\/span> 3597.90<\/span><\/div>\n
3<\/span>\u00a0\u00a0\u00a0\u00a0 <\/span>USA\u00a0\u00a0\u00a0\u00a0 <\/span>Peacock<\/span> 12<\/span>\/<\/span>07<\/span>\/<\/span>2003<\/span>\u00a0\u00a0 <\/span>10250<\/span> Order<\/span>.<\/span>Amount<\/span> 1552.60<\/span><\/div>\n
4<\/span>\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0<\/span>UK\u00a0\u00a0 <\/span>Dodsworth<\/span> 15<\/span>\/<\/span>07<\/span>\/<\/span>2003<\/span>\u00a0\u00a0 <\/span>10255<\/span> Order<\/span>.<\/span>Amount<\/span> 2490.50<\/span><\/div>\n
5<\/span>\u00a0\u00a0\u00a0\u00a0 <\/span>USA\u00a0\u00a0 <\/span>Leverling<\/span> 15<\/span>\/<\/span>07<\/span>\/<\/span>2003<\/span>\u00a0\u00a0 <\/span>10251<\/span> Order<\/span>.<\/span>Amount<\/span>\u00a0\u00a0<\/span>654.06<\/span><\/div>\n
6<\/span>\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0<\/span>UK\u00a0\u00a0\u00a0\u00a0<\/span>Buchanan<\/span> 16<\/span>\/<\/span>07<\/span>\/<\/span>2003<\/span>\u00a0\u00a0 <\/span>10248<\/span> Order<\/span>.<\/span>Amount<\/span>\u00a0\u00a0<\/span>440.00<\/span><\/div>\n<\/div>\n<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n<\/div>\n<\/div>\n

As said the column Order.Amount has been \u201cmelted\u201d into a variable column with the respective values in the value column.<\/p>\n

Now that our data have been melted (and you have to do this only once, unless you change idea on what you want as id and as measure), we are ready to\u00a0cast<\/em> them to build the pivot table.<\/p>\n

cast<\/em> requires us to indicate, beside a reference to the melted data, how we want to re-aggregate the values.<\/p>\n

The basic syntax is (and, yes, we are omitting some parameters that we won\u2019t need in this tutorial):<\/p>\n

\n
<\/div>\n
\n\n\n\n
\n
\n
1<\/div>\n<\/div>\n<\/td>\n
\n
\n
cast<\/span>(<\/span>data<\/span>,<\/span> formula<\/span> =<\/span> .<\/span>.<\/span>.<\/span> ~<\/span> variable<\/span>,<\/span> fun<\/span>.<\/span>aggregate<\/span>=<\/span>NULL<\/span>,<\/span> .<\/span>.<\/span>.<\/span>,<\/span> margins<\/span>=<\/span>FALSE<\/span>,<\/span> subset<\/span>=<\/span>TRUE<\/span>)<\/span><\/div>\n<\/div>\n<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n<\/div>\n<\/div>\n

formula<\/em>\u00a0and fun.aggregate<\/em> are the most important two because they indicate how we want to reshape the data and which functions to use for aggregating the values.<\/p>\n

Think of\u00a0 formula<\/em>\u00a0as saying:<\/p>\n

\n
<\/div>\n
\n\n\n\n
\n
\n
1<\/div>\n<\/div>\n<\/td>\n
\n
\n
what <\/span>do<\/span> you <\/span>want <\/span>as<\/span> rows<\/span> ~<\/span> what <\/span>do<\/span> you <\/span>want <\/span>as<\/span> columns<\/span><\/div>\n<\/div>\n<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n<\/div>\n<\/div>\n

While\u00a0fun.aggregate<\/em> says how you want to aggregate the values of the variable(s) in order to reshape them as described by\u00a0formula<\/em>. This is the equivalent of selecting \u201cCount of\u201d, or \u201cSum of\u201d etc. in Excel.<\/p>\n

Going back to our example will make this point clearer.<\/p>\n

We want to produce a pivot table which contains the total sales for each sales person. Therefore we chose Salesperson as row and variable (which is Sales.Amount) as column. The aggregate function will be\u00a0sum<\/em> to obtain the sum of the variable (Sales.Amount) for each sales person. Here the command:
\n<\/i><\/p>\n

\n
<\/div>\n
\n\n\n\n
\n
\n
1<\/div>\n<\/div>\n<\/td>\n
\n
\n
data<\/span>.<\/span>c<\/span> <<\/span>–<\/span> cast<\/span>(<\/span>data<\/span>.<\/span>m<\/span>,<\/span> Salesperson<\/span> ~<\/span> variable<\/span>,<\/span> sum<\/span>)<\/span><\/div>\n<\/div>\n<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n<\/div>\n<\/div>\n

And here is the resulting pivot table:<\/p>\n

\n
<\/div>\n
\n\n\n\n
\n
\n
1<\/div>\n
2<\/div>\n
3<\/div>\n
4<\/div>\n
5<\/div>\n
6<\/div>\n
7<\/div>\n
8<\/div>\n
9<\/div>\n
10<\/div>\n<\/div>\n<\/td>\n
\n
\n
Salesperson <\/span>Order<\/span>.<\/span>Amount<\/span><\/div>\n
1<\/span>\u00a0\u00a0\u00a0\u00a0<\/span>Buchanan<\/span>\u00a0\u00a0\u00a0\u00a0 <\/span>68792.25<\/span><\/div>\n
2<\/span>\u00a0\u00a0\u00a0\u00a0<\/span>Callahan<\/span>\u00a0\u00a0\u00a0\u00a0<\/span>123032.67<\/span><\/div>\n
3<\/span>\u00a0\u00a0\u00a0\u00a0 <\/span>Davolio<\/span>\u00a0\u00a0\u00a0\u00a0<\/span>182500.09<\/span><\/div>\n
4<\/span>\u00a0\u00a0 <\/span>Dodsworth<\/span>\u00a0\u00a0\u00a0\u00a0 <\/span>75048.04<\/span><\/div>\n
5<\/span>\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0<\/span>Fuller<\/span>\u00a0\u00a0\u00a0\u00a0<\/span>162503.78<\/span><\/div>\n
6<\/span>\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0<\/span>King<\/span>\u00a0\u00a0\u00a0\u00a0<\/span>116962.99<\/span><\/div>\n
7<\/span>\u00a0\u00a0 <\/span>Leverling<\/span>\u00a0\u00a0\u00a0\u00a0<\/span>201196.27<\/span><\/div>\n
8<\/span>\u00a0\u00a0\u00a0\u00a0 <\/span>Peacock<\/span>\u00a0\u00a0\u00a0\u00a0<\/span>225763.68<\/span><\/div>\n
9<\/span>\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0<\/span>Suyama<\/span>\u00a0\u00a0\u00a0\u00a0 <\/span>72527.63<\/span><\/div>\n<\/div>\n<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n<\/div>\n<\/div>\n

As expected (or not?!?) is identical to the Excel one. Congratulations on your first pivot table in R!<\/p>\n

But wait before you pat yourself on the shoulder. In Excel there is an extra row showing the Grand Total and we don\u2019t have it in R. How can we fix it?<\/p>\n

5. Adding Grand Totals<\/h3>\n

That\u2019s easy actually. Look back at the syntax for\u00a0cast<\/em>. There is an argument called\u00a0margins<\/em> which comes to rescue.\u00a0margins<\/em> accepts a vector that can contain the strings \u201cgrand_col\u201d and \u201cgrand_row\u201d. This adds an extra column or extra row (or both) with a grand total to the resulting data frame. The grand total is obtained through the same fun.aggregate.<\/em><\/p>\n

Let\u2019s add a row with the grand total.<\/p>\n

\n
<\/div>\n
\n\n\n\n
\n
\n
1<\/div>\n<\/div>\n<\/td>\n
\n
\n
data<\/span>.<\/span>c<\/span> <<\/span>–<\/span> cast<\/span>(<\/span>data<\/span>.<\/span>m<\/span>,<\/span> Salesperson<\/span> ~<\/span> variable<\/span>,<\/span> sum<\/span>,<\/span> margins<\/span>=<\/span>c<\/span>(<\/span>“grand_row”<\/span>)<\/span>)<\/span><\/div>\n<\/div>\n<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n<\/div>\n<\/div>\n

And here it is, identified by (all):<\/p>\n

\n
<\/div>\n
\n\n\n\n
\n
\n
1<\/div>\n
2<\/div>\n
3<\/div>\n
4<\/div>\n
5<\/div>\n
6<\/div>\n
7<\/div>\n
8<\/div>\n
9<\/div>\n
10<\/div>\n
11<\/div>\n<\/div>\n<\/td>\n
\n
\n
Salesperson <\/span>Order<\/span>.<\/span>Amount<\/span><\/div>\n
1<\/span>\u00a0\u00a0\u00a0\u00a0 <\/span>Buchanan<\/span>\u00a0\u00a0\u00a0\u00a0 <\/span>68792.25<\/span><\/div>\n
2<\/span>\u00a0\u00a0\u00a0\u00a0 <\/span>Callahan<\/span>\u00a0\u00a0\u00a0\u00a0<\/span>123032.67<\/span><\/div>\n
3<\/span>\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0<\/span>Davolio<\/span>\u00a0\u00a0\u00a0\u00a0<\/span>182500.09<\/span><\/div>\n
4<\/span>\u00a0\u00a0\u00a0\u00a0<\/span>Dodsworth<\/span>\u00a0\u00a0\u00a0\u00a0 <\/span>75048.04<\/span><\/div>\n
5<\/span>\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 <\/span>Fuller<\/span>\u00a0\u00a0\u00a0\u00a0<\/span>162503.78<\/span><\/div>\n
6<\/span>\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 <\/span>King<\/span>\u00a0\u00a0\u00a0\u00a0<\/span>116962.99<\/span><\/div>\n
7<\/span>\u00a0\u00a0\u00a0\u00a0<\/span>Leverling<\/span>\u00a0\u00a0\u00a0\u00a0<\/span>201196.27<\/span><\/div>\n
8<\/span>\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0<\/span>Peacock<\/span>\u00a0\u00a0\u00a0\u00a0<\/span>225763.68<\/span><\/div>\n
9<\/span>\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 <\/span>Suyama<\/span>\u00a0\u00a0\u00a0\u00a0 <\/span>72527.63<\/span><\/div>\n
10<\/span>\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 <\/span>(<\/span>all<\/span>)<\/span>\u00a0\u00a0 <\/span>1228327.40<\/span><\/div>\n<\/div>\n<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n<\/div>\n<\/div>\n

Well done!<\/p>\n

6. And how to Filter<\/h3>\n

Ok, ok. You are a picky one! I know you noted that our pivot table lacks one more feature respect to the Excel one. It has in fact no ability to Filter by Country. Did you get it yet? No!?!<\/p>\n

Yes, the solution is in the other parameter for\u00a0cast<\/em> which we did not consider so far (beside those we omitted):\u00a0subset<\/em>.<\/p>\n

subset<\/em> allows us to limit the casting to only a subset of the melted data selected according to certain criteria. It requires a vector of TRUE or FALSE corresponding to the rows of the melted data we want to select (TRUE) or exclude (FALSE) before the casting is performed.<\/p>\n

Say that we want to calculate the performance only for the American sales people. We need to select only those rows for which Country==\u201dUSA\u201d. This is exactly the additional condition we need to specify within\u00a0cast<\/em> and it corresponds to selecting the Filter Country = USA a the top of the Excel pivot table.<\/p>\n

\n
<\/div>\n
\n\n\n\n
\n
\n
1<\/div>\n<\/div>\n<\/td>\n
\n
\n
data<\/span>.<\/span>c<\/span> <<\/span>–<\/span> cast<\/span>(<\/span>data<\/span>.<\/span>m<\/span>,<\/span> Salesperson<\/span> ~<\/span> variable<\/span>,<\/span> sum<\/span>,<\/span> margins<\/span>=<\/span>c<\/span>(<\/span>“grand_row”<\/span>)<\/span>,<\/span> subset<\/span>=<\/span>(<\/span>Country<\/span>==<\/span>“USA”<\/span>)<\/span>)<\/span><\/div>\n<\/div>\n<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n<\/div>\n<\/div>\n

And here is the filtered pivot. The grand total has also been adjusted accordingly.<\/p>\n

\n
<\/div>\n
\n\n\n\n
\n
\n
1<\/div>\n
2<\/div>\n
3<\/div>\n
4<\/div>\n
5<\/div>\n
6<\/div>\n
7<\/div>\n<\/div>\n<\/td>\n
\n
\n
Salesperson <\/span>Order<\/span>.<\/span>Amount<\/span><\/div>\n
1<\/span>\u00a0\u00a0\u00a0\u00a0<\/span>Callahan<\/span>\u00a0\u00a0\u00a0\u00a0 <\/span>123032.7<\/span><\/div>\n
2<\/span>\u00a0\u00a0\u00a0\u00a0 <\/span>Davolio<\/span>\u00a0\u00a0\u00a0\u00a0 <\/span>182500.1<\/span><\/div>\n
3<\/span>\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0<\/span>Fuller<\/span>\u00a0\u00a0\u00a0\u00a0 <\/span>162503.8<\/span><\/div>\n
4<\/span>\u00a0\u00a0 <\/span>Leverling<\/span>\u00a0\u00a0\u00a0\u00a0 <\/span>201196.3<\/span><\/div>\n
5<\/span>\u00a0\u00a0\u00a0\u00a0 <\/span>Peacock<\/span>\u00a0\u00a0\u00a0\u00a0 <\/span>225763.7<\/span><\/div>\n
6<\/span>\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 <\/span>(<\/span>all<\/span>)<\/span>\u00a0\u00a0\u00a0\u00a0 <\/span>894996.5<\/span><\/div>\n<\/div>\n<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n<\/div>\n<\/div>\n

Congratulations, now we are really done! \":-)\"<\/p>\n

I hope you have enjoyed this tutorial on how to make simple pivot tables in R. Please leave a comment below if you would like to see more tutorials on the same topic.<\/p>\n

Till next time!<\/p>\n

 <\/p>\n

Note: Stripping $ and commas and converting to numeric<\/h3>\n

Ok, so you decided not to change the format of Column E (Amount) to General before saving the table as CSV. I had warned you. Now your punishment will be terrible and will include\u2026 regular expressions! Read on.<\/p>\n

Once you open the CSV file in R, you can verify that the column Order.Amount has indeed been imported as a Factor. In order to be able to do calculations on it, we need to convert it to numeric first. This can be easily achieved using the function as.numeric<\/em>, however the $ sign at the beginning and the commas separating the\u00a0thousands will cause it to generate unexpected results. Before running through\u00a0as.numeric<\/em>\u00a0we need therefore to strip both the $ sign and the commas. This can be quickly done using\u00a0gsub<\/em> and regular expressions. Teaching you regular expressions goes beyond the scope of this article, but the following code does the job.<\/p>\n

\n
Strip $ and , from Amount<\/span><\/p>\n
\n
<\/div>\n
<\/div>\n
<\/div>\n
<\/div>\n
<\/div>\n

R<\/span><\/div>\n<\/div>\n

<\/div>\n
\n\n\n\n
\n
\n
1<\/div>\n
2<\/div>\n<\/div>\n<\/td>\n
\n
\n
data<\/span>$<\/span>Order<\/span>.<\/span>Amount<\/span> <<\/span>–<\/span> gsub<\/span>(<\/span>“([\/$,])”<\/span>,<\/span> “”<\/span>,<\/span> data<\/span>$<\/span>Order<\/span>.<\/span>Amount<\/span>)<\/span><\/div>\n
data<\/span>$<\/span>Order<\/span>.<\/span>Amount<\/span> <<\/span>–<\/span> as<\/span>.<\/span>numeric<\/span>(<\/span>data<\/span>$<\/span>Order<\/span>.<\/span>Amount<\/span>)<\/span><\/div>\n<\/div>\n<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n<\/div>\n<\/div>\n

Obviously the two steps above can be combined into one by nesting\u00a0gsub<\/em> into\u00a0as.numeric<\/em>.<\/p>\n

 <\/p>\n

Source data:<\/h3>\n<\/div>\n","protected":false},"excerpt":{"rendered":"

about a year ago Marco Ghislanzoni [Total: 10 \u00a0 \u00a0Average: 2.5\/5] If you are making the transition from Excel to R and still can\u2019t figure… <\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"closed","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[20],"tags":[],"class_list":["post-813","post","type-post","status-publish","format-standard","hentry","category-r"],"_links":{"self":[{"href":"https:\/\/zhuoyao.net\/index.php\/wp-json\/wp\/v2\/posts\/813","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/zhuoyao.net\/index.php\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/zhuoyao.net\/index.php\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/zhuoyao.net\/index.php\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/zhuoyao.net\/index.php\/wp-json\/wp\/v2\/comments?post=813"}],"version-history":[{"count":0,"href":"https:\/\/zhuoyao.net\/index.php\/wp-json\/wp\/v2\/posts\/813\/revisions"}],"wp:attachment":[{"href":"https:\/\/zhuoyao.net\/index.php\/wp-json\/wp\/v2\/media?parent=813"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/zhuoyao.net\/index.php\/wp-json\/wp\/v2\/categories?post=813"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/zhuoyao.net\/index.php\/wp-json\/wp\/v2\/tags?post=813"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}