Performance tips#

using DataFrames
using BenchmarkTools
using CategoricalArrays
using PooledArrays
using Random

Access by column number is faster than by name#

x = DataFrame(rand(5, 1000), :auto)
@btime $x[!, 500]; ## Faster
  3.086 ns (0 allocations: 0 bytes)
@btime $x.x500;  ## Slower
  11.443 ns (0 allocations: 0 bytes)

When working with data DataFrame use barrier functions or type annotation#

function f_bad() ## this function will be slow
    Random.seed!(1)
    x = DataFrame(rand(1000000, 2), :auto)
    y, z = x[!, 1], x[!, 2]
    p = 0.0
    for i in 1:nrow(x)
        p += y[i] * z[i]
    end
    p
end

@btime f_bad();
# if you run @code_warntype f_bad() then you notice
# that Julia does not know column types of `DataFrame`
  106.113 ms (5999022 allocations: 122.06 MiB)

solution 1 is to use barrier function (it should be possible to use it in almost any code) for the calculation. You will notice much less memopry allocations and faster performance.

function f_inner(y, z)
    p = 0.0
    for i in eachindex(y, z)
        p += y[i] * z[i]
    end
    p
end

function f_barrier()
    Random.seed!(1)
    x = DataFrame(rand(1000000, 2), :auto)
    f_inner(x[!, 1], x[!, 2])
end

@btime f_barrier();
  3.813 ms (44 allocations: 30.52 MiB)

or use inbuilt function if possible

using LinearAlgebra

function f_inbuilt()
    Random.seed!(1)
    x = DataFrame(rand(1000000, 2), :auto)
    dot(x[!, 1], x[!, 2])
end

@btime f_inbuilt();
  3.239 ms (44 allocations: 30.52 MiB)

solution 2 is to provide the types of extracted columns. However, there are cases in which you will not know these types.

function f_typed()
    Random.seed!(1)
    x = DataFrame(rand(1000000, 2), :auto)
    y::Vector{Float64}, z::Vector{Float64} = x[!, 1], x[!, 2]
    p = 0.0
    for i in 1:nrow(x)
        p += y[i] * z[i]
    end
    p
end

@btime f_typed();
  3.780 ms (44 allocations: 30.52 MiB)

In general for tall and narrow tables it is often useful to use Tables.rowtable, Tables.columntable or Tables.namedtupleiterator for intermediate processing of data in a type-stable way.

Consider using delayed DataFrame creation technique#

also notice the difference in performance between copying vs non-copying data frame creation

function f1()
    x = DataFrame([Vector{Float64}(undef, 10^4) for i in 1:100], :auto, copycols=false) ## we work with a DataFrame directly
    for c in 1:ncol(x)
        d = x[!, c]
        for r in 1:nrow(x)
            d[r] = rand()
        end
    end
    x
end

function f1a()
    x = DataFrame([Vector{Float64}(undef, 10^4) for i in 1:100], :auto) ## we work with a DataFrame directly
    for c in 1:ncol(x)
        d = x[!, c]
        for r in 1:nrow(x)
            d[r] = rand()
        end
    end
    x
end

function f2()
    x = Vector{Any}(undef, 100)
    for c in 1:length(x)
        d = Vector{Float64}(undef, 10^4)
        for r in eachindex(d)
            d[r] = rand()
        end
        x[c] = d
    end
    DataFrame(x, :auto, copycols=false) ## we delay creation of DataFrame after we have our job done
end

function f2a()
    x = Vector{Any}(undef, 100)
    for c in eachindex(x)
        d = Vector{Float64}(undef, 10^4)
        for r in eachindex(d)
            d[r] = rand()
        end
        x[c] = d
    end
    DataFrame(x, :auto) ## we delay creation of DataFrame after we have our job done
end

@btime f1();
@btime f1a();
@btime f2();
@btime f2a();
  27.633 ms (1949728 allocations: 37.40 MiB)
  28.487 ms (1950028 allocations: 45.03 MiB)
  1.142 ms (728 allocations: 7.66 MiB)
  1.563 ms (1028 allocations: 15.29 MiB)

