如何对DataFrame多列执行melt操作？pd.melt效果不佳求解决方案

Hey there! I totally get the frustration when pd.melt doesn't behave as expected for multi-column reshaping—been there, done that. Let's break down common scenarios and how to fix them, with concrete examples:

Scenario 1: You have paired "variable-value" column groups #

This is the most common pain point—like having columns such as math_score, math_rank, english_score, english_rank where you want to group related columns together.

Example DataFrame #

import pandas as pd

df = pd.DataFrame({
    'student_id': [1, 2, 3],
    'math_score': [90, 85, 92],
    'math_rank': [1, 3, 2],
    'english_score': [88, 91, 85],
    'english_rank': [2, 1, 3]
})

Solution 1: Use pd.wide_to_long (built for this case!) #

This function is designed specifically for reshaping wide tables with repeated column prefixes. It's way cleaner than nested melts here:

long_df = pd.wide_to_long(
    df,
    stubnames=['score', 'rank'],  # The parts of the column names that repeat
    i='student_id',               # Column(s) to keep as identifiers
    j='subject',                  # Name for the new column that holds the group label
    sep='_',                      # Separator between stubname and group label
    suffix='\w+'                  # Regex to match the group label (here, word characters)
).reset_index()

Result will have columns: student_id, subject, score, rank—exactly what you want.

Solution 2: Multi-level columns + melt #

If you prefer sticking with melt, first split your column names into a multi-level index, then melt and pivot to clean up:

# Split column names into two levels (e.g., "math_score" → ("math", "score"))
df.columns = df.columns.str.split('_', expand=True)

# Melt to get long format, then pivot to pair metrics back together
melted = df.melt(
    id_vars=[('student_id', '')],
    var_name=['subject', 'metric'],
    value_name='value'
)

final_df = melted.pivot(
    index=['student_id', 'subject'],
    columns='metric',
    values='value'
).reset_index()

# Remove the columns name for cleanliness
final_df.columns.name = None

Scenario 2: You need to melt multiple value columns while keeping several identifier columns #

If your goal is simpler—like turning columns math, english, science into a single subject column while keeping id and name as identifiers—pd.melt works perfectly, you just need to specify the right parameters:

Example DataFrame #

df = pd.DataFrame({
    'id': [1, 2, 3],
    'name': ['Alice', 'Bob', 'Charlie'],
    'math': [90, 85, 92],
    'english': [88, 91, 85],
    'science': [95, 89, 90]
})

Solution #

melted_df = df.melt(
    id_vars=['id', 'name'],  # Columns to keep as-is
    var_name='subject',      # Name for the new column holding the original column names
    value_name='score'       # Name for the new column holding the values
)

This will give you a clean long table with one row per student-subject pair.

If your specific use case doesn't fit these scenarios, feel free to share a snippet of your DataFrame structure and desired output—I can tweak this further!

内容的提问来源于stack exchange，提问作者Galileo