AggregationΒΆ
An aggregate or aggregation is a function where the values of multiple rows are processed together to form a single summary value.
For performing an aggregation, DataFusion provides the DataFrame.aggregate()
In [1]: from datafusion import SessionContext
In [2]: from datafusion import column, lit
In [3]: from datafusion import functions as f
In [4]: import random
In [5]: ctx = SessionContext()
In [6]: df = ctx.from_pydict(
...: {
...: "a": ["foo", "bar", "foo", "bar", "foo", "bar", "foo", "foo"],
...: "b": ["one", "one", "two", "three", "two", "two", "one", "three"],
...: "c": [random.randint(0, 100) for _ in range(8)],
...: "d": [random.random() for _ in range(8)],
...: },
...: name="foo_bar"
...: )
...:
In [7]: col_a = column("a")
In [8]: col_b = column("b")
In [9]: col_c = column("c")
In [10]: col_d = column("d")
In [11]: df.aggregate([], [f.approx_distinct(col_c), f.approx_median(col_d), f.approx_percentile_cont(col_d, lit(0.5))])
Out[11]:
DataFrame()
+----------------------------+--------------------------+------------------------------------------------+
| APPROX_DISTINCT(foo_bar.c) | APPROX_MEDIAN(foo_bar.d) | APPROX_PERCENTILE_CONT(foo_bar.d,Float64(0.5)) |
+----------------------------+--------------------------+------------------------------------------------+
| 8 | 0.590372304131194 | 0.590372304131194 |
+----------------------------+--------------------------+------------------------------------------------+
When the group_by list is empty the aggregation is done over the whole DataFrame. For grouping
the group_by list must contain at least one column
In [12]: df.aggregate([col_a], [f.sum(col_c), f.max(col_d), f.min(col_d)])
Out[12]:
DataFrame()
+-----+----------------+--------------------+---------------------+
| a | SUM(foo_bar.c) | MAX(foo_bar.d) | MIN(foo_bar.d) |
+-----+----------------+--------------------+---------------------+
| foo | 252 | 0.9706736713756111 | 0.18648672407828037 |
| bar | 168 | 0.8774759324625627 | 0.10675689061337379 |
+-----+----------------+--------------------+---------------------+
More than one column can be used for grouping
In [13]: df.aggregate([col_a, col_b], [f.sum(col_c), f.max(col_d), f.min(col_d)])
Out[13]:
DataFrame()
+-----+-------+----------------+---------------------+---------------------+
| a | b | SUM(foo_bar.c) | MAX(foo_bar.d) | MIN(foo_bar.d) |
+-----+-------+----------------+---------------------+---------------------+
| foo | one | 78 | 0.9321593793034524 | 0.6443762566250755 |
| bar | three | 99 | 0.10675689061337379 | 0.10675689061337379 |
| bar | two | 2 | 0.590372304131194 | 0.590372304131194 |
| foo | two | 81 | 0.9706736713756111 | 0.3018896128345987 |
| foo | three | 93 | 0.18648672407828037 | 0.18648672407828037 |
| bar | one | 67 | 0.8774759324625627 | 0.8774759324625627 |
+-----+-------+----------------+---------------------+---------------------+