# JuliaDB flatten multiple columns

**URL:** <https://discourse.julialang.org/t/juliadb-flatten-multiple-columns/24804>\
**Category:** General Usage\
**Tags:** question\
**Created:** [May 31, 2019, 3:20pm UTC](https://discourse.julialang.org/t/juliadb-flatten-multiple-columns/24804 "2019-05-31T15:20:27Z")\
**Posts on this page:** 8\
**Page:** 1

<div class="post-metadata">

**Author:** ![yakir12](https://sea2.discourse-cdn.com/julialang/user_avatar/discourse.julialang.org/yakir12/32/297_2.png) [@yakir12](https://discourse.julialang.org/u/yakir12)\
**Post date:** [May 31, 2019, 3:20pm UTC](https://discourse.julialang.org/t/juliadb-flatten-multiple-columns/24804/1 "2019-05-31T15:20:27Z")

</div>

I want to “flatten” a table with multiple cells that contain vectors (these have equal lengths per table-row):

```julia
t = table((group = [1,1,2,2], x = [1,2,3,4]))
t2 = groupby(t, :group, usekey = true) do k, r
    (x2 = r.x .+ k.group, y2 = rand(length(r)))
end

```

If I flatten it on say column `x2`, column `y2` doesn’t get “flattened”:

```julia
julia> flatten(t2, :x2)
Table with 4 rows, 3 columns:
group x2 y2
group x2 y2
─────────────────────────────────
1 2 [0.154278, 0.31287]
1 3 [0.154278, 0.31287]
2 5 [0.0977201, 0.0372307]
2 6 [0.0977201, 0.0372307]

```

What I need is to “ungroup” table `t2` by `group`…

---

<div class="post-metadata">

**Author:** ![yakir12](https://sea2.discourse-cdn.com/julialang/user_avatar/discourse.julialang.org/yakir12/32/297_2.png) [@yakir12](https://discourse.julialang.org/u/yakir12)\
**Post date:** [May 31, 2019, 5:00pm UTC](https://discourse.julialang.org/t/juliadb-flatten-multiple-columns/24804/2 "2019-05-31T17:00:49Z")

</div>

OK, this seems to work:

```julia
julia> merge(table.(rows(select(t2, Not(:group))))...)
Table with 4 rows, 2 columns:
x2 y2
─────────────
2 0.154278
3 0.31287
5 0.0977201
6 0.0372307

```

But alas, this works only if there were just two groups, cause merge works on just two tables.

---

<div class="post-metadata">

**Author:** ![yakir12](https://sea2.discourse-cdn.com/julialang/user_avatar/discourse.julialang.org/yakir12/32/297_2.png) [@yakir12](https://discourse.julialang.org/u/yakir12)\
**Post date:** [May 31, 2019, 5:15pm UTC](https://discourse.julialang.org/t/juliadb-flatten-multiple-columns/24804/3 "2019-05-31T17:15:26Z")

</div>

OK, with `foldl` this actually works for any number of groups:

```julia
julia> t = table((group = [1,1,2,2,3,3], x = [1,2,3,4,5,6]))
Table with 6 rows, 2 columns:
group x
────────
1 1
1 2
2 3
2 4
3 5
3 6

julia> t2 = groupby(t, :group, usekey = true) do k, r
           (x2 = r.x .+ k.group, y2 = rand(length(r)))
           end
Table with 3 rows, 3 columns:
group x2 y2
────────────────────────────────────
1 [2, 3] [0.0581229, 0.019784]
2 [5, 6] [0.63852, 0.855233]
3 [8, 9] [0.023636, 0.783005]

julia> foldl(merge, table.(rows(select(t2, Not(:group)))))
Table with 6 rows, 2 columns:
x2 y2
─────────────
2 0.0581229
3 0.019784
5 0.63852
6 0.855233
8 0.023636
9 0.783005

```

---

<div class="post-metadata">

**Author:** ![MaximilianJHuber](https://sea2.discourse-cdn.com/julialang/user_avatar/discourse.julialang.org/maximilianjhuber/32/2579_2.png) [@MaximilianJHuber](https://discourse.julialang.org/u/MaximilianJHuber)\
**Post date:** [May 31, 2019, 5:20pm UTC](https://discourse.julialang.org/t/juliadb-flatten-multiple-columns/24804/4 "2019-05-31T17:20:35Z")

</div>

Or let your `groupby` produce only a single column which is a `Tuple` of values:

```julia
t = table((group = [1,1,2,2], x = [1,2,3,4]))
t2 = groupby(t, :group, usekey = true, flatten = true) do k, r
    (z = [(r.x[i] + k.group, rand()) for i in 1:length(r)])
end

```

yields:

```julia
Table with 4 rows, 3 columns:
1 2 3
──────────────
1 2 0.798469
1 3 0.301879
2 5 0.883682
2 6 0.965717

```

---

<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:** [May 31, 2019, 5:29pm UTC](https://discourse.julialang.org/t/juliadb-flatten-multiple-columns/24804/5 "2019-05-31T17:29:40Z")

</div>

As proposed above you can return an object that iterates `NamedTuples`. The easiest is to use `Columns`, so this:

```julia
t = table((group = [1,1,2,2], x = [1,2,3,4]))
t2 = groupby(t, :group, usekey = true) do k, r
    (x2 = r.x .+ k.group, y2 = rand(length(r)))
end

```

Would be replaced by

```julia
t = table((group = [1,1,2,2], x = [1,2,3,4]))
t2 = groupby(t, :group, usekey = true, flatten = true) do k, r
    Columns(x2 = r.x .+ k.group, y2 = rand(length(r)))
end

```

---

<div class="post-metadata">

**Author:** ![yakir12](https://sea2.discourse-cdn.com/julialang/user_avatar/discourse.julialang.org/yakir12/32/297_2.png) [@yakir12](https://discourse.julialang.org/u/yakir12)\
**Post date:** [May 31, 2019, 5:31pm UTC](https://discourse.julialang.org/t/juliadb-flatten-multiple-columns/24804/6 "2019-05-31T17:31:15Z")

</div>

> [@piever](#):
>
> Columns(x2 = r.x .+ k.group, y2 = rand(length(r)))

_That_ is the correct solution (for me). Awesome, thanks @piever!

---

<div class="post-metadata">

**Author:** ![yakir12](https://sea2.discourse-cdn.com/julialang/user_avatar/discourse.julialang.org/yakir12/32/297_2.png) [@yakir12](https://discourse.julialang.org/u/yakir12)\
**Post date:** [May 31, 2019, 5:34pm UTC](https://discourse.julialang.org/t/juliadb-flatten-multiple-columns/24804/7 "2019-05-31T17:34:10Z")

</div>

> [@piever](#):
>
> an object that iterates `NamedTuples` . The easiest is to use `Columns`

BTW, why not `rows`, that iterates named tuples as well, no?

---

<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:** [May 31, 2019, 5:39pm UTC](https://discourse.julialang.org/t/juliadb-flatten-multiple-columns/24804/8 "2019-05-31T17:39:02Z")

</div>

They return the same object: `rows((x=col1, y=col2))` would also work. Either case actually returns a `StructVector` (a vector of structs stored as a struct of vectors). When porting IndexedTables to StructArrays, we kept the `Columns` name for backward compatibility (it is now an alias for `StructVector). OTOH it is a bit confusing that `Columns(t::NamedTuple)`is the same as`rows(t::NamedTuple)`, not sure what’s the correct way forward in terms of API.