*37*

In Excel, **pivot tables** offer an easy way to group and summarize data.

For example, if we have the following dataset in Excel then we can use a pivot table to quickly summarize the total sales by region:

This tells us:

- Region A had 51 total sales
- Region B had 85 total sales
- Region C had 140 total sales

Or we could summarize by another metric such as the average sales by region:

It turns out that we can quickly create similar pivot tables in R by using theÂ **group_by()** andÂ **summarize()** functions from the dplyr package.

This tutorial provides several examples of how to do so.

**Example: Create Pivot Tables in R**

First, letâ€™s create the same dataset in R that we used in the previous examples from Excel:

#create data frame df frame(region=c('A', 'A', 'A', 'B', 'B', 'B', 'C', 'C', 'C', 'C'), device=c('X', 'X', 'Y', 'X', 'Y', 'Y', 'X', 'X', 'Y', 'Y'), sales=c(12, 18, 21, 22, 34, 29, 38, 36, 34, 32)) #view data frame df region device sales 1 A X 12 2 A X 18 3 A Y 21 4 B X 22 5 B Y 34 6 B Y 29 7 C X 38 8 C X 36 9 C Y 34 10 C Y 32

Next, letâ€™s load the dplyr package and use theÂ **group_by()** andÂ **summarize()** functions to group by region and find the sum of sales by region:

library(dplyr) #find sum of sales by region df %>% group_by(region) %>% summarize(sum_sales = sum(sales)) # A tibble: 3 x 2 region sum_sales 1 A 51 2 B 85 3 C 140

We can see that these numbers match the numbers shown in the introductory Excel example.

We can also calculate the average sales by region:

#find average sales by region df %>% group_by(region) %>% summarize(mean_sales = mean(sales)) # A tibble: 3 x 2 region mean_sales 1 A 17 2 B 28.3 3 C 35

Once again, these numbers match the numbers shown in the Excel example from earlier.

Note that we can also group by multiple variables. For example, we could find the sum of sales grouped by regionÂ *and* device type:

#find sum of sales by region and device type df %>% group_by(region, device) %>% summarize(sum_sales = sum(sales)) # A tibble: 6 x 3 # Groups: region [3] region device sum_sales 1 A X 30 2 A Y 21 3 B X 22 4 B Y 63 5 C X 74 6 C Y 66

**Additional Resources**

How to Perform a VLOOKUP (Similar to Excel) in R

The Complete Guide: How to Group & Summarize Data in R