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

[Total: 1 \u00a0 \u00a0Average: 3\/5]<\/span><\/div>\n

I noted that a previous post about Pivot Tables in R with melt and cast<\/a> has gained quite some popularity, so I thought to write on the same topic but using this time a different R function: aggregate<\/em>.\u00a0While aggregate<\/em> is not as powerful as\u00a0melt<\/em> and\u00a0cast<\/em> from the\u00a0reshape<\/em> package, it may result sometimes simpler and more intuitive to use, so it is worth to learn how it works.<\/p>\n

The steps to obtain, prepare and read into R the data we need to work with aggregate<\/em> are exactly the same as in the article<\/a> mentioned previously, however I will include them below for your convenience.<\/p>\n

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

Use the link at the bottom of this article<\/a> 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\u00a0read.csv<\/em>\u00a0function 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\u00a0setwd<\/em>\u00a0or 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
8<\/div>\n<\/div>\n<\/td>\n
\n
\n
><\/span> head<\/span>(<\/span>data<\/span>)<\/span><\/div>\n
\u00a0\u00a0<\/span>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>\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0<\/span>37812<\/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>\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0<\/span>37813<\/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>\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0<\/span>37814<\/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>\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0<\/span>37817<\/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>\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0<\/span>37817<\/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>\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0<\/span>37818<\/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

Now we are ready to get to work with\u00a0aggregate<\/em>.<\/p>\n

4. How aggregate works<\/h3>\n

As mentioned above,\u00a0aggregate<\/em> is a much simpler function than\u00a0 melt<\/em> and\u00a0cast.<\/em>\u00a0While it can have multiple syntaxes, the one we will work with, which applies well to data frames, is the following:<\/p>\n

\n
<\/div>\n
\n\n\n\n
\n
\n
1<\/div>\n<\/div>\n<\/td>\n
\n
\n
aggregate<\/span>(<\/span>formula<\/span>,<\/span> data<\/span>,<\/span> FUN<\/span>,<\/span> .<\/span>.<\/span>.<\/span>,<\/span>\u00a0subset<\/span>,<\/span> na<\/span>.<\/span>action<\/span> =<\/span> na<\/span>.<\/span>omit<\/span>)<\/span><\/div>\n<\/div>\n<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n<\/div>\n<\/div>\n

The most relevant argument to understand is\u00a0formula<\/em>. A formula as applicable to aggregate<\/em>\u00a0is something like this:<\/p>\n

\n
<\/div>\n
\n\n\n\n
\n
\n
1<\/div>\n<\/div>\n<\/td>\n
\n
\n
what_to_aggregate<\/span> ~<\/span> aggregate_by<\/span><\/div>\n<\/div>\n<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n<\/div>\n<\/div>\n

In the case of our data and following the example in the previous post, we want to aggregate the\u00a0Order.Amount<\/em> by\u00a0Salesperson<\/em>, therefore the formula we need is simply:<\/p>\n

\n
<\/div>\n
\n\n\n\n
\n
\n
1<\/div>\n<\/div>\n<\/td>\n
\n
\n
Order<\/span>.<\/span>Amount<\/span> ~<\/span> Salesperson<\/span><\/div>\n<\/div>\n<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n<\/div>\n<\/div>\n

Beside the formula, we need to specify the\u00a0data<\/em> argument, which will be equal to our data frame\u00a0data<\/em> (I know, sorry for haven chosen a data frame name that is the same of an argument name! I hope you can bear with me anyway\u2026), and the FUN<\/em> argument, which is the function we want to use for the aggregation. In the case of our example, we want to aggregate by summing up all sales order for each sales person, therefore\u00a0FUN=sum<\/em> is what we need.<\/p>\n

The extra arguments\u00a0 subset<\/em> allows us to subset the data before aggregating them. Say, for example, you want to make a pivot only for the sales persons in USA, you can use\u00a0subset=Country==\u201dUSA\u201d<\/em>. Remember that the assignment operator = is different than the comparison operator ==. If it is confusing to you how to read the\u00a0subset<\/em>\u00a0argument, you can also write it like this:\u00a0subset=(Country==\u201dUSA\u201d)<\/em>.<\/p>\n

Finally the argument\u00a0na.action<\/em> allows us to specify what to do with missing (N\/A) values. The default is to omit them from the aggregation.<\/p>\n

Ok, enough with the explanations, now we are ready to make our first pivot table with\u00a0aggregate<\/em>. Here is the command:<\/p>\n

\n
<\/div>\n
\n\n\n\n
\n
\n
1<\/div>\n<\/div>\n<\/td>\n
\n
\n
data<\/span>.<\/span>a<\/span> <<\/span>–<\/span> aggregate<\/span>(<\/span> Order<\/span>.<\/span>Amount<\/span> ~<\/span> Salesperson<\/span>,<\/span> data<\/span>=<\/span>data<\/span>,<\/span> FUN<\/span>=<\/span>sum<\/span>)<\/span><\/div>\n<\/div>\n<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n<\/div>\n<\/div>\n

