Lists, dummy columns and bins

Three kinds of column hold several facts in one place, and pandas has a tool for each. Cells that hold lists, such as python;sql;excel, become one row per item: the pandas explodeexplode: A reshape that gives each item of a list-like cell its own row and repeats the other fields. The declared input policy determines how empty and missing lists contribute rows. method repeats the rest of the row for every item. A column of labels becomes dummy columns, one true-or-false indicator columnindicator column: A column that marks whether each row has a specified label, often with Boolean values or zeros and ones. Several labels per observation can produce several marked columns. per label, with get_dummies, and from_dummies turns them back into one column. A column of numbers becomes bins, labelled ranges such as 18–25: cut uses edges you choose, while qcut puts the edges at quantilesquantile: A value selected or interpolated from ordered usable observations at a stated probability. Different conventions can return different values. More in “Descriptive statistics that do not mislead”, so that every bin holds about the same number of rows. These three reshapes come up in nearly every survey, job-board and customer analysis, for counting skills, preparing model inputs, or grouping ages and spending into bands.

This lesson works through twelve job applications, invented for it, with every run recorded: it explodes their skill lists and counts them per city, turns a label column into dummy columns and back, and bins the applicants’ ages both ways.

Explode preserves each item and repeats its row

Read A01’s three skills, then compare the twelve application rows with the number of listed items.

id,city,age,source,skills
A01,Pune,22,portal,python;sql;excel
A02,Pune,29,referral,sql;excel
A03,Indore,18,portal,excel
A04,Indore,35,walk-in,python;sql
A05,Pune,41,portal,python;power bi
A06,Nagpur,26,referral,sql
A07,Nagpur,31,portal,python;sql;tableau
A08,Pune,24,walk-in,excel;power bi
A09,Indore,47,referral,sql;excel
A10,Nagpur,19,portal,python
A11,Pune,58,referral,excel;sql
A12,Indore,25,portal,python;excel

str.split(";") turns each skills cell into a Python list, and the pandas explode method then gives every list item its own row, copying the other columns:

Follow A01’s three skill items into three rows, then check the same expansion over all twelve applications.

The 23 skill rows remain twelve applications. Compare the repeated row labels with the fresh-index crosstab.

import pandas as pd

# Twelve invented job applications; each lists its skills in one cell, separated by semicolons.
apps = pd.read_csv("applications.csv")

skills = apps.assign(skill=apps["skills"].str.split(";")).explode("skill")
print(skills[["id", "city", "skill"]].head(6))  # the index labels repeat: one row per list item
print(f"{len(apps)} applications -> {len(skills)} rows, one per skill")
print()

print(skills["skill"].value_counts())
print()

# Repeated index labels trip up anything that aligns on the index.
try:
    pd.crosstab(skills["city"], skills["skill"])
except ValueError as err:
    print(f"crosstab failed: {err}")

skills = apps.assign(skill=apps["skills"].str.split(";")).explode("skill", ignore_index=True)
print(pd.crosstab(skills["city"], skills["skill"]))  # applicants per city and skill

    id    city   skill
0  A01    Pune  python
0  A01    Pune     sql
0  A01    Pune   excel
1  A02    Pune     sql
1  A02    Pune   excel
2  A03  Indore   excel
12 applications -> 23 rows, one per skill

skill
sql         7
excel       7
python      6
power bi    2
tableau     1
Name: count, dtype: int64

crosstab failed: cannot reindex on an axis with duplicate labels
skill   excel  power bi  python  sql  tableau
city                                         
Indore      3         0       2    2        0
Nagpur      0         0       2    2        1
Pune        4         2       2    3        0

Lists of 3, 2, 1 … items gave 23 rows for 12 applications, and after the explode, counting skills is an ordinary value_counts. Look at the index of the first rows, though: 0, 0, 0, 1, 1. Explode keeps each row’s original label on all of its copies. Repeated labels can break an operation that requires unambiguous index alignment; here pd.crosstab refused with “cannot reindex on an axis with duplicate labels”. Passing ignore_index=True, or calling reset_index(drop=True) afterwards, gives the rows fresh labels, and the crosstab then shows the applicants per city and skill.

Two more behaviours matter once real data arrives. In pandas, an empty list explodes into one row holding NaN, so it is not lost silently. Several list columns explode together, item by item, when their lists have equal lengths in every row. Lists typed by people bring spaces, capitals and repeats as well, and each of those changes the counts.

Indicator columns mark labels without adding observations

A model or a statistics routine usually needs numbers, not labels. get_dummies turns one label column into dummy columns, one per distinct label, each true where the row has that label:

Follow A01’s portal label into its marked source column, then recover the label from that row.

The source indicators still have one row per application. The skill indicators can mark several labels in that same row.

import pandas as pd

apps = pd.read_csv("applications.csv")

# One label per row: one indicator column per label.
flags = pd.get_dummies(apps["source"], prefix="source")
print(apps[["id", "source"]].join(flags).head(4))
print(f"dtype of the indicators: {flags.dtypes.unique()[0]}")
print()

