How to Convert a Wide DataFrame to Long Format in PySpark

You can use the melt function with the following basic syntax to convert a PySpark DataFrame from a wide format to a long format:

df_long = df.melt(ids=['team'], values=['Guard', 'Forward', 'Center'], 
                  variableColumnName='position', 
                  valueColumnName='points')

This particular example converts a wide DataFrame named df to a long DataFrame named df_long.

The following section demonstrates how to use this syntax in a complete, practical coding example.

Detailed Syntax and Parameters of `df.melt()`

The df.melt() method accepts several key parameters that control how the DataFrame is reshaped. Proper configuration of these parameters is vital for ensuring the output structure aligns with analytic requirements. These parameters allow the user precise control over which columns are maintained as identifiers and how the new variable and value columns are named.

The four primary parameters of the melt function are:

• ids: This parameter takes a list of column names that should remain as identifier variables. These columns are duplicated across the new, expanded rows and form the key structure of the long format output. If omitted, all non-value columns are treated as ID columns.
• values: This parameter takes a list of column names containing the data values that need to be unpivoted. These are the columns that will be collapsed into a single ‘value’ column.
• variableColumnName: A string specifying the name of the new column that will store the names of the original columns specified in the values list (e.g., ‘position’ in the example).
• valueColumnName: A string specifying the name of the new column that will store the actual data content from the original columns specified in the values list (e.g., ‘points’ in the example).

The flexibility offered by naming the output columns using variableColumnName and valueColumnName is particularly useful for improving code readability and ensuring that the final long format DataFrame is immediately meaningful within the context of the business problem. Using descriptive names like position and points instead of generic defaults (like variable and value) enhances data governance and interpretability downstream.

Setting Up the PySpark Environment

Before executing the reshape operation, it is necessary to initialize a Spark session and create the initial wide format DataFrame. This setup ensures that the environment is configured correctly and provides a concrete dataset to work with. Suppose we are working with basketball statistics, where the performance data for two teams (A and B) is organized by player position across columns. This is the definition of a wide data format.

The following code snippet demonstrates the necessary steps to import the required classes, instantiate the SparkSession, define the data, and create the initial DataFrame named df. Notice how the initial output shows the data spread across four columns: team (the ID variable), and Guard, Forward, and Center (the value variables).

from pyspark.sql import SparkSession
spark = SparkSession.builder.getOrCreate()

#define data
data = [['A', 22, 34, 17 ], 
        ['B', 25, 10, 12]]

#define column names
columns = ['team', 'Guard', 'Forward', 'Center']

#create dataframe using data and column names
df = spark.createDataFrame(data, columns) 
  
#view dataframe
df.show()

+----+-----+-------+------+
|team|Guard|Forward|Center|
+----+-----+-------+------+
|   A|   22|     34|    17|
|   B|   25|     10|    12|
+----+-----+-------+------+

The resulting DataFrame df is clearly in the wide format. Each team (A and B) occupies a single row, and the point totals for each position are spread horizontally. Our goal is to consolidate the position data (Guard, Forward, Center) into rows, making ‘position’ a new grouping variable and ‘points’ the corresponding value.

Practical Example: Reshaping a Wide DataFrame

Now that the environment is set up and the initial wide DataFrame is prepared, we apply the melt function to perform the required data reshaping. We must carefully define the ids (identifier columns) and values (columns to unpivot).

In this scenario, ‘team’ is the unique identifier we want to retain across rows, so it is passed to ids. The columns ‘Guard’, ‘Forward’, and ‘Center’ contain the values we wish to stack, so they are passed to values. Finally, we assign explicit names to the new columns: position for the variable name and points for the data value. This precise control over column mapping results in the desired long format structure, as shown below:

#create long DataFrame
df_long = df.melt(ids=['team'], values=['Guard', 'Forward', 'Center'], 
                  variableColumnName='position', 
                  valueColumnName='points')

#view long DataFrame
df_long.show()

+----+--------+------+
|team|position|points|
+----+--------+------+
|   A|   Guard|    22|
|   A| Forward|    34|
|   A|  Center|    17|
|   B|   Guard|    25|
|   B| Forward|    10|
|   B|  Center|    12|
+----+--------+------+

Analyzing the Reshaped (Long) Output

The resulting DataFrame, df_long, clearly demonstrates the successful transformation into the long format. Where the original data contained two rows, the reshaped data now contains six rows (two original rows multiplied by three value columns). This increase in row count is characteristic of unpivoting operations, as each individual measurement becomes its own record.

Specifically, the team identifier is now repeated along the rows, ensuring that each data point remains correctly associated with its source team. The original column headers (‘Guard’, ‘Forward’, ‘Center’) have been collapsed and their names are now used as values in the second column, which we explicitly named position using the variableColumnName argument. Finally, the actual point totals (22, 34, 17, etc.) are consolidated into the third column, which we named points using the valueColumnName argument.

This structure is highly optimized for analytical aggregation. For example, calculating the total points scored by all Guards across all teams is now a simple groupBy('position').agg(sum('points')) operation. In the original wide format, this operation would have been trivial for this small dataset but far more complex and prone to errors if dozens of position columns existed. The long format is thus superior for scalability and statistical processing.

Legacy Methods: Using `stack` or `pivot`

While the melt function is the recommended modern approach for wide-to-long format transformations, earlier versions of PySpark or complex scenarios sometimes required the manual construction of the unpivot using SQL expressions or the stack function. The initial paragraph of this article referenced a combination of selectExpr, groupBy, pivot, and agg, which is typically used for the opposite transformation (long-to-wide), but variations involving stack within selectExpr were used for unpivoting.

The stack function, used within a selectExpr clause, manually defines the pairs of variable names and corresponding values to be unpivoted. This method is highly verbose and demands meticulous definition of column indices and names, making it brittle to changes in the source schema. For instance, unpivoting three columns requires a stack(3, 'Guard', Guard, 'Forward', Forward, 'Center', Center) statement. This approach lacks the clarity and ease of maintenance provided by the parameter-driven melt function, reinforcing why melt should be prioritized for standard wide-to-long format data reshaping.

In summary, while understanding the underlying mechanisms that necessitate data reshaping remains crucial, the df.melt() function provides an elegant, robust, and industry-standard solution for handling wide-to-long transformations in contemporary PySpark workflows.

The following tutorials explain how to perform other common tasks in PySpark: