Add a new function Replace to Table.enso

[Cassandra-Clark]

As a user, I want a way to match a table against a lookup_table on a key value and then to specify which column will be replaced in the original table. This is differentiated from merge because it applies specifically to one column, rather than to all columns with matching names where the key_value matches.

A scaffold of the API is below:

    ## ALIAS find replace
       GROUP Standard.Base.Calculations
       ICON join
       Finds matching values in a column or set of columns from table based on
       a lookup table, matching to values from `match_column` and replacing
       with `value_column`
       This operation is similar to `Table.update_rows`, but just returns a new
       `Table` instance, instead of updating the table in-place (which is only
       possible for Database tables).

       Arguments:
       - columns: Specifies the columns to match with the lookup table.
       Must identify values uniquely within `lookup_table`.
       - lookup_table: The table to use for looking up values.
       - allow_unmatched_rows: Specifies how to handle missing rows in the lookup.
         If `False` (the default), an `Unmatched_Rows_In_Lookup` error is raised.
         If `True`, the unmatched rows are left unchanged. Any new columns will
         be filled with `Nothing`.
       - on_problems: Specifies how to handle problems if they occur, reporting
         them as warnings by default.

       ? Result Ordering

         When operating in-memory, this operation preserves the order of rows
         from this table (unlike `join`).
         In the Database backend, there are no guarantees related to ordering of
         results.

       ? Error Conditions

         - If this table or the lookup table is lacking any of the columns
           specified in `columns`, a `Missing_Input_Columns` error is raised.
         - If an empty vector is provided for `columns`, a
           `No_Input_Columns_Selected` error is raised.
         - If a single row is matched by multiple entries in the lookup table,
           a `Non_Unique_Key` error is raised.
         - If a column that is being updated from the lookup table has a type
           that is not compatible with the type of the corresponding column in
           this table, a `No_Common_Type` error is raised.
         - If a match_column contains `Nothing` values, either in the table or lookup table,
           a `Null_Values_In_Key_Columns` error is raised.
         - If `allow_unmatched_rows` is `False` and there are rows in this table
           that do not have a matching row in the lookup table, an
           `Unmatched_Rows_In_Lookup` error is raised.
         - The following problems may be reported according to the `on_problems`
           setting:
           - If any of the `columns` is a floating-point type,
             a `Floating_Point_Equality`.
    @columns Widget_Helpers.make_column_name_selector
    replace : (Vector (Integer | Text | Regex) | Text | Integer | Regex) -> Table -> Text | Integer -> Text | Integer -> Problem_Behavior -> Table ! Missing_Input_Columns | Non_Unique_Key | Unmatched_Rows_In_Lookup
    replace self columns:(Text | Integer) lookup_table:Table  match_column:(Text | Integer)=0 value_column:(Text | Integer)=1 on_problems:Problem_Behavior=Problem_Behavior.Report_Warning =
        _ = [lookup_table, field, find_value, replace, on_problems]
        Error.throw (Unsupported_Database_Operation.Error "Table.replace is not implemented yet for the Database backends.")```

[jdunkerley]

Should also have a conversion from a Map to a Table added in.

[enso-bot]

Greg Travis reports a new STANDUP for today (2024-01-29):

Progress: Disabled collation tests; struggle with CI; try to run gui It should be finished by 2024-02-01.

Next Day: Table.replace

[GregoryTravis]

Is match_column always one column, or do we allow multiple?

Finds matching values in a column or set of columns from table based on
a lookup table, matching to values from match_column and replacing
with value_column

Does this imply that we might compare multiple columns from the main table with a single column in the lookup table? Or is it a straightforward multi-column aggregate key comparison?

[enso-bot]

Greg Travis reports a new STANDUP for today (2024-01-30):

Progress: Wrestle with CI, collect logs for local gui failure; start on Table.replace It should be finished by 2024-02-01.

Next Day: Table.replace

[enso-bot]

Greg Travis reports a new STANDUP for today (2024-01-31):

Progress: Table.replace (in-mem) and tests It should be finished by 2024-02-01.

Next Day: Table.replace (db)

[enso-bot]

Greg Travis reports a new STANDUP for today (2024-02-01):

Progress: Finished Table.replace (in-mem) and tests, sent PR It should be finished by 2024-02-01.

Next Day: Table.replace (db)

[enso-bot]

Greg Travis reports a new 🔴 DELAY for today (2024-02-02):

Summary: There is 4 days delay in implementation of the Add a new function Replace to Table.enso (#8578) task.
It will cause 0 days delay for the delivery of this weekly plan.

Delay Cause: Map conversion is tricky in db

[enso-bot]

Greg Travis reports a new STANDUP for today (2024-02-02):

Progress: Implemented Table.replace for database except for Map conversion It should be finished by 2024-02-05.

Next Day: map conversion

[enso-bot]

Greg Travis reports a new 🔴 DELAY for today (2024-02-05):

Summary: There is 1 days delay in implementation of the Add a new function Replace to Table.enso (#8578) task.
It will cause 0 days delay for the delivery of this weekly plan.

Delay Cause: Map conversion is tricky in db

[enso-bot]

Greg Travis reports a new STANDUP for today (2024-02-05):

Progress: Implemented Map conversion in database Table.replace It should be finished by 2024-02-06.

Next Day: tests

[enso-bot]

Greg Travis reports a new 🔴 DELAY for today (2024-02-06):

Summary: There is 1 days delay in implementation of the Add a new function Replace to Table.enso (#8578) task.
It will cause 0 days delay for the delivery of this weekly plan.

Delay Cause: Map conversion is tricky in db

[enso-bot]

Greg Travis reports a new STANDUP for today (2024-02-06):

Progress: Tests for Map conversion in database Table.replace; weird parsing errors It should be finished by 2024-02-07.

Next Day: from-conversions