Dataframes from PostgresQL queries

[nathanvy]

Hi,

In my application I would like to query a Postgres database using postmodern and then perform some analysis with lisp-stat. postmodern returns data as a list of rows, each of those a list. For example for a table FOO with columns id and bar, a select statement might return:

select * from FOO;

   id   |  bar
--------+--------
   1    |  blah
   2    |  adsf
   3    |  jkjlkj
   ...

And in lisp that looks like:

'((1 "blah")
  (2 "asdf")
  (3 "jklkj")
  ... 
 )

I would like to get the list that postmodern returns into a lisp-stat data frame. Is there a convenient way to do this? Could I just walk the list from postgres using loop and then build up a dataframe row-by-row?

[snunez1]

The way to do that is to use vector-row-reader. It was created specifically for this purpose, but doesn't appear to have made it into the cl-postgres documentation. If it does what you like, a pull request in the docs will be greatly appreciated (either in cl-postgres and/or here in data-frames. If in data frames, send me the paragraphs and I'll insert so you don't need the whole doc toolchain.)

See marijnh/Postmodern#272 for the discussion that led to vector-row-reader. I see there is an alist-row-reader that might be worth checking out.

[nathanvy]

Thanks, I'll check that out ASAP and get back to you.

[snunez1]

That's what, I think, the pivot table is for (refer to the issue in Postmodern). Pivot rows->columns, then use vector-row-reader to get a vector of column vectors. From there you can use the data-frame constructors.

[nathanvy]

