The expressiveness of the API w.r.t. SQL

[xor-xor]

Hi,

First of all - thank you for this project (and lisp-stat in general) - I hope it will get some traction!

I started playing with it recently, trying to re-create some of my routines from Pandas, but I have failed quite fast with some operations - and these are rather basic ones.

For example, GROUP BY. The documentation for sqldf states that "Any query that can be done in SQL can also be done in the API [...]" (and by "API" I understand data-frame's Lisp API) - which leaves me a bit puzzled, because I can't find anything similar to SQL's GROUP BY here.

Same situation with selecting a subset of columns for pretty-printing, which is trivially doable in sqldf (e.g. (sqldf:sqldf "select col1, col2, col9 from some_df")), but not in "plain" data-frame.

So my question is: are such things even possible in data-frame? If yes, then what is the "canonical" way of doing them?

Thanks!

EDIT: I'm aware of the possibility of selecting multiple columns with columns function, but it returns a vector of vectors, not a "sliced" data frame. Sure, I can wrap it in make-df, but this gets way too verbose for such basic operation.

[snunez1]

Hi xor-xor,

I'm glad you asked, as this is something I've been thinking about for the last few months. I have become frustrated with both the current sqldf and using the data-frame API. So, I'm going to give a somewhat longer answer than is strictly necessary because it will give you some background, but also serve as a record of the thinking that went into the future design. I'm glad you open this as a discussion, because I would like to hear what others think.

First, let me answer your basic question. You can slice multiple columns with the slice function (which can also be accessed with select). This will return a data frame of Cartesian selections. For example, using mtcars, we can say:

LS-USER> (select mtcars t '(gear wt cyl))
#<DATA-FRAME (32 observations of 3 variables)>
LS-USER> (head *)

;;   GEAR    WT CYL
;; 0    4 2.620   6
;; 1    4 2.875   6
;; 2    4 2.320   4
;; 3    3 3.215   6
;; 4    3 3.440   8
;; 5    3 3.460   6

The choice of select as a function name was, in hindsight, unfortunate. The package was originally named cl-slice, and I had a vision of turning it into a general purpose data selection function for data frames, like SQL.

So, select was supposed to be expanded into a general purpose SQL-like facility, and sqldf was a short-term solution for those types of queries until that was done. What I have discovered though (which should have been obvious from the start), is that building a query engine is a lot of work! Just the basics are extremely difficult, let alone optimisation. I was going to implement the data frame sql/select functionality one bit at a time as I needed it, but I found myself always using sqldf instead.

At this point, I question the need/wisdom of duplicating query functionality in data frame. Why not just make sqldf the default query mechanism in Lisp-Stat? Here are the pros & cons as I see them (please add more if you can think of any)

Pros

• SQL is well documented, and SQL skills widely available -- no need to learn a special data frame query language
• SQLite has had many man-hours put into optimising the queries. On even medium sized data sets I found sqldf faster, even taking into account the time spent transferring data to/from in-memory tables
• The SQLite on-disk format would make a good binary format for saving data frames, and allow inspection & manipulation of data frames with third party tools

Cons

• SQL doesn't cover all data cleansing tasks well, in particular in-column manipulation, so there will be some additional work in adding that to data frames
• A binary shared library is required. I don't consider this a strong con. We're about to implement a GUI of sorts for plot display, and that requires webkit DLL. Also, I think Cephes should be included. Whilst I have implemented a handful of special functions to support the random-variates package (unreleased), I don't think re-implementing Cephes in Common Lisp is a good use of limited programming resources.

At the moment, the biggest obstacle is the string based method of specifying queries. Using s-sql, from Postmodern, will be both more lisp-like and also eliminate some of the problems with the SQL string parser currently in sqldf, like multi-table joins.

So, summarising, my current thinking is:

• Make sqldf the canonical way of manipulating data-frames
• Use s-sql to specify the queries, so we don't have to reinvent the sql parser wheel
• Supplement in-column data manipulations with data frame functions, e.g. search and replace with cl-ppcre

Comments, questions, feedback welcome.

[xor-xor]

Wow, thanks for such a prompt and informative reply!

Regarding slicing columns with select - actually, I think that the name of this function is perfectly fine/intuitive, only the examples given in the docs are a bit "obfuscating", as they concentrate solely on selecting rows, not columns - hence my confusion.

As for making sqldf the default query mechanism in data-frame - I generally agree with the pros & cons that you enumerated so far. A crucial thing for me is the ability to group rows and run some aggregation functions on them (see e.g. split-apply-combine in Pandas).

SQLite - or rather, SQL in general - obviously provides such thing, but the choice of aggregation functions is somewhat limited (e.g. you have AVG, but not MEDIAN), and you can't write your own ones without diving into SQLite's API, which is way too low-level for such cases (again, compare this with Pandas, where writing UDFs is just trivial).

On the other hand, s-sql looks vastly superior when the choice of aggregation functions is concerned - to the point that I've started wondering, if I would ever need to write any UDFs - but from what I understand, these are simply "bindings" for the functions provided by PostgreSQL itself, so we won't get them for free with SQLite.

[snunez1]

There is a project, SBCL-librarian, that promises to create C shared libraries using common lisp. If the project continues to be developed, it looks like a nice way to add UDFs to SQLite.

A more expedient method may be to use the SQLite extension functions, which include aggregates for stdev, variance, mode, median, lower_quartile, upper_quartile. I could compile and distribute a modified SQLite library, though a better option might be to convince the SQLite project to include these by default.

How many more use cases would these additional aggregation function cover for you?

[xor-xor]

Those extension functions that you've linked are mostly for math and strings, with just a couple of statistical ones added to the mix, so although they certainly would be a welcome addition, I'm afraid that they don't improve the overall situation in a significant way.

sbcl-librarian looks interesting, though. It doesn't provide any ready-made functions by itself, so there are no immediate gains here, but it opens up a possibility to add new ones when needed (say, when requested by the users via GitHub issues), while still being in the CL land. Thanks for pointing out this project to me - I'm going to play with it a bit in my spare time, because the idea of calling Lisp functions from C programs seems quite crazy interesting on its own ;)

And BTW, functions that can be added by those mechanisms should not be considered as User-Defined Functions (UDFs), IMO. Here's an example of what I mean by that (in Pandas):

import numpy as np

# Pandas doesn't have a function for calculating p99, so we need to define it as an UDF.
def p99(x): return np.percentile(x, [99])[0]

# This roughly translates to something like:
# `SELECT MEDIAN(SOME_OTHER_COLUMN), P99(SOME_OTHER_COLUMN) FROM DF GROUP BY SOME_COLUMN`
df.groupby("some_column").some_other_column.agg(["median", p99])

The crucial difference is that UDFs can be defined during runtime/session (i.e. without restarting/reloading anything).