Pivot Tables Zoo
Pivot Tables are one of the many types of tabular visualization in the DataFrame Zoo. Zooming in, Pivot tables themselves have their own zoo that will be explored here.
Data setup
Pivot Table Zoo
Row fields only
This is a simple grouping by a set of fields and there is no pivoting operation yet. This is as if there are 0 pivoted column fields.
Single row & column fields
Multiple row fields & single column field
Multiple row & column fields
Multiple value fields
Value fields & column fields
By default multiple value fields are displayed together within each pivoted column field values. This setting allows showing all the pivot column field values within each value field.
Value fields in rows
While the pivoted columns can be sorted and the sorting happens on the column values, there may be a need to sort by the actual values along the pivoted columns. To do this, user can first display the values as stacked rows and then they contain the sort controls to sort the data along the columns.
Value fields row first
Column fields only
Combined with the values stacking mentioned above, displaying only with pivot column fields provides a standard comparision table with a set of products along the columns and their features (metrics in this case) along the rows.
Hierarchical rows
Multiple column fields in the pivot table are rendered as hierarchical by default. It is also possible to display the rows hierarchically when there are multiple pivot row fields.
All the display options mentioned above for the pivot table are also available for the hierarchical pivot table.
Summary
There are many ways to present the pivot table as per the visualization needs of the use cases. SQL Frames provides all this flexibility of rendering with a simple low-code API on top of an equally simple API to manipulate the data model to convert it into pivot tables.