You can add rows to a DataFrame in place and it is fast#

x = DataFrame(rand(10^6, 5), :auto)
y = DataFrame(transpose(1.0:5.0), :auto)
z = [1.0:5.0;]

@btime vcat($x, $y); ## creates a new DataFrame - slow
@btime append!($x, $y); ## in place - fast

x = DataFrame(rand(10^6, 5), :auto) ## reset to the same starting point
@btime push!($x, $z); ## add a single row in place - fast
  2.687 ms (212 allocations: 38.16 MiB)
  1.192 μs (29 allocations: 1.50 KiB)
  443.960 ns (16 allocations: 256 bytes)

Allowing missing as well as categorical slows down computations#

using StatsBase

function test(data) ## uses countmap function to test performance
    println(eltype(data))
    x = rand(data, 10^6)
    y = categorical(x)
    println(" raw:")
    @btime countmap($x)
    println(" categorical:")
    @btime countmap($y)
    nothing
end

test(1:10)
test([randstring() for i in 1:10])
test(allowmissing(1:10))
test(allowmissing([randstring() for i in 1:10]))
Int64
 raw:
  1.882 ms (8 allocations: 7.63 MiB)
 categorical:
  15.506 ms (1000004 allocations: 30.52 MiB)
String
 raw:
  18.031 ms (4 allocations: 448 bytes)
 categorical:
  28.813 ms (1000004 allocations: 30.52 MiB)
Union{Missing, Int64}
 raw:
  6.026 ms (4 allocations: 464 bytes)
 categorical:
  15.741 ms (1000004 allocations: 30.52 MiB)
Union{Missing, String}
 raw:
  20.340 ms (4 allocations: 448 bytes)
 categorical:
  30.785 ms (1000004 allocations: 30.52 MiB)

When aggregating use column selector and prefer integer, categorical, or pooled array grouping variable#

df = DataFrame(x=rand('a':'d', 10^7), y=1);
gdf = groupby(df, :x)

GroupedDataFrame with 4 groups based on key: x

First Group (2500733 rows): x = 'a': ASCII/Unicode U+0061 (category Ll: Letter, lowercase)
2500708 rows omitted
Rowxy
CharInt64
1a1
2a1
3a1
4a1
5a1
6a1
7a1
8a1
9a1
10a1
11a1
12a1
13a1
2500722a1
2500723a1
2500724a1
2500725a1
2500726a1
2500727a1
2500728a1
2500729a1
2500730a1
2500731a1
2500732a1
2500733a1

Last Group (2501232 rows): x = 'c': ASCII/Unicode U+0063 (category Ll: Letter, lowercase)
2501207 rows omitted
Rowxy
CharInt64
1c1
2c1
3c1
4c1
5c1
6c1
7c1
8c1
9c1
10c1
11c1
12c1
13c1
2501221c1
2501222c1
2501223c1
2501224c1
2501225c1
2501226c1
2501227c1
2501228c1
2501229c1
2501230c1
2501231c1
2501232c1

traditional syntax, slow

@btime combine(v -> sum(v.y), $gdf)
  17.042 ms (332 allocations: 19.09 MiB)
4×2 DataFrame
Rowxx1
CharInt64
1a2500733
2d2497020
3b2501015
4c2501232

use column selector

@btime combine($gdf, :y => sum)
  6.893 ms (198 allocations: 10.14 KiB)
4×2 DataFrame
Rowxy_sum
CharInt64
1a2500733
2d2497020
3b2501015
4c2501232
transform!(df, :x => categorical => :x);
gdf = groupby(df, :x)

GroupedDataFrame with 4 groups based on key: x

