title: Spark - Transforming key-value pairs into columns
body: <p>I'm having 100,000 files, each contains data for M items, and each item has N attributes, with the format of each file is like this:</p>
  
  <pre><code>itemID,key,value    
  item1_1,key1,value1
  item1_1,key2,value2
  ...
  item1_1,keyN,valueN
  item1_2,key1,value1
  ...
  item1_2,keyN,valueN
  ...
  item1_M,keyN,valueN 
  </code></pre>
  
  <p>(1st file has data for items from item1_1 to item1_M. 2nd file has data for items from item2_1 to item2_M,...)</p>
  
  <p>For further processing, I want to transform the data into a DataFrame like:</p>
  
  <pre><code>item1_2,v1,v2,...,vN
  ...
  item1000000_M,v1,v2,...,vN
  </code></pre>
  
  <p>I tried to achieve this by using Spark's pivot:    </p>
  
  <pre><code>val df = spark.read.csv("/path/*")
  df.groupBy("itemID").pivot("key",Seq("key1","key2",...,"keyN")).agg(first("value"))
  </code></pre>
  
  <p>For 10GB of zipped data, it took hours to complete. I believe that using the pivot function is not a good idea here, but don't know of any alternative options yet.
  What can be a better approach?</p>
  
  <p>Thanks for your support.</p>