# Efficiently filter rows while reading very large CSV-ish file

**URL:** <https://discourse.julialang.org/t/efficiently-filter-rows-while-reading-very-large-csv-ish-file/118157>\
**Category:** Data\
**Created:** [August 13, 2024, 10:07pm UTC](https://discourse.julialang.org/t/efficiently-filter-rows-while-reading-very-large-csv-ish-file/118157 "2024-08-13T22:07:38Z")\
**Posts on this page:** 6\
**Page:** 1

<div class="post-metadata">

**Author:** ![evanfields](https://sea2.discourse-cdn.com/julialang/user_avatar/discourse.julialang.org/evanfields/32/1744_2.png) [@evanfields](https://discourse.julialang.org/u/evanfields)\
**Post date:** [August 13, 2024, 10:07pm UTC](https://discourse.julialang.org/t/efficiently-filter-rows-while-reading-very-large-csv-ish-file/118157/1 "2024-08-13T22:07:38Z")

</div>

I have a text file representing k-mer counts. It’s order of magnitude a billion lines long, 14 gigabytes gzipped. It looks like so (4 early rows shown by `zless`):

```julia
AAAAAAAAAAAAAAAAAAAAGATTTTAAATATGCAAATTT 6 1 3 0 2 1 2 1 0 0 1
AAAAAAAAAAAAAAAAAAAAGTTAAAAAAAAAAAAAAAAA 0 2 1 1 0 0 0 0 0 0 1
AAAAAAAAAAAAAAAAAAAAGTTAAAAAAAAAGTTAAAAA 1 5 2 0 10 0 0 0 6 0 0
AAAAAAAAAAAAAAAAAAAAGTTCAGGTTGTTAAAAAAAC 0 1 9 0 46 0 3 0 16 0 0

```

My goal is to efficiently create a DataFrame from the small fraction (~1%) of rows that pass some boolean filter. From the `CSV.jl` docs I thought `CSV.Rows` would be an efficient way to iterate row-by-row, seeing each row exactly once and collecting the rows to keep.

But I can’t get even a single line out of my big file:  
`CSV.Rows("counts_big.txt.gz", header = false, limit = 1, delim = ' ')`  
Ran for several minutes until I got a MacOS popup warning that my hard drive was full and I killed it. (Iterating over a 10,000 line gzipped subset works just fine.)

Am I misusing `CSV.Rows`, or is another tool more appropriate here?

Thanks in advance for the help!

---

<div class="post-metadata">

**Author:** ![nhz2](https://sea2.discourse-cdn.com/julialang/user_avatar/discourse.julialang.org/nhz2/32/44428_2.png) [@nhz2](https://discourse.julialang.org/u/nhz2)\
**Post date:** [August 13, 2024, 10:23pm UTC](https://discourse.julialang.org/t/efficiently-filter-rows-while-reading-very-large-csv-ish-file/118157/2 "2024-08-13T22:23:55Z")

</div>

You could try using [GitHub - JuliaIO/CodecZlib.jl: zlib codecs for TranscodingStreams.jl.](https://github.com/JuliaIO/CodecZlib.jl)

```julia
stream = GzipDecompressorStream(open("counts_big.txt.gz";read=true))
try
    for line in eachline(stream)
        # Do something with the line, 
        # like filtering and pushing to some vector.
    end
finally
    close(stream)
end

```

---

<div class="post-metadata">

**Author:** ![evanfields](https://sea2.discourse-cdn.com/julialang/user_avatar/discourse.julialang.org/evanfields/32/1744_2.png) [@evanfields](https://discourse.julialang.org/u/evanfields)\
**Post date:** [August 14, 2024, 1:47pm UTC](https://discourse.julialang.org/t/efficiently-filter-rows-while-reading-very-large-csv-ish-file/118157/3 "2024-08-14T13:47:33Z")

</div>

Yeah, that may be the right answer. I think CSV.jl uses CodecZlib under the hood to read gzip compressed files, but I was hoping to leverage CSV.jl’s existing infrastructure for parsing, type converting, efficient memory management, etc.

---

<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:** [August 14, 2024, 2:41pm UTC](https://discourse.julialang.org/t/efficiently-filter-rows-while-reading-very-large-csv-ish-file/118157/4 "2024-08-14T14:41:35Z")

</div>

Have looked at [this solution](https://discourse.julialang.org/t/reading-a-few-rows-from-a-big-csv-file/68611/16)?

---

<div class="post-metadata">

**Author:** ![evanfields](https://sea2.discourse-cdn.com/julialang/user_avatar/discourse.julialang.org/evanfields/32/1744_2.png) [@evanfields](https://discourse.julialang.org/u/evanfields)\
**Post date:** [August 14, 2024, 3:40pm UTC](https://discourse.julialang.org/t/efficiently-filter-rows-while-reading-very-large-csv-ish-file/118157/5 "2024-08-14T15:40:00Z")

</div>

That’s the flavor of what I’ve tried, but that linked solution is operating on an uncompressed file, whereas I’m not able to read even a single line from my big gzip-compressed file with CSV.Rows.

---

<div class="post-metadata">

**Author:** ![era127](https://avatars.discourse-cdn.com/v4/letter/e/eb8c5e/32.png) [@era127](https://discourse.julialang.org/u/era127)\
**Post date:** [August 14, 2024, 8:04pm UTC](https://discourse.julialang.org/t/efficiently-filter-rows-while-reading-very-large-csv-ish-file/118157/7 "2024-08-14T20:04:37Z")

</div>

Maybe this will work for you if you’re able to add the Where condition. If you want to sample 1% of the data you can add `USING SAMPLE 1%` to the end of the query.

```Julia
using DuckDB
db = DuckDB.DB()
x = DuckDB.query(db, "select * from read_csv('text.csv.gz', compression='gzip', header=F, sep=' ') where ???")

```