Long or Wide

The Daily Grind's monthly sales arrive one row per store per month. The owner wants to read them as a grid, stores down the side and months across the top. Same numbers, different shape. Data is long when each fact is its own row and wide when facts spread across columns, and most reshaping is moving between the two.

Watch it happen

Three rows, long:

result = sales.groupBy("store").pivot("month").agg(F.sum("amount"))

groupBy chooses the rows of the grid, pivot chooses its columns from the values of one field, and the aggregate fills the cells. S2 had no April row, so its April cell is null, not zero; add .fillna(0) if the report should read 0.

Pivot on a few values, not many

Every distinct value of the pivoted field becomes a column. Months, categories and statuses make a readable grid; customer ids make ten thousand columns. If the field has more than a handful of values, you probably wanted a groupBy.

You can name the columns you want, pivot("month", [3, 4]), which fixes their order, keeps a stray value from adding a column you didn't expect, and lets the engine skip a pass over the data to find the values. With .fillna(0) on the end, S2's April reads 0:

Wide back to long

The reverse move takes columns and turns them into rows:

wide.unpivot("store", ["3", "4"], "month", "amount")

Note the column names: a pivot names its columns after the values it found, so they are the strings "3" and "4", not the numbers, and unpivot is given them as strings. The month column that comes back is text for the same reason; parse it if you need a number.

A spreadsheet someone hands you is usually wide. Make it long first; every tool in this track expects one fact per row.

One row per element

A column that holds a list, the toppings on an order, the tags on a post, is a different kind of wide: several facts in one cell. explode gives each element its own row:

result = orders.withColumn("extra", F.explode(F.split("extras", ",")))

The row for order 1 became two rows. Count rows after an explode and you are counting elements, not orders.

And an order whose extras is null disappears: explode on a null or an empty list produces no rows, so the order is gone from the result. When every order must survive, explode_outer keeps it with a null in the new column. One more thing to know: an empty string is not an empty list. split("", ",") gives one element, the empty string, so an order whose extras is "" keeps one row with an empty extra.

Three problems, in the order they build:

1. The Store-Month Grid — months across the top.
2. Status Breakdown Grid — pivot, then fill the gaps.
3. Tag Explosion — split a string, explode it, count the pieces.