title: Imputing null values in spark dataframe, based on the row category, by fetching the values from another dataframe in Scala
body: <p>So i have a dataframe as shown below, that has been stored as a temporary view by the name <code>mean_value_gn5</code> so that i can query using <code>sql()</code>, whenever i need to fetch the data.</p>
  
  <pre><code>+-------+----+
  |Species|Avgs|
  +-------+----+
  |    NO2|  43|
  |    NOX|  90|
  |     NO|  31|
  +-------+----+
  </code></pre>
  
  <p>This dataframe stores the categorical average of the 'Species' rounded off to the nearest whole number using the <code>ceil()</code> function. I need to use these categorical averages to impute the missing values of column <code>Value</code> in my dataframe of interest <code>clean_gn5</code>. I created a new column <code>Value_imp</code> which would hold my final column with the imputed values.</p>
  
  <p>I made an attempt to do so as follows:</p>
  
  <pre><code>clean_gn5 = clean_gn5.withColumn("Value_imp",
        when($"Value".isNull, sql("Select Avgs from mean_value_gn5 where Species = "+$"Species").select("Avgs").head().getLong(0).toInt)
          .otherwise($"Value"))
  </code></pre>
  
  <p>The above mentioned code runs, but the values are getting incorrectly imputed i.e. for the row containing <code>Species</code> as <code>NO</code> the value getting imputed is <code>43</code> instead of <code>31</code>.</p>
  
  <p>Prior to doing this I first checked if i was able to fetch the values correctly by executing the following:</p>
  
  <pre><code>println(sql("Select Avgs from mean_value_gn5 where Species = 'NO'").select("Avgs").head().getLong(0))
  </code></pre>
  
  <p>I am able to fetch the value correctly after hardcoding the <code>Species</code> and as per my understanding <code>$"Species"</code> should help me fetch the value corresponding to the <code>Species</code> column for that particular row.</p>
  
  <p>Also I thought that probably i was missing the single quotes around the hardcoded <code>Species</code> value i.e. <code>'NO'</code>. So I tried doing the following</p>
  
  <pre><code> clean_gn5 = clean_gn5.withColumn("Value_imp",
        when($"Value".isNull, sql("Select Avgs from mean_value_gn5 where Species = '"+$"Species"+"'").select("Avgs").head().getLong(0).toInt)
          .otherwise($"Value"))
  </code></pre>
  
  <p>But that resulted in the following exception.</p>
  
  <pre><code>Exception in thread "main" java.util.NoSuchElementException: next on empty iterator
  </code></pre>
  
  <p>I am fairly new to Spark and Scala.</p>