Joining DataFrames

From QPR ProcessAnalyzer Wiki
Revision as of 20:00, 21 February 2024 by Ollvihe (talk | contribs)
Jump to navigation Jump to search

Join function for DataFrame performs a joining operation between two DataFrames.

Parameters:

  1. DataFrame: Other DataFrame in the join.
  2. Columns to match: Columns which the joining is based on, can be defined as follows:
    • If joining using one column having the same name in both DataFrames, the column name is specified as as string.
    • If joining using several columns having the same names in both DataFrames, the column names are specified as a string array.
    • If joining using columns having different names between the DataFrames, columns are specified as an array of key-value pairs, where the key is the column name in the left side DataFrame, and value is the column name in the right side DataFrame.
  3. Join type which can be
    • inner (default): row is generated if both DataFrames have the key.
    • leftouter: at least one row is generated for each left side DataFrame row, even if there is no matching other row (in that case null is given as value for the other columns).
    • rightouter: at least one row is generated for each right side DataFrame row, even if there is no matching other row (in that case null is given as value for the other columns).
    • outer: at least one row is generated both for the left and right side DataFrames even if there is no matching other row (in that case null is given as value for the other columns).
    • cross: One row is generated for each combination of the joined DataFrames. The cross join doesn't have the matching criteria at all. Note that the cross join might be very slow operation.

Examples:

let left = ToDataFrame([[0, "zero"], [1, "one"]], ["id", "left"]);
let right = ToDataFrame([[0, "zero"], [2, "two"], [3, "three"]], ["id", "right"]);
left.join(right, "id").ToCsv();

Returns: id;left;right 0;zero;zero

let left = ToDataFrame([[0, "zero"], [1, "one"]], ["id", "left"]); let right = ToDataFrame([[0, "zero"], [2, "two"], [3, "three"]], ["id", "right"]); left.join(right, "id", "leftouter").ToCsv();

Returns: id;left;right 0;zero;zero 1;one;

let left = ToDataFrame([[0, 0, "zerozeroleft"], [0, 1, "zeroleft"], [1, 2, "oneleft"]], ["idleft1", "idleft2", "left"]); let right = ToDataFrame([[0, 0, "zerozeroright"], [0, 1, "zeroright"], [2, 3, "tworight"], [3, 4, "threeright"]], ["idright1", "idright2", "right"]); left.join(right, ["idleft1": "idright1"], "inner");

Returns: idleft1;idleft2;left;idright2;right 0;0;zerozeroleft;0;zerozeroright 0;0;zerozeroleft;1;zeroright 0;1;zeroleft;0;zerozeroright 0;1;zeroleft;1;zeroright

let left = ToDataFrame([[0, 0, "zerozeroleft"], [0, 1, "zeroleft"], [1, 2, "oneleft"]], ["idleft1", "idleft2", "left"]); let right = ToDataFrame([[0, 0, "zerozeroright"], [0, 1, "zeroright"], [2, 3, "tworight"], [3, 4, "threeright"]], ["idright1", "idright2", "right"]); left.join(right, ["idleft1": "idright1", "idleft2": "idright2"], "inner");

Returns: idleft1;idleft2;left;right 0;0;zerozeroleft;zerozeroright 0;1;zeroleft;zeroright