How to insert an array / list as a new column in Ibis?

[vadimnazarov]

Hello everyone,

First of all, I greatly appreciate the work you have done with Ibis. Thank you! I have been using Ibis and really enjoying it, but I recently stumbled upon a relatively simple yet unsolvable use case. I need to add a new column to an existing Ibis table with values from a NumPy array (or a list). However, I can't find a straightforward way to achieve this. Code:

import ibis
import pandas as pd

ibis.options.interactive = True

data = ibis.memtable(pd.DataFrame({"a":[1,2,3], "b":[4,5,6]}))
data.mutate(new_col = [7,8,9])

┏━━━━━━━┳━━━━━━━┳━━━━━━━━━━━━━━━━┓
┃ a     ┃ b     ┃ new_col        ┃
┡━━━━━━━╇━━━━━━━╇━━━━━━━━━━━━━━━━┩
│ int64 │ int64 │ array<int8>    │
├───────┼───────┼────────────────┤
│     1 │     4 │ [7, 8, ... +1] │
│     2 │     5 │ [7, 8, ... +1] │
│     3 │     6 │ [7, 8, ... +1] │
└───────┴───────┴────────────────┘

I want to add new_col as a new column to data, with new_col having the same length as the number of rows in data to get this table:

┏━━━━━━━┳━━━━━━━┳━━━━━━━━━┓
┃ a     ┃ b     ┃ new_col ┃
┡━━━━━━━╇━━━━━━━╇━━━━━━━━━┩
│ int64 │ int64 │ int8    │
├───────┼───────┼─────────┤
│     1 │     4 │       7 │
│     2 │     5 │       8 │
│     3 │     6 │       9 │
└───────┴───────┴─────────┘

How to do that?

Thank you!

[cpcloud]

Hi @vadimnazarov 👋🏻

This may seem simple, but the implicit assumption in this "just working" is that the i-th element of the new_col list corresponds to the i-th row of the table you're attaching it to.

This assumption doesn't hold in general in relational algebra: you have to explicitly tell the query engine how to associate rows, using some kind of join.

We actually just added support for how="positional" joins to support "concat"-style joins for some of the local backends, which may be what you want:

In [3]: from ibis.interactive import *

In [4]: import pandas as pd
   ...: data = ibis.memtable(pd.DataFrame({"a":[1,2,3], "b":[4,5,6]}))
   ...: data.mutate(new_col = [7,8,9])
Out[4]:
┏━━━━━━━┳━━━━━━━┳━━━━━━━━━━━━━━━━━━━━━━┓
┃ a     ┃ b     ┃ new_col              ┃
┡━━━━━━━╇━━━━━━━╇━━━━━━━━━━━━━━━━━━━━━━┩
│ int64 │ int64 │ array<int8>          │
├───────┼───────┼──────────────────────┤
│     1 │     4 │ [7, 8, ... +1]       │
│     2 │     5 │ [7, 8, ... +1]       │
│     3 │     6 │ [7, 8, ... +1]       │
└───────┴───────┴──────────────────────┘

In [5]: data.join(ibis.memtable({"new_col": [7,8,9]}), how="positional")
Out[5]:
┏━━━━━━━┳━━━━━━━┳━━━━━━━━━┓
┃ a     ┃ b     ┃ new_col ┃
┡━━━━━━━╇━━━━━━━╇━━━━━━━━━┩
│ int64 │ int64 │ int64   │
├───────┼───────┼─────────┤
│     1 │     4 │       7 │
│     2 │     5 │       8 │
│     3 │     6 │       9 │
└───────┴───────┴─────────┘