← Back to Documentations

Signature Description 
enum class  gen_join_type : unsigned char  {

    no_match = 1,      // Don't include any of the join participants 
    include_left = 2,  // Include the LHS
    include_right = 3, // Include the RHS
    include_both = 4,  // Include both join participants
};
Enumerated type to specify result of a predicate for join.


Signature Description Parameters 
template<typename RHS_T,
         comparable LHS_COL_T, comparable RHS_COL_T,
         typename F,
         typename ... Ts>
DataFrame<unsigned long>
gen_join(const RHS_T &rhs,
         const char *lhs_col_name,
         const char *rhs_col_name,
         F &predicate) const requires
std::invocable<F, const IndexType &,
                  const typename RHS_T::IndexType &,
                  const LHS_COL_T &,
                  const RHS_COL_T &> &&
std::same_as<std::invoke_result_t<F, const IndexType &,
                                     const typename RHS_T::IndexType &,
                                     const LHS_COL_T &,
                                     const RHS_COL_T &>,
             gen_join_type>;
This is the most general method to join two DataFrames. It requires the name of two columns, one from self (lhs) and one from rhs. The columns may or may not have the same type. It also takes a function called predicate. Datapoints from both self and rhs indices and the two columns are passed to predicate one by one.
The returned DataFrame's index column is unsigned long.

NOTE: The datapoints are passed to predicate in the same order that they are. So DataFrames' order (sorting) and predicate logic must match.
NOTE: The columns are processed until the minimum length of the two columns. If you have columns of different length, you may consider calling make_consistent() before joining.
NOTE: All same name columns in lhs and rhs will have lhs. and rhs. prefixes in their names in the returned DataFrame.
NOTE: The result DataFrame will at least have two column names lhs.INDEX and rhs.INDEX containing the lhs and rhs indices datapoints

The predicate has the following parameters:
  1. A const reference to the index datapoint of self
  2. A const reference to the index datapoint of rhs
  3. A const reference to the lhs (self) column datapoint
  4. A const reference to the rhs column datapoint

NOTE: This join is done by what is called in the industry a table-scan
 RHS_T: Type of the rhs DataFrame
LHS_COL_T: Type of the lhs column
RHS_COL_T: Type of the rhs column
F: Type of the predicate
Ts: List all the types of all data columns. A type should be specified in the list only once.
rhs: rhs DataFrame
lhs_col_name: lhs (self) column name.
rhs_col_name: rhs column name.
predicate: A function/functor described above that determines the result
 
template<typename RHS_T,
         comparable LHS_COL1_T, comparable RHS_COL1_T,
         comparable LHS_COL2_T, comparable RHS_COL2_T,
         typename F,
         typename ... Ts>
DataFrame<unsigned long>
gen_join(const RHS_T &rhs,
         const char *lhs_col1_name,
         const char *rhs_col1_name,
         const char *lhs_col2_name,
         const char *rhs_col2_name,
         F &predicate) const requires
std::invocable<F, const IndexType &,
                  const typename RHS_T::IndexType &,
                  const LHS_COL1_T &,
                  const RHS_COL1_T &,
                  const LHS_COL2_T &,
                  const RHS_COL2_T &> &&
std::same_as<std::invoke_result_t<F, const IndexType &,
                                     const typename RHS_T::IndexType &,
                                     const LHS_COL1_T &,
                                     const RHS_COL1_T &,
                                     const LHS_COL2_T &,
                                     const RHS_COL2_T &>,
             gen_join_type>;
This is like the above gen_join but the joining logic is based on two sets of columns.

The predicate has the following parameters:
  1. A const reference to the index datapoint of self
  2. A const reference to the index datapoint of rhs
  3. A const reference to the lhs (self) first column datapoint
  4. A const reference to the rhs first column datapoint
  5. A const reference to the lhs (self) second column datapoint
  6. A const reference to the rhs second column datapoint
 RHS_T: Type of the rhs DataFrame
