TECH **Python for Data Science: Reshaping Data (melt, pivot, stack/unstack) – Zero-Fluff Study Guide**

Python for Data Science: Reshaping Data (melt, pivot, stack/unstack) – Zero-Fluff Study Guide

1. What This Is & Why It Matters

Reshaping data is the process of reorganizing a DataFrame’s structure—changing how rows, columns, and values are arranged—without altering the underlying data. Think of it like rearranging a spreadsheet: you might flip rows into columns, collapse multiple columns into key-value pairs, or expand nested data into a flat table.

Why This Matters in Production

• ETL Pipelines Break Without It: If your raw data is in the wrong shape, your aggregations, visualizations, or machine learning models will fail silently (or throw cryptic errors).
• APIs & Dashboards Expect Specific Formats: A REST API might return JSON with nested fields, but your dashboard expects a flat table. Reshaping bridges the gap.
• Legacy Code & Mergers: You inherit a dataset where "columns" are actually categories (e.g., 2020_sales, 2021_sales). You need to melt it into a clean year/sales format to analyze trends.
• Performance: A poorly shaped DataFrame can slow down operations (e.g., a wide table with 1000 columns vs. a long table with 3 columns and 1000 rows).

Real-World Scenario

You’re building a sales dashboard for a retail client. Their raw data looks like this:

But your dashboard’s plotting library expects data in this format:

Without reshaping, your dashboard won’t render. This guide shows you how to fix it.

2. Core Concepts & Components

1. Wide vs. Long Format

• Wide Format: Each variable has its own column (e.g., Jan_2023, Feb_2023). Good for human-readable reports.
• Production Insight: Wide tables are terrible for time-series analysis (e.g., forecasting, rolling averages). Use long format instead.
• Long Format: Variables are stored in rows (e.g., month, sales). Ideal for databases, APIs, and plotting.
• Production Insight: Most ML libraries (e.g., scikit-learn, TensorFlow) expect long-format data.

2. melt() – Unpivot from Wide to Long

• Definition: Converts columns into rows, creating a "key-value" pair structure.
• Production Insight: Essential for normalizing data before feeding it into a database or API. Example: Converting {"product_id": 101, "Jan_2023": 150} into {"product_id": 101, "month": "Jan_2023", "sales": 150}.

3. pivot() – Reshape from Long to Wide

• Definition: The inverse of melt()—converts rows into columns.
• Production Insight: Useful for creating summary tables (e.g., pivoting sales by region). Be careful: pivot() fails if there are duplicate entries for the same index/column pair.

4. pivot_table() – Pivot with Aggregation

• Definition: Like pivot(), but handles duplicates by aggregating (e.g., sum, mean).
• Production Insight: Critical for business reports (e.g., "Show me total sales by product and region"). Without aggregation, you’ll get ValueError: Index contains duplicate entries.

5. stack() – Pivot Columns into Rows (MultiIndex)

• Definition: Converts column labels into row indices, creating a MultiIndex.
• Production Insight: Useful for hierarchical data (e.g., time-series with multiple metrics). Often paired with unstack().

6. unstack() – Pivot Rows into Columns (MultiIndex)

• Definition: The inverse of stack()—converts row indices into columns.
• Production Insight: Helps flatten MultiIndex DataFrames for export to CSV or databases.

7. wide_to_long() – Specialized Melt for Prefixed Columns

• Definition: A variant of melt() for columns with prefixes (e.g., 2023_Jan, 2023_Feb).
• Production Insight: Saves time when dealing with time-series data where columns follow a naming pattern.

8. explode() – Expand Lists into Rows

• Definition: Splits list-like values in a column into separate rows.
• Production Insight: Common in NLP pipelines (e.g., splitting a list of tokens into one row per token).

3. Step-by-Step Hands-On Section

Prerequisites

• Python 3.8+ installed.
• pandas installed (pip install pandas).
• A dataset to reshape (we’ll use a sample sales dataset).

Task: Reshape a Sales Dataset for Analysis & Visualization

