# Hierarchical or multi-index for data frames

**URL:** <https://discourse.julialang.org/t/hierarchical-or-multi-index-for-data-frames/17076>\
**Category:** Data\
**Created:** [November 1, 2018, 11:41pm UTC](https://discourse.julialang.org/t/hierarchical-or-multi-index-for-data-frames/17076 "2018-11-01T23:41:35Z")\
**Posts on this page:** 11\
**Page:** 1

<div class="post-metadata">

**Author:** ![MatthiasKauer](https://sea2.discourse-cdn.com/julialang/user_avatar/discourse.julialang.org/matthiaskauer/32/2192_2.png) [@MatthiasKauer](https://discourse.julialang.org/u/MatthiasKauer)\
**Post date:** [November 1, 2018, 11:41pm UTC](https://discourse.julialang.org/t/hierarchical-or-multi-index-for-data-frames/17076/1 "2018-11-01T23:41:35Z")

</div>

Hi,  
I’ve been looking around for data frame implementations. I found that [GitHub - JuliaData/DataFrames.jl: In-memory tabular data in Julia](https://github.com/JuliaData/DataFrames.jl) does not support a hierarchical or multi-index approach.  
For clarification, I’m coming from pandas where multi-index is described here: [https://pandas.pydata.org/pandas-docs/stable/advanced.html#multiindex-advanced-indexing](https://pandas.pydata.org/pandas-docs/stable/advanced.html#multiindex-advanced-indexing)

After a bit of searching, I am under the impression that multi-index in DataFrames.jl is left out by design. Mentioned in Aug 2017: [A spreadsheet-like pivot function - #2 by nalimilan](https://discourse.julialang.org/t/a-spreadsheet-like-pivot-function/2701/2)

I’m now wondering if it is really left out by design and what the reasons are behind that decision. Is it mainly performance-driven?

What are the alternatives?  
As a workaround, I could use a dict of data frames. Are there other libraries that I’m missing?  
@nalimilan mentioned "[`NamedArray`](https://github.com/davidavdav/NamedArrays.jl/) an [`AxisArray`](https://github.com/JuliaArrays/AxisArrays.jl) or an [`IndexedTable`](https://github.com/JuliaComputing/IndexedTables.jl)" back then.

As an example, I want to implement the following pseudo-code:

```julia
df = DataFrame({"AAPL": {"open": [1, 2, 3, 4], "close": [1, 2, 3, 4]}, "MSFT": "open": [10, 20,...], "close": [10, 20,...]}}
df['close'].max() # max for AAPL and MSFT
df['range'] = df['close'] - df['open'] # calculates for both AAPL and MSFT

```

What is the idiomatic approach for this in Julia?

---

<div class="post-metadata">

**Author:** ![simonbyrne](https://sea2.discourse-cdn.com/julialang/user_avatar/discourse.julialang.org/simonbyrne/32/19_2.png) [@simonbyrne](https://discourse.julialang.org/u/simonbyrne)\
**Post date:** [November 2, 2018, 3:36am UTC](https://discourse.julialang.org/t/hierarchical-or-multi-index-for-data-frames/17076/2 "2018-11-02T03:36:36Z")

</div>

> [@MatthiasKauer](#):
>
> I’m now wondering if it is really left out by design and what the reasons are behind that decision. Is it mainly performance-driven?

Not really, in the sense that DataFrames has never had a strong “design”: it is the oldest of the “tabular” packages in Julia (in fact, it is the [sixth-oldest package](https://github.com/JuliaLang/METADATA.jl/tree/41dab7c7f059e5b0e85149abdd2263bcffd68d74)). Overall, it was roughly influenced by R data.frames, but feature additions have mainly been driven by what users have needed for their own work.

However the tabular data ecosystem suffers a bit from [the Lisp Curse](http://www.winestockwebdesign.com/Essays/Lisp_Curse.html), in that it is often easier to write a new package that solves your problem than try to shoehorn your solution into an existing package, so there have been a number of other tabular related packages developed over time. For your particular problem, I would suggest IndexedTables, which has been designed for this exact use-case. You might also be interested in [JuliaDB](https://juliadb.org/), which is a distributed extension of IndexedTables, but unfortunately is not yet running on 1.0.

---

<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:** [November 2, 2018, 10:21am UTC](https://discourse.julialang.org/t/hierarchical-or-multi-index-for-data-frames/17076/3 "2018-11-02T10:21:59Z")

</div>

Actually I think basic design principles have been followed quite consistently over time. DataFrames indeed inspired by R’s data.frame, and by the more modern dplyr/tibble approach, rather than by Pandas.

I’m not very familiar with Pandas, but AFAIK you can achieve the same thing without multi-indexes using subsetting or split-apply-combine operations. It would be interesting to think about how this kind of feature could be added to DataFrames, and whether it would conflict with other features. Indexes could be represented by special columns, which could also be used a primary keys to increase performance of grouping operations.

---

<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:** [November 2, 2018, 10:47am UTC](https://discourse.julialang.org/t/hierarchical-or-multi-index-for-data-frames/17076/4 "2018-11-02T10:47:04Z")

</div>

I’ve never used it personally but there is an implementation of something like this already: [GitHub - invenia/KeyedFrames.jl: A DataFrame that also keeps track of its unique key](https://github.com/invenia/KeyedFrames.jl)

It could be nice to add something like this to DataFrames for consistency with IndexedTables. At some point I was playing with the idea of replacing `ColDict` from IndexedTables (an untyped `IndexedTable` that allows replacing columns with columns of a different type) with a `DataFrame` but the one thing missing was support for primary keys in DataFrames (also DataFrame requires name for the columns but from what I understand IndexedTables may do so to in the future).

---

<div class="post-metadata">

**Author:** ![MatthiasKauer](https://sea2.discourse-cdn.com/julialang/user_avatar/discourse.julialang.org/matthiaskauer/32/2192_2.png) [@MatthiasKauer](https://discourse.julialang.org/u/MatthiasKauer)\
**Post date:** [November 4, 2018, 7:26am UTC](https://discourse.julialang.org/t/hierarchical-or-multi-index-for-data-frames/17076/5 "2018-11-04T07:26:35Z")

</div>

Thank you for your responses.  
The Lisp Curse essay has been eye-opening.  
I always considered it positive for a programming language when library development was simple and similar to the application development. Now, I see that it makes consensus much harder.

> I’m not very familiar with Pandas, but AFAIK you can achieve the same thing without multi-indexes using subsetting or split-apply-combine operations.

You certainly can achieve this in many different ways. If you store as follows, you can add and subtract columns, completely ignoring the symbols.

```julia
symbol | seqnum | open | close
AAPL | 1 | 1 | 2
AAPL | 2 | 3 | 4
MSFT | 1 | 10 | 11
MSFT | 2 | 12 | 13
....
df['close'].max() # would require some grouping first but should not be too cumbersome
df['range'] = df['close'] - df['open'] # should just do the right thing

```

Can’t really decide whether I find this “good enough” right now.

Is there an overview on what’s happening in this domain right now?  
I’m aware of:

- [NamedArray](https://github.com/davidavdav/NamedArrays.jl/)
- [AxisArray](https://github.com/JuliaArrays/AxisArrays.jl)
- [IndexedTable](https://github.com/JuliaComputing/IndexedTables.jl) or the entire JuliaDB framework
- [DataFrames.jl](https://github.com/JuliaData/DataFrames.jl)
- [KeyedFrames](https://github.com/invenia/KeyedFrames.jl) (thank you @piever)

Interfaces that aim to translate between different implementions appear to be:

- [DataStreams.jl](https://github.com/JuliaData/DataStreams.jl)
- [IndexedTables.jl](https://github.com/JuliaComputing/IndexedTables.jl)
- [Tables.jl](https://github.com/JuliaData/Tables.jl)

What’s the best way to find out how these implementations compare? Has someone written a meta-review of some sort?  
Who are all the Julia\_ companies behind these projects? JuliaComputing is the main driver behind Julia, right?  
Are JuliaData and JuliaArrays different companies, subdivisions of JuliaComputing or “casual” groups of people that happen to be interested in this topic?

---

<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:** [November 4, 2018, 11:13am UTC](https://discourse.julialang.org/t/hierarchical-or-multi-index-for-data-frames/17076/6 "2018-11-04T11:13:44Z")

</div>

> [@MatthiasKauer](#):
>
> Are JuliaData and JuliaArrays different companies, subdivisions of JuliaComputing or “casual” groups of people that happen to be interested in this topic?

As far as I understand, of those you mentioned above, JuliaComputing is the only company and the other organizations are mostly volunteers, but this doesn’t necessarily mean the packages there are maintained less promptly (for example, DataFrames was updated to Julia 1.0 before IndexedTables and JuliaDB - for technical reasons - is still in the process of being ported): DataFrames is so widely used that the odds of it becoming abandoned / unmaintained are quite small. I think the APIs of DataFrames and IndexedTables / JuliaDB are slowly converging (and DataFramesMeta / JuliaDBMeta give you some macros to simplify things and look also quite similar to one another). If you can’t choose the easiest is to go through the same tutorial for both packages and decide which one you like best:

- [DataFrames(Meta)](https://cbrownley.wordpress.com/2017/11/29/data-wrangling-in-julia-based-on-dplyr-flights-tutorials/) (this is from a blog post, I wonder whether there is an “official” version of the tutorial)
- [JuliaDB](https://juliadb.org/latest/manual/tutorial.html) and [JuliaDBMeta](https://piever.github.io/JuliaDBMeta.jl/latest/tutorial.html) (warning: this is not ported to Julia 1.0 yet, but may give you an idea of whether you find the API more or less intuitive)

---

<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:** [November 4, 2018, 11:55am UTC](https://discourse.julialang.org/t/hierarchical-or-multi-index-for-data-frames/17076/7 "2018-11-04T11:55:02Z")

</div>

Concerning the problem at hand, I would tend to believe that for this specific usecase the “long” format seems better.

However, the cool thing is that in Julia things mix and match very easily. I’ve just realised that you can combine DataFrames with [StructArrays](https://github.com/piever/StructArrays.jl) for some nice tricks here. I still find the long format more convenient, but this is kind of fun - in the spirit of the lisp curse…

```julia
julia> using DataFrames, StructArrays

julia> df = DataFrame(AAPL = StructArray(open = 1:3, close = 2:4),
       MSFT = StructArray(open = 10:12, close = (10:12) .+ rand.()))
3×2 DataFrame
│ Row │ AAPL │ MSFT │
│ │ NamedTup… │ NamedTup… │
├─────┼───────────────────────┼──────────────────────────────┤
│ 1 │ (open = 1, close = 2) │ (open = 10, close = 10.2042) │
│ 2 │ (open = 2, close = 3) │ (open = 11, close = 11.0493) │
│ 3 │ (open = 3, close = 4) │ (open = 12, close = 12.1066) │

julia> df.AAPL.open
1:3

julia> df.MSFT.close
3-element Array{Float64,1}:
 10.204195399872209
 11.04927211234995 
 12.106563317898413

julia> max_close(t) = maximum(t.close)
max_close (generic function with 1 method)

julia> aggregate(df, max_close)
1×2 DataFrame
│ Row │ AAPL_max_close │ MSFT_max_close │
│ │ Int64 │ Float64 │
├─────┼────────────────┼────────────────┤
│ 1 │ 4 │ 12.1066 │

```

the idea being that in Julia you can have an array-like object (that can therefore be used as a column) that iterates `NamedTuples` but has also accessible columns (data is stored in efficient columnar way).

I think the interplay between DataFrames and custom array type in Julia has a lot of promise, this type of data nesting could be one interesting direction.

EDIT: just to clarify, I do not recommend really using any of the above - the long format seems better - but was curious to see how many things work already out of the box and I think adding a simple two-way converter between DataFrames and some `AbstractArray` of `NamedTuples` with columnar storage (where the same API as `DataFrames` would still work wherever possible) is definitely interesting.

---

<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:** [November 4, 2018, 11:55am UTC](https://discourse.julialang.org/t/hierarchical-or-multi-index-for-data-frames/17076/8 "2018-11-04T11:55:13Z")

</div>

Apart from Julia Computing, which is a company, most Julia\* organizations are just open teams of volunteers from various horizons, with lots of intersection between them. So the ecosystem isn’t as fragmented as it may seem.

Basically, the tables implementations are currently DataFrames (and KeyedFrames which is based on it), IndexedTables/JuliaDB, and TypedTables. DataFrames is the simple in-memory table storage, JuliaDB supports distributed and out-of-core operations (with IndexedTable being a simple in-memory version which is strongly typed), and TypedTables is another strongly-typed table. Whether strong typing is beneficial probably depends on whether you work with many tables with different column types or not, as the compilation overhead can be significant.

AxisArrays and NamedArrays are quite different I would say, they are more arrays than tables, even though they can have some use cases in common with tables.

Then there are currently two generic interfaces: Tables (which replaces DataStreams) and TableTraits. There’s hope that we could agree on a single common interface, but it’s still [under discussion](https://discourse.julialang.org/t/tables-jl-vs-tabletraits-jl-was-textparse-jl-is-fast-again/16982/1). As @piever said we would also like to converge to similar APIs when that’s possible.

---

<div class="post-metadata">

**Author:** ![MatthiasKauer](https://sea2.discourse-cdn.com/julialang/user_avatar/discourse.julialang.org/matthiaskauer/32/2192_2.png) [@MatthiasKauer](https://discourse.julialang.org/u/MatthiasKauer)\
**Post date:** [November 11, 2018, 8:33am UTC](https://discourse.julialang.org/t/hierarchical-or-multi-index-for-data-frames/17076/9 "2018-11-11T08:33:42Z")

</div>

Again, thank you so much for your input.

> Then there are currently two generic interfaces: Tables (which replaces DataStreams) and TableTraits. There’s hope that we could agree on a single common interface, but it’s still [under discussion](https://discourse.julialang.org/t/tables-jl-vs-tabletraits-jl-was-textparse-jl-is-fast-again/16982/1).

This thread really puts Tables.jl into context.

@piever: I really liked the two tutorials you linked. And nesting arrays into DataFrame seems really creative. Definitely has potential although probably not as standard operating procedure, like you said.

---

<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:** [November 11, 2018, 10:26am UTC](https://discourse.julialang.org/t/hierarchical-or-multi-index-for-data-frames/17076/10 "2018-11-11T10:26:17Z")

</div>

> [@MatthiasKauer](#):
>
> The Lisp Curse essay has been eye-opening.  
> I always considered it positive for a programming language when library development was simple and similar to the application development. Now, I see that it makes consensus much harder.

To be fair, I don’t think the Lisp Curse essay linked above describes the current situation with Julia packages, particularly data frames & friends, very well.

My understanding is that to a large extent, experimentation is happening because people want to explore various approaches to make things fast and convenient at the same time. Julia has introduced a combination of parametric types, multiple dispatch and AOT compilation which is unprecedented, and people need learn how to work with this efficiently. Also, the language is shaped by the results of these experiments (look at the history of the Nullable type).

It is best to approach Julia with the understanding that there will be many different approaches in the medium run before a few of them will emerge as a _de facto_ standard. But the language is eminently capable of handling this, provided that there is a common interface.

---

<div class="post-metadata">

**Author:** ![singularitti](https://sea2.discourse-cdn.com/julialang/user_avatar/discourse.julialang.org/singularitti/32/17678_2.png) [@singularitti](https://discourse.julialang.org/u/singularitti)\
**Post date:** [October 9, 2019, 8:23am UTC](https://discourse.julialang.org/t/hierarchical-or-multi-index-for-data-frames/17076/11 "2019-10-09T08:23:06Z")

</div>

> [@MatthiasKauer](#):
>
> Is there an overview on what’s happening in this domain right now?  
> I’m aware of:
> 
> - [NamedArray](https://github.com/davidavdav/NamedArrays.jl/)
> - [AxisArray](https://github.com/JuliaArrays/AxisArrays.jl)
> - [IndexedTable](https://github.com/JuliaComputing/IndexedTables.jl) or the entire JuliaDB framework
> - [DataFrames.jl](https://github.com/JuliaData/DataFrames.jl)
> - [KeyedFrames](https://github.com/invenia/KeyedFrames.jl) (thank you @piever)
> 
> Interfaces that aim to translate between different implementions appear to be:
> 
> - [DataStreams.jl](https://github.com/JuliaData/DataStreams.jl)
> - [IndexedTables.jl](https://github.com/JuliaComputing/IndexedTables.jl)
> - [Tables.jl](https://github.com/JuliaData/Tables.jl)
> 
> What’s the best way to find out how these implementations compare? Has someone written a meta-review of some sort?

See the “Data Processing: what can I do with my data once it’s in Julia?” part from [this wonderful review](https://www.juliabloggers.com/a-tour-of-the-data-ecosystem-in-julia/).