CSV.read() return value

[quinnj]

I'd like to decide on the Julia structure that CSV.read() returns. Speak now or forever hold your peace (or write your own parser, i don't care). The current candidates are:

• Matrix{Any}: easy to allocate and work with, but we lose type information and header values
• Dict{String,Vector{T}}: column_name => column_values::Vector{T}, use sentinel values for nulls
• Dict{String,NullableArray{T}}: same as above, but we naturally represent nulls
• DataFrame with NullableArray columns: hook into DF, but hook into DF
• Tables.Table: this would be essentially an in-memory or on-disk SQLite database (I'll probably plan on supporting this one regardless, but maybe it could be the only option?)
• Any other recommendations out there?

I'm leaning towards Dict{String,NullableArray{T}} as it's the most straightforward

@johnmyleswhite @davidagold @StefanKarpinski @jiahao @RaviMohan

[jiahao]

How is Tables.Table represented?

cc @jakebolewski

[quinnj]

A Tables.Table is a somewhat opaque Julia struct to an underlying in-memory or on-disk SQLite database. It defines common "table"/"frame" like operations that actually generate SQL statements that get executed underneath (e.g. indexing columns/rows, boolean indexing, group by, sorting, etc.). The idea is for it to "feel" like a dataframe-like object with the full SQL table engine underneath.

I'm still working out the interface for using Julia functions on a Table type since it's entirely compatible through SQLite's UDF interface; just need to iron out how exactly a user does that.

[ScottPJones]

I thought I'd seen something about a NamedTuple package, I wonder if that might work well.

[RaviMohan]

In my data loading experiments, I used Union (Array{Nullable(Int64),1},Array{Nullable(Int64),1} .. ) essentially a union of Arrays of nullable . This is essentially the typed version of Pandas's basic datastucture and seems to work well enough.

That said, my focus was on solving the performance issues readdlm and Dataframe.read had (vs R and Pandas) when trying to load > 10 million rows, which is a different problem from the writing a generic CSV parser. And it works in that we can beat R's read.table handily and comes within a constant factor of R's data.table.fread. In a production grade system, inferring a column's type (vs declaring it) gets tricky, but is still doable .

I am experimenting with approaches to this that don't lose performance, and also with implementation of operators on the loaded dataset, which gets interesting in the presence of really large datasets and calls for query optimization etc. In short, I'm focused on loading and working with really large data sets .

Which is a different, if intersecting, problem from writing a generic CSV Parser, which is much more complex, with many gnarly special cases.

This might be blasphemy but I think the right approach might be to just choose a reasonable datastructure and go with it, and to test for if performance/memory problems and refactor as required.

I vote for Dict{String,NullableArray{T}} .

My only suggestion, fwiw, here is that you might want to do timing/memory checks with large (10 - 100 million row) data sets post implementation to confirm we are getting better performance than @jiahao found with the existing readdlm implementations ( JuliaLang/julia#10428 for details) .

I'm fairly sure that Dict{String,NullableArray{T}} will work . And worst case someone can write a converter to whatever other datastructure is required.

My 2 cents.

@johnmyleswhite @davidagold @StefanKarpinski @jiahao @quinnj

[ScottPJones]

I really don't like either of the Dict{String,...} approaches. It means that you have to be growing N different vectors (N == number of columns), as you read data, and what happens when there is no header.

[ViralBShah]

I really would love to have a DataFrame (not necessarily the current one, but whatever our new implementation will be) be what comes out, which is then easily composable across so many other packages.

[StefanKarpinski]

I think that the CSV reader should ideally be composable with different consumers that construct different data types. I.e. the parsing and type inference logic should go in one place while different consumers can provide different logic for constructing different data structures. Going even further, it should be possible to decouple the input format from this as well so that you can N data formats and M data types and not require N*M readers to handle all possible combinations. The tricky bit is getting the abstraction right to allow this flexibility without sacrificing performance.

[johnmyleswhite]

Will comment more on this later, but I started making an abstract CSV parser once in the past: https://github.com/johnmyleswhite/CSVReaders.jl

FWIW, I'm much more excited about Jacob's work than about finishing the one I started.

[ScottPJones]

Just looked at that, looks like a nice abstraction, would it make sense to fit Jacob's code into that framework, adding support for some of the stuff that Jacob described above?

[StefanKarpinski]

@johnmyleswhite, how do you feel about the idea of that abstraction, having given it a try? Are you meh on the idea of that decoupling in principle or just want a fresh start on the implementation and/or design?

[johnmyleswhite]

I like the idea as a goal to aspire to, but I'd have to work on it again to say whether it could ever be made to work. It's a use case where you need to be obsessive about inlining -- but I'm not still sure that's sufficient because some type information might not be available at the right points in time.

[quinnj]

FWIW, my Pipelines.jl idea is the same kind of I/O abstraction for hooking up arbitrary readers/writers. I've come to realize that it's certainly more of a "phase 2" kind of idea where a lot of the actual readers/writers still need a lot of development (hence my push on CSV, SQLite, ODBC, etc.).

I certainly think it's the right long-term idea, but for now, I'm more focused on doing a few connections between types (CSV=>Julia structure, CSV=>SQLite table, ODBC=>CSV, ODBC=SQLite, etc.).

For this issue, I'd just like to figure out a good Julia-side structure to return the contents of a CSV file. I think I'm leaning towards Dict{String,NullableVector{T}} or DataFrame with NullableVector{T} columns.

[johnmyleswhite]

I'd suggest OrderedDict{String,NullableVector{T}}. That retains more information about the input format, which might help in debugging.

[ScottPJones]

@quinnj @johnmyleswhite Are you not concerned about 1) what to do with headerless CSV files and 2) performance issues of having to expand each column separately, when a new row is added?
I've frequently had to deal with data with hundreds of columns, it also seems that access to a row would be much slower, due to hitting different cache lines for each column.
Of course, there are use cases for columnar databases, and the OrderedDict might be a good structure for that, maybe one should be able to create either an OrderedDict or a NamedTuple for a CSV input.

[quinnj]

Could point, John. Where did Jeff's PR for making Base.Dict ordered by default land? It'd be great to avoid another package dependency for this.

@ScottPJones, nope not concerned. For headerless CSVs (which are the minority in my experience), we can auto-generate column names (i.e. "Column1", "Column2", etc.) like DataFrames, R's dataframe, data.table, pandas, etc. all do and is pretty much standard in these cases. Note you can also provide your own column names if you want.

I'm also not sure what your point about "expand each column separately" means. You can't add rows to a Matrix anyway, so whether it's a Dict{String,Vector} or a DataFrame, it's the same operation of adding additional elements to the individual columns. I'm also not sure why would we necessarily care about the performance of adding rows to a Julia structure in a CSV parsing package? That seems like a concern for Base or DataStructures or DataFrames.