多键多值DataFrame重塑：宽表转长表技术问询

Got it, let's work through this wide-to-long conversion problem—especially since your dataset has courses missing some metric columns (like bio1Csem in your example), which can trip up standard methods. I'll share a robust, flexible approach that works for both small test tables and your large real-world dataset.

First, let's replicate a sample wide table matching your scenario #

Let's start with a small test table where some courses lack certain metric columns (e.g., bio only has a score column, no sem column):

import pandas as pd

# Sample wide DataFrame: math/phys have score + sem, bio only has score
df_wide = pd.DataFrame({
    'student_id': [1, 2, 3],
    'math_score': [85, 92, 78],
    'math_sem': ['Fall2023', 'Spring2024', 'Fall2023'],
    'phys_score': [79, 88, 90],
    'phys_sem': ['Spring2024', 'Fall2023', 'Spring2024'],
    'bio_score': [91, 83, 76]
})

Solution: Melt + Extract + Pivot (Flexible for Missing Columns) #

This method avoids needing to pre-fill missing columns and automatically handles courses with incomplete metrics:

1. Melt all course-related columns into a long format
   First, we'll collapse all course metric columns into two columns: one for the course-metric label, and one for the value. We keep student_id as our identifier.

   melted = df_wide.melt(
       id_vars=['student_id'],
       var_name='course_metric',
       value_name='value'
   )
   

2. Split the course-metric label into separate course and metric columns
   Use a regular expression to extract the course name and metric type (e.g., score, sem) from the combined label. This works even if some metrics are missing for certain courses.

   # Adjust the regex if your column naming uses a different separator (e.g., camelCase)
   melted[['course', 'metric']] = melted['course_metric'].str.extract(r'^(\w+)_(\w+)$')
   

3. Pivot back to get metrics as columns
   Now we'll reshape the data so each metric (like score, sem) becomes a column, grouped by student and course. Missing metrics will automatically show up as NaN, which is exactly what we want for courses that lack those columns.

   df_long = melted.pivot_table(
       index=['student_id', 'course'],
       columns='metric',
       values='value',
       aggfunc='first'  # Use first since each student-course-metric has one value
   ).reset_index()
   

What the final long table looks like #

Your output will look something like this, with NaN filling in for missing metrics:

Why this works for large tables #

• No pre-processing needed: You don't have to manually add missing columns for every course-metric combination.
• Efficient: Pandas' melt, str.extract, and pivot_table are optimized for large datasets, so they'll handle your big table without performance issues.
• Flexible: If your column naming convention changes (e.g., using hyphens instead of underscores), just tweak the regex pattern in str.extract.

Alternative: Using pd.wide_to_long (For Consistent Naming) #

If you prefer wide_to_long, you'll need to first add missing columns (filled with NaN) for all metric types. This is less flexible but works if your column names follow strict patterns:

# Add missing bio_sem column
df_wide['bio_sem'] = pd.NA

# Convert to long format
df_long_alt = pd.wide_to_long(
    df_wide,
    stubnames=['score', 'sem'],
    i='student_id',
    j='course',
    sep='_',
    suffix=r'\w+'
).reset_index()

But for your use case (with arbitrary missing columns), the first method is far more practical.

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