Pivot Spark Dataframe
Question: I am starting to use Spark Dataframes and I need to be able to pivot the data
to create multiple columns out of 1 column with multiple rows. There is built
in functionality for that in Scalding and I believe in Pandas in python, but I
can't find anything for the new Spark Dataframe.

I assume I can write custom function of some sort that will do this but I'm
not even sure how to start, especially since I am a novice with Spark. I
anyone knows how to do this with built in functionality or suggestions for how
to write something in Scala, it is greatly appreciated.


Answer: [As mentioned](http://stackoverflow.com/a/33799008/1560062) by
[@user2000823](https://stackoverflow.com/users/2000823/user2000823) Spark
provides `pivot` function since version 1.6. General syntax looks as follows:

    
    
    df
      .groupBy(grouping_columns)
      .pivot(pivot_column, [values]) 
      .agg(aggregate_expressions)
    

Usage examples using [`nycflights13`](https://github.com/hadley/nycflights13)
and [`spark-csv`](https://github.com/databricks/spark-csv):

**Python** :

    
    
    from pyspark.sql.functions import avg
    
    flights = (sqlContext
        .read
        .format("com.databricks.spark.csv")
        .options(inferSchema="true", header="true")
        .load("flights.csv")
        .na.drop())
    
    flights.registerTempTable("flights")
    sqlContext.cacheTable("flights")
    
    gexprs = ("origin", "dest", "carrier")
    aggexpr = avg("arr_delay")
    
    flights.count()
    ## 336776
    
    %timeit -n10 flights.groupBy(*gexprs ).pivot("hour").agg(aggexpr).count()
    ## 10 loops, best of 3: 1.03 s per loop
    

**Scala** :

    
    
    val flights = sqlContext
      .read
      .format("com.databricks.spark.csv")
      .options(Map("inferSchema" -> "true", "header" -> "true"))
      .load("flights.csv")
    
    flights
      .groupBy($"origin", $"dest", $"carrier")
      .pivot("hour")
      .agg(avg($"arr_delay"))
    

**Performance considerations** :

Generally speaking pivoting is an expensive operation.

  * if you can try to provide `values` list:
    
        vs = list(range(25))
    %timeit -n10 flights.groupBy(*gexprs ).pivot("hour", vs).agg(aggexpr).count()
    ## 10 loops, best of 3: 392 ms per loop
    

  * [in some cases it proved to be beneficial](http://stackoverflow.com/q/35427812/1560062) to `repartition` and / or pre-aggregate the data

