If that sounds interesting, you can find it here: https://github.com/erezsh/Preql
If that sounds interesting, you can find it here: https://github.com/erezsh/Preql
df{\*, -column1}
df{column1: new_name1, column2, column3: new_name3}
I noticed in working with pandas you often need to do lookups into other dataframes. It's partially solved by assignment operator if left field equals right index, or .map method, the same way.But often you need a lookup with merging by an arbitrary column, then grouping and aggregation by the left table items. This is partially doable without special functions. But this can be a killer feature if one makes this for spatial joins.
Very often you need to do the following:
gdf1 = geodataframe of points
gdf2 = geodataframe of points
need to make gdf1.geometry.buffer(500 m) and sjoin it with gdf2.geometry, then lookup gdf2 fields and bring them to gdf1, and keep original gdf1.geometry (points). This operation takes a dozen of lines and leaves lots of variable garbage if not put into a separate function.But IMO it could be condensed to something more natively supported.
Something like.. BQL http://intelligiblebabble.com/a-better-query-language-bql-la... which went nowhere https://github.com/lelandrichardson/BQL
My approach has been to design a very simple (in the lisp sense) syntax, kind of the opposite to SQL where everything is hard-coded into the parser. I've adopted a "pipeline programming"-like approach, where the operations are just (special) functions, which also helps with extensibility. Have you thought about this? From a cursory look, it seems Preql does rely on keywords. Admittedly fewer than SQL, but it also doesn't cover all of its features.
Having said that, the amount of keywords and operators that you see in Preql right now, isn't likely to grow by much. I have the basic and common operators covered, and for the most part, the rest can be done with regular functions.
I agree about introspection, which is why in Preql you can ask for the type of any value, list the columns of a table, get the resulting SQL of an expression as a string, and so on. And certainly more can and should be done towards that.
from stops
| let region :be case(zoneid,
:when ("P", "0", "B") :then "Prague",
:when ("1", "2") :then "Almost-Prague",
:else "Regional")
| where lat != 0 && lon != 0 && region != "Prague"
| select name, region
"from", "let", "where" and "select" are filters, functions that take a query description object and return a new one. "name, region" is just an arg-tuple with two unnamed elements and "case" is a function taking one polymorphic value and a variable number of nested arg-tuples with ":when: and ":then" named elements.Filters can be arranged in any order, unlike SQL, so this would work:
from stops
| group_by lat, lon
| group_by lat
| select min(group.lon)I also noticed it's popping up everywhere, in this case sql rows (kind of). Conceptually I like the idea of using it as the mechanism for function arguments, but I didn't want the syntax to be too confusing for beginners.
Although yours seems a little bit different, because different fields can have the same name.
Just for the heck of it, here's how your second example would look in preql:
(stops
{ lat, lon => }
{ lat => lon: min(lon) }
{ lon } // not necessary. just drops the lat column
)I can share some sketches if you wish (maybe it can give some ideas).
But generally, I think the code could be rather structured around calculation graph, than a linear sequence of operations.
I don't quite see how a computation graph would work as the core of a textual language and I'm sceptical about using it with existing DBMSs (I'd like to actually use my creation one day :) ), but I'm open to ideas.
[0]: for example this monstrosity: https://gitlab.com/dvdkon/jrutil/-/blob/dbb971c18526e68dcc97...