Replace column values when matching keys in a Map

Question

I have a dataframe with a column named source_system that has the values contained in the keys of this Map:

val convertSourceSystem = Map (
        "HON_MUREX3FXFI"  -> "MX3_FXFI",
        "MAD_MUREX3FXFI"  -> "MX3_FXFI",
        "MEX_MUREX3FXFI"  -> "MX3_LT",
        "MX3BRASIL"       -> "MX3_BR",
        "MX3EUROPAEQ_MAD" -> "MX3_EQ",
        "MX3EUROPAEQ_POL" -> "MX3_EQ",
        "MXEUROPA_MAD"    -> "MX2_EU",
        "MXEUROPA_PT"     -> "MX2_EU",
        "MXEUROPA_UK"     -> "MX2_EU",
        "MXLATAM_CHI"     -> "MX2_LT",
        "MXLATAM_NEW"     -> "MX2_LT",
        "MXLATAM_SOV"     -> "MX2_LT",
        "POR_MUREX3FXFI"  -> "MX3_FXFI",
        "SHN_MUREX3FXFI"  -> "MX3_FXFI",
        "UK_MUREX3FXFI"   -> "MX3_FXFI",
        "SOV_MX3LATAM"    -> "MX3_LT"
    )

I need to replace them to the short code, and using a foldLeft to do a withColumn is giving me only null values, because its replacing all the values and the last source_system is not in the map:

val ssReplacedDf = irisToCreamSourceSystem.foldLeft(tempDf) { (acc, filter) =>
      acc.withColumn("source_system", when( col("source_system").equalTo(lit(filter._1)),
          lit(filter._2)))
    }

Raphael Roth · Accepted Answer · 2020-01-09 16:37:48Z

2

I would suggest another solution by joining the translation table :

// convert Map to a DataFrame
val convertSourceSystemDF = convertSourceSystem.toSeq.toDF("source_system","source_system_short")

tempDf.join(broadcast(convertSourceSystemDF),Seq("source_system"),"left")
  // override column with short name, alternatively use withColumnRenamed
  .withColumn("source_system",$"source_system_short")
  .drop("source_system_short)

answered Jan 9, 2020 at 16:37

Raphael Roth

27.3k19 gold badges98 silver badges152 bronze badges

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

Collectives™ on Stack Overflow

Replace column values when matching keys in a Map

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