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.