Wikipedia says:
Pivot tables are a technique in data processing. They arrange and rearrange (or "pivot") statistics in order to draw attention to useful information. This leads to finding figures and facts quickly making them integral to data analysis.
PeakPivot is a pure-swift implementation of pivot tables modelled on the implementation offered by Microsoft Excel and Google Sheets. PeakPivot provides the base types and business logic for summarising tabular data into a pivot table.
PeakPivot defines a number of types that model
- The input data necessary for building a pivot table,
- The configuration values for a pivot table builder,
- The resulting output pivot table.
Below is screenshot of a pivot table built in Microsoft Excel from the people.csv file. It is annotated to show the corresponding PeakPivot types.
These types are the input to the pivot building logic. They are highlighted in blue in the diagram above.
Table. A array of TableRows. This is the input to data to be pivotted. For example this may come from a CSV file.
TableRow. A key-value pair of [FieldName : FieldValue]. This represents a single row in a table. If using a CSV the FieldName corresponds to the column-name, and the FieldValue corresponds to the column-value for that row.
FieldName. A string representing a column-name in the input table.
FieldValue. A string representing a cell-value for a given column and row. Note that String is used as the type here to make it easier for CSV parsing and conforming FieldValue to Equatable (necessary for unit testing). Any non-string value can be boxed into a FieldValue if required.
These types configure the pivot that is built. They are highlighted in green in the diagram above.
BuildPivot. A protocol defining the business logic for building a pivot from a supplied Table and array of FieldNames to pivot around. A protocol extension provides a default implementation of the build() function. Either conform a type to BuildPivot or use the provided concrete implementation PivotBuilder.
PivotBuilder. A concrete implementation of the BuildPivot protocol that provides defaults for the configuration varaibles.
FieldName. A string representing a column-name from the input table to pivot around.
BuildPivotFilter. Describes how to apply a filter to a pivot table to exclude certain FieldNames and values.
BuildPivotDescriptor. Describes how to apply a sort to pivot table.
These types are the output from the pivot building logic. They are highlighted in red in the diagram above.
Pivot. A pivot table, generated by processing a Table using a BuildPivot. Defines the PivotRows in the output table, the FilterFields that can be used for subsequent filting, and the total number of rows in the table.
PivotRow. A row in a pivot table, that stores the computed value information (the count, sum, percentage etc), what level in the overall table is sits at, it's title (corresponds to the FieldName in the input Table), and any subrows it has. The subrows represent the nesting of groups in a pivot table. The number of levels to this nesting is equal to (number of FieldNames - 1) as passed into the BuildPivot.
FilterField. All the fields from a generated pivot table that can be used to filter the pivot table on a subsequent build. Use these FilterFields to create BuildPivotFilters as needed.
To recreate the pivot table shown in the Excel spreadsheet above we load in the people.csv csv file using SwiftCSV (including with PeakPivot) and construct a PivotBuilder with the corresponding input and configuration data.
Set the fields variable to the FieldNames you want to group the pivot table using. The last FieldName in the array defines the FieldValues in the input Table to summarise using sum, count and percentage operators.
Once configured call the build() function.
do {
let csvURL = URL(fileURLWithPath: "url/to/people.csv")
let csv = try CSV(url: csvURL)
let csvRows = csv.namedRows
let builder = PivotBuilder()
// Set the input
builder.table = csvRows
builder.fields = ["title", "age"] // "age" is summarised
// Configure the builder
builder.sortDescriptor = .byTitle(ascending: false)
builder.filters = [BuildPivotFilter(fieldName: "title", exclude: ["Blank", "Rev"])] // exclude "Blank" and "Rev" from the pivot table
builder.sumsEnabled = true // compute sums
builder.percentagesEnabled = true // compute percentages
// Run the builder
let pivot = try builder.build()
// Below are examples of the output
// pivot.rows will equal
let pivotRows = [
PivotRow(level: 0, title: "Ms", value: PivotRow.Value(count: 1, sum: 33, percentage: 1/9), subRows: [
PivotRow(level: 1, title: "33", value: PivotRow.Value(count: 1, sum: 33, percentage: 1/9), subRows: nil),
]),
PivotRow(level: 0, title: "Mrs", value: PivotRow.Value(count: 1, sum: 45, percentage: 1/9), subRows: [
PivotRow(level: 1, title: "45", value: PivotRow.Value(count: 1, sum: 45, percentage: 1/9), subRows: nil),
]),
PivotRow(level: 0, title: "Mr", value: PivotRow.Value(count: 2, sum: 75, percentage: 2/9), subRows: [
PivotRow(level: 1, title: "54", value: PivotRow.Value(count: 1, sum: 54, percentage: 1/9), subRows: nil),
PivotRow(level: 1, title: "21", value: PivotRow.Value(count: 1, sum: 21, percentage: 1/9), subRows: nil),
]),
PivotRow(level: 0, title: "Honorable", value: PivotRow.Value(count: 2, sum: 83, percentage: 2/9), subRows: [
PivotRow(level: 1, title: "54", value: PivotRow.Value(count: 1, sum: 54, percentage: 1/9), subRows: nil),
PivotRow(level: 1, title: "29", value: PivotRow.Value(count: 1, sum: 29, percentage: 1/9), subRows: nil),
]),
PivotRow(level: 0, title: "Dr", value: PivotRow.Value(count: 3, sum: 143, percentage: 3/9), subRows: [
PivotRow(level: 1, title: "63", value: PivotRow.Value(count: 1, sum: 63, percentage: 1/9), subRows: nil),
PivotRow(level: 1, title: "42", value: PivotRow.Value(count: 1, sum: 42, percentage: 1/9), subRows: nil),
PivotRow(level: 1, title: "38", value: PivotRow.Value(count: 1, sum: 38, percentage: 1/9), subRows: nil),
])
// pivot.total will equal
let pivotTotal = 9
// pivot.fieldFields will equal
let filterFields = [
FilterField(name: "title", values: [
"Dr",
"Honorable",
"Mr",
"Mrs",
"Ms",
"Rev",
Blank
]),
FilterField(name: "age", values: [
"21",
"25",
"29",
"33",
"35",
"36",
"38",
"40",
"41",
"42",
"44",
"45",
"52",
"54",
"57",
"58",
"63",
"68"
])
]
]
} catch {
// Handle errors
}For more examples of how to use PeakPivot see the unit tests. There is also a basic example iOS project that uses PeakPivot.
PeakPivot supports the Swift Package Manager. Add the PeakPivot dependency in your package.swift.
dependencies: [
.package(
url: "https://github.com/3squared/PeakPivot.git",
.upToMajor(from: "2.0.0")
),
]This project is licensed under the MIT License - see the LICENSE file for details
The Peak Framework is a collection of open-source microframeworks created by the team at 3Squared, named for the Peak District. It is made up of:
| Name | Description |
|---|---|
| PeakOperation | Provides enhancement and conveniences to Operation, making use of the Result type. |
| PeakNetwork | A networking framework built on top of Session using PeakOperation, leveraging the power of Codable. |
| PeakCoreData | Provides enhances and conveniences to Core Data. |