title: Spark Scala Dataframe - replace/join column values with values from another dataframe (but is transposed)
body: <p>I have a table with ~300 columns filled with characters (stored as String):</p>
  
  <pre><code>valuesDF:
  
  | FavouriteBeer | FavouriteCheese | ...
  |---------------|-----------------|--------
  | U             | C               | ...
  | U             | E               | ...
  | I             | B               | ...
  | C             | U               | ...
  | ...           | ...             | ...
  </code></pre>
  
  <p>I have a Data Summary, which maps the characters onto their actual meaning. It is in this form:</p>
  
  <pre><code>summaryDF:
  
  | Field            | Value | ValueDesc     |
  |------------------|-------|---------------|
  |  FavouriteBeer   |   U   |  Unknown      |
  |  FavouriteBeer   |   C   |  Carlsberg    |
  |  FavouriteBeer   |   I   |  InnisAndGunn |
  |  FavouriteBeer   |   D   |  DoomBar      |
  |  FavouriteCheese |   C   |  Cheddar      |
  |  FavouriteCheese |   E   |  Emmental     |
  |  FavouriteCheese |   B   |  Brie         |
  |  FavouriteCheese |   U   |  Unknown      |
  |  ...             |  ...  |    ...        |
  </code></pre>
  
  <p>I want to programmatically replace the character values of each column in <code>valuesDF</code> with the Value Descriptions from <code>summaryDF</code>. This is the result I'm looking for:</p>
  
  <pre><code>finalDF:
  
  | FavouriteBeer | FavouriteCheese | ...
  |---------------|-----------------|--------
  | Unknown       | Cheddar         | ...
  | Unknown       | Emmental        | ...
  | InnisAndGunn  | Brie            | ...
  | Carlsberg     | Unknown         | ...
  | ...           | ...             | ...
  </code></pre>
  
  <p>As there are ~300 columns, I'm not keen to type out <code>withColumn</code> methods for each one.</p>
  
  <p>Unfortunately I'm a bit of a novice when it comes to programming for Spark, although I've picked up enough to get by over the last 2 months. </p>
  
  <hr>
  
  <p>What I'm pretty sure I need to do is something along the lines of:</p>
  
  <ol>
  <li><code>valuesDF.columns.foreach { col =&gt; ...... }</code> to iterate over each column</li>
  <li>Filter <code>summaryDF</code> on <code>Field</code> using <code>col</code> String value</li>
  <li>Left join <code>summaryDF</code> onto <code>valuesDF</code> based on current column</li>
  <li><code>withColumn</code> to replace the original character code column from <code>valuesDF</code> with new description column</li>
  <li>Assign new DF as a <code>var</code></li>
  <li>Continue loop</li>
  </ol>
  
  <p>However, trying this gave me Cartesian product error (I made sure to define the join as <code>"left"</code>).</p>
  
  <p>I tried and failed to pivot <code>summaryDF</code> (as there are no aggregations to do??) then join both dataframes together.</p>
  
  <p>This is the sort of thing I've tried, and always getting a <code>NullPointerException</code>. I know this is <strong>really</strong> not the right way to do this, and can see why I'm getting Null Pointer... but I'm really stuck and reverting back to old, silly &amp; bad Python habits in desperation.</p>
  
  <pre><code>var valuesDF = sourceDF
  // I converted summaryDF to a broadcasted RDD 
  // because its small and a "constant" lookup table
  summaryBroadcast
   .value
   .foreach{ x =&gt;
  
     // searchValue = Value (e.g. `U`), 
     // replaceValue = ValueDescription (e.g. `Unknown`), 
  
     val field = x(0).toString
     val searchValue = x(1).toString
     val replaceValue = x(2).toString
  
     // error catching as summary data does not exactly mapping onto field names
     // the joys of business people working in Excel...
     try {
       // I'm using regexp_replace because I'm lazy
       valuesDF = valuesDF
         .withColumn( attribute, regexp_replace(col(attribute), searchValue, replaceValue ))
     }
     catch {case _: Exception =&gt;
       null
     }
  }
  </code></pre>
  
  <p>Any ideas? Advice? Thanks.</p>