# Performance: Fast way to access numbers in Dataframes or alternatives

**URL:** <https://discourse.julialang.org/t/performance-fast-way-to-access-numbers-in-dataframes-or-alternatives/89884>\
**Category:** Performance\
**Tags:** dataframes, data\_structures\
**Created:** [November 7, 2022, 2:28pm UTC](https://discourse.julialang.org/t/performance-fast-way-to-access-numbers-in-dataframes-or-alternatives/89884 "2022-11-07T14:28:27Z")\
**Posts on this page:** 13\
**Page:** 1

<div class="post-metadata">

**Author:** ![pjuergens](https://sea2.discourse-cdn.com/julialang/user_avatar/discourse.julialang.org/pjuergens/32/28380_2.png) [@pjuergens](https://discourse.julialang.org/u/pjuergens)\
**Post date:** [November 7, 2022, 2:28pm UTC](https://discourse.julialang.org/t/performance-fast-way-to-access-numbers-in-dataframes-or-alternatives/89884/1 "2022-11-07T14:28:27Z")

</div>

Hello together,

I’m searching for a fast (or the fastest) way to access datapoints/numbers in a dataframes-like structure. I have 57 timeseries (columns) with 43800 datapoints (rows) each that are read in from a csv-file to a dataframe (DataFrames.jl). Currently I’m accessing single datapoints via row-index and column-label, i.e. e.g. `GLD[i, "hour"]` where GLD is the dataframe and i some index. However, this seems to be very time consuming.

So my question is: what is the fastest way to deal with this kind of data? Is there a faster way to access the data in the dataframe? Should I convert it to a (named) array or tuple or is there any other more performant datastructure?

---

<div class="post-metadata">

**Author:** ![gbaraldi](https://sea2.discourse-cdn.com/julialang/user_avatar/discourse.julialang.org/gbaraldi/32/22101_2.png) [@gbaraldi](https://discourse.julialang.org/u/gbaraldi)\
**Post date:** [November 7, 2022, 2:34pm UTC](https://discourse.julialang.org/t/performance-fast-way-to-access-numbers-in-dataframes-or-alternatives/89884/2 "2022-11-07T14:34:18Z")

</div>

Since you want to iterate through them all you might want a container that’s a bit faster than the usual dataframe from DataFrames.jl. Maybe [GitHub - JuliaData/TypedTables.jl: Simple, fast, column-based storage for data analysis in Julia](https://github.com/JuliaData/TypedTables.jl) is a bit faster?

---

<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:** [November 7, 2022, 2:39pm UTC](https://discourse.julialang.org/t/performance-fast-way-to-access-numbers-in-dataframes-or-alternatives/89884/3 "2022-11-07T14:39:57Z")

</div>

Accessing the data should not be time-consuming at all. TypedTables shouldn’t be an improvement over DataFrames for data access alone.

It’s likely the real problem is that you are not using a function barrier. For best performance, write functions that act on the vectors directly, not the whole data frame.

```julia
function foo(a, b)
    a[100] + b[101]
end
foo(df.a, df.b)

```

DataFramesMeta.jl’s `@with` macro can help with this.

```julia
@with df begin 
    :a[100] + :b[101]
end

```

---

<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:** [November 7, 2022, 3:19pm UTC](https://discourse.julialang.org/t/performance-fast-way-to-access-numbers-in-dataframes-or-alternatives/89884/4 "2022-11-07T15:19:29Z")

</div>

> [@pjuergens](#):
>
> Should I convert it to a (named) array or tuple or is there any other more performant datastructure?

Per comments below - the answer is “it depends” on your code. In some cases the way to go it so call `Tables.columntable` and convert it to a `NamedTuple`, but often, as @pdeffebach commented using a function barrier or DataFramesMeta.jl will be enough.

---

<div class="post-metadata">

**Author:** ![pjuergens](https://sea2.discourse-cdn.com/julialang/user_avatar/discourse.julialang.org/pjuergens/32/28380_2.png) [@pjuergens](https://discourse.julialang.org/u/pjuergens)\
**Post date:** [November 8, 2022, 7:45am UTC](https://discourse.julialang.org/t/performance-fast-way-to-access-numbers-in-dataframes-or-alternatives/89884/5 "2022-11-08T07:45:01Z")

</div>

Thank you, @pdeffebach , the function barrier solved the issue - decreasing computation time and allocations massively.

Can you roughly explain why?

---

<div class="post-metadata">

**Author:** ![Sukera](https://avatars.discourse-cdn.com/v4/letter/s/ce7236/32.png) [@Sukera](https://discourse.julialang.org/u/Sukera)\
**Post date:** [November 8, 2022, 8:57am UTC](https://discourse.julialang.org/t/performance-fast-way-to-access-numbers-in-dataframes-or-alternatives/89884/6 "2022-11-08T08:57:48Z")

</div>

> [@pjuergens](#):
>
> Can you roughly explain why?

Julia is built around functions, which are compiled to fast, native code. While you can do everything in global scope, it won’t be optimal as global variables need to have their types, size etc checked on every access, which takes time.

As for why allocations change, it’s hard to say without knowing the code you’re running.

---

<div class="post-metadata">

**Author:** ![aplavin](https://sea2.discourse-cdn.com/julialang/user_avatar/discourse.julialang.org/aplavin/32/222056_2.png) [@aplavin](https://discourse.julialang.org/u/aplavin)\
**Post date:** [November 8, 2022, 9:41am UTC](https://discourse.julialang.org/t/performance-fast-way-to-access-numbers-in-dataframes-or-alternatives/89884/7 "2022-11-08T09:41:12Z")

</div>

Is it natural in your case to have a function that operates on whole objects/rows (it often is)? Such as, `func(obj) = obj.value / (obj.day + obj.hour/24)`, and then apply this function to rows of your table.

If that’s the case, then arrays are an obvious and builtin datastructure: `tbl = [(day=1, hour=2, value=123.456), ...]`. Thanks to `Tables.jl`, this is a fully capable table in Julia, and can be converted from/to other table types at will. Access to a single row is cheap and done by `tbl[i]`; apply the function to all rows is also performant, just do `map(func, tbl)`.

For even better performance, you may want columnar storage, such as `StructArrays` or the already mentioned `TypedTables`. They are both tables again! And the interface to access a single row or apply the function to all rows stays the same, `tbl[i]` and `map(func, tbl)`.

---

<div class="post-metadata">

**Author:** ![pjuergens](https://sea2.discourse-cdn.com/julialang/user_avatar/discourse.julialang.org/pjuergens/32/28380_2.png) [@pjuergens](https://discourse.julialang.org/u/pjuergens)\
**Post date:** [November 9, 2022, 8:55am UTC](https://discourse.julialang.org/t/performance-fast-way-to-access-numbers-in-dataframes-or-alternatives/89884/8 "2022-11-09T08:55:24Z")

</div>

Unfortunately, the function barrier reduced the problem, but it seems to me like getting data from the dataframe is still the bottleneck in my code. I tried to put it in a minimal working example below, reproducing the structure of my code. So basically in the function `foo` I just need a slice of some columns of the dataframe that I need to access by name and operations are just done on single numbers.

The profiler shows that the line calling `foo` is critical, mainly due to call of `getindex` and `string`. @time results in `0.058843 seconds (875.52 k allocations: 44.086 MiB, 30.16% gc time)`, so also a lot of allocations and garbage collection is happening here.

Some background: I’m translating some pascal-code to julia. In pascal the dataframe is just a matrix accessed by indizes. The pascal-code takes 5 seconds to run, my julia-translation currently takes 30 seconds, so there should be some performance problem 😉 But maybe I’m also missing something fundamental, as I am new to julia.

```julia
using DataFrames

len_df = 5 * 8760
df = DataFrame(
    "column 1" => rand(len_df),
    "column 2" => rand(len_df),
    "other column 1" => rand(len_df),
    "other column 2" => rand(len_df),
    "a" => rand(len_df),
    "b" => rand(len_df),
    "c" => rand(len_df),
    "d" => rand(len_df),
    "e" => rand(len_df),
)

function foo(start_index::Integer, a::Array{Float64}, b::Array{Float64})
    sum = 0.0
    for i in start_index:start_index+24
        sum += a[i] * b[i]
    end
    return sum
end

function bar(df, len_df)
    for i in 1:(len_df-24)
        for j in 1:2
            foo(i, df[!, "column $j"], df[!, "other column $j"])
        end
    end
end

@time bar(df, len_df)
@profview bar(df, len_df)

```

---

<div class="post-metadata">

**Author:** ![kristoffer.carlsson](https://sea2.discourse-cdn.com/julialang/user_avatar/discourse.julialang.org/kristoffer.carlsson/32/22_2.png) [@kristoffer.carlsson](https://discourse.julialang.org/u/kristoffer.carlsson)\
**Post date:** [November 9, 2022, 9:29am UTC](https://discourse.julialang.org/t/performance-fast-way-to-access-numbers-in-dataframes-or-alternatives/89884/9 "2022-11-09T09:29:03Z")

</div>

> [@pjuergens](#):
>
> Can you roughly explain why?

The performance docs have a section about this: [Performance Tips · The Julia Language](https://docs.julialang.org/en/v1/manual/performance-tips/#kernel-functions)

---

<div class="post-metadata">

**Author:** ![lawless-m](https://sea2.discourse-cdn.com/julialang/user_avatar/discourse.julialang.org/lawless-m/32/30869_2.png) [@lawless-m](https://discourse.julialang.org/u/lawless-m)\
**Post date:** [November 9, 2022, 9:55am UTC](https://discourse.julialang.org/t/performance-fast-way-to-access-numbers-in-dataframes-or-alternatives/89884/10 "2022-11-09T09:55:21Z")

</div>

even just pre-generating your column names makes a significant difference

```julia
julia> function bar(df, len_df)
    for i in 1:(len_df-24)
        for j in 1:2
            foo(i, df[!, "column $j"], df[!, "other column $j"])
        end
    end
end

julia> @time bar(df, len_df)
  0.082328 seconds (963.66 k allocations: 48.902 MiB, 5.15% gc time, 42.16% compilation time)

julia> @time bar(df, len_df)
  0.047580 seconds (875.52 k allocations: 44.086 MiB, 7.91% gc time)

```

vs

```julia
julia> function bar(df, len_df)
           for i in 1:(len_df-24)
               foo(i, df[!, "column 1"], df[!, "other column 1"])
               foo(i, df[!, "column 2"], df[!, "other column 2"])
           end
       end
bar (generic function with 1 method)

julia> @time bar(df, len_df)
  0.023997 seconds (18.70 k allocations: 1.009 MiB, 61.50% compilation time)

julia> @time bar(df, len_df)
  0.011519 seconds

```

---

<div class="post-metadata">

**Author:** ![pjuergens](https://sea2.discourse-cdn.com/julialang/user_avatar/discourse.julialang.org/pjuergens/32/28380_2.png) [@pjuergens](https://discourse.julialang.org/u/pjuergens)\
**Post date:** [November 9, 2022, 11:20am UTC](https://discourse.julialang.org/t/performance-fast-way-to-access-numbers-in-dataframes-or-alternatives/89884/11 "2022-11-09T11:20:20Z")

</div>

wow, that’s indeed significant. However, I only see the difference in my minimal example and cannot reproduce it in my actual code. Maybe there is overall another problem in the code that I just don’t see at the moment…

The profiler points to the functions `getindex` from dataframe.jl:525, indexed\_iterate from tuple.jl:89 and the return statement of my function - which is `foo` in my example (with Flags: GC). Even if I delete the content of the function just returning a fixed number, it doesn’t change anything - that’s why I thought the problem lies in passing the input-arguments to the function.

Thanks already for all your help!

---

<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:** [November 9, 2022, 11:37am UTC](https://discourse.julialang.org/t/performance-fast-way-to-access-numbers-in-dataframes-or-alternatives/89884/12 "2022-11-09T11:37:44Z")

</div>

It might well be that your MWE is too trivial, but aren’t you just doing

```julia
using RollingFunctions
rolling(sum, df."column 1" .* df."other column 1", 24)

```

---

<div class="post-metadata">

**Author:** ![pjuergens](https://sea2.discourse-cdn.com/julialang/user_avatar/discourse.julialang.org/pjuergens/32/28380_2.png) [@pjuergens](https://discourse.julialang.org/u/pjuergens)\
**Post date:** [November 15, 2022, 9:35am UTC](https://discourse.julialang.org/t/performance-fast-way-to-access-numbers-in-dataframes-or-alternatives/89884/13 "2022-11-15T09:35:43Z")

</div>

Thanks to everybody! I further increased performance the following way

- initializing arrays for the columns of the dataframe outside of the for-loops and using them when calling the function instead of accessing the dataframe there
- I had some issues with the struct that stored the return-value of my function. Passing the type as parameter to the struct as explained in the [performance tips](https://docs.julialang.org/en/v1/manual/performance-tips/#Avoid-fields-with-abstract-type) and fixing the dimensions of the array had a huge impact on performance and avoids dynamic dispatch

and @nilshg you are very right, my MWE was too trivial 😉