title: Spark Dataframe : Pivot with sorting
body: <p>I am reading the following <code>json</code> file into Dataframe in spark:</p>
  
  <pre><code>{"id" : "a", "country" : "uk", "date" : "2016-01-01"}
  {"id" : "b", "country" : "uk", "date" : "2016-01-02"}
  {"id" : "c", "country" : "fr", "date" : "2016-02-01"}
  {"id" : "d", "country" : "de", "date" : "2016-03-01"}
  {"id" : "e", "country" : "tk", "date" : "2016-04-01"}
  {"id" : "f", "country" : "be", "date" : "2016-05-01"}
  {"id" : "g", "country" : "nl", "date" : "2016-06-01"}
  {"id" : "h", "country" : "uk", "date" : "2016-06-01"}
  </code></pre>
  
  <p>I then apply <code>groupBy</code> on it and pivot it on date, here's the (pseudo) code:</p>
  
  <pre><code>val df = spark.read.json("file.json")
  val dfWithFormattedDate = df.withColumn("date", date_format(col("date"), "yyyy-MM"))
  dfWithFormattedDate.groupBy("country").pivot("date").agg(countDistinct("id").alias("count")).orderBy("country")
  </code></pre>
  
  <p>This gives me the <code>Dataframe</code> with country and pivoted dates (months) as columns. I would then like to <code>order</code> the results in descending order of total count. However, I don't have <code>count</code> as one of the columns and I can't apply <code>pivot</code> after applying <code>count()</code> on groupBy as it returns <code>Dataset</code> and not <code>RelationalGroupedDataset</code>. I have tried the following as well:</p>
  
  <p><code>dfWithFormattedDate.groupBy("country").pivot("date").count()</code></p>
  
  <p>This does not give me <code>count</code> column either. Is there any way I can gave both <code>count</code> and pivoted dates in resultant Dataset so that I can order by count desc?</p>
  
  <p><strong>Update</strong></p>
  
  <p>Here's the current output:</p>
  
  <pre><code>country|2016-01|2016-02|2016-03| ....
     fr  | null  |  1    | null  | 
     be  | null  |  null | null  |
     uk  | 2     |  null | null  |
  </code></pre>
  
  <p>Here's the expected output:</p>
  
  <pre><code>country|count|2016-01|2016-02|2016-03| ....
     uk  |  3  | 2     |  null | null  | 
     fr  |  1  | null  |  1    | null  | 
     be  |  1  | null  |  null | null  |
  </code></pre>
  
  <p>As you can see, I need the <code>count</code> column in the result and order the rows in descending order of <code>count</code>. Ordering without explicitly having <code>count</code> column is fine as well.</p>