Feedback from package authors

[bramtayl]

So this is still experimental, but I'm dying for feedback from package authors. I hope this isn't rude. @davidanthoff @nalimilan I updated the readme to show how one macro can replace all of DataFramesMeta and Query. The syntax is slightly less terse but much more flexible.

[bramtayl]

Bunch of updates, new examples in the README. Added support for SplitApplyCombine @andyferris (though not tested yet)

[bramtayl]

Again sorry for the pings I just get overly excited about programming lol

[bramtayl]

Ok so I'm basically happy with whats here. I've got a chaining mechanism, a lazy call mechanism, and several model query verbs. I've got extremely unoptimized methods for these verbs on NamedTables. My hope is that people will import these methods and optimize them for various data structures, so that essentially DataFramesMeta could just hold the DataFrames methods and QueryOperators could just hold the Enumerable methods.

[andyferris]

Hi @bramtayl,

It's reassuring, in many ways, to see so many of us interested in the same set of problems. :) And it's great to be excited!

What I didn't really get (until maybe a hint in your most recent comment) was what the overall design philosophy was. Some kind of introduction in the README or documentation would probably be quite useful. Similarly, an example that shows how it all comes together. I'm taking this package as a convenient interface to manipulate NamedTuples and iterables of NamedTuples? I guess I'm asking what makes this approach unique compared to others? (Or, put bluntly, what itch are you trying to scratch?)

Anyway, I'm happy to discuss - let us know how we can be useful.

Added support for SplitApplyCombine

Can you elaborate on this?

I've got extremely unoptimized methods for these verbs on NamedTables.

I'm not sure what NamedTables refers to, exactly?

(I do love that you are thinking of these functions as "verbs". I've come to think of SplitApplyCombine as providing verbs, TypedTables as providing nouns and AcceleratedArrays as providing "adverbs". I'm not sure if that is silly?)

[bramtayl]

NamedTables was a typo for NamedTuples. So basically this is just a straightforward port of dplyr from R. The methods only work on NamedTuples right now, but I think that they could work on just about anything vaguely tabular. QueryOperators has a set of unexported verbs with methods on Enumerables, and DataFramesMeta has a set of unexported verbs with methods on DataFrames. If instead both packages just extended the methods in this package, then we could have one uniform tabular query interface?

Previous iterations of this package had actually sat down and created these bridges. So for example, I had

group(data::Enumerable, n::Nameless) =
    QueryOperators.groupby(data::Enumerable, n.f, n.expression)
group(data::AbstractDataFrame, n) =
    DataFrames.groupby(data, n)
group(data, n) =
    SplitApplyCombine.group(n, data)

These are gone from the package at the moment by Occam's razor. But, for example, with a bit of elbow grease, I think most if not all of QueryOperators could be refactored as methods of LightQuery verbs. SplitApplyCombine is a bit trickier cause it's not explicitly indicated for tabular data...

I haven't taken a look at JuliaDB yet cause last time I checked it wasn't working on 1.0, but I'm pretty sure it could be integrated in a similar way.

[andyferris]

OK - thanks for the explanation.

If instead both packages just extended the methods in this package, then we could have one uniform tabular query interface?

It would be good to share the same fundamental operations, yes. I strongly feel that these should eventually become operations in Base (for things that are roughly equivalent in functionality to say map, reduce or filter) or a standard library (we have LinearAlgebra, imagine a RelationalAlgebra stdlib).

SplitApplyCombine is a bit trickier cause it's not explicitly indicated for tabular data...

Yes, indeed, my personal approach so far has been to see what generic operations are good for doing relational algebra. For example, if you take the textbook definition of a relation as a collection of (named) tuples, port that to Julia and say that this is any object that supports iterate and gives NamedTuple{names} (for the same names in each row), then we already have a whole bunch of functionality in Base. We can map rows to give new rows, we can filter rows, we can even make comprehensions and generators which even do an inner join say on column b of table1 and table2 along the lines of [(a = row1.a, b = row1.b, c = row2.c) for row1 in table1, row2 in table2 if row1.b == row2.b]. In other words, in Julia 1.0 I think we have a good chunk of this interface already.

If we can fill this out with all the groups and innerjoins and so-on that we need, I was hoping we wouldn't need a specific interface for tabular data, just simply rely on the standard Julia interface for containers. Really, there's a bit of trickery to get things like optimizations for column-based storage working, have lazy, chainable operations, and so-on, but IMO that's actually not too bad.

[bramtayl]

I'm with you that row-wise operations are basically reducible to Base iterators.

