map function to array of struct

[dschneiderch]

Looking for help applying map to an array of structs. the values in the struct need to be concatenated a certain way and then unnested alongside the user. maybe there is another way to do this?

I get this error
BinderException: Binder Error: Referenced column "__ibis_param_row__" not found in FROM clause!

MRE:

# %%
import ibis
from ibis import _
ibis.options.interactive = True


# %%
t = ibis.memtable({"user":['a','b'], "attribute": [[{"id": "attr1", "sv":1}, {"id": "attr2", "sv": 2}, {"id": "attr3", "sv": 3}],[{"id": "attr1", "sv":1}, {"id": "attr5", "sv": None}]]})

# %%
t.mutate(segment_id=_.attribute.map(lambda row: row['id']+"_"+row["sv"]).unnest()).select(_.user, _.segment_id)
# also need to drop nulls somehow

# %% [markdown]
# Expected Output:
# ```
# 'a','attr1_1'
# 'a','attr2_2'
# 'a','attr3_3'
# 'b','attr1_1'
# ```

[dschneiderch]

I'd still be interested in a map solution because i think i twould be more flexible, but I was able to unnest first and get the expected output I listed originally.

t.mutate(attributes=_.attribute.unnest()).mutate(segment_id=_.attributes.id.concat("_",_.attributes.sv.cast('string'))).filter(_.segment_id.notnull()).select(_.user, _.segment_id)

[dschneiderch]

In my actual problem, I actually need slightly different output so I was hoping I could provide a function to map a la

def create_segment_id(row):
    return row['id']+"_"+row["sv"]

and then use arr.join(",") to get

'a','attr1_1,attr2_2,attr3_3'
'b','attr1_1'

[dschneiderch]

well, continuing on the approach I got working with unnest first, I was able to get the result in my previous reply with

t.mutate(attributes=_.attribute.unnest()).mutate(segment_id=_.attributes.id.concat("_",_.attributes.sv.cast('string'))).filter(_.segment_id.notnull()).select(_.user, _.segment_id).group_by('value').agg(v=_.segment_id.collect().join(","))

How can I do this with map to limit how much data needs to be unnested though?

[dschneiderch]

Looks like the error was a bit misleading and I just needed to cast my column to a string for the concat to work. For a slightly different output than my original question, i can do this:

t.mutate(
    segment_id=_.attribute.map(
        lambda row: row["id"].concat("_", row["sv"].cast("string"))
    )
).select(_.user, _.segment_id).unnest("segment_id").group_by("user").agg(
    v=_.segment_id.collect().join(",")
)

This is my first time using ibis and this is really cool. lets me do things in BigQuery that I couldn't do easily in sql.

[dschneiderch]

Still trying to optimize....

How can I just do the ArrayValue.join() without unnesting and then collecting again?

This will get me just the segment_id column in the form I want but drops the user field.

t.mutate(
    segment_id=_.attribute.map(
        lambda row: row["id"].concat("_", row["sv"].cast("string"))
    )
).select(_.user, _.segment_id).segment_id.join(',')

I tried group_by('user').agg(v=_.segment_id.join(",")) but i get this error

SignatureValidationError: Aggregate(<ibis.expr.operations.relations.Project object at 0x11835a390>, {'user': <ibis.expr.operations.relations.Field object at 0x11830a9e0>}, {"ArrayStringJoin(segment_id, ',')": <ibis.expr.operations.strings.ArrayStringJoin object at 0x11835a5d0>}) has failed due to the following errors:
  `metrics`: {"ArrayStringJoin(segment_id, ',')": <ibis.expr.operations.strings.ArrayStringJoin object at 0x11835a5d0>} is not matching GenericMappingOf(key=InstanceOf(type=<class 'str'>), value=AllOf(patterns=(GenericCoercedTo(origin=<class 'ibis.expr.operations.core.Value'>, params={'T': +T, 'S': <class 'ibis.expr.datashape.Scalar'>}, checker=GenericInstanceOf(type=ibis.expr.operations.core.Value[+T, ibis.expr.datashape.Scalar], origin=<class 'ibis.expr.operations.core.Value'>, fields={'dtype': CoercedTo(type=<class 'ibis.expr.datatypes.core.DataType'>, func=<bound method DataType.__coerce__ of <class 'ibis.expr.datatypes.core.DataType'>>), 'shape': InstanceOf(type=<class 'ibis.expr.datashape.Scalar'>)})), Not(pattern=InstanceOf(type=<class 'ibis.expr.operations.core.Alias'>)))), type=CoercedTo(type=<class 'ibis.common.collections.FrozenOrderedDict'>, func=<class 'ibis.common.collections.FrozenOrderedDict'>))

Expected signature: Aggregate(parent: Relation, groups: FrozenOrderedDict[str, Annotated[Value, Not(pattern=InstanceOf(type=<class 'Alias'>))]], metrics: FrozenOrderedDict[str, Annotated[Value[+T, Scalar], Not(pattern=InstanceOf(type=<class 'Alias'>))]])

[dschneiderch]

Keep It Simple, Stupid

t.mutate(
    segment_id=_.attribute.map(
        lambda row: row["id"].concat("_", row["sv"].cast("string"))
    )
).mutate(segment_ids = _.segment_id.join(",")).select(_.user, _.segment_ids)

┃ user ┃ segment_ids ┃
┡━━━━━━━━╇━━━━━━━━━━━━━━━━━━━━━━━━━┩
│ string │ string │
├────────┼─────────────────────────┤
│ a │ attr1_1,attr2_2,attr3_3 │
│ b │ attr1_1 │