title: Unable to write a csv file in spark
body: <p>I'm trying to write a csv file using spark 1.6.1.
  Say I have a csv file like this: </p>
  
  <pre><code>date,category
  19900108,apples
  19900108,apples
  19900308,peaches
  19900408,peaches
  19900508,pears
  19910108,pears
  19910108,peaches
  19910308,apples
  19910408,apples
  19910508,apples
  19920108,pears
  19920108,peaches
  19920308,apples
  19920408,peaches
  19920508,pears
  </code></pre>
  
  <p>I want to create an output csv file like this:</p>
  
  <pre><code>date,apples,peaches,pears
  1990,2,2,1
  1991,3,1,1
  1992,1,2,2
  </code></pre>
  
  <p>I am using this scala code to load the file:</p>
  
  <pre><code>spark-shell --packages com.databricks:spark-csv_2.11:1.2.0
  
  import org.apache.spark.SparkContext
  import org.apache.spark.SparkConf
  import org.apache.spark.sql.SQLContext
  import org.apache.spark.sql.functions._
  
  val sqlContext = new SQLContext(sc)
  
  var df = sqlContext.read.format("com.databricks.spark.csv").option("header","true").option("inferSchema", "true").load("data/sample.csv")
  
  df = df.withColumn("year", df.col("date").substr(0,4)) 
  df.groupBy("year").pivot("category").agg("category"-&gt;"count").show()
  </code></pre>
  
  <p>When I run this I get this dataframe which is exactly what I want</p>
  
  <pre><code>+----+------+-------+-----+
  |year|apples|peaches|pears|
  +----+------+-------+-----+
  |1990|     2|      2|    1|
  |1991|     3|      1|    1|
  |1992|     1|      2|    2|
  +----+------+-------+-----+
  </code></pre>
  
  <p>But when I try to write this to a CSV using this code:</p>
  
  <pre><code> df.coalesce(1).write.format("com.databricks.spark.csv").option("header", "true").save("mydata4.csv")
  </code></pre>
  
  <p>This is the csv file I am getting when I open it, which is not what I am looking for.</p>
  
  <pre><code>date,category,year
  19900108,apples,1990
  19900108,apples,1990
  19900308,peaches,1990
  19900408,peaches,1990
  19900508,pears,1990
  19910108,pears,1991
  19910108,peaches,1991
  19910308,apples,1991
  19910408,apples,1991
  19910508,apples,1991
  19920108,pears,1992
  19920108,peaches,1992
  19920308,apples,1992
  19920408,peaches,1992
  19920508,pears,1992
  </code></pre>
  
  <p>Am I missing something? Am I doing something wrong? </p>