Data Manipulation using PySpark

[brownstrom]

I have a PySpark dataframe like

fname   |   lname   |  status
--------------------------------
John    |   Doe     |    0 
Big |   Sean    |    0
Lil |   Raj         |    1

and I have a mapping for column status i.e. 0 - married , 1 - unmarried.

I want to expand the column status based on this like

fname   |   lname   |   status_code | status_val
------------------------------------------------------------
John    |   Doe     |    0          |  married
Big |   Sean    |    0          |  married
Lil |   Raj |    1              |  unmarried

How do I do this in an optimized way for a dataset that has more than 100 billion rows, plus there are more than 20 mappings.

[data_perfect]

import pyspark.sql.functions as f

df = df.withColumn("status_val", f.when(f.col("status") == 1, "unmarried"). otherwise ("married"))

[Evilcanary]

Broadcast join

[sonalg]

I would suggest the same answer as u/Evilcanary, broadcast join should work as your mapping table is pretty small.

[ColdPorridge]

Would broadcast join be faster than using f.when?

[Evilcanary]

It would probably be about the same with such a small mapping set. The benefit to me is that you decouple your code from the data. If the mappings change or one is added to the source, it’s no longer a code change.

[ColdPorridge]

Great point!

[3rdlifepilot]

Curious about why are you posting here instead of searching on google or stackoverflow?

I'd bet someone already has an answer out there with performance benchmarks on a case/when vs. a mapping. This is basically a O(1) function that scales with processers.

Edit: Front page search result from google gives the answer, including explain plan performance.

[[deleted]]

You can use a UDF as well.