You have a wide-format sales dataset (sales_wide.csv):

product_id,Jan_2023,Feb_2023,Mar_2023
101,150,200,180
102,300,250,320
103,50,75,60

Goal: Convert it to long format for time-series analysis.

Step 1: Load the Data

import pandas as pd

df = pd.read_csv("sales_wide.csv")
print(df.head())

Output:

   product_id  Jan_2023  Feb_2023  Mar_2023
0         101       150       200       180
1         102       300       250       320
2         103        50        75        60

Step 2: Melt the Data (Wide → Long)

df_long = df.melt(
    id_vars=["product_id"],          # Columns to keep as-is
    value_vars=["Jan_2023", "Feb_2023", "Mar_2023"],  # Columns to melt
    var_name="month",                # New column for melted column names
    value_name="sales"               # New column for melted values
)
print(df_long.head())

Output:

   product_id     month  sales
0         101  Jan_2023    150
1         102  Jan_2023    300
2         103  Jan_2023     50
3         101  Feb_2023    200
4         102  Feb_2023    250

Why This Works: - id_vars: Columns that stay as identifiers (e.g., product_id).
- value_vars: Columns to "unpivot" into rows.
- var_name: Name for the new column holding the original column names (month).
- value_name: Name for the new column holding the original values (sales).

Step 3: Clean the Month Column (Optional)

The month column has _2023—let’s remove it for cleaner analysis:

df_long["month"] = df_long["month"].str.replace("_2023", "")
print(df_long.head())

Output:

   product_id month  sales
0         101   Jan    150
1         102   Jan    300
2         103   Jan     50
3         101   Feb    200
4         102   Feb    250

Step 4: Pivot Back to Wide Format (Long → Wide)

Now, let’s say you need to compare sales by product and month in a wide table:

df_wide = df_long.pivot(
    index="product_id",    # Rows
    columns="month",       # Columns
    values="sales"         # Values to fill the table
)
print(df_wide)

Output:

month      Feb  Jan  Mar
product_id
101        200  150  180
102        250  300  320
103         75   50   60

Key Notes: - pivot() fails if there are duplicate index/columns pairs. Use pivot_table() if duplicates exist.
- The output is a MultiIndex DataFrame (columns are month, rows are product_id).

Step 5: Use pivot_table() for Aggregation

Suppose you have duplicate entries (e.g., multiple sales records for the same product/month). pivot_table() handles this by aggregating:

# Example with duplicates
df_dupes = pd.DataFrame({
    "product_id": [101, 101, 102, 102],
    "month": ["Jan", "Jan", "Feb", "Feb"],
    "sales": [150, 200, 250, 300]
})

df_pivot = df_dupes.pivot_table(
    index="product_id",
    columns="month",
    values="sales",
    aggfunc="sum"  # Default is "mean"
)
print(df_pivot)

Output:

month      Feb  Jan
product_id
101        NaN  350
102        550  NaN

Step 6: Stack/Unstack for MultiIndex DataFrames

Let’s create a MultiIndex DataFrame and reshape it:

# Create a MultiIndex DataFrame
index = pd.MultiIndex.from_tuples(
    [("101", "Jan"), ("101", "Feb"), ("102", "Jan"), ("102", "Feb")],
    names=["product_id", "month"]
)
df_multi = pd.DataFrame({"sales": [150, 200, 300, 250]}, index=index)
print(df_multi)

Output:

                sales
product_id month
101        Jan     150
           Feb     200
102        Jan     300
           Feb     250

Stack/Unstack:

# Stack: Pivot columns into rows (creates a Series)
stacked = df_multi.stack()
print(stacked)

Output:

product_id  month
101         Jan      sales    150
                     sales    200
102         Jan      sales    300
                     sales    250
dtype: int64

# Unstack: Pivot rows into columns (back to DataFrame)
unstacked = df_multi.unstack()
print(unstacked)

Output:

        sales
month     Feb  Jan
product_id
101       200  150
102       250  300

