This is a common PySpark (Spark SQL) interview and practice question. Below is the full answer with a clear, step-by-step explanation.

Question

Problem A retail company stores monthly sales data in a PySpark DataFrame. The business wants to generate a report where each month becomes a separate column, showing the total sales for every region. Sample Data +------+-----+------+ |region|month|sales | +------+-----+------+ |East |Jan |1000 | |East |Feb |1200 | |East |Mar |1500 | |West |Jan |900 | |West |Feb |1100 | |West |Mar |1400 | +------+-----+------+ Task Transform the data so that each month becomes a separate column containing the total sales for each region. Expected Output +------+----+----+----+ |region|Jan |Feb |Mar | +------+----+----+----+ |East |1000|1200|1500| |West |900 |1100|1400| +------+----+----+----+

Answer & Explanation

**What you need to produce**

You must turn a three‑column table (region, month, sales) into a wide table where each month is a separate column and the cell value is the total sales for that region in that month. The result should look like:

```
region Jan Feb Mar
East 1000 1200 1500
West 900 1100 1400
```

**Why the “pivot” approach is the right one**

In Spark the operation you are after is a classic *pivot*: you have a categorical column (`month`) whose distinct values should become new columns, and you need an aggregation (`sum`) to fill those cells. Spark’s `pivot()` method does exactly this in a single, efficient step. Trying to do it with manual joins or with `groupBy` + `collect_list` would be far more code, slower, and error‑prone.

**Step‑by‑step solution**

1. **Start a Spark session** – you need a `SparkSession` to work with DataFrames.

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

2. **Create the input DataFrame** – the sample data can be built from a Python list of tuples.

```python
data = [
("East", "Jan", 1000),
("East", "Feb", 1200),
("East", "Mar", 1500),
("West", "Jan", 900),
("West", "Feb", 1100),
("West", "Mar", 1400)
]

df = spark.createDataFrame(data, ["region", "month", "sales"])
```

3. **Pivot the data** – group by the row identifier (`region`), pivot on the column that should become headers (`month`), and aggregate the values with `sum`.

```python
from pyspark.sql.functions import sum # import the aggregation function

result = (
df
.groupBy("region") # one output row per region
.pivot("month") # turn each distinct month into a column
.agg(sum("sales")) # fill the cells with the total sales
.orderBy("region") # optional: keep rows in a predictable order
)
```

*What happens under the hood?*
- `groupBy("region")` creates a logical grouping key.
- `pivot("month")` tells Spark to collect all distinct values of `month` (Jan, Feb, Mar) and treat each as a separate column.
- `agg(sum("sales"))` computes the sum of `sales` for each (region, month) pair. Because there is only one row per pair in the sample, the sum is just the original value, but the same code works when you have multiple rows for the same region‑month.

4. **Show the result** – `result.show()` prints a nicely formatted table.

```python
result.show()
```

Expected console output:

```
+------+----+----+----+
|region| Jan| Feb| Mar|
+------+----+----+----+
| East|1000|1200|1500|
| West| 900|1100|1400|
+------+----+----+----+
```

**Things to keep in mind**

- **Missing months** – If a region does not have sales for a particular month, the pivot will produce a `null` in that cell. If you prefer zeros, add a `fillna(0)` step:

```python
result = result.fillna(0)
```

- **Controlling the column order** – By default Spark orders the pivot columns alphabetically. If you need a custom order (e.g., Jan, Feb, Mar, Apr …), pass the list of desired values to `pivot`:

```python
months = ["Jan", "Feb", "Mar"]
result = df.groupBy("region").pivot("month", months).agg(sum("sales"))
```

- **Performance tip** – When the number of distinct pivot values is large, Spark has to allocate a column for each value, which can be memory‑intensive. In this problem the set of months is tiny, so the simple pivot is perfectly fine.

- **Alternative approaches** – Spark also offers `crosstab("region","month")`, which creates a contingency table, but it always uses `count` as the aggregation and returns string values, so you would still need extra steps to sum sales and cast types. The `pivot` + `agg(sum)` pattern is more flexible and is the idiomatic way to solve this type of reporting problem.

**Putting it all together – final script**

```python
from pyspark.sql import SparkSession
from pyspark.sql.functions import sum

spark = SparkSession.builder.getOrCreate()

data = [
("East", "Jan", 1000),
("East", "Feb", 1200),
("East", "Mar", 1500),
("West", "Jan", 900),
("West", "Feb", 1100),
("West", "Mar", 1400)
]

df = spark.createDataFrame(data, ["region", "month", "sales"])

result = (
df
.groupBy("region")
.pivot("month") # you could also pass a list of months here
.agg(sum("sales"))
.orderBy("region")
)

result.show()
```

Running this script yields exactly the table the business expects: one row per region and a column for each month containing the total sales. Mastering the `groupBy → pivot → agg` pattern will let you reshape many similar “long‑to‑wide” reporting tasks in PySpark quickly and efficiently.