title: Using when and otherwise while converting boolean values to strings in Pyspark
body: <p>I have a data frame in <code>Pyspark</code></p>
  
  <pre class="lang-python prettyprint-override"><code>df.show()
  
  
  +---+----+-------+----------+-----+------+
  | id|name|testing|avg_result|score|active|
  +---+----+-------+----------+-----+------+
  |  1| sam|   null|      null| null|  true|
  |  2| Ram|      Y|      0.05|   10| false|
  |  3| Ian|      N|      0.01|    1| false|
  |  4| Jim|      N|       1.2|    3|  true|
  +---+----+-------+----------+-----+------+
  </code></pre>
  
  <p>The schema is below:</p>
  
  <pre class="lang-python prettyprint-override"><code>DataFrame[id: int, name: string, testing: string, avg_result: string, score: string, active: boolean]
  </code></pre>
  
  <p>I want to convert <code>Y</code> to <code>True</code>, <code>N</code> to <code>False</code> <code>true</code> to <code>True</code> and <code>false</code> to <code>False</code>.</p>
  
  <p>When I do like below:</p>
  
  <pre class="lang-python prettyprint-override"><code>for col in cols:
      df = df.withColumn(col, f.when(f.col(col) == 'N', 'False').when(f.col(col) == 'Y', 'True').
                         when(f.col(col) == 'true', True).when(f.col(col) == 'false', False).otherwise(f.col(col)))
  </code></pre>
  
  <p>I get below error and there is no change in data frame</p>
  
  <pre class="lang-python prettyprint-override"><code>pyspark.sql.utils.AnalysisException: u"cannot resolve 'CASE WHEN (testing = N) THEN False WHEN (testing = Y) THEN True WHEN (testing = true) THEN true WHEN (testing = false) THEN false ELSE testing' due to data type mismatch: THEN and ELSE expressions should all be same type or coercible to a common type;"
  
  +---+----+-------+----------+-----+------+
  | id|name|testing|avg_result|score|active|
  +---+----+-------+----------+-----+------+
  |  1| sam|   null|      null| null|  true|
  |  2| Ram|      Y|      0.05|   10| false|
  |  3| Ian|      N|      0.01|    1| false|
  |  4| Jim|      N|       1.2|    3|  true|
  +---+----+-------+----------+-----+------+
  </code></pre>
  
  <p>When I do like below</p>
  
  <pre class="lang-python prettyprint-override"><code>for col in cols:
      df = df.withColumn(col, f.when(f.col(col) == 'N', 'False').when(f.col(col) == 'Y', 'True').otherwise(f.col(col)))
  </code></pre>
  
  <p>I get below error </p>
  
  <pre class="lang-python prettyprint-override"><code>pyspark.sql.utils.AnalysisException: u"cannot resolve 'CASE WHEN if ((isnull(active) || isnull(cast(N as double)))) null else CASE cast(cast(N as double) as double) WHEN cast(1 as double) THEN active WHEN cast(0 as double) THEN NOT active ELSE false THEN False WHEN if ((isnull(active) || isnull(cast(Y as double)))) null else CASE cast(cast(Y as double) as double) WHEN cast(1 as double) THEN active WHEN cast(0 as double) THEN NOT active ELSE false THEN True ELSE active' due to data type mismatch: THEN and ELSE expressions should all be same type or coercible to a common type;"
  </code></pre>
  
  <p>But the data frame changes to</p>
  
  <pre class="lang-python prettyprint-override"><code>+---+----+-------+----------+-----+------+
  | id|name|testing|avg_result|score|active|
  +---+----+-------+----------+-----+------+
  |  1| sam|   null|      null| null|  true|
  |  2| Ram|   True|      0.05|   10| false|
  |  3| Ian|  False|      0.01|    1| false|
  |  4| Jim|  False|       1.2|    3|  true|
  +---+----+-------+----------+-----+------+
  </code></pre>
  
  <p><code>New attempt</code> </p>
  
  <pre class="lang-python prettyprint-override"><code>for col in cols:
      df = df.withColumn(col, f.when(f.col(col) == 'N', 'False').when(f.col(col) == 'Y', 'True').
                         when(f.col(col) == 'true', 'True').when(f.col(col) == 'false', 'False').otherwise(f.col(col)))
  </code></pre>
  
  <p><code>Error received</code></p>
  
  <pre class="lang-python prettyprint-override"><code>pyspark.sql.utils.AnalysisException: u"cannot resolve 'CASE WHEN if ((isnull(active) || isnull(cast(N as double)))) null else CASE cast(cast(N as double) as double) WHEN cast(1 as double) THEN active WHEN cast(0 as double) THEN NOT active ELSE false THEN False WHEN if ((isnull(active) || isnull(cast(Y as double)))) null else CASE cast(cast(Y as double) as double) WHEN cast(1 as double) THEN active WHEN cast(0 as double) THEN NOT active ELSE false THEN True WHEN if ((isnull(active) || isnull(cast(true as double)))) null else CASE cast(cast(true as double) as double) WHEN cast(1 as double) THEN active WHEN cast(0 as double) THEN NOT active ELSE false THEN True WHEN if ((isnull(active) || isnull(cast(false as double)))) null else CASE cast(cast(false as double) as double) WHEN cast(1 as double) THEN active WHEN cast(0 as double) THEN NOT active ELSE false THEN False ELSE active' due to data type mismatch: THEN and ELSE expressions should all be same type or coercible to a common type;"
  </code></pre>
  
  <p>How can I get the data frame to be like</p>
  
  <pre class="lang-python prettyprint-override"><code>+---+----+-------+----------+-----+------+
  | id|name|testing|avg_result|score|active|
  +---+----+-------+----------+-----+------+
  |  1| sam|   null|      null| null|  True|
  |  2| Ram|   True|      0.05|   10| False|
  |  3| Ian|  False|      0.01|    1| False|
  |  4| Jim|  False|       1.2|    3|  True|
  +---+----+-------+----------+-----+------+
  </code></pre>