基于列名相似度匹配值：宽格式DataFrame转目标长格式的技术问询

Hey there! Let's sort out this DataFrame reshaping issue you're facing. Here are two practical approaches to convert your wide-format DataFrame into the long-format structure you need:

First, let's recreate your original DataFrame for context #

import pandas as pd

# Your initial wide-format DataFrame
df = pd.DataFrame({
    "Year 1 Grade": [60],
    "Year 2 Grade": [70],
    "Year 3 Grade": [80],
    "Year 4 Grade": [100],
    "Year 1 Students": [20],
    "Year 2 Students": [32],
    "Year 3 Students": [18],
    "Year 4 Students": [25]
})

Approach 1: Automated reshaping with melt + pivot (great for scalability) #

This method works even if you add more years later, no manual adjustments needed:

# Convert all columns into a long format with variable-value pairs
melted = df.melt(var_name="Year_Metric", value_name="Value")

# Split the combined column name into Year and Metric (Grade/Students)
melted[["_", "Year", "Metric"]] = melted["Year_Metric"].str.split(" ", expand=True)
# Clean up the Year column to keep just the number (convert to integer)
melted["Year"] = melted["Year"].astype(int)

# Pivot back to get Grade and Students as separate columns
result = melted.pivot(index="Year", columns="Metric", values="Value").reset_index()
result.columns.name = None  # Remove the extra column label

# Reorder columns to match your target layout
result = result[["Year", "Grade", "Students"]]

print(result)

Approach 2: Manual matching (aligns with your original plan) #

If you already have a list of years, you can loop through them and explicitly match the column names to populate your new DataFrame—this fixes the matching step you were stuck on:

years = [1, 2, 3, 4]
# Initialize your target DataFrame
result = pd.DataFrame(columns=["Year", "Grade", "Students"])

for year in years:
    # Construct the exact column names using the current year
    grade_col = f"Year {year} Grade"
    students_col = f"Year {year} Students"
    
    # Add a row with the corresponding values
    new_row = pd.DataFrame({
        "Year": [year],
        "Grade": [df[grade_col].iloc[0]],
        "Students": [df[students_col].iloc[0]]
    })
    result = pd.concat([result, new_row], ignore_index=True)

print(result)

Both methods will output exactly the target format you shared:

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