I enjoyed this post very much. Perfect length for a coffee, easy reading, nice touch of playfulness, I learned something (not least that there’s a non-mutating toSorted in JS, who knew?!) and it made me think too (in the K and Lil sections).

As a bonus, I have been reminded that there’s a fruit beginning with “C” (cherry) so I can avoid my usual standard but awkward three item example array of ["Apple", "Banana", "Carrot"].

Lil is such a beautiful little language.

An important dimension of how languages express this problem: where do the names live, and where do the expressions get evaluated.

• K, and Javascript (lists): column names live in the top scope, expressions get evaluated immediately.
  • Example: names@>sales, both ‘columns’ accessed directly.
  • Like you say, @Internet_Janitor, this makes it really easy for the columns to get out of sync when only one of them gets sliced or sorted. To say nothing of name collisions once you work with multiple should-be-tables.
• Javascript (list of dicts): column live in dict scope, expressions get evaluated immediately.
  • example: fruits.filter(x => x.name[0] != 'D') . Note the x => x.name lambda: a bare name would get evaluated immediately in the top scope, so we wrap it in a lambda that .filter() can pass each row to.
• Lil, and R + dplyr (my example below): column names live in the table scope, and table manipulations take expressions that get evaluated in the table scope.
  • Example: select orderby count@name asc from fruits.
  • Example: fruits |> arrange(str_len(name))

Okay, time to show the R example; then I’ll write a few more words on what happens if “evaluate this in the table context” is not available.

library(tidyverse)
fruits <- tribble(  # just a data frame, entered rowwise
    ~name, ~sales,
    "Cherry", 22,
    "Lime"  ,  5,
    "Durian", 97,
    "Banana", 12,
    "Apple" ,  1,
)

fruits |> arrange(name)  # alphabetically
fruits |> arrange(str_len(name))  # by name length
fruits |> arrange(desc(sales))  # by descending sales
fruits |> 
    filter( ! str_detect(name, "^D")) |>  # banish D-fruit
    arrange(desc(sales))  # by descending sales

How would that last example look if the table namespace had to be specified on every column access?

fruits |>
    filter( ! str_detect(fruits$name, "^D")) |>
    arrange(desc(fruits$sales))

This is already mildly noisy, and it’ll get even noiser when:

• there are multiple columns involved in an expression (cars$km_preowned + cars$km_work + cars$km_personal) / cars$years_active)
• or your library accesses columns like mytable["mycolumn"] instead of mytable.mycolumn\
  • In such cases, libraries commonly take it upon themselves to maintain an expression handler that takes column names, or entire expressions, as strings. Which provides a lot of benefit, but is also a lot of work.

Anyway, returning to the noise of repeated mytable.mycolumn: such noise creates a real pressure to make table names too short. Even a name like fruits can start to feel like a burden when you have to repeat it on. every. column. access. If your language lets the user specify the table once, and then work with columns within it, that’s real ergonomics.

Shout out to raising durian awareness. Wish it were a bit more affordable, but good fruit with top-tier texture.

This is a lovely blog post.

Now that I’ve seen it, the idea of “tables” and queries as a primitive in a non-DB scripting language seems so natural. I’m kicking myself for never having wondered about it before.

One of my take-aways from this is that I learned, after a good 30 years programming with languages that have some form of “sort takes a function that returns negative when a < b, 0 when a == b and positive when a > b”, that I can just … subtract.

For decades, I’ve implemented this as:

if a < b: return -1
else if: a > b return 1
else: return 0

I’ve never seen it implemented as a-b before.

Mind blown…

SQL is a pretty good fit:

create table fruits (name text, sales integer);
insert into fruits values ('Cherry', 22), ('Lime', 5), ('Durian', 97), ('Banana', 12), ('Apple', 1);

select name from fruits order by name; 
select name from fruits order by sales;
select name from fruits order by sales desc;
select name from fruits where name not like 'D%' order by sales;

(Try it in your browser over at SQL Fiddle.)

I would like to know what the solutions are like to this problem in APL and J as well, since those are the only two languages in this family I have any familiarity with, although I’m so poor at them I don’t think I could write the code well enough myself.

Perhaps it would be best to avoid in-place mutation?

No.

I got curious for how it’d work in pandas, so after some kludging here’s what I got:

import pandas as pd

df = pd.DataFrame({
    'fruit': ["Cherry", "Lime", "Durian", "Banana", "Apple"],
    'sales': [22, 5, 97, 12, 1]
})

query = df[df['fruit'].str[0] != 'D'].sort_values(by="sales", ascending=False)
print(query)

It doesn’t feel as elegant as the lil, but it’s tolerable enough. Thanks for the motivation to finally learn some pandas!

That table data structure is pretty cool! I know Lua also has tables, but I’m not sure they’re the same.

I think Ruby is quite elegant too:

fruit_names = ["Cherry", "Lime", "Durian", "Banana", "Apple"]
fruit_sales = [22, 5, 97, 12, 1]
fruits = fruit_names.zip(fruit_sales)

# Alphabetic:
fruits.sort

# By length:
fruits.sort_by { |name, _| name.length }

# By sales, descending:
fruits.sort_by { |_, sales| -sales }

# By sales, D-fruits banished:
fruits.reject { |name, _| name.start_with? "D" }.sort_by { |_, sales| -sales }

With the catch that you need to add .to_h if you want a hash map back. I also could’ve used numbered params, but I don’t like those as much.

the worrisome fact that JavaScript’s built-in sorting functions do not guarantee a stable sort

This isn’t true and hasn’t been for years. (And I think even for long before most or all browsers used a stable sort.)

https://developer.mozilla.org/en-US/docs/Web/JavaScript/Reference/Global_Objects/Array/sort#browser_compatibility