Skip to content
Tutorials Center
My learning

Topics

Lesson 23 of 37 ยท Joins, Groups and Windows

Pivot, Unpivot and Concatenation

Reshape tables in Polars: pivot long data wide, unpivot wide data long, stack and glue frames with concat, and turn list columns into rows with explode.

The same data can be shaped in different ways. A long table has one row per observation (region, month, revenue); a wide table has one column per category (region, Jan, Feb, Mar). Reports and charts often want wide; analysis and grouping often want long. Polars converts between the two with pivot and unpivot, and glues tables together with pl.concat.

pivot: long to wide

pivot needs three things: on (the column whose values become new column names), index (the column(s) that stay as rows), and values (the numbers to place in the cells). If several rows land in the same cell, you must say how to combine them with aggregate_function.

Python A revenue matrix

Press Run (or Ctrl + Enter) to see the result here.

Program input ยท what you'd type while the program runs

Type the answers before you press Run, one per line. Each time the program reads a line (with input()), it gets the next line from this box.

East had no February row, so that cell is null. Pivoting never invents a value.

When one cell has several rows, pass an aggregation ("sum", "mean", "first", "len", and so on):

Python Aggregating duplicates while pivoting

Press Run (or Ctrl + Enter) to see the result here.

Program input ยท what you'd type while the program runs

Type the answers before you press Run, one per line. Each time the program reads a line (with input()), it gets the next line from this box.

Polars needs to read the data to discover which columns the pivot produces. Because the output schema depends on the data, pivot exists only on DataFrame, not on LazyFrame. In a lazy pipeline, collect() first, pivot, and continue (or use group_by plus agg with conditional expressions to build the columns yourself).

Building wide columns yourself

When you know the categories ahead of time, a group_by with filtered aggregations does the same work, stays lazy, and gives you full control of names and order:

Python Manual pivot with conditional sums

Press Run (or Ctrl + Enter) to see the result here.

Program input ยท what you'd type while the program runs

Type the answers before you press Run, one per line. Each time the program reads a line (with input()), it gets the next line from this box.

Note that this version yields 0 for East's February (the sum of nothing), while pivot yields null. Decide which meaning you want.

unpivot: wide to long

unpivot is the reverse. index lists the columns to keep as identifiers; on lists the columns to melt (or leave it out to melt everything else). The names go into a variable column and the cell values into a value column; rename them with variable_name and value_name.

Python Wide to long

Press Run (or Ctrl + Enter) to see the result here.

Program input ยท what you'd type while the program runs

Type the answers before you press Run, one per line. Each time the program reads a line (with input()), it gets the next line from this box.

Long format is what group_by, join and most plotting libraries prefer: one column for the category, one for the measurement. A very common workflow is "unpivot, group, pivot back".

flowchart LR
    W["wide<br/>region, Jan, Feb, Mar"] -- "unpivot" --> L["long<br/>region, month, revenue"]
    L -- "pivot" --> W
    L --> G["group_by / join / plot"]

Long format is the hub; wide format is for reading.

concat: stacking and gluing frames

pl.concat takes a list of frames and combines them. The how argument decides the direction.

how Behaviour
"vertical" (default) Stack rows; schemas must match exactly
"vertical_relaxed" Stack rows, coerce compatible types (e.g. i32 and i64)
"diagonal" Stack rows; missing columns are filled with null
"diagonal_relaxed" Diagonal plus type coercion
"horizontal" Place frames side by side (same height; names must differ)
Python Vertical, diagonal and horizontal

Press Run (or Ctrl + Enter) to see the result here.

Program input ยท what you'd type while the program runs

Type the answers before you press Run, one per line. Each time the program reads a line (with input()), it gets the next line from this box.

Plain how="vertical" raises an error if a column is missing or has a different type, which is a useful safety net when you stack many monthly files. Add a column that tells you where each row came from with with_columns(pl.lit("jan").alias("source")) before concatenating.

pl.concat also works on lists of LazyFrames, giving one lazy plan, which is how you process many files without loading them first.

Adding columns, not frames

To add a single column or Series, use df.with_columns(...) or df.hstack([series]) rather than concat. To add a row, df.vstack(other) or pl.concat. Repeated one-row appends in a loop are slow: collect the rows in a Python list and build the DataFrame once.

explode: list column to rows

A list column holds several values per row. explode turns each element into its own row and repeats the other columns.

Python From lists to rows and back

Press Run (or Ctrl + Enter) to see the result here.

Program input ยท what you'd type while the program runs

Type the answers before you press Run, one per line. Each time the program reads a line (with input()), it gets the next line from this box.

An empty list explodes to a single null row, so order 3 keeps its row. If you would rather drop empty lists, filter them out first or use .drop_nulls() afterwards. Splitting strings is the other classic source of lists: pl.col("tags").str.split(",") followed by explode gives one row per tag, ready for counting tags with group_by.

Check your understanding

Quick check ยท 4 questions

of 4 answered You scored / 4 ยท perfect!

  1. 1. In df.pivot(on='month', index='region', values='revenue'), what becomes the new column names?

    The 'on' column supplies the new column names; 'index' stays as rows and 'values' fills the cells.
  2. 2. Why does pivot require aggregate_function in some cases?

    A cell can hold only one value. If the index/on combination is not unique, an aggregation such as sum or first resolves the duplicates.
  3. 3. Which concat mode fills columns that are missing in some frames with null?

    how='diagonal' stacks rows and fills absent columns with null; plain 'vertical' would raise an error.
  4. 4. What does explode do to a row whose list is empty?

    Empty lists become a single null row, so the row is preserved.

Summary

  • pivot(on, index, values, aggregate_function) makes wide tables; it is eager only.
  • unpivot(index, on, variable_name, value_name) makes long tables.
  • pl.concat(frames, how=...) stacks vertically, diagonally or sets frames side by side.
  • explode unfolds list columns into rows; combine with str.split for tag-like data.
  • Build row lists in Python and create the frame once; do not append rows in a loop.

Next: Reading and Writing CSV