# Common API for tabular data backends

**URL:** <https://discourse.julialang.org/t/common-api-for-tabular-data-backends/21546>\
**Category:** Data\
**Created:** [March 6, 2019, 7:49pm UTC](https://discourse.julialang.org/t/common-api-for-tabular-data-backends/21546 "2019-03-06T19:49:28Z")\
**Posts on this page:** 20\
**Page:** 2

<div class="post-metadata">

**Author:** ![piever](https://sea2.discourse-cdn.com/julialang/user_avatar/discourse.julialang.org/piever/32/1815_2.png) [@piever](https://discourse.julialang.org/u/piever)\
**Post date:** [March 7, 2019, 7:06pm UTC](https://discourse.julialang.org/t/common-api-for-tabular-data-backends/21546/22 "2019-03-07T19:06:44Z")

</div>

> [@nalimilan](#):
>
> Also we have found in DataFrames that two kind of `groupapply` / `groupmap` operations can make sense, depending on whether you return a `GroupedDataFrame` (which can make sense when there are several rows per group) or a plain `DataFrame` .

This is handled in JuliaDB by the keyword `flatten = false`: meaning if the grouping function returns a table (and not a namedtuple) the result will be a table of tables, unless the user sets `flatten = true` (or calls `flatten` on the resulting table). I understand this is changing would be quite breaking for DataFrames as traditionally `by` in DataFrames encouraged returning a `DataFrame` and flattened by default, but with `NamedTuples` in base I think encouraging to return a `NamedTuple` and requiring an explicit `flatten` if one returns a `DataFrame` may be better.

---

<div class="post-metadata">

**Author:** ![nalimilan](https://sea2.discourse-cdn.com/julialang/user_avatar/discourse.julialang.org/nalimilan/32/147_2.png) [@nalimilan](https://discourse.julialang.org/u/nalimilan)\
**Post date:** [March 7, 2019, 8:53pm UTC](https://discourse.julialang.org/t/common-api-for-tabular-data-backends/21546/23 "2019-03-07T20:53:35Z")

</div>

We could use different operations for this. `map` on a `GroupedDataFrame` currently returns a `GroupedDataFrame`, and we have `combine` to get a `DataFrame`. This sounds similar to `flatten`. `mutate` should probably combine/flatten, so maybe it’s enough to have it in the common API. DataFrames can always keep `by` for convenience.

---

<div class="post-metadata">

**Author:** ![pdeffebach](https://sea2.discourse-cdn.com/julialang/user_avatar/discourse.julialang.org/pdeffebach/32/10320_2.png) [@pdeffebach](https://discourse.julialang.org/u/pdeffebach)\
**Post date:** [March 7, 2019, 10:43pm UTC](https://discourse.julialang.org/t/common-api-for-tabular-data-backends/21546/24 "2019-03-07T22:43:55Z")

</div>

Another difference is that JuliaDB has the concept of an ID column, which DataFrames doesn’t have.

We should probably open up an issue for each of these in `TableOperations.jl`. One for `getindex`, one for `setindex` (or mutate), one for grouping etc.

---

<div class="post-metadata">

**Author:** ![Yifan\_Liu](https://avatars.discourse-cdn.com/v4/letter/y/4da419/32.png) [@Yifan\_Liu](https://discourse.julialang.org/u/Yifan_Liu)\
**Post date:** [March 7, 2019, 10:49pm UTC](https://discourse.julialang.org/t/common-api-for-tabular-data-backends/21546/25 "2019-03-07T22:49:14Z")

</div>

In terms of simplicity, it is very difficult to beat tidyverse. I really appreciate the efforts of unifying these functions.

On the other hand, I wish to see something like R’s data.table.

---

<div class="post-metadata">

**Author:** ![pdeffebach](https://sea2.discourse-cdn.com/julialang/user_avatar/discourse.julialang.org/pdeffebach/32/10320_2.png) [@pdeffebach](https://discourse.julialang.org/u/pdeffebach)\
**Post date:** [March 7, 2019, 11:06pm UTC](https://discourse.julialang.org/t/common-api-for-tabular-data-backends/21546/26 "2019-03-07T23:06:35Z")

</div>

To be honest I think Stata beats `dplyr` in most instances for data manipulation. Whole industries run on stata and people don’t want to switch. One reason Stata has succeeded is it’s syntax for grouped operations, appending, collapsing, and more. I’ll see if I can write something longer clarifying my views on this.

---

<div class="post-metadata">

**Author:** ![nickeubank](https://sea2.discourse-cdn.com/julialang/user_avatar/discourse.julialang.org/nickeubank/32/3878_2.png) [@nickeubank](https://discourse.julialang.org/u/nickeubank)\
**Post date:** [March 7, 2019, 11:23pm UTC](https://discourse.julialang.org/t/common-api-for-tabular-data-backends/21546/27 "2019-03-07T23:23:12Z")

</div>

I agree, but I think it’s only able to because the language is organized around one purpose and a “one open data set at a time” model so there’s no syntax wasted specifying the dataset from which the variable is being called, etc. Not sure you can get that in a world with multiple concurrent datasets or in a general purpose language.

---

<div class="post-metadata">

**Author:** ![Yifan\_Liu](https://avatars.discourse-cdn.com/v4/letter/y/4da419/32.png) [@Yifan\_Liu](https://discourse.julialang.org/u/Yifan_Liu)\
**Post date:** [March 7, 2019, 11:24pm UTC](https://discourse.julialang.org/t/common-api-for-tabular-data-backends/21546/28 "2019-03-07T23:24:05Z")

</div>

Could you kindly provide the list of industries that use stata as their main data wrangling tool? To be honest, this is the first time for me to hear Stata be in the discussion of data science.

I used Stata and Gretl like 15 years ago, I am not sure how Stata has changed, but back then, it was far from a pleasant experience to use

---

<div class="post-metadata">

**Author:** ![Nosferican](https://sea2.discourse-cdn.com/julialang/user_avatar/discourse.julialang.org/nosferican/32/9275_2.png) [@Nosferican](https://discourse.julialang.org/u/Nosferican)\
**Post date:** [March 8, 2019, 10:15am UTC](https://discourse.julialang.org/t/common-api-for-tabular-data-backends/21546/29 "2019-03-08T10:15:41Z")

</div>

In terms of `data.table`, other than the inplace operations, the indices for fast sorting, joining, grouping may be useful.

Stata is almost exclusive to economics (govt and academia). Other than people that have been using it since Stata 6 or something like that, I doubt many consider it a great tool for data wrangling / manipulation.

---

<div class="post-metadata">

**Author:** ![Tamas\_Papp](https://sea2.discourse-cdn.com/julialang/user_avatar/discourse.julialang.org/tamas_papp/32/25949_2.png) [@Tamas\_Papp](https://discourse.julialang.org/u/Tamas_Papp)\
**Post date:** [March 8, 2019, 12:08pm UTC](https://discourse.julialang.org/t/common-api-for-tabular-data-backends/21546/30 "2019-03-08T12:08:07Z")

</div>

> [@Yifan\_Liu](#):
>
> Could you kindly provide the list of industries that use stata as their main data wrangling tool?

It is extremely prevalent in economics/econometrics. The grouping syntax meshes rather well with corresponding tools for inference (eg run these regressions on the grouped dataset), and it handles large datasets well.

The only downside to Stata is that it is difficult to do something that does not fit well into the concept of working with tables. But advanced Stata users are very ingenious in this respect — if you have the chance, watch an experienced Stata user work for a week, it is a real education. This aspect of Stata is not something Julia should replicate though IMO, and neither should we copy the tidyverse verbatim.

I think that a “Julian” solution will require quite a few iterations, experimentation, and design changes, so I would not sweat too much about minor details here as the interface is likely to be changed a few more times in the medium run.

What I would like to see is a simplification of the API, which allows building up operations from a limited vocabulary. My current playground is [FunctionalTables.jl](https://github.com/tpapp/FunctionalTables.jl), which is not production read or stable (but I use it anyway for daily work). I found that I can combine “aggregation” and “mapping by groups” by making functions return one-line tables. Eg in the example below, each `map` call does the same thing, and the `aggregator` function is just the shorthand for the first functional form.

```julia
julia> ft = FunctionalTable((a = isodd(i), b = i) for i in 1:10)
FunctionalTable of 10 rows, no ordering
    a = Bool[true, false, true, false, true, …]
    b = Int8[1, 2, 3, 4, 5, …]

julia> map(by(ft, :a)) do _, table
       (b = mean(table.b), )
       end
map(by(ft, :a)) do _, table
FunctionalTable of 2 rows, ordering ↑a
    a = Bool[false, true]
    b = Float64[6.0, 5.0]

julia> map(by(ft, :a)) do _, table
       map(mean, columns(table))
       end
FunctionalTable of 2 rows, ordering ↑a
    a = Bool[false, true]
    b = Float64[6.0, 5.0]

julia> map(aggregator(mean), by(ft, :a))
FunctionalTable of 2 rows, ordering ↑a
    a = Bool[false, true]
    b = Float64[6.0, 5.0]

```

The ignored argument `_` is the key. I think one can express _all_ n-table join operations by traversing the grouped tables, and just calling a function for rows of the same key. But this needs coding up and is WIP.

---

<div class="post-metadata">

**Author:** ![Yifan\_Liu](https://avatars.discourse-cdn.com/v4/letter/y/4da419/32.png) [@Yifan\_Liu](https://discourse.julialang.org/u/Yifan_Liu)\
**Post date:** [March 8, 2019, 5:28pm UTC](https://discourse.julialang.org/t/common-api-for-tabular-data-backends/21546/31 "2019-03-08T17:28:29Z")

</div>

I don’t get how functionaltable is better than existing packages, is there any performance gain? Is :a and table.a exchangeable? Can the operations be chained? The syntax seems more verbose.

---

<div class="post-metadata">

**Author:** ![pdeffebach](https://sea2.discourse-cdn.com/julialang/user_avatar/discourse.julialang.org/pdeffebach/32/10320_2.png) [@pdeffebach](https://discourse.julialang.org/u/pdeffebach)\
**Post date:** [March 8, 2019, 5:36pm UTC](https://discourse.julialang.org/t/common-api-for-tabular-data-backends/21546/32 "2019-03-08T17:36:31Z")

</div>

This illustrates another goal I would like to work towards with this new API, and one of the things I like about Stata as opposed to dplyr… fewer parentheses.

Having everything be a tuple or named tuple is a recipe for lisp-y parentheses confusion.

Standardizing to have `Vector`s of inputs (type stability doesn’t matter if they are just symbols referring to column names), along with `do` function syntax where possible, along with an API that works well with piping, can go a long way to reducing this.

---

<div class="post-metadata">

**Author:** ![Tamas\_Papp](https://sea2.discourse-cdn.com/julialang/user_avatar/discourse.julialang.org/tamas_papp/32/25949_2.png) [@Tamas\_Papp](https://discourse.julialang.org/u/Tamas_Papp)\
**Post date:** [March 8, 2019, 6:12pm UTC](https://discourse.julialang.org/t/common-api-for-tabular-data-backends/21546/33 "2019-03-08T18:12:18Z")

</div>

I am not yet convinced it is better (it is an experiment) — it exploits the fact that tables are immutable for compression and sorting. But I used it only as an illustration for how aggregation and split-apply-combine can be done with the same interface and a small wrapper function.

IMO the tidyverse functionality (in R) relies heavily on some special features of R, mostly [non-standard evaluation](http://adv-r.had.co.nz/Computing-on-the-language.html). Because of this, I don’t think it is easy to transplant the tidyverse surface syntax to Julia, so perhaps experimenting with new approaches more suited tio Julia is reasonable (I am not saying that this shoud be done in major packages like DataFrames though). As long as we have support interop packages like Tables.jl, it is easy to combine various approaches.

---

<div class="post-metadata">

**Author:** ![Yifan\_Liu](https://avatars.discourse-cdn.com/v4/letter/y/4da419/32.png) [@Yifan\_Liu](https://discourse.julialang.org/u/Yifan_Liu)\
**Post date:** [March 8, 2019, 6:24pm UTC](https://discourse.julialang.org/t/common-api-for-tabular-data-backends/21546/34 "2019-03-08T18:24:28Z")

</div>

I agree with you on this point. Julia data wrangling tools should look like Julia. When I work with Julia, I need to translate large amount of MATLAB code, so I code exactly as I did in MATLAB which is array oriented. When it comes to data cleaning, I use dataframesmeta, which looks very similar with r and is data frame oriented. I guess style consistency is a thing for me.

---

<div class="post-metadata">

**Author:** ![piever](https://sea2.discourse-cdn.com/julialang/user_avatar/discourse.julialang.org/piever/32/1815_2.png) [@piever](https://discourse.julialang.org/u/piever)\
**Post date:** [March 9, 2019, 12:39pm UTC](https://discourse.julialang.org/t/common-api-for-tabular-data-backends/21546/35 "2019-03-09T12:39:08Z")

</div>

> [@Tamas\_Papp](#):
>
> What I would like to see is a simplification of the API, which allows building up operations from a limited vocabulary. My current playground is [FunctionalTables.jl](https://github.com/tpapp/FunctionalTables.jl), which is not production read or stable (but I use it anyway for daily work). I found that I can combine “aggregation” and “mapping by groups” by making functions return one-line tables. Eg in the example below, each `map` call does the same thing, and the `aggregator` function is just the shorthand for the first functional form.

This is a good point, instead of `groupby`, `groupmap` and `groupreduce` as discussed above, one may consider `groupby`, `map(f, groupby(...))` and `reduce(op, groupby(...))`, even though I’m still not fully happy with the terminology as `map` is mapping across groups, i.e. apply `f` on each group, whereas `reduce` is reducing withing groups, so it’s more of a `map(t -> reduce(op, t), groupby)`.

---

<div class="post-metadata">

**Author:** ![pdeffebach](https://sea2.discourse-cdn.com/julialang/user_avatar/discourse.julialang.org/pdeffebach/32/10320_2.png) [@pdeffebach](https://discourse.julialang.org/u/pdeffebach)\
**Post date:** [March 13, 2019, 8:23pm UTC](https://discourse.julialang.org/t/common-api-for-tabular-data-backends/21546/36 "2019-03-13T20:23:35Z")

</div>

One example of a universal table interface working in the wild is with R’s new `sf` package. `sf` tables just tibbles, but with geo-data attached to each observation, and they are wonderful to work with.

I can use all `dplyr` verbs on them just as I could a `data.frame` or a `tibble` object. The verbs themselves modify the attached table and another set of `st_` methods modify the spatial data attached. I don’t have to learn anything new, I don’t have to change the way I write code, it \text{Just Works}^{\text{tm}}

I don’t know the particulars of how class inheritance works in R, but it’s easy to imagine how a universal table API could make the introduction of new structs holding custom data very easy.

Another note about my experience working with these objects. Piping is a very useful style to adhere to because it forces the user to write methods with the same type of input as output if they want to use their own functions in a line of pipes along with `mutate` and `filter`.

Having all the table operations take in and return the same type would be a great goal to have.

---

<div class="post-metadata">

**Author:** ![sdanisch](https://sea2.discourse-cdn.com/julialang/user_avatar/discourse.julialang.org/sdanisch/32/1406_2.png) [@sdanisch](https://discourse.julialang.org/u/sdanisch)\
**Post date:** [March 13, 2019, 10:35pm UTC](https://discourse.julialang.org/t/common-api-for-tabular-data-backends/21546/37 "2019-03-13T22:35:58Z")

</div>

> [@pdeffebach](#):
>
> I don’t know the particulars of how class inheritance works in R, but it’s easy to imagine how a universal table API could make the introduction of new structs holding custom data very easy.

Actually, this is already possible, and I’m working on basic geometry types for geology etc that conform the table interface:

> <https://github.com/JuliaGeometry/GeometryBasics.jl/blob/master/test/runtests.jl#L77>

Only tests, no documentation for now 😉

---

<div class="post-metadata">

**Author:** ![tk3369](https://sea2.discourse-cdn.com/julialang/user_avatar/discourse.julialang.org/tk3369/32/2824_2.png) [@tk3369](https://discourse.julialang.org/u/tk3369)\
**Post date:** [March 14, 2019, 1:21am UTC](https://discourse.julialang.org/t/common-api-for-tabular-data-backends/21546/38 "2019-03-14T01:21:17Z")

</div>

I love piping syntax but would it work like streams rather than generating tons of intermediate data? it would be awesome with some kind of “pipe-fusion” feature.

---

<div class="post-metadata">

**Author:** ![bramtayl](https://sea2.discourse-cdn.com/julialang/user_avatar/discourse.julialang.org/bramtayl/32/3614_2.png) [@bramtayl](https://discourse.julialang.org/u/bramtayl)\
**Post date:** [March 14, 2019, 3:52am UTC](https://discourse.julialang.org/t/common-api-for-tabular-data-backends/21546/39 "2019-03-14T03:52:29Z")

</div>

To some extent all of these goals are things I tried to address in [LightQuery.jl](https://bramtayl.github.io/LightQuery.jl/latest/)

---

<div class="post-metadata">

**Author:** ![bramtayl](https://sea2.discourse-cdn.com/julialang/user_avatar/discourse.julialang.org/bramtayl/32/3614_2.png) [@bramtayl](https://discourse.julialang.org/u/bramtayl)\
**Post date:** [March 14, 2019, 4:07am UTC](https://discourse.julialang.org/t/common-api-for-tabular-data-backends/21546/40 "2019-03-14T04:07:11Z")

</div>

And because the package is so young, feel free to suggest syntax/API changes and I can incorporate them.

---

<div class="post-metadata">

**Author:** ![davidanthoff](https://sea2.discourse-cdn.com/julialang/user_avatar/discourse.julialang.org/davidanthoff/32/223493_2.png) [@davidanthoff](https://discourse.julialang.org/u/davidanthoff)\
**Post date:** [March 14, 2019, 4:51am UTC](https://discourse.julialang.org/t/common-api-for-tabular-data-backends/21546/41 "2019-03-14T04:51:49Z")

</div>

@sdanisch’s example nicely highlights that the core design of [Query.jl](https://github.com/queryverse/Query.jl) is _not_ about tables, but is all about iterators of values. It just happens that it also works well for tables. I think that kind of monadic design, that I simply copied from LINQ (just really want to make clear that this was _not_ my idea), is one of these super simple but powerful ideas that make for a super composable design that extends well beyond the tabular world. There is a ton of academic research work out of Microsoft Research and a bunch of really nice Channel9 interviews on this stuff, for folks that are interested.

> [@tk3369](#):
>
> I love piping syntax but would it work like streams rather than generating tons of intermediate data? it would be awesome with some kind of “pipe-fusion” feature.

Everything is fused into streams in [Query.jl](https://github.com/queryverse/Query.jl), something like `source |> @filter(_.a==3) |> @mutate(b=3*_.x) |> DataFrame` has no intermediate temporaries.

[Previous page](https://discourse.julialang.org/t/common-api-for-tabular-data-backends/21546.md?page=1)

[Next page](https://discourse.julialang.org/t/common-api-for-tabular-data-backends/21546.md?page=3)
