SPARK Reference Guide
Revision Time: 6 mins
Aggregations Reference
Grouped aggregations, pivot operations, rollup, cube, and statistical functions.
groupBy
Return: GroupedDataGroups rows using specified columns for subsequent aggregate calculations.
Used In: Departmental Metrics, Categorical Summaries
Syntax signature:
df.groupBy(*cols)Code snippet:
python
df.groupBy('department').avg('salary')Expected Output:
| department | avg(salary) |
|---|---|
| Engineering | 95000.0 |
Remember: Triggers network shuffle. Filter rows as heavily as possible BEFORE invoking `groupBy()`.
agg
Return: DataFrameComputes multiple aggregations simultaneously on a GroupedData object using PySpark functions.
Used In: Multi-metric Dashboards, Summary Reports
Syntax signature:
df.groupBy(...).agg(*exprs)Code snippet:
python
from pyspark.sql.functions import countDistinct, avg, max, min, sum
df.groupBy('department').agg(
avg('salary').alias('avg_sal'),
max('salary').alias('max_sal'),
countDistinct('employee_id').alias('emp_count')
)Expected Output:
| department | avg_sal | max_sal | emp_count |
|---|---|---|---|
| Engineering | 95000.0 | 120000 | 42 |
Remember: Use `.alias()` inside `agg()` to give clear descriptive names to calculated metric columns.
pivot
Return: DataFramePivots a column of the DataFrame and performs aggregations, turning distinct row values into columns.
Used In: Cross-tabulation Reports, Matrix Representations
Syntax signature:
df.groupBy(group_col).pivot(pivot_col, [values]).agg(...)Code snippet:
python
# Pivot sales by year
df.groupBy('product').pivot('year', [2022, 2023]).sum('revenue')Expected Output:
| product | 2022 | 2023 |
|---|---|---|
| Laptop | 5000 | 7500 |
Common Mistakes:Omitting explicit values list in `pivot()`, triggering expensive extra dataset scans.
Remember: PERFORMANCE TIP: Always supply explicit list of pivot values (`pivot('year', [2022, 2023])`). Without the list, Spark scans the dataset extra times to detect distinct values.
rollup & cube
Return: DataFrameGenerates multi-tier hierarchical subtotals and grand totals across grouping dimensions.
Used In: Executive Dashboards, OLAP Data Cubes
Syntax signature:
df.rollup(*cols).agg(...) | df.cube(*cols).agg(...)Code snippet:
python
df.rollup('country', 'city').sum('sales')Expected Output:
Subtotals for (Country, City), (Country, ALL), and Grand Total (ALL, ALL)Remember: `rollup` creates hierarchical subtotals from left to right. `cube` computes ALL possible combinations of $2^N$ grouping subsets.