How to reshape wide data to long format in Pandas (with Code)

The Short Answer (TL;DR)

Use melt with explicit identifier and measurement columns to turn each selected wide cell into one long-format row. Name the variable and value columns, retain a stable row identifier and check the expected row count before dropping missing measurements.

import pandas as pd
wide = pd.DataFrame({"id":[1,2],"baseline":[10.,20.],"followup":[12.,23.]})
print(wide.melt(id_vars=["id"],value_vars=["baseline","followup"],var_name="visit",value_name="score"))

Detailed Implementation

This example uses synthetic data and does not need a network connection. Install the recorded package versions in a separate Python environment before running it.

python -m pip install numpy==2.5.4 pandas==3.0.6

from pathlib import Path
import numpy as np
import pandas as pd

def to_long(frame, visits=("baseline","followup")):
    visits = list(visits)
    if not frame.columns.is_unique or not visits or len(set(visits))!=len(visits):
        raise ValueError("Use unique columns and distinct selected visits.")
    if not {"person","site",*visits}.issubset(frame.columns) or set(visits).intersection({"person","site"}):
        raise ValueError("Identifiers and measurements must be present and disjoint.")
    if set(frame.columns).intersection({"row_id","visit","score"}):
        raise ValueError("An output column name already exists.")
    for column in visits:
        if not pd.api.types.is_numeric_dtype(frame[column]) or pd.api.types.is_bool_dtype(frame[column]):
            raise ValueError("Selected scores must be numeric and use the same units.")
        if not np.isfinite(frame[column].dropna().to_numpy(dtype=float)).all():
            raise ValueError("Observed scores must be finite.")
    source = frame.copy()
    source.insert(0,"row_id",np.arange(len(source)))
    result = source.melt(id_vars=["row_id","person","site"],value_vars=visits,
                         var_name="visit",value_name="score",ignore_index=True)
    if len(result)!=len(frame)*len(visits) or result.duplicated(["row_id","visit"]).any():
        raise AssertionError("The wide-cell to long-row mapping is not one-to-one.")
    return result

# Synthetic repeated scores in the same units; person labels can repeat.
wide = pd.DataFrame({"person":["p1","p1","p2"],"site":["north","south","north"],
    "baseline":pd.array([10,None,30],dtype="Float64"),
    "followup":pd.array([12,22,None],dtype="Float64")},index=[7,7,2])
result = to_long(wide)
report = result.to_string(index=False,na_rep="NA") + f"\nwide rows={len(wide)}; long rows={len(result)}\n"
Path("wide-to-long-scores.txt").write_text(report,encoding="utf-8")
print(report)

The long table contains six rows: three baseline and three followup entries. Two scores remain missing, all four observed scores are preserved, and a pivot by row_id and visit reconstructs the selected wide score columns exactly.

What is one row after melting?

The input has three source records and two selected visits, so the output has six rows. Each row represents one specific source record and one visit. The row_id identifies the source record; person and site are copied alongside it. The first three rows hold baseline scores and the next three hold followup scores. This is a change of representation, not an aggregation, interpolation or new measurement.

Why create a row identifier if person already exists?

Person p1 occurs on two source records, and the original index also contains duplicate labels. Neither alone uniquely identifies a source row. The temporary row_id runs from zero through two and is carried into each melted row. Together, row_id and visit uniquely identify each selected wide cell. If a persistent source record identifier is available, validate and use that instead; a generated ordinal is only stable for the input ordering used in this example.

What happens to missing scores?

Melt keeps the two missing selected cells as two long rows. Four observed scores still sum to 74. Dropping missing score rows would reduce the result to four rows and change the completeness audit, so make that filtering a separate documented decision. A missing value at one visit does not authorize copying a different visit. Keep the visit label next to its score so an unavailable baseline cannot be confused with an observed followup.

Can the wide table be reconstructed?

The validation pivots by the unique row_id and visit pair, restores the selected column order and original index, and checks every score including missing values. The source person and site are checked separately against the source row. Pivot needs unique identifier-variable pairs; pivot_table would aggregate duplicates and can conceal an invalid mapping. Preserve the original table when exact original dtypes, metadata or omitted columns matter.

Which measurements belong in one value column?

The two score columns in this example use the same measurement units and compatible nullable numeric dtypes. Melting unrelated currencies, lengths and counts into one unlabeled value column would create a misleading table. Use explicit value_vars so a newly added metadata column cannot accidentally become a measurement. The helper rejects colliding output names, repeated visit selections, missing fields, nonnumeric scores and infinite observed scores; unit compatibility remains a stated source-data assumption.

Mathematical Context

With n source rows and m distinct selected measurement columns, melt produces n times m rows if missing cells are retained. The mapping sends source cell (i,j) to a row containing source row identifier i, measurement label j and the original value x_ij. It does not change the measurement units. Here n=3 and m=2, giving six rows with four observed values and two missing values. A reverse pivot recovers the selected matrix when each source-row and measurement-label pair occurs exactly once; that uniqueness condition is what the explicit row identifier and validation protect.

\[ (i,j,x_{ij})\longmapsto\mathrm{one\ long\ row},\quad N_{\mathrm{long}}=n\,m \]

Reproducibility

Executed with Python 3.12.14; numpy 2.5.4, pandas 3.0.6. Automated checks confirm the stated output properties.

Primary documentation

• Pandas melt: identifiers, selected columns and index policy
• Pandas reshaping: melt and pivot examples
• Pandas pivot: unique index-column pairs

Related guides

• Inspect the distribution of observed measurements
• Display observations alongside group summaries

When a long table contains repeated identifier-variable pairs, pivot a table with duplicate key pairs using an explicit aggregation rule and observed counts instead of silently discarding observations.