Data science tools

[derekmahar]

(Continuation of discussion about data science tools.)

How common and useful is Apache Arrow in data science?

[kindly]

Apache Arrow is more an underlying technology for many higher level tools like https://github.com/pola-rs/polars and https://arrow.apache.org/datafusion/

In my opinion, the tech is still a bit young, but is great if you are doing interoperability between languages. I think even duckdb-rust uses arrow for passing data between the c++ library and rust.

[jqnatividad]

I've been eyeing pola.rs for a while, and looking to leverage it as an engine for qsv.

@baggiponte also suggested using it - #725

Right now, I'm thinking of tipping my toes into it by trying to use it for its time-series capabilities, particularly it temporal groupby and resampling capabilities, as we often need to do so with CSV files from IoT type feeds.

And it'd be very convenient to do so from the command-line, without having to write a script/program.

Are there any other "data-science" type CSV data-wrangling tasks from the command-line on your wishlist?

[derekmahar]

I'm highly interested in an asof join command similar to Polars' asof join operation and Pandas' merge_asof function. The DuckDB team has discussed implementing an asof join in DuckDB, but has no concrete schedule. Fortunately, there is a workaround using a "range intersection join". In 2018, in the Miller project, I unwittingly requested an asof join. In that feature request, I mentioned the same SQL "range intersection join" solution that some members of the DuckDB team again discussed. Since I made that request, John Kerl, the Miller creator and maintainer, was busy re-implementing Miller in Go, so he hasn't yet had time to implement my feature request. Should be any day now! ;)

In the meantime, I think qsv would benefit immensely from an asof join command! :)

[derekmahar]

Had I known earlier about Python Panda's merge_asof, I probably would have started using Python much sooner!

[derekmahar]

It's funny. In 2018, I started a quest to find a tool that could perform an asof join between two CSV files simply because I wanted to calculate the value of USD stock and cryptocurrency trades in CAD. Most accountants would perform this calculation using Excel and the VLOOKUP function, but I'm not an accountant. Since then, I've experimented with Miller, csvq, SQLite, data.table in R, DuckDB, and Pandas in Python. So far, DuckDB looks like the most promising tool to solve my asof join problem for larger data sets.

[derekmahar]

If my understanding of the concept is correct, I think I might use a temporal groupby to calculate the average price of a security during the 30 minute period before the expiration of a particular trading contract which happens to fall on hourly boundaries. I've used DuckDB to perform this calculation simply by running an SQL average groupby query on timestamps truncated to hour precision and including all prices in the average that occurred within the 30 minute window before the hour.