LHS_COL1_T: Type of the lhs first column
RHS_COL1_T: Type of the rhs first column
LHS_COL2_T: Type of the lhs second column
RHS_COL2_T: Type of the rhs second column
F: Type of the predicate
Ts: List all the types of all data columns. A type should be specified in the list only once.
rhs: rhs DataFrame
lhs_col1_name: lhs (self) first column name.
rhs_col1_name: rhs first column name.
lhs_col2_name: lhs (self) second column name.
rhs_col2_name: rhs second column name.
predicate: A function/functor described above that determines the result
 
template<typename RHS_T, typename F, typename ... Ts>
DataFrame<unsigned long>
gen_join(const RHS_T &rhs, F &predicate) const requires
std::invocable<F, const IndexType &, const typename RHS_T::IndexType &> &&
std::same_as<std::invoke_result_t<F, const IndexType &,
                                     const typename RHS_T::IndexType &>,
             gen_join_type>;
This is like the above gen_join but the joining logic is only based on index columns of LHS (self) and RHS (there is no data column involved).

The predicate has the following parameters:
  1. A const reference to the index datapoint of self
  2. A const reference to the index datapoint of rhs
 RHS_T: Type of the rhs DataFrame
F: Type of the predicate
Ts: List all the types of all data columns. A type should be specified in the list only once.
rhs: rhs DataFrame
predicate: A function/functor described above that determines the result
 
