Cleaning Messy Datasets: A Guide to Pandas wide_to_long

If you have ever pulled data from public APIs or government portals, you already know the golden rule of data engineering: raw data is rarely formatted the way you actually need it.

A common headache is dealing with datasets stored in a wide format—where metrics across multiple years or categories are spread horizontally across dozens of columns. Trying to load a wide table straight into a relational database like PostgreSQL or SQLite is a querying nightmare.

In this quick guide, we’ll look at why wide data breaks your pipeline, and how to use Pandas' built-in wide_to_long function to flatten it out into a clean, query-ready format.

The Problem: The Wide Data Trap
Imagine you are pulling medication spending data from a public endpoint. Instead of getting a clean, normalized row for each year, the API hands you a JSON payload that looks something like this:

[
  {
    "drug_name": "Atorvastatin",
    "spending_2022": 1200000,
    "spending_2023": 1450000,
    "spending_2024": 1600000
  },
  {
    "drug_name": "Lisinopril",
    "spending_2022": 800000,
    "spending_2023": 850000,
    "spending_2024": 900000
  }
]

If you try to map this directly to a database table, you end up hardcoding columns for every single year. If the dataset updates next year to include 2025, your schema breaks.

We need to convert this into a long format, where the year lives in a single column, and the spending value lives in another.

Step 1: Pull and Load the Data
First, let's write a quick script using Python's requests library to fetch our data and load it into a Pandas DataFrame. (Always remember to include a timeout so your script doesn't hang indefinitely if the API drops!)

import pandas as pd
import requests

url = "https://api.example.com/medication-spending"

# Always use a timeout for production safety
response = requests.get(url, timeout=10)
response.raise_for_status()

# Load into a DataFrame
df = pd.DataFrame(response.json())
print(df.head())

index    drug_name    spending_2022   spending_2023    spending_2024
0        Atorvastatin   1200000         1450000           1600000

1        Lisinopril     800000          850000            900000

Step 2: Reshaping with wide_to_long
Instead of writing custom loops or messy melt logic, Pandas provides a native tool specifically designed for this: pd.wide_to_long().

To use it effectively, your wide column names need a consistent naming convention (like spending_2022, spending_2023, where the prefix matches and the suffix is the variable).

Here is how we transform the table:

# First, ensure we have a unique identifier for each row if one doesn't exist
df['id'] = df.index

# Unpivot the wide columns into long format
long_df = pd.wide_to_long(
    df, 
    stubnames='spending',     # The prefix of the columns we want to flatten
    i='id',                   # The unique row identifier
    j='year'                  # The name of the new column that will hold the suffix (2022, 2023, etc.)
).reset_index(drop=True)

print(long_df)

index   drug_name   year    spending
0   Atorvastatin    2022    1200000
1   Atorvastatin    2023    1450000
2   Atorvastatin    2024    1600000
3   Lisinopril      2022    800000
4   Lisinopril      2023    850000
5   Lisinopril      2024    900000

Why This Matters for Pipelines
By flattening your data into this structure, you achieve two major things:

Schema Stability: Adding data for 2025 doesn't require altering your database schema or rewriting your table definitions. It just adds new rows.

Easy Analytics: You can now write standard SQL aggregate queries (like GROUP BY year) without fighting against hardcoded column names.

Next time you build an ingestion script pulling from wide API payloads, skip the manual mapping and letwide_to_long handle the heavy lifting.