基于映射文件重命名CSV表头时遇类型错误的技术问询

Let's break down why this error pops up and walk through how to fix it.

Why You're Getting This Error #

The crash happens during the groupby("index").sum() step. Here's the breakdown of what's going wrong:

1. After renaming columns, you transpose the DataFrame (df.T), swapping rows and columns.
2. When grouping by the new "index" column (originally your DataFrame's columns) and running .sum(), pandas tries to add values in each group.
3. If any group contains both numeric values and string values, pandas throws this type mismatch error—you can't add a string to an integer, after all.

Common root causes:

• Some columns in your aggregate.csv have string values (like "N/A", text comments, or malformed numbers) instead of numeric ones.
• Your regex pattern for cleaning column names misses some entries, leaving uncleaned names that lead to groupings with mixed data types.
• Unmapped columns (not present in your mapping.csv) retain their original names, causing unexpected groupings that mix incompatible data types.

Step-by-Step Fixes #

1. Convert All Columns to Numeric First

Before doing any renaming or aggregation, force all columns to numeric types (turning non-numeric entries into NaN, which pandas handles safely during summation):

# Convert columns to numeric, coerce invalid entries to NaN
df = df.apply(pd.to_numeric, errors='coerce')

2. Make Column Name Cleaning More Flexible

Your original regex might miss variations of the #X - Name format (like missing spaces around the hyphen). Update it to handle edge cases:

# Flexible regex to match "#X - Name", "#X-Name", "# 123  -  Name", etc.
df = df.rename(columns=lambda x: re.sub(r'#\d+\s*-\s*(.*)', r'\1', x).strip())

This handles:

• Any number of digits (\d+ instead of [0-9]*)
• Optional spaces around the hyphen (\s*-\s*)
• Trims extra whitespace from the cleaned column name with .strip()

3. Filter Out Unmapped Columns

If some columns don't exist in your mapping file, they can cause unexpected groupings. Keep only columns that have a mapping:

# Keep only columns present in the header map
mapped_cols = [col for col in df.columns if col in header_map.keys()]
df = df[mapped_cols]

4. Debug to Identify Problem Groups

To pinpoint exactly which group is causing the error, add a debug step before grouping:

df_transposed = df.T.reset_index()
# Print data types for each group
for group_name, group_data in df_transposed.groupby("index"):
    print(f"Group: {group_name}")
    print(f"Data types:\n{group_data.dtypes}\n")

This will show you which group has mixed string and numeric types, so you can fix those columns in your source data or mapping.

Updated Full Function #

Here's the revised rename_columns function with all fixes included:

def rename_columns(self, df, dictionary):
    """ 重命名数据框列名并聚合重复列
    Args:
        df: 待处理数据框
        dictionary: 新旧名称映射文件路径
    Returns:
        df_sum: 重命名后并聚合的数据框
    """
    print("rename_columns")
    # Load mapping and create header map
    dictionary = pd.read_csv(os.path.join(os.getcwd(), dictionary))
    header_map = dictionary.set_index("old").to_dict()["new"]
    
    # Convert all columns to numeric first
    df = df.apply(pd.to_numeric, errors='coerce')
    
    # Clean column names with flexible regex
    df = df.rename(columns=lambda x: re.sub(r'#\d+\s*-\s*(.*)', r'\1', x).strip())
    
    # Filter to only columns present in the mapping
    df = df[[col for col in df.columns if col in header_map]]
    
    # Apply mapping and aggregate
    df = df.rename(columns=header_map)
    df_sum = df.T.reset_index().groupby("index").sum().T
    
    return df_sum

Extra Checks #

• Double-check your mapping.csv for duplicate "new" names—these can group incompatible data types together.
• Scan your source aggregate.csv for non-numeric values (like comments or placeholders) that might have slipped into numeric columns.

内容的提问来源于stack exchange，提问作者Revolucion for Monica