# CSV read performance vs Pandas

**URL:** <https://discourse.julialang.org/t/csv-read-performance-vs-pandas/21667>\
**Category:** General Usage\
**Created:** [March 9, 2019, 8:27am UTC](https://discourse.julialang.org/t/csv-read-performance-vs-pandas/21667 "2019-03-09T08:27:10Z")\
**Posts on this page:** 10\
**Page:** 2

<div class="post-metadata">

**Author:** ![bicycle1885](https://sea2.discourse-cdn.com/julialang/user_avatar/discourse.julialang.org/bicycle1885/32/107_2.png) [@bicycle1885](https://discourse.julialang.org/u/bicycle1885)\
**Post date:** [March 25, 2019, 1:09am UTC](https://discourse.julialang.org/t/csv-read-performance-vs-pandas/21667/21 "2019-03-25T01:09:24Z")

</div>

> Are there any strategies that can further improve the the performance when multithreading comes around?

I think there are several ways to speed up reading. The current bottleneck of CSV reading is not in tokenization but in parsing. TableReader.jl parses independent columns one by one but this is obviously parallelizable (vertical parallelization). Or, first count the number of lines in a buffered chunk and then split it into smaller mini chunks for parallel tokenization and parsing (horizontal parallelization). I’ve not tried both yet, but I think these are feasible if Julia supports more sophisticated multithreading.

> Of course full specialization will result in faster code, but the compilation will slow it down!

Full specialization for a data scheme and a set of parser parameters may improve the performance. However, I think the effect would be modest because modern CPUs are enough smart to lessen the harm of unnecessary code blocks thanks to branch prediction and so on.

---

<div class="post-metadata">

**Author:** ![c42f](https://sea2.discourse-cdn.com/julialang/user_avatar/discourse.julialang.org/c42f/32/52842_2.png) [@c42f](https://discourse.julialang.org/u/c42f)\
**Post date:** [March 25, 2019, 3:22am UTC](https://discourse.julialang.org/t/csv-read-performance-vs-pandas/21667/22 "2019-03-25T03:22:24Z")

</div>

Have you considered horizontal parallelism with a heuristic for synchronizing rows _independently_ between chunks rather than a non parallel pre-scan? This might sound crazy, but I’ve had great success with this on binary stream data in the past.

The idea is that a worker task can read a chunk of character data from the middle of the file (independent of all other tasks), use a heuristic to synchronize the character stream with the record (ie, row) boundaries, and continue tokenizing / parsing from there.

Copying data from chunked outputs into the result arrays can be done in parallel as well, so there’s almost zero synchronization or serial processing involved.

The obvious downside is that you need code to save where the record boundaries were inferred for each chunk, check that they match between chunks and have a consistent fallback code path for difficult cases. As long as the heuristic works with high probability this isn’t a performance issue on average, but it’s potentially a fiddly and annoying code path to maintain. To get the most out of the parallelism you also need any compressed input stream to be seekable which generally requires extra complexity (for example gzip-\>dictzip).

I should say I don’t know if this will work well for CSV! I bring it up because it’s inherently parallel and has worked surprisingly well for me in the past.

---

<div class="post-metadata">

**Author:** ![xiaodai](https://sea2.discourse-cdn.com/julialang/user_avatar/discourse.julialang.org/xiaodai/32/15937_2.png) [@xiaodai](https://discourse.julialang.org/u/xiaodai)\
**Post date:** [March 25, 2019, 3:48am UTC](https://discourse.julialang.org/t/csv-read-performance-vs-pandas/21667/23 "2019-03-25T03:48:47Z")

</div>

> [@c42f](#):
>
> heuristic

every time I see this word I think “replace it with a critics using reinforcement learning techniques!”. Could be an interesting research project.

---

<div class="post-metadata">

**Author:** ![c42f](https://sea2.discourse-cdn.com/julialang/user_avatar/discourse.julialang.org/c42f/32/52842_2.png) [@c42f](https://discourse.julialang.org/u/c42f)\
**Post date:** [March 25, 2019, 3:55am UTC](https://discourse.julialang.org/t/csv-read-performance-vs-pandas/21667/24 "2019-03-25T03:55:20Z")

</div>

It’s probably fair to say a heuristic is an inference made with incomplete information (and hence an uncertain result). So yes you could replace a handcrafted heuristic with a learned model.

On that note you might find the following interesting:  
[https://arxiv.org/abs/1712.01208](https://arxiv.org/abs/1712.01208)

---

<div class="post-metadata">

**Author:** ![davidanthoff](https://sea2.discourse-cdn.com/julialang/user_avatar/discourse.julialang.org/davidanthoff/32/223493_2.png) [@davidanthoff](https://discourse.julialang.org/u/davidanthoff)\
**Post date:** [March 25, 2019, 3:59pm UTC](https://discourse.julialang.org/t/csv-read-performance-vs-pandas/21667/25 "2019-03-25T15:59:39Z")

</div>

I reran my entire benchmark suite with the latest version of all the CSV packages, you can see the results [here](https://www.queryverse.org/benchmarks/).

I should also say that I can replicate that [TableReader.jl](https://github.com/bicycle1885/TableReader.jl) is the fastest package for the `parking-citations.csv` file currently. That file in particular must trigger some code path in the packages that is not yet tested by my benchmark suite, and I’m currently trying to narrow it down what exactly that is. I have a suspicion, but it will require a bit more digging to really pin it down, and then I hope to add another test case to the benchmark suite that has that particular pattern in it.

I now also have some results for a “cold” read [here](https://vega.github.io/editor/#/url/vega-lite/N4KABGBEBuDGD2A7WBDALpAXGA2uCYoBBkAJuiloZAK4BOANlZAPQBGApsgBYC2KdANZ0OAZxoM0olrFHQRKUhzoA6WdEgBfADT5ikNHRSJRAM3h1eVPMWJFb+0wEtJy5uTQ1eK9Gg68ABzQwAF4QsAByAHcBXhoAiLAAMiSwJ0QlAA94UwAKHAiAKQknFDAAYQBlADUVACsGCO1I4oZSipqAMRcxesbmopKyquqAJQ5FZT6mlqGwABUUNgYOccnVBpnBtrKABWNyUWmB1vb5jky0fbpRDmPI3YBPNG4kMABBOjp4KK2nl7en2+UTAAQEKAYK36D2er0QYH2GRQoi2ozApgUpFR6MxoPBkI40IiaMxdAiAF1tB4vCowbBBCgAOYcACUAD4QgAGSB6Ww6Xl2AW2SCoBiwCToDjudA0qJOF6IFBhCKGGgcRIAfkiAHV5dwwAA5d5gaAQtUosDYCK6gE0YJGk1msQRSC6Bz6ZHMOUKlAAfWgyjYEJ57rAmiFlKFkFMKFgHAw2HsDkgwKoSfd0achNIXr1iv9geDbtDUDQjwCUuwkEQ8F46SLQv03AmSjoaYM8pWzBt+odpoY5q0jbDQv5ydEFdgaeH0dj8enJagqcTw8cWYYOarCAYoldq5IZYrzAspHrTGLi8gzfW7bQncrUAAxGAcmBt14TEPF+HQ2OMxOOCnFdLxjOME0IfcIBTH4F0XEhnGzZhnC7C84IMcsH2rWszz3OCSGvVtb3vZgAElEACO10R6RVeClH84Po39UPHSdYJAudwOAEV4AHXhEHbBCN2YYFd2adCjyrE8cM0P9LxEUQeIDdtRFFB8uMyZh0iUCsMi4DAZOY-9WOAvCoAYFBHjcbAbFMiB01syAuAQU9EEZNjbJIDSTI84VBM3UsnDrVzcJ84U7zQLsq1uBAMlEyC0KZRkREZSVmCCkLQoPDDmAARxoYxwvQJwlPipjSozbcLHczLM0Qqs6QZZkMsyqAVIhNTIBU5taOYVA-EZCxHgAJm5RjQrG2zZI8yB+CEZggzbcqICmvD7NMxzkHgFy3O80LIEyIbqr2vzmDvIK3MMnzIES5LUqrfgNMu6bD0wvKCvlIqSpasMnrw-ajquk6qzO9ILqWjMbo4FK-DS9Jmpql7cvyxBCrvL6Wom0yVvW2bBGEiQpXB7HFy4pyttB9svOoIGAvOrQdCgXHTqcekv1MyMHLJ7aAZLSBHgE9d-MgBqmSlMTwsiqBXVLbKqxrILg0x394qVghVcxsaprWsgKHbegmCrdgnL4ARhDECQpBkORMWUEXmQDG4nCQI51C0QyOwizD9npUWTWUUQnc-d25VIF4qBGzl3aZ4GLgwd2uYp3b9Cprj+zVKgAEZOWJqB+cTWqhPq2NGrFmWJKgeWcPF4jsEQCQGBzgxY4FuqoAdgOkDZ4hNfwDmoCDekkvgGgMi9bh5TF-BuMQZwduoPxLnbINbjaRBMLrUhSBQqAIScRl+KrFZTH08NNCAA). For those results I load the package with `using` in a new julia process, and then time the very first load of the file, so this includes JIT compile time etc.

Here are my thoughts on next steps for [TextParse.jl](https://github.com/JuliaComputing/TextParse.jl)/[CSVFiles.jl](https://github.com/queryverse/CSVFiles.jl) on the performance side of things:

- I think there is still generally a lot of room for improving simply the core parsing routines. They are very good at this point already, but there are _so_ many more things one could experiment with.
- I _really_ like the LRU string cache idea that @bicycle1885 is using in [TableReader.jl](https://github.com/bicycle1885/TableReader.jl), and I think that would actually be a very nice fit for the internal [TextParse.jl](https://github.com/JuliaComputing/TextParse.jl) structure, so I’ll probably investigate whether I can add that.
- Once PARTR is out, I plan to introduce multi-threading. The design I have in mind is essentially what @c42f outlined above, I think that is the right strategy for a CSV parser. One thing I really like about [TextParse.jl](https://github.com/JuliaComputing/TextParse.jl) is that it is written in an entirely functional, side-effect free style. Apart from the final vector for the results there is no global or mutable state to worry about!
- I am pretty convinced at this point that the whole idea of compiling schema specific parsing kernels was a great idea to test out, but ultimately is not the right strategy for many common scenarios. I have a design in my head that I think is a relatively small change, hopefully will get rid of most of the extra compile time, and I think will help _a lot_ with first use experience. The design I have in my mind will keep the compiled kernels around for some special cases where I think they make a lot of sense. This part is more a hypothesis at this point, I might be wrong about the whole approach, we’ll see 🙂

---

<div class="post-metadata">

**Author:** ![matthieu](https://avatars.discourse-cdn.com/v4/letter/m/da6949/32.png) [@matthieu](https://discourse.julialang.org/u/matthieu)\
**Post date:** [March 25, 2019, 6:27pm UTC](https://discourse.julialang.org/t/csv-read-performance-vs-pandas/21667/26 "2019-03-25T18:27:26Z")

</div>

These benchmarks look very useful for developers, but I am not sure I walk away with a clear picture of the relative performances of all these packages. Maybe it would be useful to benchmark these packages on a set of real world CSV from economics/finance/biology etc, all with approx 1,000,000 rows?

Another suggestion is to have the results of the cold read by default.

---

<div class="post-metadata">

**Author:** ![Juan](https://sea2.discourse-cdn.com/julialang/user_avatar/discourse.julialang.org/juan/32/7657_2.png) [@Juan](https://discourse.julialang.org/u/Juan)\
**Post date:** [March 25, 2019, 8:32pm UTC](https://discourse.julialang.org/t/csv-read-performance-vs-pandas/21667/27 "2019-03-25T20:32:04Z")

</div>

What about Arrow for Julia?

> **[GitHub - ExpandingMan/Arrow.jl: DEPRECATED in favor of...](https://github.com/ExpandingMan/Arrow.jl)**
>
> DEPRECATED in favor of \[JuliaData/Arrow.jl\](https://github.com/JuliaData/Arrow.jl) - GitHub - ExpandingMan/Arrow.jl: DEPRECATED in favor of \[JuliaData/Arrow.jl\](https://github.com/JuliaData/Arrow.jl)

and ASDF.jl

> **[GitHub - eschnett/ASDF.jl: A Julia implementation of the Advanced Scientific...](https://github.com/eschnett/ASDF.jl)**
>
> A Julia implementation of the Advanced Scientific Data Format (ASDF) - GitHub - eschnett/ASDF.jl: A Julia implementation of the Advanced Scientific Data Format (ASDF)

---

<div class="post-metadata">

**Author:** ![tk3369](https://sea2.discourse-cdn.com/julialang/user_avatar/discourse.julialang.org/tk3369/32/2824_2.png) [@tk3369](https://discourse.julialang.org/u/tk3369)\
**Post date:** [March 26, 2019, 1:46am UTC](https://discourse.julialang.org/t/csv-read-performance-vs-pandas/21667/28 "2019-03-26T01:46:18Z")

</div>

It would be nice to have a streaming csv-to-whatever converter, where the whatever format is something fast and typed properly.

Parquet would be nice since it plays well with the big data environment. Feather would be fine as well.

Once the file is converted (just once) then there’s no more struggling with CSV. If we think along this direction then ideally all published data sets should be converted to something more user friendly so we don’t have to deal with CSV oddities 😉

---

<div class="post-metadata">

**Author:** ![xiaodai](https://sea2.discourse-cdn.com/julialang/user_avatar/discourse.julialang.org/xiaodai/32/15937_2.png) [@xiaodai](https://discourse.julialang.org/u/xiaodai)\
**Post date:** [May 6, 2019, 2:35pm UTC](https://discourse.julialang.org/t/csv-read-performance-vs-pandas/21667/29 "2019-05-06T14:35:13Z")

</div>

> [@davidanthoff](#):
>
> I reran my entire benchmark suite with the latest version of all the CSV packages, you can see the results [here](https://www.queryverse.org/benchmarks/).

Looking forward to an update to CSV.jl 0.5 which seems to be quite a bit faster and more robust than before.

---

<div class="post-metadata">

**Author:** ![davidanthoff](https://sea2.discourse-cdn.com/julialang/user_avatar/discourse.julialang.org/davidanthoff/32/223493_2.png) [@davidanthoff](https://discourse.julialang.org/u/davidanthoff)\
**Post date:** [May 6, 2019, 2:40pm UTC](https://discourse.julialang.org/t/csv-read-performance-vs-pandas/21667/30 "2019-05-06T14:40:04Z")

</div>

Will do once I’m done with my current travels in a few days!

[Previous page](https://discourse.julialang.org/t/csv-read-performance-vs-pandas/21667.md?page=1)