h2oai / h2oai/datatable

f[] under groupby

Open
#2,470 5 comments 0 reactions 0 assignees View on GitHub
design-doc
Dominant language
C++
Stars
1.9k
Forks
164
Avg merge
7h 31m
Merged PRs (30d)
1

Description

In #2460 we want to change the meaning of the `f[:]` symbol within the groupby so that it means "all columns excluding those used for grouping". There are several rationales for this change:

- `DT[:, :, by()]` already have such meaning, so this change will make the API more self-consistent;
- during groupby the grouping columns have fundamentally different role, and therefore mixing them up with the "regular" columns is somewhat inappropriate;
- with the old meaning of `f[:]` the key columns were appearing twice in the resulting frame, since the keys are automatically appended to the result;
- queries where you need to aggregate all columns (except the group columns) are very common, and we need a convenient API for them. The obvious choice seems to be `DT[:, mean(f[:]), by(...)]` (in R this would be `DT[, lapply(.SD, mean), by=...]`).

In practice, consider a dataset with columns `[A, B, C, D]`. Then grouping by 'C' should result in
```
>>> DT[:, f[:], by(f.C)]
| C A B D
-- + -- -- -- --
```
However, `:` is just one case of a slice: a trivial slice. We should expect consistency between this trivial slice and other slices:
```
>>> DT[:, f[::-1], by(f.C)] # select all columns in reverse order
| C D B A
-- + -- -- -- --

>>> DT[:, f[-2:], by(f.C)] # select only the last 2 columns
| C B D
-- + -- -- --

>>> DT[:, f[2:3], by(f.C)] # select the 3rd column (note that it's D not C)
| C D
-- + -- --

>>> DT[:, f[2], by(f.C)] # again select the 3rd column
| C D
-- + -- --

>>> DT[:, 2, by(f.C)] # also select the 3rd column
| C D
-- + -- --
```

Thus, `DT[:, 2]` would select column `C`, whereas in the presence of a groupby (by column "C") it would already refer to column `D`. This could potentially be a source of confusion, especially if the user writes `DT[:, f[2], by(f[2])]` and `f[2]` ends up meaning different things in these two places. But I guess that's the price you pay for referring to columns by their numbers instead of names...

Speaking of names, it is absolutely necessary to be able to refer to groupby columns by their names within `j`, so that they could be used in expressions. For example:
```
>>> DT[:, f.A + f.C, by("C")] # f.C is usable even though f[:] does not contain column "C"
```
I'm not sure how to reconcile these divergent goals: consistency and usability...

One way would be to say that even though there is no column "C" in `f[:]`, the lookup `f["C"]` should still be able to resolve that column "magically".

Another way would be to say that `f.C` is no longer valid, and you should use `by.C` instead:
```
>>> DT[:, f.A + by.C, by("C")]
```
This has the advantage that it allows us to also implement queries such as `by[:]`(all groupby columns), `by[:2]` (first 2 groupby columns), `by[-1]` (the last groupby column), etc. The disadvantage is that the user would have to learn one more syntax in addition to `f` and remember to use it. Though I guess it would also better reinforce the notion that `f[:]` is kinda different under a groupby, and the user should better be aware of that difference...

-----
This is an open-ended discussion. Thoughts / suggestions / comments are welcome.

Contributor guide

Open the contributing guide

Assessment

This issue has not been assessed yet.

Get new issues in your inbox

A short digest of beginner-friendly GitHub issues.