How to find rows in df2 which are not available in df1?

user_16831723 0
Viewed 39

How I can find the complement of a dataframe with respect of another dataframe? In pandas it can be done by the following code:

df = df1.merge(df2, how = 'outer' ,indicator=True).loc[lambda x : x['_merge']=='right_only']

Example:

+---------+----+
|     City|Temp|
+---------+----+
| New York|  59|
|  Chicago|  29|
|    Tokyo|  73|
|    Paris|  56|
|New Delhi|  48|
+---------+----+

+---------+----+
|     City|Temp|
+---------+----+
|   London|  55|
| New York|  55|
|    Tokyo|  73|
|New Delhi|  85|
|    Paris|  56|
+---------+----+

Result:

+---------+----+----------+
|     City|Temp|_merge    |
+---------+----+----------+
|   London|  55|right_only|
|New Delhi|  85|right_only|
| New York|  55|right_only|
+---------+----+----------+
pyspark
3 Answers

df1.join(df2, ['City', 'Temp'], 'outer').filter(" id1 IS NULL ")

dt1 = [
    (0, 'New York',  59),
    (1, 'Chicago',   29),
    (2, 'Tokyo',     73),
    (3, 'Paris',     56),
    (4, 'New Delhi', 48),
]
df1 = spark.createDataFrame(dt1, ['id1','City', 'Temp'])

dt2 = [
    (0, 'London',    55),
    (1, 'New York',  55),
    (2, 'Tokyo',     73),
    (3, 'New Delhi', 85),
    (4, 'Paris',     56),
]
df2 = spark.createDataFrame(dt2, ['id2','City', 'Temp'])

(
    df1.join(df2, ['City', 'Temp'], 'outer')
        .filter(" id1 IS NULL ")
    .sort('id2')
        .show(10, False)
)

# +---------+----+----+---+
# |City     |Temp|id1 |id2|
# +---------+----+----+---+
# |London   |55  |null|0  |
# |New York |55  |null|1  |
# |New Delhi|85  |null|3  |
# +---------+----+----+---+
user_195463010

You can use subtract.

df = df2.subtract(df1)

Result

+---------+----+
|     City|Temp|
+---------+----+
| New York|  55|
|   London|  55|
|New Delhi|  85|
+---------+----+
user_29561350

You can also try "left_anti" join. Its Venn diagram looks like this:

enter image description here

And the code would look like this:

df = (
    df2
    .join(df1, ['City', 'Temp'], 'left_anti')
)

output:

+---------+----+
|     City|Temp|
+---------+----+
|   London|  55|
|New Delhi|  85|
| New York|  55|
+---------+----+
user_104904280
Related