iTechGuides is reader-supported. When you buy through links on our site, we may earn an affiliate commission. As an Amazon Associate I earn from qualifying purchases. Learn more
Use pandas.wide_to_long when your DataFrame has repeated measurement columns with a consistent stub-and-suffix pattern, such as score_2022 and score_2023. You specify the shared stub, the row identifier, and the name for the suffix field; pandas then gathers the repeated values into a long-format column. For irregular column names or finer control over selected columns, use melt instead.
When wide_to_long fits
wide_to_long is designed for wide DataFrames where related columns follow a naming pattern: a shared prefix, or stub, followed by a suffix that identifies each measurement period or sub-observation. For example, score_2022 and score_2023 share the stub score; the year is the suffix.
With multiple repeated measurements, each group can have its own stub. Columns such as score_2022, score_2023, hours_2022, and hours_2023 can be reshaped together because both groups use the same year suffixes. pandas describes the function as “Less flexible but more user-friendly than melt.” The API reference documents the function and its parameters.
Reshape repeated columns into long format
Suppose each row represents one person, with a unique person_id, and the DataFrame includes the score and hours columns shown above. Set stubnames to the measurement prefixes, i to the source-row identifier, and j to the name you want for the suffix column:
#1 Best Overall
long = pd.wide_to_long(
df,
stubnames=["score", "hours"],
i="person_id",
j="year",
sep="_",
)The resulting DataFrame has one observation per person-and-year combination. The repeated values are in columns named score and hours, while year identifies the suffix from the original column names. The person identifier and suffix form the result’s index; other columns that are not part of the repeated measurements remain available. The pandas example also retains an unrelated X column.
Choose the row identifier carefully
The i argument identifies each original row. It can be one column or a list of columns, but together the specified values must identify the source rows for the reshape. If no single field is unique, use the complete combination that identifies a row:
long = pd.wide_to_long(
df,
stubnames=["score"],
i=["school_id", "person_id"],
j="year",
sep="_",
)Use the identifier from your data rather than treating the DataFrame’s row position as a durable key. The output index combines the identifier value or values with the suffix, so choose an identifier that distinguishes the original records.
Understand sep, suffix, and the output type
sep specifies the separator between a stub and its suffix. For columns named score_2022, set sep="_"; pandas removes that separator when interpreting the suffix. If your columns use another convention, set sep to the separator they actually contain.
suffix is a regular expression that determines which suffixes are recognized. By default, pandas matches numeric suffixes. Numeric suffixes are converted to an integer or floating-point type in the result. If the suffixes are labels such as one and two, provide a pattern that matches them, such as r"w+"; non-numeric suffixes remain labels. The pandas 3.1.0rc0 API example illustrates non-numeric suffixes.
Check that the suffix pattern describes the labels you intend to reshape. If your column names contain multiple kinds of suffixes, a broad pattern may match more columns than intended. For additional parameter details, see the versioned pandas 2.1.4 API reference.
Rank #4
Check the column pattern before reshaping
Before calling the function, make sure the intended measurement columns share the stated stubs and suffix format. A column that does not match those patterns is not one of the repeated measurements; ordinary non-measurement columns can remain in the output.
Do these 3 things before closing this tab:
1Clear out junk files and repair common Windows errors2Scan for outdated or missing drivers - takes under a minute3Repair Windows errors before they cause bigger problems- List the measurement groups you want to gather in
stubnames. - Confirm that each stub is followed by the suffix in the same position and format.
- Set
ito the complete identifier for the original rows. - Use
sepand, if needed,suffixto describe the column-name convention. - Inspect the returned index and suffix values to ensure they represent the intended row-and-sub-observation combinations.
Choose wide_to_long or melt
Both functions unpivot wide data, but they suit different column layouts. The pandas reshaping guide covers both approaches; its melt examples explicitly select identifier and value columns and configure the generated variable and value columns.
| Need | Better fit | Why |
|---|---|---|
| Several measurement groups share a systematic stub-plus-suffix naming convention | wide_to_long |
Stub names, row identifier, and suffix field express the reshape directly. |
| Column names are irregular or do not follow a common stub-plus-suffix pattern | melt |
You can explicitly choose identifier and value columns rather than relying on the naming pattern. |
| You need more explicit control over which columns become values and how the variable/value columns are named | melt |
Its arguments provide direct control over the chosen columns and generated output fields. |
Use pivot or pivot_table when you need to reshape in the other direction. pivot_table can aggregate when index-and-column combinations are duplicated; this is distinct from unpivoting with wide_to_long or melt.
Check pandas version when compatibility matters
The general pandas documentation and user guide cited here are for pandas 3.0.6, while the API page for version 2.1.4 is versioned and the non-numeric suffix example is from the 3.1.0 release candidate. Those references document the patterns described above, but if your environment requires exact compatibility, check the API documentation for the pandas version you deploy.
Quick Recap
Product prices and availability are accurate as of the date/time indicated and are subject to change. Any price and availability information displayed on Amazon at the time of purchase will apply.
What’s actually slowing this PC down?
Pick the symptom - the matching free tool is one click away.