# And back: from_dummies finds the column that is True in each row.
print(pd.from_dummies(flags, sep="_").head(4))
print()

# Several labels per cell: str.get_dummies splits on the separator and marks every label it finds.
skill_flags = apps["skills"].str.get_dummies(sep=";")
print(skill_flags.head(4))
print()
print(skill_flags.sum().sort_values(ascending=False))  # the same counts as explode and value_counts

    id    source  source_portal  source_referral  source_walk-in
0  A01    portal           True            False           False
1  A02  referral          False             True           False
2  A03    portal           True            False           False
3  A04   walk-in          False            False            True
dtype of the indicators: bool

     source
0    portal
1  referral
2    portal
3   walk-in

   excel  power bi  python  sql  tableau
0      1         0       1    1        0
1      1         0       0    1        0
2      1         0       0    0        0
3      0         0       1    1        0

excel       7
sql         7
python      6
power bi    2
tableau     1
dtype: int64

• pd.get_dummies(apps["source"], prefix="source") made three dummy columns, source_portal, source_referral and source_walk-in, all of them bool.
• pd.from_dummies(flags, sep="_") reversed them: in each row it found the one column that is true and wrote its label back into a source column.
• For cells holding several labels, str.get_dummies(sep=";") splits on the separator and marks every label found, so one row can have several ones. Its column sums are the skill counts that explode and value_counts gave.

Two options are worth knowing by name. drop_first=True leaves out the first label’s column, because in a linear model the dropped label becomes the baseline that the others are compared with. dummy_na=True adds a column that marks missing values, which are otherwise all false.

Choose fixed ranges or ranks for numeric values

With specified edges, cut gives fixed ranges; qcut derives them from ranks in the input. Both turn numbers into categories:

Compare the same twelve sorted ages in the overview: the fixed ranges have 5, 4 and 3 rows, while these distinct-valued quartiles have four groups of 3.

Follow age 25 into the right-closedright-closed interval: An interval that includes its upper endpoint. In (a, b], values equal to b are included and values equal to a are excluded. More in “Nearest-key and range joins: merge_asof” first fixed band. The quartile boundary of 23.5 puts it in the second rank group instead.

import pandas as pd

ages = pd.read_csv("applications.csv")["age"]
print(sorted(ages.tolist()))
print()

# cut: you choose the edges; each band includes its right edge, so (17, 25] holds the ages 18 to 25.
bands = pd.cut(ages, bins=[17, 25, 35, 60], labels=["18-25", "26-35", "36-60"])
print(bands.value_counts(sort=False))
print()

# qcut: four groups of (nearly) equal size; the edges come from the data's quartiles.
quartiles, edges = pd.qcut(ages, q=4, retbins=True)
print(quartiles.value_counts(sort=False))
print(f"quartile edges: {edges.tolist()}")

[18, 19, 22, 24, 25, 26, 29, 31, 35, 41, 47, 58]

age
18-25    5
26-35    4
36-60    3
Name: count, dtype: int64

age
(17.999, 23.5]    3
(23.5, 27.5]      3
(27.5, 36.5]      3
(36.5, 58.0]      3
Name: count, dtype: int64
quartile edges: [18.0, 23.5, 27.5, 36.5, 58.0]

pd.cut(ages, bins=[17, 25, 35, 60], labels=[…]) used the edgesbin edge: A boundary used to place a numeric value in a range. The interval convention states which endpoint belongs to each adjacent bin and how out-of-range or missing values are handled. as given. Each band includes its right edge, so (17, 25] holds the ages 18 to 25, and the bands hold 5, 4 and 3 applicants. pd.qcut(ages, q=4) computed the quartiles of these ages, 23.5, 27.5 and 36.5, and used them as edges, so each of the four groups holds exactly 3 applicants. retbins=True returned those edges, which you need if the same groups must be applied to other data later.

The details that decide which band a value lands in also have names. include_lowest=True makes the first band include its left edge. right=False builds bands such as [18, 26), closed on the left instead of the right. duplicates="drop" lets qcut merge edges that coincide when many values are tied. Ranking orders values before forming groups. Polars offers these reshapes too; Polars 2 changes the empty-list default. bin_intervals uses specified breaks. bin_quantiles uses value quantiles. bin_ranks uses ranks.

Messy list cells: counts that lie, and how to fix them Pro: opens with a Pro, Premium or Ultimate pass

Binning without losing rows: edges, ties and moving quantiles Pro: opens with a Pro, Premium or Ultimate pass

Explode and bins in polars, stable across versions Pro: opens with a Pro, Premium or Ultimate pass

Practise: identify the reshape and its row unit

Use the list, label and numeric examples to distinguish observation rows from derived columns.

Interview questions

Key takeaways

• str.split and explode give each list item its own row. Explode repeats the index labels, so use ignore_index=True before anything that aligns on the index.
• get_dummies turns a label column into indicator columns and from_dummies reverses it; str.get_dummies(sep=…) handles several labels per cell.
• cut bins numbers by edges you choose and includes each right edge; qcut puts the edges at quantiles, so its bins hold about equal numbers of rows.
• retbins=True returns the edges, so that the same bins can be applied to other data.