excel_pivot_table: Excel Pivot Table

excel_pivot_tableR Documentation

Excel Pivot Table


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


  .filters = NULL,
  .sort = NULL,
  fill_na = NA



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


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


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


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


This argument is not yet in use


This argument is not yet in use


A value to replace missing values with. Default is NA


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)


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



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

tidyquant documentation built on April 3, 2023, 5:13 p.m.