Pyspark: What type of join can I use?

Ali Z 0
Viewed 27

To start, I have the following dataframes, A and B. Below is the result I am trying to output the following.

To sum up the goal, I am looking to create a resulting dataframe where:

  1. I use df A as the base (it has more columns than B that I'd like to keep)
  2. If a value is None in A and a number in B, I take B.
  3. If a value is a number in both, I take B.
  4. If a value is a number in A but None in B, I take A.

Any suggestions? Also, if there is a way of doing this through a SQL query, that would work as well.

join pyspark sql python
1 Answers

Suggestion. Possible better way of doing this, but this should work. You would need to do this for all columns you want to do this on.

a_df = spark.table('table_a').select(PK, x2, col('Original').alias('A')) #dataframe a
b_df = spark.table('table_b').select(PK, col('Original').alias('B')) #dataframe b

joined_df = a_df.join(b_df, a_df.PK == b_df.PK, 'left') #join a on b

result_df = joined_df.withColumn('Original' , 
when((col('A').isNull() & col('B').isNotNull()), col('B'))\
.when((col('A').isNotNull() & col('B').isNull()), col('A')).otherwise(col('B')))

#If A is Null & B is not Null, then B
#If A is not Null & B is Null, then A
#Otherwise (if both are Null, both are not null) then B
Oscar Dyremyhr0
Related