static void test_gen_join()  {

    std::cout << "\nTesting gen_join( ) ..." << std::endl;

    std::vector<unsigned long>  idx = { 123450, 123451, 123452, 123453, 123454, 123455, 123456, 123457, 123458, 123459, 123460, 123461, 123462, 123466 };
    std::vector<double>         d1 = { 1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12, 13, 14 };
    std::vector<double>         d2 = { 8, 9, 10, 11, 12, 13, 14, 20, 22, 23, 30, 31, 32, 1.89 };
    std::vector<double>         d3 = { 15, 16, 15, 18, 19, 16, 21, 0.34, 1.56, 0.34, 2.3, 0.34, 19.0 };
    std::vector<int>            i1 = { 22, 23, 24, 25, 99 };
    ULDataFrame                 df;

    df.load_data(std::move(idx),
                 std::make_pair("col_1", d1),
                 std::make_pair("col_2", d2),
                 std::make_pair("col_3", d3),
                 std::make_pair("col_4", i1));

    auto    vw = df.get_view<double, int>({ "col_1", "col_2", "col_3", "col_4" });

    std::vector<unsigned long>  idx2 = { 123452, 123453, 123455, 123458, 123454, 223450, 223451, 223454, 223456, 123459, 223459, 223460, 223461, 123466 };
    std::vector<double>         d12 = { 11, 12, 13, 14, 15, 16, 17, 18, 19, 110, 111, 112, 113, 114 };
    std::vector<double>         d22 = { 8, 19, 110, 111, 9, 113, 114, 99, 122, 123, 130, 131, 20, 11.89 };
    std::vector<double>         d32 = { 115, 116, 115, 118, 119, 116, 121, 10.34, 11.56, 10.34, 12.3, 10.34, 119.0 };
    std::vector<int>            i12 = { 122, 123, 124, 125, 199 };
    ULDataFrame                 df2;

    df2.load_data(std::move(idx2),
                  std::make_pair("xcol_1", d12),
                  std::make_pair("col_2", d22),
                  std::make_pair("xcol_3", d32),
                  std::make_pair("col_4", i12));

    auto    vw2 = df2.get_view<double, int>({ "xcol_1", "col_2", "xcol_3", "col_4" });

    auto    predicate =
        [](const unsigned long &, const unsigned long &, const double &lhs_val, const double &rhs_val) -> gen_join_type  {
            if (lhs_val == rhs_val)
                return (gen_join_type::include_both);
            return (gen_join_type::no_match);
        };

    auto    inner_result = df.gen_join<decltype(df2), double, double, decltype(predicate), double, int>(df2, "col_2", "col_2", predicate);
    auto    inner_result_vw = vw.gen_join<decltype(df2), double, double, decltype(predicate), double, int>(df2, "col_2", "col_2", predicate);

    assert(inner_result.get_index().size() == 1);
    assert(inner_result.get_column<double>("xcol_1")[0] == 11.0);
    assert(inner_result.get_column<double>("xcol_3")[0] == 115.0);
    assert(inner_result.get_column<int>("lhs.col_4")[0] == 22);
    assert(inner_result.get_column<unsigned long>("rhs.INDEX")[0] == 123452);
    assert(inner_result.get_column<unsigned long>("lhs.INDEX")[0] == 123450);

    assert(inner_result_vw.get_index().size() == 1);
    assert(inner_result_vw.get_column<double>("col_1")[0] == 1.0);
    assert(inner_result_vw.get_column<int>("lhs.col_4")[0] == 22);
    assert(inner_result_vw.get_column<unsigned long>("rhs.INDEX")[0] == 123452);

    auto    predicate2 =
        [](const unsigned long &, const unsigned long &, const double &lhs_val, const double &rhs_val) -> gen_join_type  {
            if (lhs_val == rhs_val)
                return (gen_join_type::include_both);
            return (gen_join_type::include_right);
        };

    auto    result_vw2 = vw.gen_join<decltype(df2), double, double, decltype(predicate2), double, int>(df2, "col_2", "col_2", predicate2);

    assert(result_vw2.get_index().size() == 14);
    assert(result_vw2.get_column<double>("xcol_1")[0] == 11.0);
    assert(result_vw2.get_column<double>("xcol_1")[7] == 18.0);
    assert(result_vw2.get_column<double>("xcol_1")[13] == 114.0);
    assert(result_vw2.get_column<double>("xcol_3")[0] == 115.0);
    assert(result_vw2.get_column<double>("xcol_3")[10] == 12.3);
    assert(result_vw2.get_column<int>("lhs.col_4")[0] == 22);
    assert(result_vw2.get_column<int>("lhs.col_4")[6] == 0);
    assert(result_vw2.get_column<int>("lhs.col_4")[12] == 0);
    assert(result_vw2.get_column<int>("rhs.col_4")[0] == 122);
    assert(result_vw2.get_column<int>("rhs.col_4")[6] == 0);
    assert(result_vw2.get_column<int>("rhs.col_4")[12] == 0);
    assert(result_vw2.get_column<unsigned long>("rhs.INDEX")[0] == 123452);
    assert(result_vw2.get_column<unsigned long>("lhs.INDEX")[0] == 123450);
    assert(result_vw2.get_column<unsigned long>("lhs.INDEX")[8] == 0 );

    auto    predicate3 =
        [](const unsigned long &, const unsigned long &, const int &col_4, const double &xcol_1) -> gen_join_type  {
            if ((col_4 < 23 && col_4 != 0) || xcol_1 > 112.0)
                return (gen_join_type::include_both);
            return (gen_join_type::no_match);
        };
    auto    result_vw3 = vw.gen_join<ULDataFrame, int, double, decltype(predicate3), double, int>(df2, "col_4", "xcol_1", predicate3);

    assert(result_vw3.get_index().size() == 3);
    assert(result_vw3.get_column<double>("xcol_1")[0] == 11.0);
    assert(result_vw3.get_column<double>("xcol_1")[1] == 113.0);
    assert(result_vw3.get_column<double>("xcol_1")[2] == 114.0);
    assert(result_vw3.get_column<unsigned long>("lhs.INDEX")[0] == 123450);
    assert(result_vw3.get_column<unsigned long>("lhs.INDEX")[1] == 123462);
    assert(result_vw3.get_column<unsigned long>("lhs.INDEX")[2] == 123466);
    assert(result_vw3.get_column<unsigned long>("rhs.INDEX")[0] == 123452);
    assert(result_vw3.get_column<unsigned long>("rhs.INDEX")[1] == 223461);
    assert(result_vw3.get_column<unsigned long>("rhs.INDEX")[2] == 123466);
    assert(result_vw3.get_column<int>("lhs.col_4")[0] == 22);
    assert(result_vw3.get_column<int>("lhs.col_4")[1] == 0);
    assert(result_vw3.get_column<int>("lhs.col_4")[2] == 0);
    assert(result_vw3.get_column<int>("rhs.col_4")[0] == 122);
    assert(result_vw3.get_column<int>("rhs.col_4")[1] == 0);
    assert(result_vw3.get_column<int>("rhs.col_4")[2] == 0);
    assert(result_vw3.get_column<double>("lhs.col_2")[0] == 8.0);
    assert(result_vw3.get_column<double>("lhs.col_2")[1] == 32.0);
    assert(result_vw3.get_column<double>("lhs.col_2")[2] == 1.89);
    assert(result_vw3.get_column<double>("rhs.col_2")[0] == 8.0);
    assert(result_vw3.get_column<double>("rhs.col_2")[1] == 20.0);
    assert(result_vw3.get_column<double>("rhs.col_2")[2] == 11.89);
    assert(result_vw3.get_column<double>("col_3")[0] == 15.0);
    assert(result_vw3.get_column<double>("col_3")[1] == 19.0);
    assert(std::isnan(result_vw3.get_column<double>("col_3")[2]));
    assert(result_vw3.get_column<double>("xcol_3")[0] == 115.0);
    assert(result_vw3.get_column<double>("xcol_3")[1] == 119.0);
    assert(std::isnan(result_vw3.get_column<double>("xcol_3")[2]));

    // Now join only by index
    //
    auto    pred_by_idx =
        [](const unsigned long &lhs_idx, const unsigned long &rhs_idx) -> gen_join_type  {
            if (lhs_idx == rhs_idx)  return (gen_join_type::include_both);
            return (gen_join_type::no_match);
        };
    auto    res_by_idx = df.gen_join<ULDataFrame, decltype(pred_by_idx), double, int>(df2, pred_by_idx);

    assert(res_by_idx.get_index().size() == 3);
    assert(res_by_idx.get_column<double>("xcol_1")[0] == 15.0);
    assert(res_by_idx.get_column<double>("xcol_1")[1] == 110.0);
    assert(res_by_idx.get_column<double>("xcol_1")[2] == 114.0);
    assert(res_by_idx.get_column<double>("xcol_3")[0] == 119.0);
    assert(res_by_idx.get_column<double>("xcol_3")[1] == 10.34);
    assert(std::isnan(res_by_idx.get_column<double>("xcol_3")[2]));
    assert(res_by_idx.get_column<unsigned long>("lhs.INDEX")[0] == 123454);
    assert(res_by_idx.get_column<unsigned long>("lhs.INDEX")[1] == 123459);
    assert(res_by_idx.get_column<unsigned long>("lhs.INDEX")[2] == 123466);
    assert(res_by_idx.get_column<unsigned long>("rhs.INDEX")[0] == 123454);
    assert(res_by_idx.get_column<unsigned long>("rhs.INDEX")[1] == 123459);
    assert(res_by_idx.get_column<unsigned long>("rhs.INDEX")[2] == 123466);
    assert(res_by_idx.get_column<int>("lhs.col_4")[0] == 99);
    assert(res_by_idx.get_column<int>("lhs.col_4")[1] == 0);
    assert(res_by_idx.get_column<int>("lhs.col_4")[2] == 0);
    assert(res_by_idx.get_column<double>("rhs.col_2")[0] == 9.0);
    assert(res_by_idx.get_column<double>("rhs.col_2")[1] == 123.0);
    assert(res_by_idx.get_column<double>("rhs.col_2")[2] == 11.89);
    assert(res_by_idx.get_column<double>("lhs.col_2")[0] == 12.0);
    assert(res_by_idx.get_column<double>("lhs.col_2")[1] == 23.0);
    assert(res_by_idx.get_column<double>("lhs.col_2")[2] == 1.89);
}

