You signed in with another tab or window. Reload to refresh your session.You signed out in another tab or window. Reload to refresh your session.You switched accounts on another tab or window. Reload to refresh your session.Dismiss alert
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?
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.)
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.
(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.
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.
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)
(defunquery-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 0do (progn
(setf (eltopen i) (elt row 2))
(setf (elt high i) (elt row 3))
(setf (elt low i) (elt row 4))
(setf (eltclose i) (elt row 5))
(setf (elt dtg i) (elt row 1))))
(df:make-df keys (list dtg open high low close)))))
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?
(defunquery-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)))))
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)
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")
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.
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.
Okay, weighing in on mobile so please excuse formatting.
I appreciate both of you giving this time and attention, thank you.
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.
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.
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.
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.
(defparameter A #2A((12)
(34)))
;; Transpose
(aops:each-index (i j) (aref A j i)) ; => #2A((1 3); (2 4))
and then make-df:
(matrix-df #(:a:b) #2A((12)
(34)))
;#<DATA-FRAME (2 observations of 2 variables)>
I don't know how efficient that will be, but it's convenient.
@nathanvy, I'd appreciate a report on how well it works when you decide on a method and know it's efficiency. I want to update the data-frame documentation with retrieving data from Postmodern. I suspect this is going to be generally useful.
That basically looks correct. As a next step I'd try capturing the output of pomo:query in a variable and then seeing what happens when combine is called on it. Are all the vectors of the same length in whats returned from the query?
Edit: I've just tested a round trip from a data-frame to a vector-of-vectors and back:
Are all the vectors of the same length in whats returned from the query?
They are, but it looks like there are dozens of zero-elements towards the end of the outer vector. With the :vectors results style, postmodern is probably calling vector-push-extend internally which tries to avoid mallocs under the hood by doing them in chunks and filling with zeroes. I bet that's what's tripping the combine operation up.
Just wondering @nathanvy, did you ever get this to work and, if so, what was the solution? I'd love to be able to easily go from a SQL query to data-frame.
Activity
snunez1 commentedon Jun 30, 2024
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 commentedon Jun 30, 2024
Thanks, I'll check that out ASAP and get back to you.
snunez1 commentedon Jul 3, 2024
That's what, I think, the pivot table is for (refer to the issue in Postmodern). Pivot rows->columns, then use
vector-row-readerto get a vector of column vectors. From there you can use the data-frame constructors.nathanvy commentedon Jul 3, 2024
(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 commentedon Jul 3, 2024
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 commentedon Jul 3, 2024
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:
sabracrolleton commentedon Jul 3, 2024
Curious whether this works for you.
Assume ohlcv-data is returned from a query that looks like (with relevant where clause):
Is the following equivalent to your function above?
snunez1 commentedon Jul 4, 2024
@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:
sabracrolleton commentedon Jul 4, 2024
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.
is going to return (just using placeholders here)
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:
would return something like:
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:
would result in something that is even less what you want:
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 commentedon Jul 4, 2024
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 commentedon Jul 4, 2024
Okay, weighing in on mobile so please excuse formatting.
I appreciate both of you giving this time and attention, thank you.
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-timelibrary. 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.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.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 commentedon Jul 4, 2024
This doesn't eval properly for me. Something about inappropriate usage of
values-list. I'm using the:vectorsresults style so that might be the source of that. I haven't really investigated further.sabracrolleton commentedon Jul 4, 2024
@nathanvy your understanding is correct.
With respect to my alternative query-to-df function. It does NOT use the :vectors result style.
nathanvy commentedon Jul 4, 2024
Roger that. I suppose we can close this issue now. Thanks both of you.
snunez1 commentedon Jul 4, 2024
One more way this could work using the vector-of-vectors returned by
pomo:To create a matrix from a vector of vectors:
transposecan be done withfor-each:and then
make-df:I don't know how efficient that will be, but it's convenient.
@nathanvy, I'd appreciate a report on how well it works when you decide on a method and know it's efficiency. I want to update the
data-framedocumentation with retrieving data from Postmodern. I suspect this is going to be generally useful.nathanvy commentedon Jul 5, 2024
Will that work with heterogenous data types? It doesn't seem to work for me.
Here's a csv of the type of data I'm working with. The columns are, in order:
local-timetimestamp, which to handle properly requires(local-time:set-local-time-cl-postgres-readers)snunez1 commentedon Jul 5, 2024
It should work with heterogeneous data types. What error are you encountering?
nathanvy commentedon Jul 5, 2024
snunez1 commentedon Jul 5, 2024
That basically looks correct. As a next step I'd try capturing the output of
pomo:queryin a variable and then seeing what happens whencombineis called on it. Are all the vectors of the same length in whats returned from the query?Edit: I've just tested a round trip from a
data-frameto a vector-of-vectors and back:nathanvy commentedon Jul 5, 2024
They are, but it looks like there are dozens of zero-elements towards the end of the outer vector. With the
:vectorsresults style,postmodernis probably callingvector-push-extendinternally which tries to avoid mallocs under the hood by doing them in chunks and filling with zeroes. I bet that's what's tripping the combine operation up.snunez1 commentedon Jan 24, 2025
Just wondering @nathanvy, did you ever get this to work and, if so, what was the solution? I'd love to be able to easily go from a SQL query to data-frame.
nathanvy commentedon Jan 24, 2025
I never did end up getting it to work, sadly.