Spreadsheets and legacy exports tend to come wide: one row per entity,
one column per category, a value in each cell — exactly how a person reading
it in Excel would want it, and exactly the shape that is awkward to filter,
join, or load into a table. This article covers reshaping that into
long (or "tidy") format: one row per (entity, category, value) triple,
which is what most further analysis actually wants.
Wide to long with melt#
long = wide.melt( id_vars=["region"], var_name="category", value_name="amount")melt
keeps id_vars as ordinary columns and folds every other column into two:
category (the old column name) and amount (that cell's value). A
20-column wide table becomes a three-column long table with roughly 20
times as many rows — more rows, but a shape a groupby, a merge, or a
database INSERT can actually work with. This is the
tidy data principle in
practice: one observation per row.
Extracting a clean id from a composite label#
A real export's column headers are rarely a bare id. A label like
"14. North District" needs the identifier pulled out before it can be
used as a join key:
long["category_id"] = long["category"].str.split(".").str[0].astype(int).str.split(".").str[0]
takes the piece before the first period — discarding
the descriptive text — and .astype(int) turns it from text into a real
integer. The same one-liner applies to any id_vars column carrying the
same "number. description" shape.
Combining two long tables#
A quantity is often split across sources that need to land on the same key before they can be summed — say, two channels of the same measurement:
combined = a.merge(b, on=["entity_id", "category_id"], suffixes=("_a", "_b"))combined["total"] = combined["amount_a"] + combined["amount_b"]Full parameter reference: DataFrame.merge.
suffixes is what keeps the merge from raising an error over the two
identically-named amount columns — each survives under its own suffix, and
adding them together is the only step left once both are aligned on the
same key.
Two filters are usually worth applying once the combined table exists:
dropping self-pairs (entity_id == category_id, when that combination is
not meaningful) and dropping zero-value rows, so the surviving rows are only
the genuinely nonzero relationships between distinct entities.
Common mistakes#
Trying to filter or group a wide table directly#
WHERE category = 'north'-style logic against a wide table means picking
the right column, which does not compose the way filtering rows does.
Reshape to long first; everything downstream gets simpler.
Forgetting suffixes on a merge with overlapping names#
Without it, Pandas still resolves the collision, but the resulting column names are easy to get wrong in the next line. Naming them explicitly removes the ambiguity.
Keeping meaningless self-pairs or zero rows#
An entity's relationship to itself, or a pair with a zero value, is real data but rarely what "the relationships between entities" is supposed to mean for analysis. Filter it out explicitly rather than assuming it is naturally absent.
Further reading#
pandas.melt— the full parameter reference for wide-to-long reshaping.- Wickham, "Tidy Data" — the paper behind the wide-vs-long distinction.
- Merge, join, concatenate and compare
and
DataFrame.merge— the guide and the fullmergereference, includingsuffixes. Series.str.split— the string-splitting method behind the id-extraction pattern.