First Group (2500733 rows): x = CategoricalArrays.CategoricalValue{Char, UInt32} 'a'
2500708 rows omitted
Rowxy
Cat…Int64
1a1
2a1
3a1
4a1
5a1
6a1
7a1
8a1
9a1
10a1
11a1
12a1
13a1
2500722a1
2500723a1
2500724a1
2500725a1
2500726a1
2500727a1
2500728a1
2500729a1
2500730a1
2500731a1
2500732a1
2500733a1

Last Group (2497020 rows): x = CategoricalArrays.CategoricalValue{Char, UInt32} 'd'
2496995 rows omitted
Rowxy
Cat…Int64
1d1
2d1
3d1
4d1
5d1
6d1
7d1
8d1
9d1
10d1
11d1
12d1
13d1
2497009d1
2497010d1
2497011d1
2497012d1
2497013d1
2497014d1
2497015d1
2497016d1
2497017d1
2497018d1
2497019d1
2497020d1
@btime combine($gdf, :y => sum)
  6.834 ms (206 allocations: 10.62 KiB)
4×2 DataFrame
Rowxy_sum
Cat…Int64
1a2500733
2b2501015
3c2501232
4d2497020
transform!(df, :x => PooledArray{Char} => :x)
10000000×2 DataFrame
9999975 rows omitted
Rowxy
CharInt64
1a1
2a1
3d1
4d1
5d1
6b1
7d1
8a1
9a1
10d1
11b1
12b1
13c1
9999989d1
9999990b1
9999991c1
9999992c1
9999993a1
9999994b1
9999995b1
9999996d1
9999997d1
9999998b1
9999999a1
10000000c1
gdf = groupby(df, :x)

GroupedDataFrame with 4 groups based on key: x

First Group (2500733 rows): x = 'a': ASCII/Unicode U+0061 (category Ll: Letter, lowercase)
2500708 rows omitted
Rowxy
CharInt64
1a1
2a1
3a1
4a1
5a1
6a1
7a1
8a1
9a1
10a1
11a1
12a1
13a1
2500722a1
2500723a1
2500724a1
2500725a1
2500726a1
2500727a1
2500728a1
2500729a1
2500730a1
2500731a1
2500732a1
2500733a1

Last Group (2501232 rows): x = 'c': ASCII/Unicode U+0063 (category Ll: Letter, lowercase)
2501207 rows omitted
Rowxy
CharInt64
1c1
2c1
3c1
4c1
5c1
6c1
7c1
8c1
9c1
10c1
11c1
12c1
13c1
2501221c1
2501222c1
2501223c1
2501224c1
2501225c1
2501226c1
2501227c1
2501228c1
2501229c1
2501230c1
2501231c1
2501232c1
@btime combine($gdf, :y => sum)
  6.892 ms (200 allocations: 10.20 KiB)
4×2 DataFrame
Rowxy_sum
CharInt64
1a2500733
2d2497020
3b2501015
4c2501232

Use views instead of materializing a new DataFrame#

x = DataFrame(rand(100, 1000), :auto)
@btime $x[1:1, :]
  178.012 μs (3993 allocations: 159.07 KiB)
