title: Spark dataframe aggregation with reference to another array column
body: <p>I am having a performance related problem when aggregating a double array     by looking into index array.
  What I mean by that is. original Dataframe looks something like this:</p>
  <pre><code>original Dataframe 
  
  | id | prop1        | values                  |
  |----|--------------|-------------------------|
  |  1 | [2,5,1,3]    |   [ 0.1, 0.5, 0.7, 0.8] |
  |  2 | [2,1]        |   [ 0.2, 0.3 ]          |
  |  1 | [1,5]        |   [ 0.4, 0.3 ]          |
  |  2 | [3,2]        |   [ 0.0, 0.1 ]          |
  
  so in the column 2 which is prop1 is an int array having values within range of 1 to 5 but not in a order and there can be missing numbers within array.
  </code></pre>
  <p>Prop1 int array is like the index to the double array values
  what i mean by that is row 1 looks something like below when exploded</p>
  <pre><code>| id | prop1 | values |
  |----|-------|--------|
  |  1 | 2     |   0.1  |
  |  1 | 5     |   0.5  |
  |  1 | 1     |   0.7  |
  |  1 | 3     |   0.8  |
  </code></pre>
  <p>Final problem,</p>
  <p>so I need to aggregate the values of double array by looking into index array and by column id</p>
  <p>so outcome should be</p>
  <pre><code>| id | prop1          | values                   | 
  |----|----------------|--------------------------| 
  |  1 | [2,5,1,3]      |   [ 0.1, 0.8, 1.1, 0.8 ] | 
  |  2 | [2,1,3]        |   [ 0.3, 0.3, 0.0 ]      | 
  
  
  Below code I am using to extract the values by index and pivot right before merging them to array
  
  //dummy dataframe to get the sequence of 5 but the upper end is dynamic value and that can extend till 300k
  var df = (1 to 5).toDF(&quot;prop1&quot;)
  
  //joining original Df by prop1 column 
  var stgDf = originalDf.join(df,originalDf.col(&quot;prop1&quot;) ===  df.col(&quot;prop1&quot;),&quot;inner&quot;)
  
  // pivoting the values by index
  var pivotDf = stgDf.groupBy(&quot;id&quot;)
               .pivot(&quot;prop1&quot;).agg(first(&quot;values&quot;))
  
   // now aggregating the pivoted  values by id
   var expr = pivtoDf.columns.map(sum(_))
   var pivotDf.groupBy(&quot;id&quot;).agg(expr.head,expr.tail:_*)
  
   //then grouping back into array by id
  </code></pre>
  <p>This solution I did using exploding the prop1 and value, it did work with few rows  but in the actual problem the arrays of both the columns can exceed 500k values each  and no. of rows per id can exceed 30million</p>
  <p>If any one can look and help on this that would be great. application is build in scala using spark 2.4</p>
  <p>thanks in advance</p>