How to perform positional joins (concatenate columns) with ibis?

[ogrisel]

When working with file-backed data (CSV, JSON, parquet...), the positional ordering of records might be meaningful and shared by different files.

• in pandas, it's natural to merge such dataframes using pandas.concat(..., axis="columns"),
• in polars, it's also possible using polars.concat(..., how="horizontal"),
• in duckdb, there exists positional joins.

I am wondering if such a concept exists the Ibis backend-agnostic public API.

If not, it's always possible to mutate the table expressions to add a join key with ibis.row_number() and join on that, and then dropping it from the result. However, I have the feeling that some backends might not optimize this pattern (e.g. detect they can lower it to horizontal concatenation or positional joins) and furthermore it's a bit more verbose than a dedicated API.

[ogrisel]

Replying to myself, for the simple case where you only need to concatenate columns that stem from the same source, a combination of named deferred expressions and table.select can do what I want:

import ibis
from ibis import _
data = ibis.memtable({"a": range(5)})

b = (_.a + 1).name("b")
c = (_.a + 2).name("c")
data.select(b, c)

However, assume that would like to concatenate with a third column that results from a left outer join from an ancillary table, then I don't know how to do this with Ibis.

[gforsyth]

Hey @ogrisel -- we do not currently have any IR support for the concept of positional joins in Ibis. It's not a commonly supported operation in relational databases, so I think it hasn't come up.

We could add support, probably as an option to the how kwarg in .join -- the other option is to use .sql to perform the join in DuckDB, although that won't be a backend-agnostic command.

[ogrisel]

Out of curiosity is the ibis.row_number() expression always deterministically meaningful when mutating a table right after a reading operation from parquet or csv files with any of the supported backend? Or can things like multithreading break any assumption on row ordering even at that stage?

[gforsyth]

😬 the answer is "it depends".

I believe that Polars, Pandas, Dask, and DuckDB will read in a given parquet file in the same order (for DuckDB this may require setting preserve_insertion_order in DuckDB but I don't think that's required).

Other backends, I don't know. I'm pretty sure Trino makes no guarantees about ordering when reading in external data.

[cpcloud]

This is now possible with a few of the in-memory backends using how="positional" with joins. Here's DuckDB:

In [16]: import datetime as dt

In [17]: from ibis.interactive import *

In [18]: t = ibis.memtable(
    ...:     {
    ...:         "store_id": [1] * 5 + [2] * 5,
    ...:         "date": [dt.datetime(2022, 1, x) for x in [2, 3, 4, 5, 6]]
    ...:         + [dt.datetime(2022, 1, x) for x in [2, 5, 6, 7, 9]],
    ...:         "value": [1, 2, 3, 4, 5] + [1, 4, 5, 6, 8],
    ...:     },
    ...:     name="t",
    ...: )

In [19]: t
Out[19]:
┏━━━━━━━━━━┳━━━━━━━━━━━━━━━━━━━━━┳━━━━━━━┓
┃ store_id ┃ date                ┃ value ┃
┡━━━━━━━━━━╇━━━━━━━━━━━━━━━━━━━━━╇━━━━━━━┩
│ int64    │ timestamp           │ int64 │
├──────────┼─────────────────────┼───────┤
│        1 │ 2022-01-02 00:00:00 │     1 │
│        1 │ 2022-01-03 00:00:00 │     2 │
│        1 │ 2022-01-04 00:00:00 │     3 │
│        1 │ 2022-01-05 00:00:00 │     4 │
│        1 │ 2022-01-06 00:00:00 │     5 │
│        2 │ 2022-01-02 00:00:00 │     1 │
│        2 │ 2022-01-05 00:00:00 │     4 │
│        2 │ 2022-01-06 00:00:00 │     5 │
│        2 │ 2022-01-07 00:00:00 │     6 │
│        2 │ 2022-01-09 00:00:00 │     8 │
└──────────┴─────────────────────┴───────┘

In [20]: t.join(t.rename("{name}_other"), how="positional")
Out[20]:
┏━━━━━━━━━━┳━━━━━━━━━━━━━━━━━━━━━┳━━━━━━━┳━━━━━━━━━━━━━━━━┳━━━━━━━━━━━━━━━━━━━━━┳━━━━━━━━━━━━━┓
┃ store_id ┃ date                ┃ value ┃ store_id_other ┃ date_other          ┃ value_other ┃
┡━━━━━━━━━━╇━━━━━━━━━━━━━━━━━━━━━╇━━━━━━━╇━━━━━━━━━━━━━━━━╇━━━━━━━━━━━━━━━━━━━━━╇━━━━━━━━━━━━━┩
│ int64    │ timestamp           │ int64 │ int64          │ timestamp           │ int64       │
├──────────┼─────────────────────┼───────┼────────────────┼─────────────────────┼─────────────┤
│        1 │ 2022-01-02 00:00:00 │     1 │              1 │ 2022-01-02 00:00:00 │           1 │
│        1 │ 2022-01-03 00:00:00 │     2 │              1 │ 2022-01-03 00:00:00 │           2 │
│        1 │ 2022-01-04 00:00:00 │     3 │              1 │ 2022-01-04 00:00:00 │           3 │
│        1 │ 2022-01-05 00:00:00 │     4 │              1 │ 2022-01-05 00:00:00 │           4 │
│        1 │ 2022-01-06 00:00:00 │     5 │              1 │ 2022-01-06 00:00:00 │           5 │
│        2 │ 2022-01-02 00:00:00 │     1 │              2 │ 2022-01-02 00:00:00 │           1 │
│        2 │ 2022-01-05 00:00:00 │     4 │              2 │ 2022-01-05 00:00:00 │           4 │
│        2 │ 2022-01-06 00:00:00 │     5 │              2 │ 2022-01-06 00:00:00 │           5 │
│        2 │ 2022-01-07 00:00:00 │     6 │              2 │ 2022-01-07 00:00:00 │           6 │
│        2 │ 2022-01-09 00:00:00 │     8 │              2 │ 2022-01-09 00:00:00 │           8 │
└──────────┴─────────────────────┴───────┴────────────────┴─────────────────────┴─────────────┘