# Multilevel DataFrame

**URL:** <https://discourse.julialang.org/t/multilevel-dataframe/86798>\
**Category:** New to Julia\
**Tags:** dataframes, data\_structures, structure\
**Created:** [September 5, 2022, 12:53pm UTC](https://discourse.julialang.org/t/multilevel-dataframe/86798 "2022-09-05T12:53:41Z")\
**Posts on this page:** 9\
**Page:** 1

<div class="post-metadata">

**Author:** ![flobe](https://avatars.discourse-cdn.com/v4/letter/f/977dab/32.png) [@flobe](https://discourse.julialang.org/u/flobe)\
**Post date:** [September 5, 2022, 12:53pm UTC](https://discourse.julialang.org/t/multilevel-dataframe/86798/1 "2022-09-05T12:53:41Z")

</div>

Hi all,

probably the question was asked before, but I didn’t find it here in the discourse.

Basically, I’m importing multiple tables (.csv) with DataFrames.jl to Julia. In my example, each table represents a robot with coordinates, timestamps, etc.  
To keep my code simple and allow simple stack processing for all robots I’d like to add a level and rearrange the DataFrames in one DataFrame and call the robots by a hash key.

e.g. `lab.robot1.x_pos`

is this even possible with the DataFrames package? Is it the right tool?

Thanks in advance •ᴗ•

---

<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:** [September 5, 2022, 1:22pm UTC](https://discourse.julialang.org/t/multilevel-dataframe/86798/2 "2022-09-05T13:22:27Z")

</div>

Here is an example what I understand you need:

```julia
julia> using DataFrames

julia> robots = [DataFrame(x=rand(2), y=rand(2)) for i in 1:3] # 3 robots
3-element Vector{DataFrame}:
 2×2 DataFrame
 Row │ x y
     │ Float64 Float64
─────┼────────────────────
   1 │ 0.832353 0.395642
   2 │ 0.250032 0.403825
 2×2 DataFrame
 Row │ x y
     │ Float64 Float64
─────┼────────────────────
   1 │ 0.308752 0.560986
   2 │ 0.420184 0.595701
 2×2 DataFrame
 Row │ x y
     │ Float64 Float64
─────┼────────────────────
   1 │ 0.860213 0.633597
   2 │ 0.785208 0.829677

julia> vcat(robots..., source=:robot_id)
6×3 DataFrame
 Row │ x y robot_id
     │ Float64 Float64 Int64
─────┼──────────────────────────────
   1 │ 0.832353 0.395642 1
   2 │ 0.250032 0.403825 1
   3 │ 0.308752 0.560986 2
   4 │ 0.420184 0.595701 2
   5 │ 0.860213 0.633597 3
   6 │ 0.785208 0.829677 3

julia> res = reduce(vcat, robots, source=:robot_id) # alternative syntax that might be faster if you have millions of robots
6×3 DataFrame
 Row │ x y robot_id
     │ Float64 Float64 Int64
─────┼──────────────────────────────
   1 │ 0.832353 0.395642 1
   2 │ 0.250032 0.403825 1
   3 │ 0.308752 0.560986 2
   4 │ 0.420184 0.595701 2
   5 │ 0.860213 0.633597 3
   6 │ 0.785208 0.829677 3

```

Now if you want to pick robot by `:robot_id` do:

```julia
julia> gdf = groupby(res, :robot_id)
GroupedDataFrame with 3 groups based on key: robot_id
First Group (2 rows): robot_id = 1
 Row │ x y robot_id
     │ Float64 Float64 Int64
─────┼──────────────────────────────
   1 │ 0.832353 0.395642 1
   2 │ 0.250032 0.403825 1
⋮
Last Group (2 rows): robot_id = 3
 Row │ x y robot_id
     │ Float64 Float64 Int64
─────┼──────────────────────────────
   1 │ 0.860213 0.633597 3
   2 │ 0.785208 0.829677 3

julia> gdf[(robot_id=1,)] # passing a NamedTuple with robot id
2×3 SubDataFrame
 Row │ x y robot_id
     │ Float64 Float64 Int64
─────┼──────────────────────────────
   1 │ 0.832353 0.395642 1
   2 │ 0.250032 0.403825 1

julia> gdf[(1,)] # the same, but passing a Tuple with robot id
2×3 SubDataFrame
 Row │ x y robot_id
     │ Float64 Float64 Int64
─────┼──────────────────────────────
   1 │ 0.832353 0.395642 1
   2 │ 0.250032 0.403825 1

```

In this case group number and robot id are the same so you could write:

```julia
julia> gdf[1]
2×3 SubDataFrame
 Row │ x y robot_id
     │ Float64 Float64 Int64
─────┼──────────────────────────────
   1 │ 0.832353 0.395642 1
   2 │ 0.250032 0.403825 1

```

to get the first group, but in general robot id could be any identifier, eg.:

```julia
julia> reduce(vcat, robots, source=:robot_random_id => rand(UInt64, length(robots)))
6×3 DataFrame
 Row │ x y robot_random_id
     │ Float64 Float64 UInt64
─────┼──────────────────────────────────────────
   1 │ 0.832353 0.395642 8303239618947412160
   2 │ 0.250032 0.403825 8303239618947412160
   3 │ 0.308752 0.560986 2175806809747680279
   4 │ 0.420184 0.595701 2175806809747680279
   5 │ 0.860213 0.633597 12898194048670657353
   6 │ 0.785208 0.829677 12898194048670657353

```

This is a solution if you want to store all robots in a single data frame (this has many benefits - e.g. later aggregation is much easier if you do so). But maybe you would prefer something else. If this is the case please comment.

On the other hand - if you want to use robot information for compute intensive operations that are not aggregations (e.g. in simulation, where you would access intividual elements of vectors) then most likely you will be better off by not using `DataFrame` to store robot information but rather e.g. `Tables.columntable` (i.e. `NamedTuple` of vectors) as it will be type stable and faster in such applications. `DataFrame` is designed for efficient working with whole columns.

---

<div class="post-metadata">

**Author:** ![nilshg](https://sea2.discourse-cdn.com/julialang/user_avatar/discourse.julialang.org/nilshg/32/2283_2.png) [@nilshg](https://discourse.julialang.org/u/nilshg)\
**Post date:** [September 5, 2022, 2:10pm UTC](https://discourse.julialang.org/t/multilevel-dataframe/86798/3 "2022-09-05T14:10:21Z")

</div>

Just to add to Bogumil’s answer, if you have the data in a bunch of csv files with the same format you can simply read them into a single DataFrame by doing:

```julia
CSV.read(vector_of_filepaths_of_csvs, DataFrame; source = vector_of_names)

```

here the `source` kwarg will add a column `source` to your final DataFrame identifying which csv file the row in the table came from.

---

<div class="post-metadata">

**Author:** ![flobe](https://avatars.discourse-cdn.com/v4/letter/f/977dab/32.png) [@flobe](https://discourse.julialang.org/u/flobe)\
**Post date:** [September 5, 2022, 2:23pm UTC](https://discourse.julialang.org/t/multilevel-dataframe/86798/4 "2022-09-05T14:23:53Z")

</div>

Wow thanks Bogumil for the detailed answer! This has helped me a lot to understand a lot of things 🙂 But as you correctly guessed in the last section, I’m in the situation where I use robot information for computationally intensive operations … so I think I’ll stick to your suggestion using a `NamedTuple` and try to get that working 😊

---

<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:** [September 5, 2022, 3:32pm UTC](https://discourse.julialang.org/t/multilevel-dataframe/86798/5 "2022-09-05T15:32:12Z")

</div>

If you need to do compute intensive operations then do:

```julia
using CSV
using Tables
robots = [CSV.read(file, Tables.columntable) for file in vector_of_file_names]

```

and then each robot has a numeric id in this vector (1, 2, 3 etc.), so you can write e.g.

```julia
robots[1].x_pos[end]

```

To get a last `x_pos` of robot `1`.

This is type stable and efficient pattern for your work. In particular it is important to number robots from with 1 and consecutive natural numbers as this will be fastest way to look them up.

---

<div class="post-metadata">

**Author:** ![rmsmsgood](https://sea2.discourse-cdn.com/julialang/user_avatar/discourse.julialang.org/rmsmsgood/32/20544_2.png) [@rmsmsgood](https://discourse.julialang.org/u/rmsmsgood)\
**Post date:** [September 5, 2022, 4:25pm UTC](https://discourse.julialang.org/t/multilevel-dataframe/86798/6 "2022-09-05T16:25:24Z")

</div>

How about this?

```julia
using DataFrames

lab = DataFrame(randn(4,10), sort(["robot$i.x" for i in 1:5] ∪ ["robot$i.y" for i in 1:5]))

```

```julia
julia> lab = DataFrame(randn(4,10), sort(["robot$i.x" for i in 1:5] ∪ ["robot$i.y" for i in 1:5]))
4×10 DataFrame
 Row │ robot1.x robot1.y robot2.x robot2.y robot3.x robot3.y robot4.x robot4.y robot5.x robot5.y  
     │ Float64 Float64 Float64 Float64 Float64 Float64 Float64 Float64 Float64 Float64   
─────┼────────────────────────────────────────────────────────────────────────────────────────────────────────────────
   1 │ 1.18797 -0.986746 -2.13126 0.410994 -0.382896 1.57188 1.2566 0.23703 -1.63068 1.1288
   2 │ -0.5893 -0.812499 -0.172445 -0.136242 -1.44124 -0.427348 0.717058 2.1018 0.216043 0.102947
   3 │ 1.49847 -0.342672 -0.377164 -0.7423 1.35134 0.39429 1.43345 -1.6108 -0.357507 0.915174
   4 │ -0.213267 -0.733188 0.257646 -1.04185 -0.41242 -1.27896 -0.964437 -0.00448036 0.533967 -0.207548

julia> lab."robot1.x"
4-element Vector{Float64}:
  1.1879717987881062
 -0.5893002692628563
  1.4984705071964186
 -0.2132672510579861

```

Maybe this is not a ‘natural implementation’. I know, it looks like kinda joke, but actually very flexible, easy to use, simple and fast in special cases. At least, you can handle this as one variable `lab` so many operations what you know could be applied.

---

<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:** [September 5, 2022, 4:44pm UTC](https://discourse.julialang.org/t/multilevel-dataframe/86798/7 "2022-09-05T16:44:40Z")

</div>

DataFrames.jl is designed to support this pattern without a problem (you can even have millions of robots). And you can write, e.g.:

```julia
robot_idx = 10
lab[!, "robot$(robot_idx).x"

```

programmatically.

The downside is that this code is not going to be type stable (and as you say - in some cases it is not a problem, but in the other it might be a problem and you would need to use function-barrier technique) so there is a trade-off here.

---

<div class="post-metadata">

**Author:** ![flobe](https://avatars.discourse-cdn.com/v4/letter/f/977dab/32.png) [@flobe](https://discourse.julialang.org/u/flobe)\
**Post date:** [September 6, 2022, 8:01am UTC](https://discourse.julialang.org/t/multilevel-dataframe/86798/8 "2022-09-06T08:01:13Z")

</div>

Thanks for all the informative answers! I guess I’ll stick to that one but store the `columntables` in a dictionary since the robots have actually names and I don’t want to call them by index, but just by their name. Not sure if this is nice from a programming point of view, but this way I have all the functions i was hoping for ☺

Thank you, folks!

---

<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:** [September 6, 2022, 8:13am UTC](https://discourse.julialang.org/t/multilevel-dataframe/86798/9 "2022-09-06T08:13:37Z")

</div>

> [@flobe](#):
>
> Not sure if this is nice from a programming point of view

Yes - this is a correct approach. The only thing is that it will be a bit slower.