# Table transformation in JuliaDB or Queryverse or DataFramesMeta?

**URL:** <https://discourse.julialang.org/t/table-transformation-in-juliadb-or-queryverse-or-dataframesmeta/11622>\
**Category:** Data\
**Tags:** query, dataframes, juliadb, queryverse\
**Created:** [June 12, 2018, 8:05pm UTC](https://discourse.julialang.org/t/table-transformation-in-juliadb-or-queryverse-or-dataframesmeta/11622 "2018-06-12T20:05:41Z")\
**Posts on this page:** 6\
**Page:** 1

<div class="post-metadata">

**Author:** ![dmbates](https://sea2.discourse-cdn.com/julialang/user_avatar/discourse.julialang.org/dmbates/32/44_2.png) [@dmbates](https://discourse.julialang.org/u/dmbates)\
**Post date:** [June 12, 2018, 8:05pm UTC](https://discourse.julialang.org/t/table-transformation-in-juliadb-or-queryverse-or-dataframesmeta/11622/1 "2018-06-12T20:05:42Z")

</div>

I have been experimenting with different `NLopt` optimizers on particular examples of models from `MixedModels`. The current set of results is available at

[https://github.com/dmbates/MixedModelExamples/blob/master/results/results.csv](https://github.com/dmbates/MixedModelExamples/blob/master/results/results.csv)

For each `id` I want to determine the minimum objective attained over all the optimizers and subtract that from the `obj` to determine the `:excess`. I have the feeling that I should be able to do that one pass to obtain the minimum and one pass to do the join and the subtraction. But I always seem to end up with 3 passes. For example,

```julia
julia> using JuliaDB

julia> res = loadtable("/home/bates/git/MixedModelExamples/results/results.csv",indexcols=[1,2]);

julia> resaug = join(res, groupby(minimum, res, :id, select=:obj), how=:left, lkey = :id);

julia> resaug = pushcol(resaug, :excess, map(r -> r.obj - r.minimum, resaug));

julia> filter(r -> r.excess > 0.02, resaug)
Table with 15 rows, 8 columns:
id opt code nfeval obj elapsed minimum excess
──────────────────────────────────────────────────────────────────────────────────────────────
"A0" "LN_NELDERMEAD" "FTOL_REACHED" 28025 1031.93 12.2112 1030.96 0.970225
"A0" "LN_PRAXIS" "FTOL_REACHED" 549 1049.15 0.243573 1030.96 18.1959
"A3" "LN_NELDERMEAD" "MAXEVAL_REACHED" 100000 28675.1 137.02 28586.3 88.7686
"A3" "LN_PRAXIS" "FTOL_REACHED" 2883 28617.8 3.81868 28586.3 31.4375
"A3" "LN_SBPLX" "FTOL_REACHED" 16869 28591.3 22.9396 28586.3 4.96359
"A4" "LN_NELDERMEAD" "FTOL_REACHED" 11367 28674.3 15.3279 28670.9 3.41974
"A4" "LN_PRAXIS" "FTOL_REACHED" 503 28695.1 0.689952 28670.9 24.206
"AP" "LN_NELDERMEAD" "FTOL_REACHED" 3439 8.85058e5 88.9352 8.84958e5 100.191
"Ab" "LN_COBYLA" "FTOL_REACHED" 5 327.498 0.000166279 327.327 0.17135
"Ae" "LN_NELDERMEAD" "FTOL_REACHED" 68 14773.3 0.00268521 14773.2 0.0401451
"An" "LN_COBYLA" "FTOL_REACHED" 1279 7927.57 0.327538 7926.68 0.895492
"An" "LN_NELDERMEAD" "FTOL_REACHED" 44466 7927.5 10.2801 7926.68 0.822917
"An" "LN_PRAXIS" "FTOL_REACHED" 1984 7926.84 0.465446 7926.68 0.168768
"An" "LN_SBPLX" "FTOL_REACHED" 2384 7927.5 0.544271 7926.68 0.821954
"Ar" "LN_NELDERMEAD" "FTOL_REACHED" 44 -257.409 0.00131563 -260.628 3.21928

```

Is there a better way to do this operation in `JuliaDB` or in `Queryverse` or using `DataFramesMeta`?

---

<div class="post-metadata">

**Author:** ![bkamins](https://sea2.discourse-cdn.com/julialang/user_avatar/discourse.julialang.org/bkamins/32/208538_2.png) [@bkamins](https://discourse.julialang.org/u/bkamins)\
**Post date:** [June 12, 2018, 8:50pm UTC](https://discourse.julialang.org/t/table-transformation-in-juliadb-or-queryverse-or-dataframesmeta/11622/2 "2018-06-12T20:50:05Z")

</div>

If I understand what you want correctly this how you can do it with DataFramesMeta:

`@transform(groupby(df, :id), excess = :obj - minimum(:obj))`

And to produce the table you present you can use:

```julia
@linq df |>
    groupby(:id) |>
    transform(excess = :obj - minimum(:obj)) |>
    where(:excess .> 0.02)

```

EDIT: this assumes that you have loaded your data into `df` which is a `DataFrame`.

---

<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:** [June 12, 2018, 8:57pm UTC](https://discourse.julialang.org/t/table-transformation-in-juliadb-or-queryverse-or-dataframesmeta/11622/3 "2018-06-12T20:57:49Z")

</div>

I think this does what you want:

```julia
using Queryverse

load("results.csv") |> # Step 1
@groupby(_.id) |> # Step 2
@map({best_obj=minimum(_..obj), rows=_}) |> # Step 3
@mapmany(_.rows, { __.id,__.opt, __.code,__.nfeval, __.obj,__.elapsed, excess=__.obj - _.best_obj}) |> # Step 4
DataFrame # Step 5

```

Here are some explanations for each step:

1. Stuff gets loaded via [CSVFiles.jl](https://github.com/davidanthoff/CSVFiles.jl). Note that I’m not materializing this into something like a `DataFrame`, instead the query will run directly off the data from the file.
2. Now group things by the content of the `id` column. What this does is pass on a sequence of groups. Each group is like an array (all the rows that belong to that group), plus the key that belongs to the group (i.e. the value by which things were grouped).
3. We are now creating a new row/named tuple with the `{}` syntax. The `_` refers to the current element, i.e. it will always refer to a group. First we want to extract the minimum value for the `obj` column for each group. The syntax `_..obj` will extract the `obj` column from the current group (remember, a group is a set of rows, so they have columns). We then pass all the rows of the current group into the `rows` column of the named tuple we create. So this step in the pipeline will pass on a table, where one column will actually have little tables in each cell.
4. This row “flattens” the nested list. The input here is still one element per group. The first argument to `@mapmany` tells Query.jl how to go from one of the input elements, to n-output elements. In our case, we return all the rows that we had previously stored. So now we are back to a representation where we have one element for each element that we originally started with, i.e. we have undone the grouping. The second argument to `@mapmany` creates a named tuple/row. We can reference the input element by `_`, and the current element that we get by iterating the result of the first argument by `__`.
5. Materializes the whole thing into a `DataFrame`. No need to do that, actually, you could also materialize it into an [IndexedTables.jl](https://github.com/JuliaComputing/IndexedTables.jl), or save it to disc right away with say `save("foo.csv")` or `save("foo.feather")`.

---

<div class="post-metadata">

**Author:** ![dmbates](https://sea2.discourse-cdn.com/julialang/user_avatar/discourse.julialang.org/dmbates/32/44_2.png) [@dmbates](https://discourse.julialang.org/u/dmbates)\
**Post date:** [June 12, 2018, 9:17pm UTC](https://discourse.julialang.org/t/table-transformation-in-juliadb-or-queryverse-or-dataframesmeta/11622/4 "2018-06-12T21:17:58Z")

</div>

Thank you both for the explanations.

---

<div class="post-metadata">

**Author:** ![mkborregaard](https://sea2.discourse-cdn.com/julialang/user_avatar/discourse.julialang.org/mkborregaard/32/556_2.png) [@mkborregaard](https://discourse.julialang.org/u/mkborregaard)\
**Post date:** [June 13, 2018, 9:01am UTC](https://discourse.julialang.org/t/table-transformation-in-juliadb-or-queryverse-or-dataframesmeta/11622/5 "2018-06-13T09:01:16Z")

</div>

Wait, does all that code just do the same as @bkamins DataFramesMeta code just above?

---

<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:** [June 13, 2018, 1:47pm UTC](https://discourse.julialang.org/t/table-transformation-in-juliadb-or-queryverse-or-dataframesmeta/11622/6 "2018-06-13T13:47:34Z")

</div>

If you are using JuliaDB tables, the JuliaDBMeta way of doing this would be:

```julia
@transform_vec res :id {excess = :obj .- minimum(:obj)}

```

To break it down, `@transform_vec` adds or modifies columns (and works vectorially, i.e. it is a [column-wise macro](https://piever.github.io/JuliaDBMeta.jl/latest/column_macros.html)). The second optional argument is for grouping (meaning it will group by `:id` and apply the operation separately to each group). The result is still grouped, to have it as just one table you can do either:

```julia
@transform_vec res :id flatten=true {excess = :obj .- minimum(:obj)}

```

or call `flatten` on the result. You can use `@apply` to do this operation and filtering in a pipeline (and move the grouping in the `@apply` call, so that you can filter in each group before flattening):

```julia
@apply res :id flatten=true begin
   @transform_vec {excess = :obj .- minimum(:obj)}
   @where :excess > 0.2
end

```