XLookup

Excel XLOOKUP / RECHERCHEX on Spark DataFrames.

fabrictools.excel.lookup.XLookup(left: pyspark.sql.DataFrame, lookup_col: str, right: pyspark.sql.DataFrame, match_col: str, return_col: str, *, default: Any = None, output_name: str | None = None) → pyspark.sql.DataFrame[source]

Lookup a value from right and add it to left (Excel XLOOKUP / RECHERCHEX).

Performs a left join on lookup_col (left) and match_col (right), keeping the first match when right contains duplicate keys (Excel behaviour).

Parameters:

  • left (DataFrame) – Driving dataframe (equivalent to the row with [@[lookup_col]]).

  • lookup_col (str) – Column on left holding the lookup key.

  • right (DataFrame) – Lookup table.

  • match_col (str) – Column on right to match against.

  • return_col (str) – Column on right whose value is returned.

  • default (Any) – Value used when no match is found (None keeps null).

  • output_name (str | None) – Name of the added column (defaults to return_col).

Returns:

left with the looked-up column appended.

Return type:

DataFrame

Raises:

ValueError – If a required column does not resolve.

Example

>>> from fabrictools import Excel
>>> df = Excel.XLookup(projects, "RAO CODE", customer_projects, "RAO CODE", "Date")