excel_pivot_table {tidyquant}

Excel Pivot Table

Description

The Pivot Table is one of Excel's most powerful features, and now it's available in R! A pivot table is a table of statistics that summarizes the data of a more extensive table (such as from a database, spreadsheet, or business intelligence program).

These functions are designed to help users coming from an Excel background. Most functions replicate the behavior of Excel:

• Names are similar to Excel function names

• Functionality replicates Excel

Usage

pivot_table(
  .data,
  .rows,
  .columns,
  .values,
  .filters = NULL,
  .sort = NULL,
  fill_na = NA
)

Arguments

.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))

.filters

This argument is not yet in use

.sort

This argument is not yet in use

fill_na

A value to replace missing values with. Default is NA

Details

This summary might include sums, averages, or other statistics, which the pivot table groups together in a meaningful way.

The key parameters are:

• .rows - These are groups that will appear as row-wise headings for the summarization, You can modify these groups by applying collapsing functions (e.g. (YEAR()).

• .columns - These are groups that will appear as column headings for the summarization. You can modify these groups by applying collapsing functions (e.g. (YEAR()).

• .values - These are numeric data that are summarized using a summary function (e.g. SUM(), AVERAGE(), COUNT(), FIRST(), LAST(), SUM_IFS(), AVERAGE_IFS(), COUNT_IFS())

R implementation details.

• 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:

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

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

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

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

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

Value

Returns a tibble that has been pivoted to summarize information by column and row groupings

Examples

library(tidyquant)
library(tidyverse)

# PIVOT TABLE ----
# Calculate returns by year/quarter
FANG %>%
    pivot_table(
        .rows       = c(symbol, ~ QUARTER(date)),
        .columns    = ~ YEAR(date),
        .values     = ~ PCT_CHANGE_FIRSTLAST(adjusted)
    )