如何用Python将宽格式腐败感知指数CSV转换为长格式？

Hey there! Let’s work through this together—wide-to-long format shifts can throw unexpected quirks, especially with country and year columns. I’ve dealt with similar headaches before, so here’s how to get your dataset sorted:

First, Start with a Reliable Base Code #

Assuming your original newdata.csv has a column like Country as the unique identifier, and year columns named 1997, 1998, ..., 2015 for CPI scores, this standard melt setup should be your starting point:

import pandas as pd

# Load the wide-format dataset
df_wide = pd.read_csv("newdata.csv")

# Convert to long format
df_long = pd.melt(
    df_wide,
    id_vars=["Country"],  # Keep this column as the fixed identifier
    var_name="Year",      # Name for the new year column
    value_name="CPI_Score"  # Name for the CPI value column
)

# Optional but critical: Convert Year to integer (fixes string-based year issues)
df_long["Year"] = df_long["Year"].astype(int)

# Export clean long-format CSV for Excel
df_long.to_csv("long_format_cpi.csv", index=False)

Common Anomalies & Quick Fixes #

1. Country Column Has Duplicates/Empty Rows #

• Problem: If your original CSV has blank rows, merged cells, or repeated country names (common in Excel-exported files), the Country column will get messy post-melt.
• Fix: Clean the data before melting:

  # Drop rows where Country is empty
  df_wide = df_wide.dropna(subset=["Country"])
  # Remove duplicate country entries if needed
  df_wide = df_wide.drop_duplicates(subset=["Country"])
  

2. Year Column Has Extra Text (e.g., CPI_1997 instead of 1997) #

• Problem: If your year columns aren’t just raw numbers, the resulting Year column will include extra characters, making it useless for analysis.
• Fix: Extract the numeric year with string parsing:

  # Pull out 4-digit year from columns like "CPI_1997" or "Year_2015"
  df_long["Year"] = df_long["Year"].str.extract(r'(\d{4})').astype(int)
  

3. Wrong id_vars Specified #

• Problem: If you forgot to include Country in id_vars, or added other columns that should be melted, your country data will be scattered incorrectly across rows.
• Fix: Double-check that id_vars only includes columns that should stay as unique identifiers (just Country in your case, unless you have extra metadata like region).

4. Missing CPI Scores Making Columns Look Broken #

• Problem: NaN values in CPI_Score can make it seem like country/year columns are messed up, but they’re just empty score entries.
• Fix: Filter out rows with missing scores if needed:

  # Keep only rows with valid CPI scores
  df_long = df_long.dropna(subset=["CPI_Score"])
  

Verify the Fix #

Run this quick check to confirm your long-format data looks right:

print(df_long.head())

You should see output like this:

Country  Year  CPI_Score
0  Afghanistan  1997        1.5
1      Albania  1997        2.5
2      Algeria  1997        3.0

内容的提问来源于stack exchange，提问作者sara r.