1×1000 DataFrame
900 columns omitted
Rowx1x2x3x4x5x6x7x8x9x10x11x12x13x14x15x16x17x18x19x20x21x22x23x24x25x26x27x28x29x30x31x32x33x34x35x36x37x38x39x40x41x42x43x44x45x46x47x48x49x50x51x52x53x54x55x56x57x58x59x60x61x62x63x64x65x66x67x68x69x70x71x72x73x74x75x76x77x78x79x80x81x82x83x84x85x86x87x88x89x90x91x92x93x94x95x96x97x98x99x100
Float64Float64Float64Float64Float64Float64Float64Float64Float64Float64Float64Float64Float64Float64Float64Float64Float64Float64Float64Float64Float64Float64Float64Float64Float64Float64Float64Float64Float64Float64Float64Float64Float64Float64Float64Float64Float64Float64Float64Float64Float64Float64Float64Float64Float64Float64Float64Float64Float64Float64Float64Float64Float64Float64Float64Float64Float64Float64Float64Float64Float64Float64Float64Float64Float64Float64Float64Float64Float64Float64Float64Float64Float64Float64Float64Float64Float64Float64Float64Float64Float64Float64Float64Float64Float64Float64Float64Float64Float64Float64Float64Float64Float64Float64Float64Float64Float64Float64Float64Float64
10.427890.9673590.9189730.9064070.6010490.1414970.2695240.3625110.3027670.492870.9037980.1854480.9103870.2927950.4202750.8557910.6655960.7109550.5960220.3996650.4191890.5410560.8391930.1348960.5786520.4800680.1291790.2778990.2554730.9504980.911130.8998860.4783080.649950.9001010.7231880.6778910.3117350.3992260.2280160.2754720.4499180.001461140.7344310.7276050.8383240.3974140.4159770.4319870.8226080.9833840.8139280.7471250.7209620.8783770.3985020.525170.9056480.7214290.9138150.385450.1750320.7514910.7346840.9494560.7655810.7304520.7101180.5288390.8420540.3245760.09098110.4378770.6632870.4413920.06408680.4089510.04062730.484040.9654890.3245050.06092650.2050140.7300640.4568850.6588360.7960330.7254530.3140030.07942240.3723230.4054370.6770530.270650.9931290.9458710.2954120.4985310.8426250.934196
@btime $x[1, :]
  17.889 ns (0 allocations: 0 bytes)
DataFrameRow (1000 columns)
900 columns omitted
Rowx1x2x3x4x5x6x7x8x9x10x11x12x13x14x15x16x17x18x19x20x21x22x23x24x25x26x27x28x29x30x31x32x33x34x35x36x37x38x39x40x41x42x43x44x45x46x47x48x49x50x51x52x53x54x55x56x57x58x59x60x61x62x63x64x65x66x67x68x69x70x71x72x73x74x75x76x77x78x79x80x81x82x83x84x85x86x87x88x89x90x91x92x93x94x95x96x97x98x99x100
Float64Float64Float64Float64Float64Float64Float64Float64Float64Float64Float64Float64Float64Float64Float64Float64Float64Float64Float64Float64Float64Float64Float64Float64Float64Float64Float64Float64Float64Float64Float64Float64Float64Float64Float64Float64Float64Float64Float64Float64Float64Float64Float64Float64Float64Float64Float64Float64Float64Float64Float64Float64Float64Float64Float64Float64Float64Float64Float64Float64Float64Float64Float64Float64Float64Float64Float64Float64Float64Float64Float64Float64Float64Float64Float64Float64Float64Float64Float64Float64Float64Float64Float64Float64Float64Float64Float64Float64Float64Float64Float64Float64Float64Float64Float64Float64Float64Float64Float64Float64
10.427890.9673590.9189730.9064070.6010490.1414970.2695240.3625110.3027670.492870.9037980.1854480.9103870.2927950.4202750.8557910.6655960.7109550.5960220.3996650.4191890.5410560.8391930.1348960.5786520.4800680.1291790.2778990.2554730.9504980.911130.8998860.4783080.649950.9001010.7231880.6778910.3117350.3992260.2280160.2754720.4499180.001461140.7344310.7276050.8383240.3974140.4159770.4319870.8226080.9833840.8139280.7471250.7209620.8783770.3985020.525170.9056480.7214290.9138150.385450.1750320.7514910.7346840.9494560.7655810.7304520.7101180.5288390.8420540.3245760.09098110.4378770.6632870.4413920.06408680.4089510.04062730.484040.9654890.3245050.06092650.2050140.7300640.4568850.6588360.7960330.7254530.3140030.07942240.3723230.4054370.6770530.270650.9931290.9458710.2954120.4985310.8426250.934196
@btime view($x, 1:1, :)
  17.889 ns (0 allocations: 0 bytes)
