Logical order of `i` and `by`
- Dominant language
- C++
- Stars
- 1.9k
- Forks
- 164
- Avg merge
- 7h 31m
- Merged PRs (30d)
- 1
Description
The generic syntax of a group-by call to a Frame is:
```
DT[i, j, by()]
```
where `i` is a "row selector", `j` is a "column selector" (also known as "what to do" argument), and finally `by()` specifies how the grouping is to be done.
This generic description, however, leaves ambiguity in how the operations of grouping and selecting are ordered relative to each other. Possible interpretations are:
- The `i` row filter is applied first, and then the frame is grouped and the `j` result is computed. Thus, the whole expression conceptually is `DT[i, :][:, j, by]`.
- The frame is grouped and the `j` columns are computed, and then `i` filter is applied. Thus, the expression is conceptually equivalent to `DT[:, j, by][i, :]`.
This issue is an attempt to document my thought process about this choice, and also an attempt to solicit feedback from other developers/users.
## Performance
In the first interpretation, applying `i` to a frame is very efficient: `DT[i, :]` produces a *view* onto `DT` with `i` as the "row index". The data is not copied unnecessarily. This means that with this interpretation, the performance of `DT[i, j, by]` is exactly the same as the performance of the explicit `DT[i, :][:, j, by]`.
With the second interpretation, computing `DT[:, j, by]` first and then applying `[i, :]` is less efficient then what could potentially be achieved by doing `DT[i, j, by]`. This is because `DT[:, j, by]` has to compute the entire grouped result first, only to discard parts of it later.
This means that if the first interpretation is adopted, there is no efficient way to perform the computation if you want to treat `i` as the post-filter. At the same time, if the second interpretation is used, then the user who wants to apply `i` as a pre-filter can just write `DT[i, :][:, j, by]` and this will compute efficiently.
## Usability
The syntax should generally be intuitive. If a certain expression *looks* like it should be doing one thing but is instead doing another, then it's a bug. Because a user will not read the documentation. They'll simply presume it's a bug, and leave with the impression that `datatable` is buggy. With this understanding in mind, let's look at a few possible expressions:
```
DT[f.score == max(f.score), :, by(f.id)]
```
With post-by interpretation, this selects entries that achieved maximum `score` for every `id`. With pre-by interpretation, this selects a single globally maximum score, and then the `id` grouping is essentially useless.
When a single row number is used:
```
DT[0, :, by(f.foo)]
```
This selects the first row in each group with post-by interpretation, or the first row in `DT` with the pre-by interpretation (the `by` condition is now useless).
Similarly, to select the first and the last row within each group, with the post-by interpretation becomes as simple as:
```
DT[[0, -1], :, by(f.id)]
```
## Conclusion
Both from the performance and usability standpoint the "post-by" interpretation wins: `i` is better to treat as applied after the groupby, not before. There are many compelling examples where filtering within groups is needed, or the groups themselves must be discarded based on some criteria.
At the same time, I could not find a single compelling reason or use-case in favor of the "pre-by" interpretation. If such use case does arise, it can always be achieved with `DT[i, :][:, j, by]` with no loss of performance or readability.
## Implementation
- [x] Implement i+by when `i` is a single-row selector (#1584)
- [x] Implement i+by when `i` is a slice/range (#1585)
- [ ] Implement i+by when `i` is an expression (#1586)
- [x] Throw an error for i+by when `i` is a Frame/numpy array
- [ ] Implement i+by when `i` is a list (#1587)
Contributor guide
Assessment
This issue has not been assessed yet.