Join PySpark dataframes on substring match (or contains)

user_1892435 0
Viewed 6867

I would like to perform a left join between two dataframes, but the columns don't match identically. The join column in the first dataframe has an extra suffix relative to the second dataframe.

from pyspark import SparkContext
import pyspark.sql.functions as f

sc = SparkContext()

df1 = sc.parallelize([
    ['AB-101-1', 'el1', 1.5],
    ['ABC-1020-1', 'el2', 1.3],
    ['AC-1030-1', 'el3', 8.5]
]).toDF(('id1', 'el', 'v1'))

df2 = sc.parallelize([
    ['AB-101', 3],
    ['ABC-1020', 4]
]).toDF(('id2', 'v2'))

The dataframe I would like to get as a result of the left join is:

df_join = sc.parallelize([
    ['AB-101-1', 'el1', 1.5, 'AB-101', 3],
    ['ABC-1020-1', 'el2', 1.3, 'ABC-1020', 4],
    ['AC-103-1', 'el3', 8.5, None, None]
]).toDF(('id1', 'el', 'v1', 'id2', 'v2'))

I would be happy to use pyspark.sql.substring to take "all except the final 2 characters", or to use something like pyspark.sql.like, but I can't figure out how to make either of these work properly inside the join.

pyspark
3 Answers

In your specific case, regexp_extract is probably your best option, but in the general case you can use:

df_join = df1.join(df2, df2.id2.contains(df1.id1), how='left')
user_115145520
Related