Wsrtjtyk

I have 2 dataframes that I want joined.

product_no  code

12          aj

12          mn

13          aj



p_no       cde

12         *j

12         mn

13         *j



Result

product_no  code    p_no       cde

12          aj      12         *j

12          mn      12         mn

13          aj      12         *j

I want to match all codes that end with j with *j how do I do this? I know I have to join where product_no === p_no, but how do I join where if the last letter of a code is j, then join by *j?

EDIT

We are currently joining by product_no, and need to join the codes in the first dataframe to the codes in the second dataframe in an appropriate way.

The data for the second dataframe only contains 3 values for the code column: 2 letters, *j, or ** The conditions for the join are as follows:

1. If the actual code mn for example, exists in the second dataframe, then we join.


2. If the actual code is not in the second dataframe, then we check if the code in the first ends with j, if it does then we join where cde equals to *j
   


3. If the actual code does not end with j OR if we can't find *j in the corresponding dataframe, then we join by **
   

It's not clear what exactly you are trying to do. But if you want to join data frames on the condition [column last character] = *[column last character], you can use substring function as follows:

df1.join(df2, concat(lit("*"), substring(df1.col("code"),-1,1)) === df2.col("cde"))