How to pivot on multiple columns in Spark SQL?

by

Here’s a non-UDF way involving a single pivot (hence, just a single column scan to identify all the unique dates).

dff = mydf.groupBy('id').pivot('day').agg(F.first('price').alias('price'),F.first('units').alias('unit'))

Here’s the result (apologies for the non-matching ordering and naming):

+---+-------+------+-------+------+-------+------+-------+------+               
| id|1_price|1_unit|2_price|2_unit|3_price|3_unit|4_price|4_unit|
+---+-------+------+-------+------+-------+------+-------+------+
|100|     23|    10|     45|    11|     67|    12|     78|    13|
|101|     23|    10|     45|    13|     67|    14|     78|    15|
|102|     23|    10|     45|    11|     67|    16|     78|    18|
+---+-------+------+-------+------+-------+------+-------+------+

We just aggregate both on the price and the unit column after pivoting on the day.

If naming required as in question,

dff.select([F.col(c).name('_'.join(x for x in c.split('_')[::-1])) for c in dff.columns]).show()

+---+-------+------+-------+------+-------+------+-------+------+
| id|price_1|unit_1|price_2|unit_2|price_3|unit_3|price_4|unit_4|
+---+-------+------+-------+------+-------+------+-------+------+
|100|     23|    10|     45|    11|     67|    12|     78|    13|
|101|     23|    10|     45|    13|     67|    14|     78|    15|
|102|     23|    10|     45|    11|     67|    16|     78|    18|
+---+-------+------+-------+------+-------+------+-------+------+

More Related Contents:

  1. Reshaping/Pivoting data in Spark RDD and/or Spark DataFrames
  2. Convert pyspark string to date format
  3. How to split Vector into columns – using PySpark
  4. How to use JDBC source to write and read data in (Py)Spark?
  5. Applying UDFs on GroupedData in PySpark (with functioning python example)
  6. Pyspark: Split multiple array columns into rows
  7. How to change a dataframe column from String type to Double type in PySpark?
  8. Count number of non-NaN entries in each column of Spark dataframe with Pyspark
  9. Retrieve top n in each group of a DataFrame in pyspark
  10. Pyspark: explode json in column to multiple columns
  11. Spark DataFrame: Computing row-wise mean (or any aggregate operation)
  12. How to use a Scala class inside Pyspark
  13. Filter Pyspark dataframe column with None value
  14. PySpark converting a column of type ‘map’ to multiple columns in a dataframe
  15. Cast column containing multiple string date formats to DateTime in Spark
  16. How to explode multiple columns of a dataframe in pyspark
  17. Best way to get the max value in a Spark dataframe column
  18. PySpark: multiple conditions in when clause
  19. Count number of non-NaN entries in each column of Spark dataframe in PySpark
  20. Load CSV file with PySpark
  21. Cannot find col function in pyspark
  22. What is the best way to remove accents with Apache Spark dataframes in PySpark?
  23. How to pass a constant value to Python UDF?
  24. How to zip two array columns in Spark SQL
  25. When to cache a DataFrame?
  26. How to return a “Tuple type” in a UDF in PySpark?
  27. How to add suffix and prefix to all columns in python/pyspark dataframe
  28. Pyspark changing type of column from date to string
  29. PySpark: compute row maximum of the subset of columns and add to an exisiting dataframe
  30. Apache Spark — Assign the result of UDF to multiple dataframe columns

Leave a Comment