When to Use: - stack(): When you need to flatten a DataFrame for export or analysis.
- unstack(): When you need to expand a MultiIndex for visualization.

Step 7: wide_to_long() for Prefixed Columns

If your columns follow a pattern (e.g., 2023_Jan, 2023_Feb), use wide_to_long():

df_prefixed = pd.DataFrame({
    "product_id": [101, 102],
    "2023_Jan": [150, 300],
    "2023_Feb": [200, 250],
    "2024_Jan": [160, 310]
})

df_long_prefixed = pd.wide_to_long(
    df_prefixed,
    stubnames=["2023", "2024"],  # Prefixes to extract
    i="product_id",              # Identifier column
    j="month",                   # New column for suffixes
    sep="_",                     # Separator between prefix and suffix
    suffix=".+"                  # Regex for suffix (matches anything)
)
print(df_long_prefixed)

Output:

                     2023  2024
product_id month
101        Jan     150.0   NaN
           Feb     200.0   NaN
102        Jan     300.0   NaN
           Feb     250.0   NaN
101        Jan       NaN  160.0
102        Jan       NaN  310.0

Key Notes: - stubnames: The prefixes to extract (e.g., 2023, 2024).
- suffix=".+": Matches any suffix after the prefix.

4. ? Production-Ready Best Practices

Performance

• Avoid apply() in Reshaping: melt() and pivot() are vectorized—use them instead of row-wise operations.
• Use dtype Optimization: Convert columns to category dtype if they have low cardinality (e.g., month).
  python df_long["month"] = df_long["month"].astype("category")
• Memory Usage: Wide tables with many columns consume more memory. Prefer long format for large datasets.

Data Quality

• Handle Duplicates: Always check for duplicates before pivoting: python if df.duplicated(subset=["product_id", "month"]).any():
print("Warning: Duplicates found! Use pivot_table() instead.")
• Missing Values: Decide how to handle NaNs (e.g., fillna(0) or dropna()).
• Column Naming: Use consistent naming (e.g., snake_case for columns).

Code Maintainability

• Document Reshaping Steps: Add comments explaining why you’re reshaping (e.g., # Convert to long format for time-series analysis).
• Modularize: Wrap reshaping logic in functions if reused: python def wide_to_long_sales(df, id_col="product_id"):
return df.melt(id_vars=[id_col], var_name="month", value_name="sales")
• Testing: Write unit tests for reshaping logic (e.g., check if output shape matches expectations).

Integration with Other Tools

• SQL Databases: Long format is easier to query (e.g., SELECT * FROM sales WHERE month = 'Jan').
• Visualization: Libraries like seaborn and plotly expect long format for faceted plots.
• Machine Learning: Most ML libraries (e.g., scikit-learn) expect long format for feature matrices.

5. ⚠️ Common Mistakes & Traps

6. ? Exam/Certification Focus

Typical Question Patterns

1. Given a wide table, convert it to long format:
2. Trick: They might ask for melt() but show a table with prefixed columns (e.g., 2023_Jan). The answer is wide_to_long().
3. Pivot with aggregation:
4. Trick: They’ll give a table with duplicates and ask for a pivot. The answer is pivot_table(aggfunc="sum").
5. Stack vs. unstack:
6. Trick: They’ll show a MultiIndex DataFrame and ask which method flips rows/columns. stack() pivots columns into rows; unstack() does the opposite.
7. Handling missing values:
8. Trick: They’ll ask what happens if you pivot without handling NaNs. The answer: "It depends on the aggregation function (e.g., sum ignores NaNs, mean fails)."

Key Distinctions

Common Scenario-Based Questions

1. Question: You have a DataFrame with columns ["id", "2023_Q1", "2023_Q2", "2024_Q1"]. How do you reshape it to have columns ["id", "year", "quarter", "value"]?

2. Answer: Use wide_to_long():
   python
pd.wide_to_long(df, stubnames=["2023", "2024"], i="id", j="quarter", sep="_", suffix="Q.")

3. Question