And this is the result (which, as expected, is identical to that from melt<\/em> and cast<\/em>):<\/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
><\/span> data<\/span>.<\/span>a<\/span><\/div>\n
\u00a0\u00a0<\/span>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

Congratulations on your first pivot table with\u00a0aggregate<\/em>! \":-)\"<\/p>\n

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

Sorry to delude you, but this is where\u00a0aggregate<\/em> falls short of melt<\/em> and\u00a0cast<\/em>. In fact there is no option to add Grand Totals directly throughaggregate<\/em>. Yes, there are workarounds available which I may cover them in a future post, but no direct methods.<\/p>\n

6. How to filter<\/h3>\n

We briefly touched on filtering while describing the\u00a0subset<\/em> argument above. Just add a subsetting criteria to the call to\u00a0aggregate<\/em> to pre-filter the data on which to aggregate. For example, wanting to aggregate only the sales order for the sales persons based in USA the new syntax is:<\/p>\n

\n
<\/div>\n
\n\n\n\n
\n
\n
1<\/div>\n<\/div>\n<\/td>\n
\n
\n
data<\/span>.<\/span>a<\/span> <<\/span>–<\/span> aggregate<\/span>(<\/span> Order<\/span>.<\/span>Amount<\/span> ~<\/span> Salesperson<\/span>,<\/span> data<\/span>=<\/span>data<\/span>,<\/span> FUN<\/span>=<\/span>sum<\/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 this is the output:<\/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
><\/span> data<\/span>.<\/span>a<\/span><\/div>\n
\u00a0\u00a0<\/span>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<\/div>\n<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n<\/div>\n<\/div>\n

Congratulations! You have just learned another easy way to make Pivot Tables in R using\u00a0aggregate<\/em>. \":-)\"<\/p>\n

You can stop here or, if you feel brave enough and want to move to the next level, read the next optional paragraph.<\/p>\n

7. Aggregating more columns and with more criteria at once<\/h3>\n

You may wonder about the syntax of the\u00a0formula<\/em> inside\u00a0aggregate<\/em> in case you want (or need) to aggregate more columns at once (the left side of the formula) according to multiple criteria (the right side of the formula). Well, here is the syntax you can use for 3 columns and 3 criteria (by):<\/p>\n

\n
<\/div>\n
\n\n\n\n
\n
\n
1<\/div>\n<\/div>\n<\/td>\n
\n
\n
cbind<\/span>(<\/span> column_1<\/span>,<\/span> column_2<\/span>,<\/span> column<\/span>_<\/span>3 )<\/span> ~<\/span> criteria_1<\/span> +<\/span> criteria_2<\/span> +<\/span> criteria_3<\/span><\/div>\n<\/div>\n<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n<\/div>\n<\/div>\n

The aggregation function specified by FUN<\/i> will be applied to each column specified on the left side of the formula after the data have been partitioned (grouped) according to the criteria specified on the right side of the formula.<\/p>\n

To make a simpler example that fits with our data (which only have one numerical column), let\u2019s say we want to aggregate on the Order Amount primarily by Order Data and secondarily by Country. We can easily issue to R a command like this:<\/p>\n

\n
Aggregation on two criteria<\/span><\/p>\n
\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<\/div>\n<\/td>\n
\n
\n
data<\/span>.<\/span>a<\/span> <<\/span>–<\/span> aggregate<\/span>(<\/span> Order<\/span>.<\/span>Amount<\/span> ~<\/span> Order<\/span>.<\/span>Date<\/span> +<\/span> Country<\/span>,<\/span> data<\/span>=<\/span>data<\/span>,<\/span> FUN<\/span>=<\/span>sum<\/span>)<\/span><\/div>\n<\/div>\n<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n<\/div>\n<\/div>\n

This will produce a Pivot Table where the Order Amount for each Order Date and Country combination has been aggregated using the sum<\/em>function.<\/p>\n<\/div>\n","protected":false},"excerpt":{"rendered":"

12 months ago Marco Ghislanzoni [Total: 1 \u00a0 \u00a0Average: 3\/5] I noted that a previous post about Pivot Tables in R with melt and cast… <\/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-815","post","type-post","status-publish","format-standard","hentry","category-r"],"_links":{"self":[{"href":"https:\/\/zhuoyao.net\/index.php\/wp-json\/wp\/v2\/posts\/815","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=815"}],"version-history":[{"count":0,"href":"https:\/\/zhuoyao.net\/index.php\/wp-json\/wp\/v2\/posts\/815\/revisions"}],"wp:attachment":[{"href":"https:\/\/zhuoyao.net\/index.php\/wp-json\/wp\/v2\/media?parent=815"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/zhuoyao.net\/index.php\/wp-json\/wp\/v2\/categories?post=815"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/zhuoyao.net\/index.php\/wp-json\/wp\/v2\/tags?post=815"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}