title: split column on the last occurrence of a delimiter into two columns based on column condition in spark dataframe
body: <p>I have the following spark dataframe</p>
  <pre><code>+------+--------------------+-----------------+--------------------+
  |entity|            instance|             name|               value|
  +------+--------------------+-----------------+--------------------+
  |Column|            ARM_PRED|  Histogram.abs. |            638065.0|
  |Column|            ARM_PRED|Histogram.ratio. |                 1.0|
  |Column|       ATTR044_Count|       Compliance|  0.9886814039322013|
  |Column|ATTR090_MissingCount|       Compliance|  0.9997570780406385|
  |Column|             ATTR045|             Mean|  17101.922291929834|
  |Column|             ATTR045|          Maximum|           4000000.0|
  |Column|             ATTR045|          Minimum|                 0.0|
  |Column|             ATTR045|StandardDeviation|   32981.67451994775|
  |Column|       ATTR020_Count|       Compliance|    0.99814909139351|
  |Column|ATTR036_MissingCount|       Compliance|0.011260608245241473|
  |Column|       ATTR011_Count|       Compliance|    0.99814909139351|
  |Column|             ATTR069|             Mean|  0.8678189434810735|
  |Column|             ATTR069|          Maximum|               103.0|
  
  </code></pre>
  <p>When <code>name</code> column has Compliance value. I have to split <code>instance</code> column based on last occurrence of delimiter &quot;_&quot; and place values in <code>instance</code> column and <code>name</code> column.</p>
  <p>Expected output:</p>
  <pre><code>+------+--------------------+-----------------+--------------------+
  |entity|            instance|             name|               value|
  +------+--------------------+-----------------+--------------------+
  |Column|            ARM_PRED|  Histogram.abs. |            638065.0|
  |Column|            ARM_PRED|Histogram.ratio. |                 1.0|
  |Column|             ATTR044|            Count|  0.9886814039322013|
  |Column|             ATTR090|     MissingCount|  0.9997570780406385|
  |Column|             ATTR045|             Mean|  17101.922291929834|
  |Column|             ATTR045|          Maximum|           4000000.0|
  |Column|             ATTR045|          Minimum|                 0.0|
  |Column|             ATTR045|StandardDeviation|   32981.67451994775|
  |Column|             ATTR020|            Count|    0.99814909139351|
  |Column|             ATTR036|     MissingCount|0.011260608245241473|
  |Column|             ATTR011|            Count|    0.99814909139351|
  |Column|             ATTR069|             Mean|  0.8678189434810735|
  |Column|             ATTR069|          Maximum|               103.0|
  
  </code></pre>
  <p>Below code performs operation on all records, but i need to perform only when <code>name</code> column has Compliance value</p>
  <pre><code>df.withColumn(&quot;instance&quot;, split($&quot;instance&quot;, &quot;_(?!.*_)&quot;))
     .withColumn(&quot;instance&quot;, $&quot;instance&quot;(0))
     .withColumn(&quot;name&quot;, $&quot;instance&quot;(1))).show
  </code></pre>
  <p>Need help!</p>