How to melt Spark DataFrame?

by

There is no built-in function (if you work with SQL and Hive support enabled you can use stack function, but it is not exposed in Spark and has no native implementation) but it is trivial to roll your own. Required imports:

from pyspark.sql.functions import array, col, explode, lit, struct
from pyspark.sql import DataFrame
from typing import Iterable

Example implementation: 

def melt(
        df: DataFrame, 
        id_vars: Iterable[str], value_vars: Iterable[str], 
        var_name: str="variable", value_name: str="value") -> DataFrame:
    """Convert :class:`DataFrame` from wide to long format."""

    # Create array<struct<variable: str, value: ...>>
    _vars_and_vals = array(*(
        struct(lit(c).alias(var_name), col(c).alias(value_name)) 
        for c in value_vars))

    # Add to the DataFrame and explode
    _tmp = df.withColumn("_vars_and_vals", explode(_vars_and_vals))

    cols = id_vars + [
            col("_vars_and_vals")[x].alias(x) for x in [var_name, value_name]]
    return _tmp.select(*cols)

And some tests (based on Pandas doctests):

import pandas as pd

pdf = pd.DataFrame({'A': {0: 'a', 1: 'b', 2: 'c'},
                   'B': {0: 1, 1: 3, 2: 5},
                   'C': {0: 2, 1: 4, 2: 6}})

pd.melt(pdf, id_vars=['A'], value_vars=['B', 'C'])

   A variable  value
0  a        B      1
1  b        B      3
2  c        B      5
3  a        C      2
4  b        C      4
5  c        C      6

sdf = spark.createDataFrame(pdf)
melt(sdf, id_vars=['A'], value_vars=['B', 'C']).show()

+---+--------+-----+
|  A|variable|value|
+---+--------+-----+
|  a|       B|    1|
|  a|       C|    2|
|  b|       B|    3|
|  b|       C|    4|
|  c|       B|    5|
|  c|       C|    6|
+---+--------+-----+

Note: For use with legacy Python versions remove type annotations.

Related:

More Related Contents:

  1. Finding duplicates from large data set using Apache Spark
  2. Using a column value as a parameter to a spark DataFrame function
  3. Find maximum row per group in Spark DataFrame
  4. Unpivot in spark-sql/pyspark
  5. Split Spark Dataframe string column into multiple columns
  6. Avoid performance impact of a single partition mode in Spark window functions
  7. How to access element of a VectorUDT column in a Spark DataFrame?
  8. Spark Dataframe validating column names for parquet writes
  9. How to check if spark dataframe is empty?
  10. How to split a list to multiple columns in Pyspark?
  11. pyspark dataframe filter or include based on list
  12. How to save/insert each DStream into a permanent table
  13. Pyspark : forward fill with last observation for a DataFrame
  14. Spark load data and add filename as dataframe column
  15. PySpark: how to resample frequencies
  16. How to loop through each row of dataFrame in pyspark
  17. Fill in null with previously known good value with pyspark
  18. Why does Spark think this is a cross / Cartesian join
  19. PySpark – get row number for each row in a group
  20. How to find count of Null and Nan values for each column in a PySpark dataframe efficiently?
  21. Rename more than one column using withColumnRenamed
  22. Keep only duplicates from a DataFrame regarding some field
  23. pyspark: count distinct over a window
  24. How can I access python variable in Spark SQL?
  25. PySpark: How to fillna values in dataframe for specific columns?
  26. How to exclude multiple columns in Spark dataframe in Python
  27. Stratified sampling with pyspark
  28. How to turn off scientific notation in pyspark?
  29. spark.ml StringIndexer throws ‘Unseen label’ on fit()
  30. PySpark error: AttributeError: ‘NoneType’ object has no attribute ‘_jvm’

Leave a Comment