Replace values of several columns with values mapping in other dataframe PySpark

Question

I need to replace values of several columns (many more than those in the example, so I would like to avoid doing multiple left joins) of a dataframe with values from another dataframe (mapping).

Example:

df1 EXAM

id	question1	question2	question3
1	12	12	5
2	12	13	6
3	3	7	5

df2 VOTE MAPPING :

id	description
3	bad
5	insufficient
6	sufficient
12	very good
13	excellent

Output

id	question1	question2	question3
1	very good	very good	insufficient
2	very good	excellent	sufficient
3	bad	null	insufficient

Edit 1: Corrected id for excellent in vote map

Jonathan · Accepted Answer · 2023-01-18 02:52:55Z

1

First of all, you can create a reference dataframe:

df3 = df2.select(
    func.create_map(func.col('id'), func.col('desc')).alias('ref')
).groupBy().agg(
    func.collect_list('ref').alias('ref')
).withColumn(
    'ref', func.udf(lambda lst: {k:v for element in lst for k, v in element.items()}, returnType=MapType(StringType(), StringType()))(func.col('ref'))
)
+---------------------------------------------------------------------------+
|ref                                                                        |
+---------------------------------------------------------------------------+
|{3 -> bad, 12 -> good, 5 -> insufficient, 13 -> excellent, 6 -> sufficient}|
+---------------------------------------------------------------------------+

Then you can replace the value in question columns by getting the value in reference with 1 crossJoin:

df4 = df1.crossJoin(df3)\
    .select(
        'id',
        *[func.col('ref').getItem(func.col(col)).alias(col) for col in df1.columns[1:]]
    )
df4.show(10, False)
+---+----+---------+------------+
|id |q1  |q2       |q3          |
+---+----+---------+------------+
|1  |good|good     |insufficient|
|2  |good|excellent|sufficient  |
|3  |bad |null     |insufficient|
+---+----+---------+------------+

answered Jan 18, 2023 at 2:52

Jonathan

2,2832 gold badges12 silver badges31 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Jonathan Over a year ago

Hi @Jresearcher, can my solution solve your question?

Collectives™ on Stack Overflow

Replace values of several columns with values mapping in other dataframe PySpark

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related