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 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.5173692586537291 | 0.5173692586537291 |
+----------------------------+--------------------------+------------------------------------------------+
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 | 194 | 0.8929899996979089 | 0.008786198889721097 |
| bar | 99 | 0.9082469250683034 | 0.2866188772159094 |
+-----+----------------+--------------------+----------------------+
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) |
+-----+-------+----------------+--------------------+----------------------+
| bar | three | 22 | 0.2866188772159094 | 0.2866188772159094 |
| foo | three | 58 | 0.8929899996979089 | 0.8929899996979089 |
| foo | one | 25 | 0.3957626236684647 | 0.23554757356318523 |
| bar | one | 27 | 0.5646778167349309 | 0.5646778167349309 |
| foo | two | 111 | 0.5849968559932718 | 0.008786198889721097 |
| bar | two | 50 | 0.9082469250683034 | 0.9082469250683034 |
+-----+-------+----------------+--------------------+----------------------+