1×1000 SubDataFrame
900 columns omitted
Rowx1x2x3x4x5x6x7x8x9x10x11x12x13x14x15x16x17x18x19x20x21x22x23x24x25x26x27x28x29x30x31x32x33x34x35x36x37x38x39x40x41x42x43x44x45x46x47x48x49x50x51x52x53x54x55x56x57x58x59x60x61x62x63x64x65x66x67x68x69x70x71x72x73x74x75x76x77x78x79x80x81x82x83x84x85x86x87x88x89x90x91x92x93x94x95x96x97x98x99x100
Float64Float64Float64Float64Float64Float64Float64Float64Float64Float64Float64Float64Float64Float64Float64Float64Float64Float64Float64Float64Float64Float64Float64Float64Float64Float64Float64Float64Float64Float64Float64Float64Float64Float64Float64Float64Float64Float64Float64Float64Float64Float64Float64Float64Float64Float64Float64Float64Float64Float64Float64Float64Float64Float64Float64Float64Float64Float64Float64Float64Float64Float64Float64Float64Float64Float64Float64Float64Float64Float64Float64Float64Float64Float64Float64Float64Float64Float64Float64Float64Float64Float64Float64Float64Float64Float64Float64Float64Float64Float64Float64Float64Float64Float64Float64Float64Float64Float64Float64Float64
10.427890.9673590.9189730.9064070.6010490.1414970.2695240.3625110.3027670.492870.9037980.1854480.9103870.2927950.4202750.8557910.6655960.7109550.5960220.3996650.4191890.5410560.8391930.1348960.5786520.4800680.1291790.2778990.2554730.9504980.911130.8998860.4783080.649950.9001010.7231880.6778910.3117350.3992260.2280160.2754720.4499180.001461140.7344310.7276050.8383240.3974140.4159770.4319870.8226080.9833840.8139280.7471250.7209620.8783770.3985020.525170.9056480.7214290.9138150.385450.1750320.7514910.7346840.9494560.7655810.7304520.7101180.5288390.8420540.3245760.09098110.4378770.6632870.4413920.06408680.4089510.04062730.484040.9654890.3245050.06092650.2050140.7300640.4568850.6588360.7960330.7254530.3140030.07942240.3723230.4054370.6770530.270650.9931290.9458710.2954120.4985310.8426250.934196
@btime $x[1:1, 1:20]
  3.769 μs (70 allocations: 3.09 KiB)
1×20 DataFrame
Rowx1x2x3x4x5x6x7x8x9x10x11x12x13x14x15x16x17x18x19x20
Float64Float64Float64Float64Float64Float64Float64Float64Float64Float64Float64Float64Float64Float64Float64Float64Float64Float64Float64Float64
10.427890.9673590.9189730.9064070.6010490.1414970.2695240.3625110.3027670.492870.9037980.1854480.9103870.2927950.4202750.8557910.6655960.7109550.5960220.399665
@btime $x[1, 1:20]
  18.499 ns (0 allocations: 0 bytes)
DataFrameRow (20 columns)
Rowx1x2x3x4x5x6x7x8x9x10x11x12x13x14x15x16x17x18x19x20
Float64Float64Float64Float64Float64Float64Float64Float64Float64Float64Float64Float64Float64Float64Float64Float64Float64Float64Float64Float64
10.427890.9673590.9189730.9064070.6010490.1414970.2695240.3625110.3027670.492870.9037980.1854480.9103870.2927950.4202750.8557910.6655960.7109550.5960220.399665
@btime view($x, 1:1, 1:20)
  18.499 ns (0 allocations: 0 bytes)
1×20 SubDataFrame
Rowx1x2x3x4x5x6x7x8x9x10x11x12x13x14x15x16x17x18x19x20
Float64Float64Float64Float64Float64Float64Float64Float64Float64Float64Float64Float64Float64Float64Float64Float64Float64Float64Float64Float64
10.427890.9673590.9189730.9064070.6010490.1414970.2695240.3625110.3027670.492870.9037980.1854480.9103870.2927950.4202750.8557910.6655960.7109550.5960220.399665

This notebook was generated using Literate.jl.