How to set new flag based on condition in pyspark?

Question

I have two data frames like below.

  df = spark.createDataFrame(sc.parallelize([[1,1,2],[1,2,9], [2,1,2],[2,2,1],
                [4,1,5],[4,2,6]]), ["sid","cid","Cr"])
  df.show() 
+---+---+---+
|sid|cid| Cr|
+---+---+---+
|  1|  1|  2|
|  1|  2|  9|
|  2|  1|  2|
|  2|  2|  1|
|  4|  1|  5|
|  4|  2|  6|
|  5|  1|  3|
|  5|  2|  8|
+---+---+---+

next I have created df1 like below.

df1 = spark.createDataFrame(sc.parallelize([[1,1],[1,2],[1,3], [2,1],[2,2],[2,3],[4,1],[4,2],[4,3],[5,1],[5,2],[5,3]]), ["sid","cid"])

df1.show()
+---+---+
|sid|cid|
+---+---+
|  1|  1|
|  1|  2|
|  1|  3|
|  2|  1|
|  2|  2|
|  2|  3|
|  4|  1|
|  4|  2|
|  4|  3|
|  5|  1|
|  5|  2|
|  5|  3|
+---+---+

now I want my final output should be like below i.e . if any of the data presented i.e. if (df1.sid==df.sid)&(df1.cid==df.cid) then flag value 1 else 0. and missing Cr values will be '0'

+---+---+---+----+
|sid|cid| Cr|flag|
+---+---+---+----+
|  1|  1|  2|  1 |
|  1|  2|  9|  1 |
|  1|  3|  0|  0 |
|  2|  1|  2|  1 |
|  2|  2|  1|  1 |
|  2|  3|  0|  0 |
|  4|  1|  5|  1 |
|  4|  2|  6|  1 |
|  4|  3|  0|  0 |
|  5|  1|  3|  1 |
|  5|  2|  8|  1 |
|  5|  3|  0|  0 |
+---+---+---+----+

please help me on this.

@eliasah I'm sleepy and I think I flagged it incorrectly. Please re-open. Sorry for the trouble. — philantrovert, Jan 31 '18 at 14:11
I trusted your judgement @philantrovert so I overlooked it. It's ok :) — eliasah, Jan 31 '18 at 14:19
It sounds like you would like to **outer join** df and df1 and add an indicator for matching rows. If so, please update the question to be more clear. — C8H10N4O2, Jan 31 '18 at 15:11

score 1 · Accepted Answer · answered Jan 31 '18 at 15:11

With data:

from pyspark.sql.functions import col, when, lit, coalesce

df = spark.createDataFrame(
    [(1, 1, 2), (1, 2, 9), (2, 1, 2), (2, 2, 1), (4, 1, 5), (4, 2, 6), (5, 1, 3), (5, 2, 8)], 
   ("sid", "cid", "Cr"))

df1 = spark.createDataFrame(
    [[1,1],[1,2],[1,3], [2,1],[2,2],[2,3],[4,1],[4,2],[4,3],[5,1],[5,2],[5,3]],
    ["sid","cid"])

outer join:

joined = (df.alias("df")
    .join(
        df1.alias("df1"),
        (col("df.sid") == col("df1.sid")) & (col("df.cid") == col("df1.cid")),
        "rightouter"))

and select

joined.select(
    col("df1.*"),
    coalesce(col("Cr"), lit(0)).alias("Cr"), 
    col("df.sid").isNotNull().cast("integer").alias("flag")
).orderBy("sid", "cid").show()

# +---+---+---+----+                 
# |sid|cid| Cr|flag|
# +---+---+---+----+
# |  1|  1|  2|   1|
# |  1|  2|  9|   1|
# |  1|  3|  0|   0|
# |  2|  1|  2|   1|
# |  2|  2|  1|   1|
# |  2|  3|  0|   0|
# |  4|  1|  5|   1|
# |  4|  2|  6|   1|
# |  4|  3|  0|   0|
# |  5|  1|  3|   1|
# |  5|  2|  8|   1|
# |  5|  3|  0|   0|
# +---+---+---+----+

How to set new flag based on condition in pyspark?

1 Answers1