---
title: "PIVOT vs UNPIVOT: Must-Know Concepts for PySpark Developers"
description: "Learn the difference between PIVOT and UNPIVOT in PySpark and Pandas with clear examples, use cases, and best practices for data transformation."
url: https://srinimf.com/2024/09/20/pivot-vs-unpivot-a-guide-for-pyspark-and-pandas-users/
date: 2024-09-20
modified: 2026-10-10
author: "Srini"
image: https://i0.wp.com/srinimf.com/wp-content/uploads/2024/09/image-19.png?fit=1024%2C768&ssl=1
categories: ["PySpark"]
tags: ["Pivot Vs Unpivot"]
type: post
lang: en
---

# PIVOT vs UNPIVOT: Must-Know Concepts for PySpark Developers

In both PySpark and Pandas, you can use `PIVOT` and `UNPIVOT` to reshape your data for analysis. Here’s how you can implement them.

## **PIVOT in PySpark**

In PySpark, `PIVOT` is done using the `pivot()` function, which is available on a `DataFrame`.

#### Example (PIVOT in PySpark):

Here we are Pivoting on the subject, dividing it into multiple columns.

```
from pyspark.sql import SparkSession
from pyspark.sql import functions as F

# Initialize Spark session
spark = SparkSession.builder.appName("example").getOrCreate()

# Sample DataFrame
data = [
    ("John", "Math", 90),
    ("John", "Science", 85),
    ("Doe", "Math", 70),
    ("Doe", "Science", 80)
]

columns = ["Name", "Subject", "Score"]
df = spark.createDataFrame(data, columns)

# Pivot the DataFrame
pivot_df = df.groupBy("Name").pivot("Subject").agg(F.first("Score")[1](#1d1247ed-b972-42c7-96e1-0616ae1157d6))

# Show the pivoted DataFrame
pivot_df.show()
```

#### Output:

```
+----+----+-------+
|Name|Math|Science|
+----+----+-------+
|Doe |  70|     80|
|John|  90|     85|
+----+----+-------+
```

## **[UNPIVOT in](https://srinimf.com/2023/05/13/how-to-convert-rows-to-columns-pivot-function/) PySpark**

In PySpark, there is no direct `UNPIVOT` function. However, you can achieve it by using **`selectExpr` **or manually transforming with **`stack()`.**

#### Example (UNPIVOT in PySpark using `selectExpr`):

Here we are Unpivoting the subject, which means merging all subject columns into one.

```
unpivot_df = pivot_df.selectExpr("Name", "stack(2, 'Math', Math, 'Science', Science) as (Subject, Score)")

unpivot_df.show()
```

#### Output:

```
+----+-------+-----+
|Name|Subject|Score|
+----+-------+-----+
|Doe |   Math|   70|
|Doe |Science|   80|
|John|   Math|   90|
|John|Science|   85|
+----+-------+-----+
```

---

## **PIVOT in Pandas**

In Pandas, `pivot()` and `pivot_table()` are used to reshape the DataFrame.

#### Example (PIVOT in Pandas):

```
import pandas as pd

# Sample DataFrame
data = {'Name': ['John', 'John', 'Doe', 'Doe'],
        'Subject': ['Math', 'Science', 'Math', 'Science'],
        'Score': [90, 85, 70, 80]}

df = pd.DataFrame(data)

# Pivot the DataFrame
pivot_df = df.pivot(index='Name', columns='Subject', values='Score')

print(pivot_df)
```

#### Output:

```
Subject  Math  Science
Name                   
Doe        70       80
John       90       85
```

#### **Example (Using `pivot_table()`)**:

```
# Sample DataFrame with duplicate combinations
data = {'Name': ['Alice', 'Alice', 'Bob', 'Bob', 'Alice'],
        'Subject': ['Math', 'Math', 'Math', 'Science', 'Science'],
        'Score': [85, 90, 95, 80, 100]}

df = pd.DataFrame(data)

# Pivot table with aggregation (mean as the default)
pivot_table_df = df.pivot_table(index='Name', columns='Subject', values='Score')

print(pivot_table_df)
```

#### Output:

```
Subject  Math  Science
Name                   
Alice     87.5    100.0
Bob       95.0     80.0
```

## **UNPIVOT in Pandas (Melt)**

In Pandas melt() is used to Unpivot the data.

#### Example (UNPIVOT in Pandas):

```
# Unpivot the DataFrame
unpivot_df = pivot_df.reset_index()[2](#77d53f93-dc11-467e-b144-ffab307b152f) reset_index() converts an index into a regular column and resets the index to default integers.melt(id_vars='Name', var_name='Subject', value_name='Score')

print(unpivot_df)
```

#### Output:

```
Name  Subject  Score
0   Doe     Math     70
1  John     Math     90
2   Doe  Science     80
3  John  Science     85
```

---

### Summary:

- **PySpark**:
  - Use `pivot()` to reshape your data for pivoting.
  - Use `selectExpr()` with `stack()` for unpivoting.
- **Pandas**:
  - Use `pivot()` or `pivot_table()` for pivoting.
  - Use `melt()` for unpivoting.

1. The first() function is used to get the first non-null value [↩︎](#1d1247ed-b972-42c7-96e1-0616ae1157d6-link)
2. The rest_index() function is used to reset the index column into a normal column [↩︎](#77d53f93-dc11-467e-b144-ffab307b152f-link)

### Share this:

-
- [Tweet](https://twitter.com/share)
- [![](https://i0.wp.com/assets.pinterest.com/images/pidgets/pinit_fg_en_rect_gray_20.png)](https://www.pinterest.com/pin/create/link/?url=https%3A%2F%2Fsrinimf.com%2F2024%2F09%2F20%2Fpivot-vs-unpivot-a-guide-for-pyspark-and-pandas-users%2F&media=https%3A%2F%2Fi0.wp.com%2Fsrinimf.com%2Fwp-content%2Fuploads%2F2024%2F09%2Fimage-19.png%3Ffit%3D1024%252C768%26ssl%3D1&description=PIVOT%20vs%20UNPIVOT%3A%20Must-Know%20Concepts%20for%20PySpark%20Developers)
-
- [Share on Tumblr](https://www.tumblr.com/share)
- [Share on WhatsApp (Opens in new window) WhatsApp](https://srinimf.com/2024/09/20/pivot-vs-unpivot-a-guide-for-pyspark-and-pandas-users/?share=jetpack-whatsapp)
- [More](#)
-

- [Print (Opens in new window) Print](https://srinimf.com/2024/09/20/pivot-vs-unpivot-a-guide-for-pyspark-and-pandas-users/?share=print)
- [Share on Reddit (Opens in new window) Reddit](https://srinimf.com/2024/09/20/pivot-vs-unpivot-a-guide-for-pyspark-and-pandas-users/?share=reddit)
- [Share on Telegram (Opens in new window) Telegram](https://srinimf.com/2024/09/20/pivot-vs-unpivot-a-guide-for-pyspark-and-pandas-users/?share=telegram)
- [Email a link to a friend (Opens in new window) Email](mailto:?subject=%5BShared%20Post%5D%20PIVOT%20vs%20UNPIVOT%3A%20Must-Know%20Concepts%20for%20PySpark%20Developers&body=https%3A%2F%2Fsrinimf.com%2F2024%2F09%2F20%2Fpivot-vs-unpivot-a-guide-for-pyspark-and-pandas-users%2F&share=email)
-

### Like this:

Like Loading…