🧠Second Brain
Search
Pivot Table
Are pivot tables making a comeback in 2024? Rill Developer incorporates pivot tables within its web UI, Cube enables pivot table functionality in Excel WITH Cube, and DuckDB has introduced its own implementation. Tweet
# What is a Pivot Table?
A pivot table is a table of values that are aggregations of groups of individual values from a more extensive table (such as from a database, spreadsheet, or business intelligence program) within one or more discrete categories.
The aggregations or summaries of the groups of the individual terms might include sums, averages, counts, or other statistics. A pivot table is the outcome of the statistical processing of tabularized raw data and can be used for decision-making.
Although pivot table is a generic term, Microsoft held a trademark on the term in the United States from 1994 to 2020.
Pito Salas is a Curaçaoan-American Cambridge, Massachusetts-based software developer. While working with Lotus’ Advanced Technology Group in 1986, Salas invented the pivot table, a “next-generation” spreadsheet concept that was released by Lotus in 1989, as Lotus Improv. Source - Wiki
# History
gantt title Milestones in Spreadsheet & Pivot Table Software dateFormat YYYY axisFormat %Y section Early Foundations Paper Spreadsheets :milestone, m1, 1960, 0d section Digital Revolution & Spreadsheets IBM Financial Planning :milestone, m2, 1970, 0d VisiCalc :milestone, m3, 1979, 0d Lotus 1-2-3 :milestone, m4, 1983, 0d Excel for Mac :milestone, m5, 1985, 0d section Desktop Era Lotus Improv (First Pivot):milestone, m7, 1989, 0d Excel 5.0 (Pivot Tables) :milestone, m8, 1993, 0d QlikView :milestone, m9, 1993, 0d Tableau :milestone, m10, 2003, 0d PowerPivot :milestone, m12, 2010, 0d section Cloud Era Power BI :milestone, m13, 2015, 0d Sigma :milestone, m14, 2019, 0d Cube, Omni, Rill :milestone, m14, 2025, 0d click m8 href "Pivot Tables introduced" click m7 href "First pivot concept" click m12 href "PowerPivot & Power Query" click m13 href "Python integration" click m14 href "Python integration"
From Rill | Why Pivot Tables Never Die.
# Examples of the Resurgence
# DuckDB
DuckDB introduces pivot tables through the Excel pivoting API. More details can be found in Creating a SQL-Only Extension for Excel-Style Pivoting in DuckDB – DuckDB:
The
pivot_table
function allows for Excel-style pivots, includingsubtotals
,grand_totals
, and more. It is also very similar to the Pandaspivot_table
function, but with all the scalability and speed benefits of DuckDB. It contains over 250 tests, so it is intended to be useful beyond just an example!
To use the pivot table functionality in DuckDB:
|
|
An example by Michael Simons on pv/bin/import_and_update.sh at main · michael-simons/pv · GitHub.
# Rill
Vimeo Video:
Introducing the Rill pivot table
For more information, see Rill Developer and Rill | Introducing the Rill pivot table.
# Cube
Cube integrates with Google Sheets and Excel. The following example shows integration with Google Sheets as of 2024-09-26:
Source: How Cube Improves Spreadsheet Analytics - Cube Blog
# Pandas
Pivot tables are so not bad that even pandas has a pivot_table function… Bsky
# History
# Further Readings
- Pivot is just a shorter, fatter group by
- Cell and text formatting is everywhere - Luis D. Verde Arregoitia
Origin: Excel for ever, DuckDB Con in Seattle
References:
Created 2024-09-24