// -----------------------------------------------------------------------------

static void test_gen_join2()  {

    std::cout << "\nTesting gen_join( ) two columns ..." << std::endl;

    // Reuse the same frames as test_gen_join() so the two tests
    // are easy to compare side-by-side.
    //
    std::vector<unsigned long>  idx = { 123450, 123451, 123452, 123453, 123454, 123455, 123456, 123457, 123458, 123459, 123460, 123461, 123462, 123466 };
    std::vector<double>         d1 = { 1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12, 13, 14 };
    std::vector<double>         d2 = { 8, 9, 10, 11, 12, 13, 14, 20, 22, 23, 30, 31, 32, 1.89 };
    std::vector<double>         d3 = { 15, 16, 15, 18, 19, 16, 21, 0.34, 1.56, 0.34, 2.3, 0.34, 19.0 };
    std::vector<int>            i1 = { 22, 23, 24, 25, 99 };
    ULDataFrame                 df;

    df.load_data(std::move(idx),
                 std::make_pair("col_1", d1),
                 std::make_pair("col_2", d2),
                 std::make_pair("col_3", d3),
                 std::make_pair("col_4", i1));

    std::vector<unsigned long>  idx2 = { 123452, 123453, 123455, 123458, 123454, 223450, 223451, 223454, 223456, 123459, 223459, 223460, 223461, 123466 };
    std::vector<double>         d12 = { 11, 12, 13, 14, 15, 16, 17, 18, 19, 110, 111, 112, 113, 114 };
    std::vector<double>         d22 = { 8, 19, 110, 111, 9, 113, 114, 99, 122, 123, 130, 131, 20, 11.89 };
    std::vector<double>         d32 = { 115, 116, 115, 118, 119, 116, 121, 10.34, 11.56, 10.34, 12.3, 10.34, 119.0 };
    std::vector<int>            i12 = { 122, 123, 124, 125, 199 };
    ULDataFrame                 df2;

    df2.load_data(std::move(idx2),
                  std::make_pair("xcol_1", d12),
                  std::make_pair("col_2", d22),
                  std::make_pair("xcol_3", d32),
                  std::make_pair("col_4", i12));

    // Predicate 1 — pure inner join.
    // Both column pairs must satisfy their condition simultaneously:
    //   col_2 (lhs) == col_2 (rhs)   AND   col_1 (lhs) < 10
    // Only position 0 qualifies (lhs col_2=8 == rhs col_2=8, lhs col_1=1<10).
    //
    auto    pred_1 =
        [](const unsigned long &, const unsigned long &,
           const double &lhs_col2, const double &rhs_col2,
           const double &lhs_col1, const double &) -> gen_join_type  {
            if (lhs_col2 == rhs_col2 && lhs_col1 < 10.0)
                return (gen_join_type::include_both);
            return (gen_join_type::no_match);
        };

    auto    result_1 {
        df.gen_join<decltype(df2),
                    double, double,  // lhs_col2, rhs_col2 types
                    double, double,  // lhs_col1, rhs_col1 types
                    decltype(pred_1),
                    double,
                    int>(df2, "col_2", "col_2", "col_1", "xcol_1", pred_1)
    };

    // col_2 is the same name on both sides -> lhs.col_2 / rhs.col_2
    // col_1 only exists on lhs, xcol_1 only on rhs -> no prefix needed
    //
    assert(result_1.get_index().size() == 1);
    assert(result_1.get_column<unsigned long>("lhs.INDEX")[0] == 123450UL);
    assert(result_1.get_column<unsigned long>("rhs.INDEX")[0] == 123452UL);
    assert(result_1.get_column<double>("col_1")[0] == 1.0);
    assert(result_1.get_column<double>("xcol_1")[0] == 11.0);
    assert(result_1.get_column<double>("lhs.col_2")[0] == 8.0);
    assert(result_1.get_column<double>("rhs.col_2")[0] == 8.0);
    assert(result_1.get_column<double>("xcol_3")[0] == 115.0);
    assert(result_1.get_column<double>("col_3")[0] == 15.0);

    // Predicate 2 — three distinct outcomes.
    // include_both  when lhs col_2 == rhs col_2  (position 0 only)
    // include_left  when lhs col_1 < rhs xcol_1  (positions 1-13, always true
    //               because d1 tops out at 14 and d12 starts at 11 with d12[0]
    //               already > d1[0]; positions 1+ all have d1<d12)
    // include_right never fires here (lhs col_1 is always < rhs xcol_1 when
    //               the col_2 condition doesn't hold), so we get 14 rows:
    //               1 both + 13 left.
    //
    auto    pred_2 =
        [](const unsigned long &, const unsigned long &,
           const double &lhs_col2, const double &rhs_col2,
           const double &lhs_col1, const double &rhs_xcol1) -> gen_join_type  {
            if (lhs_col2 == rhs_col2)
                return (gen_join_type::include_both);
            if (lhs_col1 < rhs_xcol1)
                return (gen_join_type::include_left);
            return (gen_join_type::include_right);
        };

    auto    result_2 {
        df.gen_join<decltype(df2),
                    double, double,  // lhs_col2, rhs_col2 types
                    double, double,  // lhs_col1, rhs_xcol1 types
                    decltype(pred_2),
                    double,
                    int>(df2, "col_2", "col_2", "col_1", "xcol_1", pred_2)
    };

    assert(result_2.get_index().size() == 14);

    // row 0: include_both (lhs col_2[0]=8 == rhs col_2[0]=8)
    //
    assert(result_2.get_column<unsigned long>("lhs.INDEX")[0] == 123450UL);
    assert(result_2.get_column<unsigned long>("rhs.INDEX")[0] == 123452UL);
    assert(result_2.get_column<double>("col_1")[0] == 1.0);
    assert(result_2.get_column<double>("xcol_1")[0] == 11.0);
    assert(result_2.get_column<double>("lhs.col_2")[0] == 8.0);
    assert(result_2.get_column<double>("rhs.col_2")[0] == 8.0);

    // row 1: include_left (lhs col_1[1]=2 < rhs xcol_1[1]=12, col_2 mismatch)
    //
    assert(result_2.get_column<unsigned long>("lhs.INDEX")[1] == 123451UL);
    assert(result_2.get_column<unsigned long>("rhs.INDEX")[1] == 0UL);
    assert(result_2.get_column<double>("col_1")[1] == 2.0);
    assert(std::isnan(result_2.get_column<double>("xcol_1")[1]));
    assert(result_2.get_column<double>("lhs.col_2")[1] == 9.0);
    assert(std::isnan(result_2.get_column<double>("rhs.col_2")[1]));

    // row 7: include_left — verify mid-sequence
    //
    assert(result_2.get_column<unsigned long>("lhs.INDEX")[7] == 123457UL);
    assert(result_2.get_column<unsigned long>("rhs.INDEX")[7] == 0UL);
    assert(result_2.get_column<double>("col_1")[7] == 8.0);
    assert(std::isnan(result_2.get_column<double>("xcol_1")[7]));
    assert(result_2.get_column<double>("lhs.col_2")[7] == 20.0);
    assert(std::isnan(result_2.get_column<double>("rhs.col_2")[7]));

    // row 13: include_left — last row
    //
    assert(result_2.get_column<unsigned long>("lhs.INDEX")[13] == 123466UL);
    assert(result_2.get_column<unsigned long>("rhs.INDEX")[13] == 0UL);
    assert(result_2.get_column<double>("col_1")[13] == 14.0);
    assert(std::isnan(result_2.get_column<double>("xcol_1")[13]));
    assert(result_2.get_column<double>("lhs.col_2")[13] == 1.89);
    assert(std::isnan(result_2.get_column<double>("rhs.col_2")[13]));

    // Columns not involved in the predicate are still carried through
    // by join_helper_common_: col_3 (lhs-only), xcol_3 (rhs-only),
    // and col_4 (present in both -> lhs.col_4 / rhs.col_4).
    //
    assert(result_2.get_column<double>("col_3")[0] == 15.0);
    assert(result_2.get_column<double>("col_3")[1] == 16.0);
    assert(std::isnan(result_2.get_column<double>("xcol_3")[1]));
    assert(result_2.get_column<int>("lhs.col_4")[0] == 22);
    assert(result_2.get_column<int>("lhs.col_4")[1] == 23);
    assert(result_2.get_column<int>("rhs.col_4")[0] == 122);
    assert(result_2.get_column<int>("rhs.col_4")[1] == 0);
}

C++ DataFrame