title: spark pivot without aggregation
body: <p><a href="https://databricks.com/blog/2016/02/09/reshaping-data-with-pivot-in-apache-spark.html" rel="noreferrer">https://databricks.com/blog/2016/02/09/reshaping-data-with-pivot-in-apache-spark.html</a> explain nicely how a pivot works for spark.</p>
  
  <p>In my python code, I use pandas without an aggregation but reset the index and join:</p>
  
  <pre><code>pd.pivot_table(data=dfCountries, index=['A'], columns=['B'])
  countryToMerge.index.name = 'ISO'
  df.merge(countryToMerge['value'].reset_index(), on='ISO', how='inner')
  </code></pre>
  
  <p>How does this work in spark?</p>
  
  <p>I tried to group and join manually like:</p>
  
  <pre><code>val grouped = countryKPI.groupBy("A").pivot("B")
  df.join(grouped, df.col("ISO") === grouped.col("ISO")).show
  </code></pre>
  
  <p>but that does not work. How would the reset_index fit into spark / How would it be implemented in a spark native way?</p>
  
  <h1>edit</h1>
  
  <p>a minimal example of the python code:</p>
  
  <pre><code>import pandas as pd
  from datetime import datetime, timedelta
  import numpy as np
  dates = pd.DataFrame([(datetime(2016, 1, 1) + timedelta(i)).strftime('%Y-%m-%d') for i in range(10)], columns=["dates"])
  isos = pd.DataFrame(["ABC", "POL", "ABC", "POL","ABC", "POL","ABC", "POL","ABC", "POL"], columns=['ISO'])
  dates['ISO'] = isos.ISO
  dates['ISO'] = dates['ISO'].astype("category")
  countryKPI = pd.DataFrame({'country_id3':['ABC','POL','ABC','POL'],
                         'indicator_id':['a','a','b','b'],
                         'value':[7,8,9,7]})
  countryToMerge = pd.pivot_table(data=countryKPI, index=['country_id3'], columns=['indicator_id'])
  countryToMerge.index.name = 'ISO'
  print(dates.merge(countryToMerge['value'].reset_index(), on='ISO', how='inner'))
  
    dates  ISO  a  b
  0  2016-01-01  ABC  7  9
  1  2016-01-03  ABC  7  9
  2  2016-01-05  ABC  7  9
  3  2016-01-07  ABC  7  9
  4  2016-01-09  ABC  7  9
  5  2016-01-02  POL  8  7
  6  2016-01-04  POL  8  7
  7  2016-01-06  POL  8  7
  8  2016-01-08  POL  8  7
  9  2016-01-10  POL  8  7
  </code></pre>
  
  <p>to follow along in scala / spark</p>
  
  <pre><code>val dates = Seq(("2016-01-01", "ABC"),
      ("2016-01-02", "ABC"),
      ("2016-01-03", "POL"),
      ("2016-01-04", "ABC"),
      ("2016-01-05", "POL"),
      ("2016-01-06", "ABC"),
      ("2016-01-07", "POL"),
      ("2016-01-08", "ABC"),
      ("2016-01-09", "POL"),
      ("2016-01-10", "ABC")
    ).toDF("dates", "ISO")
      .withColumn("dates", 'dates.cast("Date"))
  
    dates.show
    dates.printSchema
  
    val countryKPI = Seq(("ABC", "a", 7),
      ("ABC", "b", 8),
      ("POL", "a", 9),
      ("POL", "b", 7)
    ).toDF("country_id3", "indicator_id", "value")
  
    countryKPI.show
    countryKPI.printSchema
  
  val grouped = countryKPI.groupBy("country_id3").pivot("indicator_id")
  </code></pre>