Any possibility for more advanced dataframe functionality.

[Vincent-de-Comarmond]

Hello again

Thank you for your efforts w.r.t. list-stat. I hope it bears fruit.

I have read the previous discussion w.r.t. data-frame. I found myself wanting to recreate some spark/pandas functionality myself. In particular I was thinking of window functions.

I cooked up something monserous myself (I've included it below) because I could not find the functionality I wanted anywhere.
So two questions:

1. Do you know of any library in the Common Lisp ecosystem which could provide pandas or spark level dataframe functionality?
2. Is the current plan to "push" all such functionality to SQLite?

The monstrosity (I apologise ... I'm not particularly good at lisp):

(ql:quickload :alexandria)
(ql:quickload :lisp-stat)
(ql:quickload :plot/vega)

(defun subseq+ (mysequence start end &key (fill nil))
  (let ((padleft (< start 0))
	(padright (> end (length mysequence)))
	(leftover (- end (length mysequence))))
    (cond ((and padleft padright)
	   (concatenate 'list
			(make-list (- start) :initial-element fill)
			mysequence
			(make-list leftover :initial-element fill)))
	  (padright
	   (concatenate 'list
			(subseq mysequence start)
			(make-list leftover :initial-element fill)))
	  (padleft
	   (concatenate 'list
			(make-list (- start) :initial-element fill)
			(subseq mysequence 0 end)))
	  (t
	   (subseq mysequence start end)))))


(defun window (mysequence applyfun &key (rows-before 0)
				     (rows-after 0))
  (if (and (= rows-before 0)
	   (= rows-after 0))
      (map 'list applyfun mysequence)
      (loop for val in mysequence and idx from 0
	    ;; Note weirdness here. subseq and subseq+ are both [). For window functions, however,
	    ;; We expect an interval of [] ... hence we need to add 1 below
	    collect (funcall applyfun (subseq+ mysequence (- idx rows-before) (+ idx rows-after 1))))))

(lisp-stat:defdf vgcars (vega:read-vega vega:cars))

(window (map 'list 'identity vgcars:cylinders) 
	(lambda (my-sub-sequence) 
	  (alexandria:mean (remove nil my-sub-sequence))) 
	:rows-before 1 
	:rows-after 1)

[snunez1]

As far as I know, there is no library in the Common Lisp ecosystem that approaches the functionality of Pandas or Spark, but that is the hope of Lisp-Stat. There's enough now to start building out an ecosystem; this will take time.

For SQLDF, it is not the intention to push this functionality out to SQL. Part of the reason is because we want to be able to write UDFs (User Defined Functions) in Common Lisp. There's some additional functionality, like split-appy-combine and stack that's in my local repo that needs cleaning up. If those interest you I can push the work in progress to a branch.

As for windowing, what I use is the displace function from array-operations. You can turn data frames into arrays and visa-versa pretty easily with as-array, or refer to a data vector by name if you're using defdf for your data frames.

If displace doesn't work for your windowing use case, post some examples here and we'll work out a good idiom for this.