I'm still pretty sure you need specific interfaces for tabular data (or at least, they make things easier). Certainly select, remove, transform, and based_on are all specific to tabular data. And if you want to just be able to just say join_by(data1, data2, :b) instead of join_by(data1, data2, (i, j) -> i.b == j.b), you need assume a tabular structure.

So bottom line: relational algebra standard library: yes! This is kinda what I tried to set out to do here but I think it would be better to be a stdlib (or at least something more official)

[andyferris]

Yes, indeed - specific interfaces for tabular data will make things much, much more usable.

I guess I am thinking these interfaces should act as syntax sugar. Functions which take symbol names to identify columns would simply create closures or whatever and call higher-order functions like map, filter, reduce, group and innerjoin (that expect functions). Those higher-order functions should be smart enough to introspect the inputs and take advantage of columnar storage and so-on. For example, I curried getproperty(::Symbol) in TypedTables for exactly this purpose (a more general, mulit-column selector and transformer is also necessary but I haven't got to that yet!). Of course, I don't particularly want users to be typing getproperty(:b) everywhere (well, one day we might use the syntax _.b which isn't too bad). But IMO this is where we can build friendlier interfaces and macros on top of all that.

[bramtayl]

Ok, in that case, how about this for a proposal:

struct Keys{Names} end
Keys(names::Symbol...) = Keys{names}()
Keys(:a, :b, :c)

You could pass in Keys instead of an anonymous function into innerjoin, group, orderby, etc. and use dispatch to get the desired tabular data specific method?

[bramtayl]

So then here's what happens if you start trying to delete functions from LightQuery:

Don't really need any more:

as_rows/as_columns/pretty: probably better suited to a specifically built tabular data interface like TypedTables

where: filter + columnwise optimization
orderby: sort + Keys + columnwise optimization
group_by: SplitApplyCombine.group + Keys + columnwise optimization
chunk_by: SplitApplyCombine.group + Keys
inner_join: SplitApplyCombine.inner_join + Keys

I think still useful:

select/remove: really functions which should exist in Base, but can stay here for now? rename would be great here too.
based_on/transform: really belong here or in a relational data stdlib
@>: one of several way of chaining, still my favorite at the moment
@_: still pretty indispensible, I think. We're going to need a meta copy of the expression for SQL translation if that ever comes about, and the PR in Base on _ anonymous functions seems like it got stuck in the mud

[andyferris]

Sure, something exactly like that.

I have f = GetProperty{:a}() as the function that does f(x) = x.a. So I was thinking something like f2 = GetProperties{(:a, :b, :c)}() being the function that makes a named tuple f2(x) = (a = x.a, b = x.b, c = x.c).

Ideally we'd make a more powerful Select thing that can not only project columns but also transform and combine information from different columns, all in the one step.

[bramtayl]

True, but different functions would want to do different things with different columns. Like groupby would just want to select the columns. orderby would want to select the columns and then run isless. inner_join would want to select the columns and then test for inequality. So I think a dedicated Keys struct makes sense?

[andyferris]

Yes. (Note that I think TypedTables and SplitApplyCombine currently have the mechanics for columnar optimization for all the items on your list, so long as you are grouping or joining by just one column).

[andyferris]

I think the different steps could be composed? For example SplitApplyCombine.innerjoin has 4 different functions for doing the various things. This was unfortunately more complex that I first wanted but you need this level of flexibility.

    innerjoin(lkey, rkey, f, comparison, left, right)

Performs a relational-style join operation between iterables `left` and `right`, returning a collection of elements `f(l, r)` for which `comparison(lkey(l), rkey(r))` is `true` where `l ∈ left`, `r ∈ right`

So you do something like innerjoin(GetProperties(:a, :b), GetProperties(:a, :b), merge, isequal, table1, table2) to do an inner join where columns a and b must match.

[bramtayl]

Ok, well I've got a (probably not constant inferable) version of rename now:

export rename
"""
    rename(data; renames)

```jldoctest
julia> using LightQuery

julia> rename((a = 1, b = 2), :a => :c)
(b = 2, c = 1)
\```
"""
function rename(data::NamedTuple, renames...)
    olds = map(pair -> pair.first, renames)
    merge(
        remove(data, olds...),
        NamedTuple{map(pair -> pair.second, renames)}(select(data, olds...)...)
    )
end

So what would you think about registering a request in Base for dedicated select, delete, and rename methods on NamedTuples (with someone over there working some constant propagation magic)

Then I can just keep transform, based_on, and the two macros here as a tiny package?