The Pivot Table is one of Excel’s most powerful features. A tool used for quickly summarizing data into a digestible table. This summary might include sums, averages, or other statistics, which the pivot table groups together in a meaningful way. It’s drag & drop feature helps you to summarize & visualize the data as one wants to.

If you are an R user, you know that aggregate() function in base R & group_by() & summarise() function from dplyr package gives summarized table, but the summarizing functions being limited unlike pivot table in Excel which has many functions.

But not to worry, it’s now possible in R with pivot_table() function in tidyquant package.

#Install & Load Packages

install.packages("tidyquant")
install.packages("tidyverse")
install.packages("knitr")
install.packages("dplyr")
library(tidyquant)
library(tidyverse)
library(knitr)
library(dplyr)

“FANG” dataset in tidyquant package is used. FANG is the OHLC EOD data of 4 symbols Facebook, Amazon, Google & Netflix for 4 year 2013 – 2016.

head(FANG)
## # A tibble: 6 x 8
##   symbol date        open  high   low close    volume adjusted
##   <chr>  <date>     <dbl> <dbl> <dbl> <dbl>     <dbl>    <dbl>
## 1 FB     2013-01-02  27.4  28.2  27.4  28    69846400     28  
## 2 FB     2013-01-03  27.9  28.5  27.6  27.8  63140600     27.8
## 3 FB     2013-01-04  28.0  28.9  27.8  28.8  72715400     28.8
## 4 FB     2013-01-07  28.7  29.8  28.6  29.4  83781800     29.4
## 5 FB     2013-01-08  29.5  29.6  28.9  29.1  45871300     29.1
## 6 FB     2013-01-09  29.7  30.6  29.5  30.6 104787700     30.6

Let’s start with, getting a table of Symbol wise total volume of share for every year

pivot_table(
  .data = FANG,
  .rows    = symbol,
  .columns = ~ YEAR(date),
  .values  = ~ SUM(volume)
) %>%  kable()
symbol 2013 2014 2015 2016
AMZN 747905700 1029066700 956936800 1037105600
FB 15143182600 11977699100 6792708200 6414357000
GOOG 1055967100 626733500 521446300 461120900
NFLX 6915790700 4898415200 4679881700 3228349500

where,

.data : A data.frame or tibble that contains data to summarize with a pivot table

.rows : Enter one or more groups to assess as expressions (e.g. ~ MONTH(date_column))

.columns : Enter one or more groups to assess expressions (e.g. ~ YEAR(date_column))

.values : Numeric only. Enter one or more summarization expression(s) (e.g. ~ SUM(value_column))

kable() : It’s very simple table generator in package knitr

Note :

• The pivot_table() function is powered by the tidyverse, an ecosystem of packages designed to manipulate data.

• All of the key parameters can be expressed using a functional form:

o Rows and Column Groupings can be collapsed. Example: .columns = ~ YEAR(order_date)

o Values can be summarized provided a single value is returned. Example: .values = ~ SUM_IFS(order_volume >= quantile(order_volume, probs = 0.75))

o Summarizations and Row/Column Groupings can be stacked (combined) with c(). Example: .rows = c(~ YEAR(order_date), company)

o Bare columns (e.g. company) do not need to be prefixed with the ~.

o All grouping and summarizing functions MUST BE prefixed with ~. Example: .rows = ~ YEAR(order_date)

Now getting MAX adjusted close price in each Quarter for all 4 years for each Symbol.

pivot_table(
    .data = FANG,
    .rows       = c(symbol, ~ QUARTER(date)),
    .columns    = ~ YEAR(date),
    .values     = ~ MAX(adjusted)
  ) %>% 
  kable()
symbol QUARTER(date) 2013 2014 2015 2016
AMZN 1 283.98999 407.04999 387.82999 636.99
AMZN 2 281.76001 342.98999 445.98999 728.24
AMZN 3 318.12000 360.84000 548.39001 837.31
AMZN 4 404.39002 338.64002 693.96997 844.36
FB 1 32.47000 72.03000 85.31000 116.14
FB 2 28.97000 67.60000 88.86000 120.50
FB 3 51.24000 79.04000 98.39000 131.05
FB 4 57.96000 81.45000 109.01000 133.28
GOOG 1 418.88183 609.47654 575.33261 764.65
GOOG 2 457.48830 578.65263 565.06256 766.61
GOOG 3 461.88392 596.08269 672.92999 787.21
GOOG 4 559.79618 577.35261 776.59998 813.11
NFLX 1 28.06429 64.99715 69.00429 117.68
NFLX 2 34.77143 64.09714 97.31286 111.51
NFLX 3 44.83286 69.19857 126.45000 100.09
NFLX 4 54.36857 66.69428 130.92999 128.35

Alternatively, if one wants to view a pivot table as in excel, where one can drag & drops variables as and when they wish then there is function available for this too in R.

rpivotTable() function in rpivotTable package is that solution.

Let’s see how it looks like :

#Install & Load package :

install.packages("rpivotTable")
library(rpivotTable)
rpivotTable(FANG)

Now let’s create 2 new variables : Year & Quarter

dt <- FANG
dt$Quarter <- QUARTER(dt$date)
dt$Year <- YEAR(dt$date)
rpivotTable(dt)

Now let’s see Year wise Max adjusted close price for each symbol

Best about this function is u can view your selection in form of different charts & not just a Table.

For example, let’s see the above table in form of a Line graph.

Note : rpivotTable() function is of use to only view a pivot table. One cannot save the table.