tidyquant: excel_pivot_table – R documentation

Become an expert in R — Interactive courses, Cheat Sheets, certificates and more!

excel_pivot_table

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

tidyquant

Tidy Quantitative Financial Analysis

v1.0.3

MIT + file LICENSE

Authors

Matt Dancho [aut, cre], Davis Vaughan [aut]

Initial release