Overwrite the rows containing NULL in Dataframe by another Dataframe in PySpark

Question

I have two dataframes df and df1, in df I have NULL in some columns, but in df1 I have non-null values for these columns. I just need to overwrite rows where the NULL exists.

The df is below:

+------------+--------------------+-------+---------------+--------------------+----------+------------+
|          Id|                Name|Country|           City|             Address|  Latitude|   Longitude|
+------------+--------------------+-------+---------------+--------------------+----------+------------+
| 42949672960|Americana Resort ...|     US|         Dillon|         135 Main St|      null|        null|
| 42949672965|Comfort Inn Delan...|     US|         Deland|400 E Internation...| 29.054737|  -81.297208|
| 60129542147|Ubaa Old Crawford...|     US|    Des Plaines|     5460 N River Rd|      null|        null|

The df1 is below:

+-------------+--------------------+-------+------------+--------------------+----------+------------+
|           Id|                Name|Country|        City|             Address|  Latitude|   Longitude|
+-------------+--------------------+-------+------------+--------------------+----------+------------+
|  42949672960|Americana Resort ...|     US|      Dillon|         135 Main St|39.6286685|-106.0451009|
|  60129542147|Ubaa Old Crawford...|     US| Des Plaines|     5460 N River Rd|42.0654049| -87.8916252|
+-------------+--------------------+-------+------------+--------------------+----------+------------+

I want this result:

+------------+--------------------+-------+---------------+--------------------+----------+------------+
|          Id|                Name|Country|           City|             Address|  Latitude|   Longitude|
+------------+--------------------+-------+---------------+--------------------+----------+------------+
| 42949672960|Americana Resort ...|     US|         Dillon|         135 Main St|39.6286685|-106.0451009|
| 42949672965|Comfort Inn Delan...|     US|         Deland|400 E Internation...| 29.054737|  -81.297208|
...
...

join then coalesce?

pltc
– pltc

2021-10-28 19:18:54 +00:00
Commented Oct 28, 2021 at 19:18 — pltc
– pltc, Commented Oct 28, 2021 at 19:18
@pltc Could you share the implementation, please?

Rustam-Z
– Rustam-Z

2021-10-28 19:21:53 +00:00
Commented Oct 28, 2021 at 19:21 — Rustam-Z
– Rustam-Z, Commented Oct 28, 2021 at 19:21

pltc · Accepted Answer · 2021-10-28 21:40:21Z

You can either left join or inner join them then using coalesce to pick first non-null lat/lon.

df1
+-----------+---------+----------+
|         id|      lat|       lon|
+-----------+---------+----------+
|42949672960|     null|      null|
|42949672965|29.054737|-81.297208|
|60129542147|     null|      null|
+-----------+---------+----------+

df2
+-----------+----------+------------+
|         id|       lat|         lon|
+-----------+----------+------------+
|42949672960|39.6286685|-106.0451009|
|60129542147|42.0654049| -87.8916252|
+-----------+----------+------------+

Join them together

from pyspark.sql import functions as F

(df1
    .join(df2, on=['id'], how='left')
    .select(
        F.col('id'),
        F.coalesce(df1['lat'], df2['lat']).alias('lat'),
        F.coalesce(df1['lon'], df2['lon']).alias('lon')
    )
    .show()
)
# +-----------+----------+------------+
# |         id|       lat|         lon|
# +-----------+----------+------------+
# |42949672965| 29.054737|  -81.297208|
# |60129542147|42.0654049| -87.8916252|
# |42949672960|39.6286685|-106.0451009|
# +-----------+----------+------------+

Collectives™ on Stack Overflow

Overwrite the rows containing NULL in Dataframe by another Dataframe in PySpark

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related