title: How to unstack dataset (using pivot)?
body: <p>I tried the new "pivot" function of 1.6 on a <a href="https://www.kaggle.com/worldbank/world-development-indicators/downloads/world-development-indicators-release-2016-01-28-06-31-53.zip" rel="nofollow noreferrer">larger stacked dataset</a>. It has 5,656,458 rows and the <code>IndicatorCode</code> column has 1344 different codes.</p>
  
  <p>The idea was to use pivot to "unstack" (in pandas terms) this data set and have a column for each IndicatorCode.</p>
  
  <pre><code>schema = StructType([ \
     StructField("CountryName", StringType(), True), \
     StructField("CountryCode", StringType(), True), \
     StructField("IndicatorName", StringType(), True), \
     StructField("IndicatorCode", StringType(), True), \
     StructField("Year", IntegerType(), True), \
     StructField("Value", DoubleType(), True)  \
  ])
  
  data = sqlContext.read.load('hdfs://localhost:9000/tmp/world-development-indicators/Indicators.csv', 
                              format='com.databricks.spark.csv', 
                              header='true', 
                              schema=schema)
  
  data2 = indicators_csv.withColumn("IndicatorCode2", regexp_replace("indicatorCode", "\.", "_"))\
                        .select(["CountryCode", "IndicatorCode2", "Year", "Value"])
  
  columns = [row.IndicatorCode2 for row in data2.select("IndicatorCode2").distinct().collect()]
  
  data3 = data2.groupBy(["Year", "CountryCode"])\
               .pivot("IndicatorCode2", columns)\
               .max("Value")
  </code></pre>
  
  <p>While this returned successfully, <code>data3.first()</code> never returned a result (I interrupted on my standalone using 3 cores after 10 min).</p>
  
  <p>My approach using <code>RDD</code> and <code>aggregateByKey</code> worked well, so I'm not searching for a solution about how to do it, but whether pivot with DataFrames can also do the trick.</p>