# Performance Report: Effect of Reading CSV file on Mergeing two DataFrames

**URL:** <https://discourse.julialang.org/t/performance-report-effect-of-reading-csv-file-on-mergeing-two-dataframes/106304>\
**Category:** Performance\
**Tags:** question, dataframes, csv\
**Created:** [November 16, 2023, 9:11am UTC](https://discourse.julialang.org/t/performance-report-effect-of-reading-csv-file-on-mergeing-two-dataframes/106304 "2023-11-16T09:11:15Z")\
**Posts on this page:** 19\
**Page:** 1

<div class="post-metadata">

**Author:** ![Sourish](https://sea2.discourse-cdn.com/julialang/user_avatar/discourse.julialang.org/sourish/32/34156_2.png) [@Sourish](https://discourse.julialang.org/u/Sourish)\
**Post date:** [November 16, 2023, 9:11am UTC](https://discourse.julialang.org/t/performance-report-effect-of-reading-csv-file-on-mergeing-two-dataframes/106304/1 "2023-11-16T09:11:15Z")

</div>

Hi

I was trying to merge two data frames - but I realised how we read the csv files - that is going to have a bearing on the same `merge` function.

```julia
using DataFrames, CSV, FileIO, CSVFiles, PyCall, BenchmarkTools
diamonds1=load("diamonds1.csv", spacedelim=false, header_exists=true) |> DataFrame;
diamonds2=load("diamonds2.csv", spacedelim=false, header_exists=true) |> DataFrame;

```

```julia
first(diamonds2,6)

6×5 DataFrame
Row	ID	price	x	y	z
Int64	Int64	Float64	Float64	Float64
1	1	326	3.95	3.98	2.43
2	2	326	3.89	3.84	2.31
3	3	327	4.05	4.07	2.31
4	4	334	4.2 4.23	2.63
5	5	335	4.34	4.35	2.75
6	6	336	3.94	3.96	2.48

```

Now I read the same datasets using the `CSV.read`

```julia
diam1 = CSV.read("diamonds1.csv", DataFrame)
diam2 = CSV.read("diamonds2.csv", DataFrame);

```

Now I write a function to merge two data frames

```julia
function test_julia_merge(df1,df2)
    merged_df = outerjoin(df1, df2, on=:ID)
    return merged_df
end

```

```julia
@benchmark test_julia_merge(diamonds1,diamonds2) 
BenchmarkTools.Trial: 2336 samples with 1 evaluation.
 Range (min … max): 1.784 ms … 10.273 ms ┊ GC (min … max): 0.00% … 70.07%
 Time (median): 1.969 ms ┊ GC (median): 0.00%
 Time (mean ± σ): 2.138 ms ± 495.263 μs ┊ GC (mean ± σ): 7.97% ± 13.29%

   ▂▆▆█▇▇▅▃▃▂▁▁ ▁ ▂ ▁
  ██████████████▆▆▄▆▄▁▁▁▁▁▄▁▇▄█▆▆▇▅▅▆▆▁▇▇█▇████▇██▇█▇▇▅▇▆▆▇▆▆ █
  1.78 ms Histogram: log(frequency) by time 3.74 ms <

 Memory estimate: 5.74 MiB, allocs estimate: 381.

```

```julia
@benchmark test_julia_merge(diam1,diam2) 

BenchmarkTools.Trial: 960 samples with 1 evaluation.
 Range (min … max): 4.632 ms … 18.102 ms ┊ GC (min … max): 0.00% … 46.31%
 Time (median): 4.976 ms ┊ GC (median): 0.00%
 Time (mean ± σ): 5.208 ms ± 847.561 μs ┊ GC (mean ± σ): 2.19% ± 5.25%

     ▂▃█▄▁                                                     
  ▃▄▆█████▅▄▄▄▄▄▄▄▄▃▃▃▂▃▂▂▂▂▂▃▂▂▂▂▂▁▂▂▂▂▂▂▂▂▂▂▂▁▁▁▁▁▂▂▂▁▂▂▁▂▂ ▃
  4.63 ms Histogram: frequency by time 7.85 ms <

 Memory estimate: 5.13 MiB, allocs estimate: 409.

```

Clearly, `CSV.read` is affecting the performance of the `merge` significantly.

Then, I compared the performance of merge against `Python`. Wrote the code using `PyCall` on the same `notebook`.

```julia
# Measuring time for merging two data frames in Python:
py"""
import pandas as pd

df1 = pd.read_csv("diamonds1.csv")
df2 = pd.read_csv("diamonds2.csv")
    

# Merge DataFrames on the 'ID' column
def merge_df():
    return pd.merge(df1, df2, on='ID', how='outer')

"""
merge_py_df = py"merge_df"
res_py = @benchmark $merge_py_df()

```

```julia
BenchmarkTools.Trial: 1220 samples with 1 evaluation.
 Range (min … max): 3.751 ms … 28.986 ms ┊ GC (min … max): 0.00% … 0.00%
 Time (median): 3.995 ms ┊ GC (median): 0.00%
 Time (mean ± σ): 4.093 ms ± 783.180 μs ┊ GC (mean ± σ): 0.00% ± 0.00%

    ▂▆█▇▇▄▆▄▁                                                  
  ▃▇█████████▇▅▅▅▆▄▄▄▄▂▃▃▃▃▃▃▂▂▂▂▃▂▂▁▁▂▂▂▁▁▁▁▁▁▂▂▂▁▁▂▁▁▁▁▂▂▂▂ ▃
  3.75 ms Histogram: frequency by time 5.63 ms <

 Memory estimate: 128 bytes, allocs estimate: 2.

```

Clearly `Julia`’s performance is poorer than `Python` when we are using `CSV.read`.

Any thoughts on the same will be a great help.

Best regards,  
Sourish

---

<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 16, 2023, 9:18am UTC](https://discourse.julialang.org/t/performance-report-effect-of-reading-csv-file-on-mergeing-two-dataframes/106304/2 "2023-11-16T09:18:10Z")

</div>

I don’t think anyone will be able to give any useful advice without the data sets you are using (is it the usual [diamonds data](https://raw.githubusercontent.com/mwaskom/seaborn-data/master/diamonds.csv) used in Kaggle competitions?)

---

<div class="post-metadata">

**Author:** ![Sourish](https://sea2.discourse-cdn.com/julialang/user_avatar/discourse.julialang.org/sourish/32/34156_2.png) [@Sourish](https://discourse.julialang.org/u/Sourish)\
**Post date:** [November 16, 2023, 9:20am UTC](https://discourse.julialang.org/t/performance-report-effect-of-reading-csv-file-on-mergeing-two-dataframes/106304/3 "2023-11-16T09:20:47Z")

</div>

Yes - it is available in the `ggplot2` package in R and in `RDatasets.jl` as well.

---

<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 16, 2023, 9:34am UTC](https://discourse.julialang.org/t/performance-report-effect-of-reading-csv-file-on-mergeing-two-dataframes/106304/4 "2023-11-16T09:34:01Z")

</div>

Can you show how this is split into the two days sets you have? The link I posted is just one csv file holding the complete data set.

---

<div class="post-metadata">

**Author:** ![rafael.guerra](https://sea2.discourse-cdn.com/julialang/user_avatar/discourse.julialang.org/rafael.guerra/32/216610_2.png) [@rafael.guerra](https://discourse.julialang.org/u/rafael.guerra)\
**Post date:** [November 16, 2023, 9:50am UTC](https://discourse.julialang.org/t/performance-report-effect-of-reading-csv-file-on-mergeing-two-dataframes/106304/5 "2023-11-16T09:50:41Z")

</div>

What are the results when the variables are interpolated?

```julia
@benchmark test_julia_merge($diamonds1, $diamonds2)
@benchmark test_julia_merge($diam1, $diam2)

```

---

<div class="post-metadata">

**Author:** ![Sourish](https://sea2.discourse-cdn.com/julialang/user_avatar/discourse.julialang.org/sourish/32/34156_2.png) [@Sourish](https://discourse.julialang.org/u/Sourish)\
**Post date:** [November 16, 2023, 9:55am UTC](https://discourse.julialang.org/t/performance-report-effect-of-reading-csv-file-on-mergeing-two-dataframes/106304/6 "2023-11-16T09:55:27Z")

</div>

I split the dataset in the following way.

```julia
first(diam1,6)
6×7 DataFrame
Row	ID	carat	cut	color	clarity	depth	table
Int64	Float64	String15	String1	String7	Float64	Float64
1	1	0.23	Ideal	E	SI2	61.5	55.0
2	2	0.21	Premium	E	SI1	59.8	61.0
3	3	0.23	Good	E	VS1	56.9	65.0
4	4	0.29	Premium	I	VS2	62.4	58.0
5	5	0.31	Good	J	SI2	63.3	58.0
6	6	0.24	Very Good	J	VVS2	62.8	57.0

```

and

```julia
first(diam2,6)

6×5 DataFrame
Row	ID	price	x	y	z
Int64	Int64	Float64	Float64	Float64
1	1	326	3.95	3.98	2.43
2	2	326	3.89	3.84	2.31
3	3	327	4.05	4.07	2.31
4	4	334	4.2 4.23	2.63
5	5	335	4.34	4.35	2.75
6	6	336	3.94	3.96	2.48

```

---

<div class="post-metadata">

**Author:** ![Sourish](https://sea2.discourse-cdn.com/julialang/user_avatar/discourse.julialang.org/sourish/32/34156_2.png) [@Sourish](https://discourse.julialang.org/u/Sourish)\
**Post date:** [November 16, 2023, 9:57am UTC](https://discourse.julialang.org/t/performance-report-effect-of-reading-csv-file-on-mergeing-two-dataframes/106304/7 "2023-11-16T09:57:18Z")

</div>

Sorry - I am not sure what is your point !

---

<div class="post-metadata">

**Author:** ![rafael.guerra](https://sea2.discourse-cdn.com/julialang/user_avatar/discourse.julialang.org/rafael.guerra/32/216610_2.png) [@rafael.guerra](https://discourse.julialang.org/u/rafael.guerra)\
**Post date:** [November 16, 2023, 10:06am UTC](https://discourse.julialang.org/t/performance-report-effect-of-reading-csv-file-on-mergeing-two-dataframes/106304/8 "2023-11-16T10:06:01Z")

</div>

According to the BenchmarkTools.jl documentation, interpolation is recommended to avoid the problems of benchmarking with globals.

---

<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 16, 2023, 10:30am UTC](https://discourse.julialang.org/t/performance-report-effect-of-reading-csv-file-on-mergeing-two-dataframes/106304/9 "2023-11-16T10:30:51Z")

</div>

Rafael means that you should write `@btime test_julia_merge($diam1, $diam2)` to interpolate the global variables `diam1` and `diam2` into the benchmark. It doesn’t actually make a difference here.

What’s happening here is that `CSV.read` by default performs some optimizations when reading in the file - you can tell this from the column types:

```julia
julia> typeof.(eachcol(diamonds1))
7-element Vector{DataType}:
 Vector{Int64} (alias for Array{Int64, 1})
 Vector{Float64} (alias for Array{Float64, 1})
 Vector{String} (alias for Array{String, 1})
 Vector{String} (alias for Array{String, 1})
 Vector{String} (alias for Array{String, 1})
 Vector{Float64} (alias for Array{Float64, 1})
 Vector{Float64} (alias for Array{Float64, 1})

```

versus

```julia
julia> typeof.(eachcol(diam1))
7-element Vector{DataType}:
 Vector{Int64} (alias for Array{Int64, 1})
 Vector{Float64} (alias for Array{Float64, 1})
 PooledVector{String15, UInt32, Vector{UInt32}} (alias for PooledArrays.PooledArray{String15, UInt32, 1, Array{UInt32, 1}})
 PooledVector{String1, UInt32, Vector{UInt32}} (alias for PooledArrays.PooledArray{String1, UInt32, 1, Array{UInt32, 1}})
 PooledVector{String7, UInt32, Vector{UInt32}} (alias for PooledArrays.PooledArray{String7, UInt32, 1, Array{UInt32, 1}})
 Vector{Float64} (alias for Array{Float64, 1})
 Vector{Float64} (alias for Array{Float64, 1})

```

On my machine I see for the DataFrames produced by `CSVFiles`:

```julia
julia> @btime test_julia_merge($diamonds1, $diamonds2);
  2.555 ms (349 allocations: 5.74 MiB)

```

and indeed with the default options for `CSV.read`:

```julia
julia> @btime test_julia_merge($diam1, $diam2);
  6.472 ms (377 allocations: 5.13 MiB)

```

but when disabling pooling:

```julia
julia> diam1_nopool = CSV.read("diamonds1.csv", DataFrame; pool = false);

julia> @btime test_julia_merge($diam1_nopool, $diam2);
  2.541 ms (349 allocations: 5.95 MiB)

```

So it might be that your problem is too small to benefit from pooling. There’s enough of a chance here though that this is a missed optimization in DataFrames that I’ll ping @bkamins to see if this is expected.

EDIT: I’ll add a complete MWE which downloads the data and creates the two separate data sets and then reads them back in below if people want to play with this:

> **Full MWE**
>
> julia\> using BenchmarkTools, CSV, CSVFiles, DataFrames, Downloads
> 
> julia\> full\_table = CSV.read(Downloads.download(“[https://raw.githubusercontent.com/mwaskom/seaborn-data/master/diamonds.csv](https://raw.githubusercontent.com/mwaskom/seaborn-data/master/diamonds.csv)”), DataFrame);
> 
> julia\> full\_table.ID = 1:nrow(full\_table);
> 
> julia\> CSV.write(“diamonds1.csv”, select(full\_table, :ID, :carat, :cut, :color, :clarity, :depth, :table));
> 
> julia\> CSV.write(“diamonds2.csv”, select(full\_table, :ID, :price, :x, :y, :z));
> 
> julia\> diamonds1=load(“diamonds1.csv”, spacedelim=false, header\_exists=true) |\> DataFrame;
> 
> julia\> diamonds2=load(“diamonds2.csv”, spacedelim=false, header\_exists=true) |\> DataFrame;
> 
> julia\> diam1 = CSV.read(“diamonds1.csv”, DataFrame);
> 
> julia\> diam2 = CSV.read(“diamonds2.csv”, DataFrame);
> 
> julia\> function test\_julia\_merge(df1,df2)  
> merged\_df = outerjoin(df1, df2, on=:ID)  
> return merged\_df  
> end;
> 
> julia\> @btime test\_julia\_merge($diamonds1, diamonds2);  
> 2.702 ms (349 allocations: 5.74 MiB)
> 
> julia\> @btime test\_julia\_merge($diamonds1, $diamonds2);  
> 2.555 ms (349 allocations: 5.74 MiB)
> 
> julia\> @btime test\_julia\_merge($diam1, $diam2);  
> 6.472 ms (377 allocations: 5.13 MiB)

---

<div class="post-metadata">

**Author:** ![Sourish](https://sea2.discourse-cdn.com/julialang/user_avatar/discourse.julialang.org/sourish/32/34156_2.png) [@Sourish](https://discourse.julialang.org/u/Sourish)\
**Post date:** [November 16, 2023, 10:42am UTC](https://discourse.julialang.org/t/performance-report-effect-of-reading-csv-file-on-mergeing-two-dataframes/106304/10 "2023-11-16T10:42:01Z")

</div>

@nilshg Thanks. This solves the problem.

Best regards,  
Sourish

---

<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 16, 2023, 10:50am UTC](https://discourse.julialang.org/t/performance-report-effect-of-reading-csv-file-on-mergeing-two-dataframes/106304/11 "2023-11-16T10:50:12Z")

</div>

There’s some discussion in this epic issue:

> <https://github.com/JuliaData/DataFrames.jl/issues/2340>
>
> Here are the things I think joins can take advantage of to improve performance:
> …
> \* allow \`join\`s on \`GroupedDataFrame\`
> \* taking into account the cardinality of rows of joined tables
> \* being able to take information that the data frame is already sorted on \`on\` variable
> \* being able to take information that the \`on\` variable defines unique unique rows in one or both of the joined tables
> \* take into account if the columns are "pooled" (this already happens)
> \* maybe special casing for single-column of bitstype joins
> \* maybe special algorithms for concrete joins (like \`innerjoin\` which is probably most common)
> 
> Please comment on what you think or have implemented to move forward with this issue (also keeping in mind that in the long term we might want multi-threading here). Also we should think of memory footprint of the algorithms we use.

but understandably seems to mostly focus on the case in which the pooled vectors are actually the vectors being joined on. I’m actually a little surprised that there’s such a big effect of pooling but it maybe this is related to the fact that the join turns all column eltypes from into `T` into `Union{T, Missing}` which maybe is more costly for pooled vectors?

This also seems to be an issue of `outerjoin` specifically, with `leftjoin` (which seems more appropriate in this example, althouh I appreciate it’s probably not your real application), I get:

```julia
julia> @btime test_julia_merge2($diamonds1, $diamonds2);
  2.017 ms (332 allocations: 5.58 MiB)

julia> @btime test_julia_merge2($diam1, $diam2);
  1.722 ms (356 allocations: 4.96 MiB)

```

---

<div class="post-metadata">

**Author:** ![Sourish](https://sea2.discourse-cdn.com/julialang/user_avatar/discourse.julialang.org/sourish/32/34156_2.png) [@Sourish](https://discourse.julialang.org/u/Sourish)\
**Post date:** [November 16, 2023, 3:25pm UTC](https://discourse.julialang.org/t/performance-report-effect-of-reading-csv-file-on-mergeing-two-dataframes/106304/12 "2023-11-16T15:25:20Z")

</div>

okay - I see. make sense.

---

<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 16, 2023, 5:50pm UTC](https://discourse.julialang.org/t/performance-report-effect-of-reading-csv-file-on-mergeing-two-dataframes/106304/13 "2023-11-16T17:50:05Z")

</div>

Yes - I think pooling is the issue here. You can disable pooling in `CSV.read` if you do not need it.

---

<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 16, 2023, 6:58pm UTC](https://discourse.julialang.org/t/performance-report-effect-of-reading-csv-file-on-mergeing-two-dataframes/106304/14 "2023-11-16T18:58:07Z")

</div>

I’ve shown above that it is indeed the issue, the question is it’s this expected and if not can something be done about it? Seems like a bit of a footgun that the default optimizations in a standard data science workflow lead to a 3x regression in outerjoin.

---

<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 16, 2023, 9:06pm UTC](https://discourse.julialang.org/t/performance-report-effect-of-reading-csv-file-on-mergeing-two-dataframes/106304/15 "2023-11-16T21:06:03Z")

</div>

[pool kwarg documentation · Issue #1090 · JuliaData/CSV.jl · GitHub](https://github.com/JuliaData/CSV.jl/issues/1090) is related, so can you please comment there.

The main issue is that for e.g. `String1` pooling is not good most likely as it will use more memory and potentially be slower. For wide `StringX` types pooling will save memory.

---

<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 16, 2023, 10:27pm UTC](https://discourse.julialang.org/t/performance-report-effect-of-reading-csv-file-on-mergeing-two-dataframes/106304/16 "2023-11-16T22:27:00Z")

</div>

also related [Always keep missing in pool by bkamins · Pull Request #65 · JuliaData/PooledArrays.jl · GitHub](https://github.com/JuliaData/PooledArrays.jl/pull/65)

---

<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 17, 2023, 9:06am UTC](https://discourse.julialang.org/t/performance-report-effect-of-reading-csv-file-on-mergeing-two-dataframes/106304/17 "2023-11-17T09:06:23Z")

</div>

> **[Is vector pooling considered harmful?](https://bkamins.github.io/julialang/2023/11/17/pooling.html)**
>
> Introduction

---

<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 17, 2023, 9:16am UTC](https://discourse.julialang.org/t/performance-report-effect-of-reading-csv-file-on-mergeing-two-dataframes/106304/18 "2023-11-17T09:16:13Z")

</div>

So is it fair to say in summary that the performance hit is expected, people that want to do `outerjoin`s (or maybe other operations which have to change element types) should not use `PooledArray`s but given that `PooledArray`s are beneficial to most users in most situations we are happy to have it as the default in `CSV.File`?

---

<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 17, 2023, 4:37pm UTC](https://discourse.julialang.org/t/performance-report-effect-of-reading-csv-file-on-mergeing-two-dataframes/106304/19 "2023-11-17T16:37:33Z")

</div>

Roughly yes. But since the case of `outerjoin` is common in [Always keep missing in pool by bkamins · Pull Request #65 · JuliaData/PooledArrays.jl · GitHub](https://github.com/JuliaData/PooledArrays.jl/pull/65) I proposed to always technically keep the option of `missing` in the pool, so that doing “allowmissing” kind of operations would not lead to recalculation of pool.