(Sorry, deleted my previous reply, probably shouldn't have)

I don't think pivoting does what I need; it's not just rows->columns.

You can see from the crosstab tutorial that it's creating columns based on the number of distinct values in a particular column of the original data set. That's valid for some use cases but my data is already in postgres exactly how I want it represented in the data-frame so a pivot operation will actually distort things.

For clarity: My data is financial OHLCV data, so what I really want is a column vector for each of Timestamp/Open/High/Low/Close/Volume. If I pivot on any of those it'll just make things a mess. I'm starting to think I will have to massage it by hand in CL; not a huge deal, it's just a lot of allocations I was hoping to avoid.

[snunez1]

This just 'feels' like it should be simpler than we're making it. Before doing it by hand in CL, perhaps re-open the issue in the Post-modern repo. Sabre and I talked about this before and he seems to think it's easy. He's a helpful guy.

[nathanvy]

This just 'feels' like it should be simpler than we're making it.

It definitely does, but SQL is fundamentally row-based so I'm starting to think it might not be possible unless there's a Postgres feature I'm not aware of.

In case anyone else lands here via google, here's what I'm using as a workaround in the interim:

;; wherein postmodern, via the :vectors results style, is returning
;; a vector-of-vectors where each vector/row is of the form:
;; #(id date-time-group open high low close volume)
(defun query-to-df (ohlcv-data)
  (let ((n (length ohlcv-data)))
    (let ((open (make-array n))
          (high (make-array n))
          (low (make-array n))
          (close (make-array n))
          (dtg (make-array n))
          (keys #(:dtg :open :high :low :close)))
      (loop for row across ohlcv-data
            for i from 0
            do (progn
                 (setf (elt open i) (elt row 2))
                 (setf (elt high i) (elt row 3))
                 (setf (elt low i) (elt row 4))
                 (setf (elt close i) (elt row 5))
                 (setf (elt dtg i) (elt row 1))))
      (df:make-df keys (list dtg open high low close)))))

[sabracrolleton]

Curious whether this works for you.

Assume ohlcv-data is returned from a query that looks like (with relevant where clause):

(pomo:query "select dtg, open, high, low, close from ohlcv")

Is the following equivalent to your function above?

(defun query-to-df (ohlcv-data)
   (let ((keys #(:dtg :open :high :low :close)))
       (df:make-df keys (loop for x in (apply #'mapcar #'list ohlcv-data)
                                                   collect (coerce x 'vector)))))

[snunez1]

@sabracrolleton, is there a way to get the results of a query as a row-major array? If so then aops:each-index could be used to transpose the matrix:

(defparameter A #2A((1 2)
                    (3 4)))
;; Transpose
(aops:each-index (i j) (aref A j i)) ; => #2A((1 3)
                                     ;        (2 4))

[sabracrolleton]

I have to admit I am getting a little confused between when you are talking sets (sql results) and arrays. Postgresql arrays can only have a single data type and it is not clear to me that the dtg column is the same data type as the open, high, low, close columns.

(query "select dtg, open, high, low, close from ohlcv" :vectors)

is going to return (just using placeholders here)

#(#(dtg1 open1 high1 low1 close1) 
  #(dtg2 open2 high2 low2 close2)
  #(dtg3 open3 high3 low3 close3) 
  #(dtg4 open4 high4 low4 close4))

but the work was done in the Postmodern cl-postgres::vector-row-reader function, not in Postgresql and I do not think it is in the order @nathanvy wants. My query-to-df function suggestion above obviously does not use the vector-row-reader function, it just transposes the rows first, then coerces them into vectors. I think it does the same thing as @nathanvy 's function, but I did not test the efficiency of either.

In terms of thinking about what Postgresql's built in functions could do, if dtg was the same data type as the others, then Postgresql's array_agg function looking something like:

(query "select array_agg(array[dtg, open,high,low,close]) from ohlcv")

would return something like:

    ((#2A((dtg1 open1 high1 low1 close1) 
          (dtg2 open2 high2 low2 close2)
          (dtg3 open3 high3 low3 close3) 
          (dtg4 open4 high4 low4 close4))))

but I do not think that is in the order you want.

If dtg is not the same data type as open, high, low, close, then it cannot be in the Postgresql array. So something like:

(query "select dtg, array_agg(array[open,high,low,close]) from ohlcv group by dtg")

would result in something that is even less what you want:

    ((dtg1
      #2A((open1 high1 low1 close1)))
     (dtg2
      #2A((open2 high2 low2 close2)))
     (dtg3
      #2A((open3 high3 low3 close3)))
     (dtg4
      #2A((open4 high4 low4 close4))))

You might think about putting open, high, low and close into a Postgresql array (changing the database table structure), but that would have its own pluses and minuses.

I am not sure whether any of this is helpful or if I have just confused things more.

[snunez1]

Ah, I seem to remember the homogeneous type requirement being an issue in previous discussions. Perhaps this is the right track then for a generic table->data-frame converter. Loop over all the column names and get them one by one as vectors. It's rare that a data-frame, in an analytics scenario, will have all the same data types.

[nathanvy]

Okay, weighing in on mobile so please excuse formatting.

1. I appreciate both of you giving this time and attention, thank you.

2. I'm an ex military guy and it's just a habit that's hard to break: DTG stands for Date/Time Group and in my application it's a time stamp from the local-time library. Open/high/low/close are double precision floats (I know I know, don't use floats to represent money, but I am in the prototype phase so a little imprecision doesn't bother me), and volume is whatever postgres supplies for a bigint type. The example I gave might not include volume because on index data there's no volume since it's just a number.

3. For clarity: My understanding is that make-dfing a data frame requires each column to be one single lisp vector, which is at odds with how most (all?) SQL servers return results.

4. It is also my understanding that there is no way to get Postgres to return the data in that format, and/or there's no pre-existing function somewhere hiding in lisp-stat or postmodern that will allow a data frame to directly ingest the results returned from postgres.

Do I have that about right?

If my understanding is correct then I'm happy to play with the profiler and find a computationally cheap way to massage the data from one format to the other. No problem.

[nathanvy]

Curious whether this works for you.

Assume ohlcv-data is returned from a query that looks like (with relevant where clause):

(pomo:query "select dtg, open, high, low, close from ohlcv")

Is the following equivalent to your function above?

(defun query-to-df (ohlcv-data)
   (let ((keys #(:dtg :open :high :low :close)))
       (df:make-df keys (loop for x in (apply #'mapcar #'list ohlcv-data)
                                                   collect (coerce x 'vector)))))

This doesn't eval properly for me. Something about inappropriate usage of values-list. I'm using the :vectors results style so that might be the source of that. I haven't really investigated further.

[sabracrolleton]

@nathanvy your understanding is correct.

With respect to my alternative query-to-df function. It does NOT use the :vectors result style.

(let ((results (pomo:query "select dtg, open, high, low, close from ohlcv")))
     (query-to-df results))

[nathanvy]

Roger that. I suppose we can close this issue now. Thanks both of you.