# CSV : problem to write big dataframes
**URL:** https://discourse.julialang.org/t/csv-problem-to-write-big-dataframes/17343
**Category:** Data
**Tags:** csv
**Created:** [November 9, 2018, 3:17pm UTC](https://discourse.julialang.org/t/csv-problem-to-write-big-dataframes/17343 "2018-11-09T15:17:39Z")
**Posts on this page:** 20
**Page:** 1
### Author:  [@Fred](https://discourse.julialang.org/u/Fred)
#### Post date: [November 9, 2018, 3:17pm UTC](https://discourse.julialang.org/t/csv-problem-to-write-big-dataframes/17343/1 "2018-11-09T15:17:39Z")
Hi,
I have to read and write some big dataframes (100-500Mo). For a 80 Mo DataFrame, I need 220 sec to write it. And for a 500 Mo dataframe , after one hour waiting I gave up…
I use the following code :
```julia
julia> using CSV
julia> using DataFrames
julia> M = CSV.read("test.tsv"; delim='\t', header=true)
13893×1001 DataFrame. Omitted printing of 992 columns
julia> @time CSV.write("df.csv", M ; delim ='\t')
220.218516 seconds (13.83 M allocations: 880.227 MiB, 0.34% gc time)
"df.csv"
```
[test.tsv](https://www.transfernow.net/113dg604g8df)
---
### Author:  [@quinnj](https://discourse.julialang.org/u/quinnj)
#### Post date: [November 9, 2018, 3:25pm UTC](https://discourse.julialang.org/t/csv-problem-to-write-big-dataframes/17343/2 "2018-11-09T15:25:46Z")
Hmmmm, that doesn’t seem great; is there any way you can share a sample of the DataFrame or at least the schema? I can try to figure out what’s causing the bottleneck.
---
### Author:  [@tanhevg](https://discourse.julialang.org/u/tanhevg)
#### Post date: [November 9, 2018, 3:54pm UTC](https://discourse.julialang.org/t/csv-problem-to-write-big-dataframes/17343/3 "2018-11-09T15:54:09Z")
What version of Julia are you using? I think `CSV` is being phased out in favor of `DelimitedFiles`. On my machine, using the file you have attached, I get:
```julia
julia> versioninfo()
Julia Version 1.0.0
Commit 5d4eaca0c9 (2018-08-08 20:58 UTC)
Platform Info:
OS: Linux (x86_64-pc-linux-gnu)
CPU: Intel(R) Xeon(R) CPU E5-2640 0 @ 2.50GHz
WORD_SIZE: 64
LIBM: libopenlibm
LLVM: libLLVM-6.0.0 (ORCJIT, sandybridge)
Environment:
JULIA = /project/soft/linux64/src/julia-1.0.0
julia> using DelimitedFiles
julia> @time x = readdlm("test.tsv");
83.725932 seconds (396.76 M allocations: 9.211 GiB, 28.83% gc time)
julia> @time writedlm("test1.tsv", x)
5.158386 seconds (1.54 M allocations: 152.232 MiB)
```
---
### Author:  [@nalimilan](https://discourse.julialang.org/u/nalimilan)
#### Post date: [November 9, 2018, 4:04pm UTC](https://discourse.julialang.org/t/csv-problem-to-write-big-dataframes/17343/4 "2018-11-09T16:04:25Z")
> [@tanhevg](#):
>
> I think `CSV` is being phased out in favor of `DelimitedFiles` .
Not at all, DelimitedFiles is just the same `readdlm` function which was provided by Julia Base in previous versions. It’s intended mainly at reading homogeneous arrays. CSV.jl is a more powerful package which should be used when reading tabular data (like data frames).
---
### Author:  [@carstenbauer](https://discourse.julialang.org/u/carstenbauer)
#### Post date: [November 9, 2018, 4:07pm UTC](https://discourse.julialang.org/t/csv-problem-to-write-big-dataframes/17343/5 "2018-11-09T16:07:59Z")
Note that there is also the (AFAIU) independent [CSVFiles.jl](https://github.com/queryverse/CSVFiles.jl).
---
### Author:  [@Fred](https://discourse.julialang.org/u/Fred)
#### Post date: [November 9, 2018, 4:41pm UTC](https://discourse.julialang.org/t/csv-problem-to-write-big-dataframes/17343/6 "2018-11-09T16:41:28Z")
Sorry, I forgot to say that I have my system up to date : Julia 1.0.1 with latest packages.
@quinnj at the end of my post there is a link to try the 80 Mo dataframe 🙂 The 500 Mo dataframe has the same outline, it is just bigger.
Surprisingly reading large dataframes is very much faster than writing them.
---
### Author:  [@Fred](https://discourse.julialang.org/u/Fred)
#### Post date: [November 9, 2018, 5:12pm UTC](https://discourse.julialang.org/t/csv-problem-to-write-big-dataframes/17343/7 "2018-11-09T17:12:28Z")
@tanhevg, thank you, when I have simple csv files, I use DelimitedFiles because I do know that it is very fast. But, for DataFrames, I use CSV.
---
### Author:  [@Fred](https://discourse.julialang.org/u/Fred)
#### Post date: [November 10, 2018, 8:29am UTC](https://discourse.julialang.org/t/csv-problem-to-write-big-dataframes/17343/8 "2018-11-10T08:29:27Z")
Hi !
Today I did a test with the new Julia 1.0.2 and compared CSV and CSVFiles witht the “small” [test.tsv](https://www.transfernow.net/113dg604g8df) file. Both are very slow. Just for fun I did a test with Julia 0.6.2 and it is much faster, so there is a 25x times regression speed to write the same data. So for big dataframes, I have no choice than staying with the old Julia 0.6 version 🙄
with Julia 1.0.2
```julia
julia> using DataFrames
julia> using CSV
julia> @time M = CSV.read("test.tsv"; delim='\t', header=true)
11.363795 seconds (62.35 M allocations: 1.948 GiB, 7.47% gc time)
13893×1001 DataFrame. Omitted printing of 992 columns
julia> @time CSV.write("df.csv", M ; delim ='\t')
206.409265 seconds (13.81 M allocations: 879.979 MiB, 0.17% gc time)
"df.csv"
julia> using DataFrames
julia> using CSVFiles
@time M = DataFrame(load("test.tsv"))
20.100596 seconds (67.96 M allocations: 3.183 GiB, 4.49% gc time)
13893×1001 DataFrame. Omitted printing of 992 columns
@time save("df2.tsv", M)
220.085667 seconds (20.76 M allocations: 1.296 GiB, 0.24% gc time)
```
with Julia 0.6.2
```julia
_
_ _ _(_)_ | A fresh approach to technical computing
(_) | (_) (_) | Documentation: https://docs.julialang.org
_ _ _| |_ __ _ | Type "?help" for help.
| | | | | | |/ _` | |
| | |_| | | | (_| | | Version 0.6.2 (2017-12-13 18:08 UTC)
_/ |\ __'_|_|_|\__'_| | Official http://julialang.org/ release
|__/ | x86_64-pc-linux-gnu
julia> using DataFrames
julia> using CSV
WARNING: Method definition ==(Base.Nullable{S}, Base.Nullable{T}) in module Base at nullable.jl:238 overwritten in module NullableArrays at /home/fred/.julia/v0.6/NullableArrays/src/operators.jl:99.
julia> @time M = CSV.read("test.tsv"; delim='\t', header=true);
14.923997 seconds (48.01 M allocations: 1.289 GiB, 1.79% gc time)
julia> @time CSV.write("df.csv", M ; delim ='\t')
9.370022 seconds (82.44 M allocations: 1.690 GiB, 3.53% gc time)
```
---
### Author:  [@y4lu](https://discourse.julialang.org/u/y4lu)
#### Post date: [November 10, 2018, 11:23am UTC](https://discourse.julialang.org/t/csv-problem-to-write-big-dataframes/17343/9 "2018-11-10T11:23:22Z")
Looks like it could be missing or limited by the read buffer in readdlm, similar for the write buffer in CSV write.
Is this an OS thing, either buffering one or the other?
```julia
readf = open("test.tsv", "r")
readbuff = IOBuffer(read(readf)); ##timing point read1 (from Disk)
close(readf);
x = readdlm(readbuff); ##timing point read2 (from memory)
x2 = DataFrame(x);
writebuff = IOBuffer();
CSV.write(writebuff, x2; delim = '\t'); ##timing point write 1 (to memory)
writef = open("testout.tsv" , "w");
write(writef, take!(writebuff)); ## ##timing point write 2 (to disk)
close(writef);
```
@fred
couple of fixes, but either half should work on it’s own - eg writebuff and so on paired with a CSV.read table
Try using your `M` in place of the `x2` above
---
### Author:  [@Fred](https://discourse.julialang.org/u/Fred)
#### Post date: [November 10, 2018, 12:13pm UTC](https://discourse.julialang.org/t/csv-problem-to-write-big-dataframes/17343/10 "2018-11-10T12:13:28Z")
Thanks @y4lu. I did not manage to test your code except for reading the file.
```julia
julia> using DelimitedFiles
julia> readf = open("test.tsv")
IOStream()
julia> readbuff = IOBuffer(read(readf));
julia> close(readf);
julia> @time x = readdlm(readbuff);
6.580607 seconds (64.12 M allocations: 1.832 GiB, 8.59% gc time)
julia> writebuff = IOBuffer();
julia> using CSV
julia> @time CSV.write(writebuff, x; delim = '\t');
ERROR: ArgumentError: no default `Tables.rows` implementation for type: Array{Any,2}
julia> writef = open("testout.tsv");
ERROR: SystemError: opening file testout.tsv: Aucun fichier ou dossier de ce type
```
---
### Author:  [@davidanthoff](https://discourse.julialang.org/u/davidanthoff)
#### Post date: [November 11, 2018, 11:08pm UTC](https://discourse.julialang.org/t/csv-problem-to-write-big-dataframes/17343/11 "2018-11-11T23:08:35Z")
I wonder whether this is somehow related to the relatively large number of columns… Both CSVFiles.jl and CSV.jl will process this data as iterators of `NamedTuple` (i.e. as rows), and I’ve never been sure whether there is maybe some cut-off where a named tuple with a very large number of fields gets inefficient?
---
### Author:  [@Fred](https://discourse.julialang.org/u/Fred)
#### Post date: [November 12, 2018, 9:52am UTC](https://discourse.julialang.org/t/csv-problem-to-write-big-dataframes/17343/12 "2018-11-12T09:52:30Z")
To write my big DataFrames, I found this solution, it is not elegant but almots 10x faster :
```julia
julia> @time printDF(M)
write results
31.063968 seconds (129.47 M allocations: 42.875 GiB, 23.42% gc time)
function printDF(df)
println("write results")
open("df.csv", "w") do f
write(f, join(names(M), "\t") * "\n") # print header
for row in 1:size(df)[1]
line = df[row,1]
for col in 2:size(df)[2]
if typeof(df[row, col]) == String
c = df[row, col]
else
c = string(df[row, col])
end
line = line * "\t" * c # merge all cells of one row
end
write(f, "$line\n") # print df line by line
end
end
end
```
---
### Author:  [@xiaodai](https://discourse.julialang.org/u/xiaodai)
#### Post date: [November 12, 2018, 10:19am UTC](https://discourse.julialang.org/t/csv-problem-to-write-big-dataframes/17343/13 "2018-11-12T10:19:32Z")
@Fred I think the idea should that you didn’t have to do this to get speed. It should already be in a package. Maybe contribute this version back
---
### Author:  [@Fred](https://discourse.julialang.org/u/Fred)
#### Post date: [November 12, 2018, 10:23am UTC](https://discourse.julialang.org/t/csv-problem-to-write-big-dataframes/17343/14 "2018-11-12T10:23:53Z")
@xiaodai I completely agree with you but my solution is not good enough to be in a package, because it assumes that the dataframe as only 2 types : strings and floats (it even assumes that the row names are strings)
---
### Author:  [@nalimilan](https://discourse.julialang.org/u/nalimilan)
#### Post date: [November 12, 2018, 1:16pm UTC](https://discourse.julialang.org/t/csv-problem-to-write-big-dataframes/17343/15 "2018-11-12T13:16:51Z")
You can also do `show(f, MIME("text/csv"), df)`. This calls a simple function similar to the custom one you showed above.
---
### Author:  [@Juan](https://discourse.julialang.org/u/Juan)
#### Post date: [November 19, 2018, 12:05pm UTC](https://discourse.julialang.org/t/csv-problem-to-write-big-dataframes/17343/16 "2018-11-19T12:05:52Z")
Finally, what is the suggested package/function to read and write large datasets containing tabular data?
---
### Author:  [@Fred](https://discourse.julialang.org/u/Fred)
#### Post date: [November 19, 2018, 1:01pm UTC](https://discourse.julialang.org/t/csv-problem-to-write-big-dataframes/17343/17 "2018-11-19T13:01:11Z")
@Juan read is not a problem, but write can be too long (more than one hour in my case) if you have many columns. So the solution is to use a custom function such as mine or @nalimilan.
If you don’t need DataFrames, I recommend you to use readdlm / writedlm, it is very fast.
```julia
julia> using DelimitedFiles
julia> open("delim_file.txt", "w") do io
writedlm(io, [x y])
end
julia> readdlm("delim_file.txt", '\t', Int, '\n')
```
---
### Author:  [@Juan](https://discourse.julialang.org/u/Juan)
#### Post date: [November 19, 2018, 1:43pm UTC](https://discourse.julialang.org/t/csv-problem-to-write-big-dataframes/17343/18 "2018-11-19T13:43:17Z")
It’s strange, on R’s data.table is the opposite, it needs much more time to read because it first needs to figure out the structure and what type of data are contained on each column and it needs to deal with missings.
---
### Author:  [@atiyabzafar](https://discourse.julialang.org/u/atiyabzafar)
#### Post date: [May 29, 2023, 10:39am UTC](https://discourse.julialang.org/t/csv-problem-to-write-big-dataframes/17343/19 "2023-05-29T10:39:20Z")
Hey,
This is a niche problem that not many would face. But I am facing this same problem. I was using CSV.jl to save dataframes and it was going fine. My scientific simulation has huge dataframes. (A Million rows) I was able to save because each element was not that big. But my latest simulation made a dataframe that was 3 GB in the RAM. CSV.write just was not able to save it and I waited for a couple of hours.
i have already optimised my data and I need all of it to do the next step.
I was wondering if there is any way to speed this up or any new library that would be able to reduce my time.
I do know the alternative way would be to write the row/data as it is created. But that would involve me writing the code again and I would like to have a shorter way.
Thanks.
---
### Author:  [@Tamas\_Papp](https://discourse.julialang.org/u/Tamas_Papp)
#### Post date: [May 29, 2023, 11:43am UTC](https://discourse.julialang.org/t/csv-problem-to-write-big-dataframes/17343/20 "2023-05-29T11:43:27Z")
Please make an MWE that reproduces the problem, and ideally report it as an issue in the CSV repository. I cannot reproduce it with a simple example, eg
```julia
using CSV, DataFrames
N = 10^8
df = DataFrame(; a = rand(Int, N), b = rand(Float64, N), c = rand('a':'z', N))
dst = "/tmp/data.csv"
@time CSV.write(dst, df)
@show filesize(dst) / 2^30 # file size in Gb
```
takes 45s on my laptop, creating an almost 4GB file (using an SSD). I find this reasonable.
> [@Please read: make it easier to help you](https://discourse.julialang.org/t/please-read-make-it-easier-to-help-you/14757):
>
> Welcome to the Julia Discourse! We are enthusiastic about helping Julia programmers, both beginner and experienced. This public service announcement (PSA) outlines best practices when asking for help. Following these points makes it easier for us to help you and more likely you’ll get a prompt, useful answer. Keywords are highlighted to make it easier to refer to specific points. Choose a descriptive title that captures the key part of your question, eg “plots with multiple axes” instead of …
[Next page](https://discourse.julialang.org/t/csv-problem-to-write-big-dataframes/17343.md?page=2)