feat: Substitute to different type

[KronosTheLate]

Is your feature request related to a problem?

This raises a type error:

data = [
    {"c1": 1, "c2": "one"},
    {"c1": 2, "c2": "two"},
    {"c1": 3, "c2": "three"},
    ]
t1 = ibis.memtable(data)
t1.mutate(t1.c1.substitute({1: True, 2: False, 3: True}).name("c1"))

I have no idea how one would map an integer to a boolean, or the other way around, without being able to provide a return type argument.

What is the motivation behind your request?

I had to do this nonsense:

[df[x].cast("string").substitute({"true": "Yes", "false": "No"}).name(x) for x in df.select(ibis.selectors.of_type("bool")).columns]

Instead of the more effective, correct, and simple:

[df[x].substitute({True: "Yes", False: "No"}).name(x) for x in df.select(ibis.selectors.of_type("bool")).columns]

Describe the solution you'd like

Something like the return_dtype argument from polars.Expr.replace or polars.Expr.map_batches.

What version of ibis are you running?

ibis-framework 10.5.0

What backend(s) are you using, if any?

No response

Code of Conduct

• I agree to follow this project's Code of Conduct

[cpcloud]

The question here is: what happens when the database encounters a value for which you have not specified a substitution?

Currently, the semantics of substitute are such that if you don't specify else_, then the types must match, because else_ defaults to preserving the original input. The preserving behavior of not specifying else_ is documented in the docstring of substitute.

More generic (and type-flexible) substitution can be done with .cases(...).

I'm not inclined to change the semantics of substitute.