Skip to main content

DB_Table.replace

replacelookup_tablecolumnsfrom_columnto_columnallow_unmatched_rowson_problems

Group: Text
Aliases: find replace

Documentation

Replaces values in the columns using lookup_table to specify a mapping from old to new values.

Arguments

  • lookup_table: the table to use as a mapping from old to new values. A Map can also be used here (in which case passing from_column or to_column is disallowed and will throw an Illegal_Argument error.
  • columns: the column or columns within self to perform the replace on.
  • from_column: the column within lookup_table to match against columns in self.
  • to_column: the column within lookup_table to get new values from.
  • 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.

Examples

Replace values in column 'x' using a lookup table.

      table = Table.new [['x', [1, 2, 3, 4]], ['y', ['a', 'b', 'c', 'd']], ['z', ['e', 'f', 'g', 'h']]]
# | x | y | z
# ---+---+---+---
# 0 | 1 | a | e
# 1 | 2 | b | f
# 2 | 3 | c | g
# 3 | 4 | d | h

lookup_table = Table.new [['x', [1, 2, 3, 4]], ['new_x', [10, 20, 30, 40]]]
# | old_x | new_x
# ---+-------+-------
# 0 | 1 | 10
# 1 | 2 | 20
# 2 | 3 | 30
# 3 | 4 | 40

result = table.replace lookup_table 'x'
# | x | y | z
# ---+----+---+---
# 0 | 10 | a | e
# 1 | 20 | b | f
# 2 | 30 | c | g
# 3 | 40 | d | h

Errors

  • If this table or the lookup table is lacking any of the columns specified by from_column, to_column, or columns, a Missing_Input_Columns 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 key column contains Nothing values in the 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 problem is reported.

Remarks

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.