如何在Pandas中批量映射两万五千多列调查数据的标签？

Got it, dealing with 25k columns manually with map() is totally impractical—let’s fix this with a scalable, automated approach. Here’s how to handle bulk mapping efficiently:

Step 1: Convert Your Label Mapping File to a Structured Dictionary #

First, we need to turn your data_label file into a dictionary where each key is a column name (like Age, Gender), and the value is another dictionary mapping codes to their labels. The exact code depends on how your data_label is stored:

Case 1: data_label is a structured table (CSV/Excel) #

If your label file is formatted as a table with columns like Column, Code, Label (e.g., each row defines one code-label pair for a column), use this:

import pandas as pd

# Load the label mapping file
data_label = pd.read_csv("data_label.csv")  # or pd.read_excel() for Excel files

# Build the mapping dictionary: {column_name: {code: label}}
label_mapping = data_label.groupby("Column").apply(
    lambda x: dict(zip(x["Code"], x["Label"]))
).to_dict()

Case 2: data_label is a plain text file (like your example) #

If your label file has lines formatted as Age对应1→20-30、2→31-40 or Gender对应1→Male、2→Female, parse it with this code:

# Read the text label file
with open("data_label.txt", "r", encoding="utf-8") as f:
    label_lines = [line.strip() for line in f if line.strip()]

label_mapping = {}
for line in label_lines:
    # Split column name and mapping pairs
    col_name, mappings_str = line.split("对应")
    col_name = col_name.strip()
    
    # Parse each code-label pair
    mapping_dict = {}
    for pair in mappings_str.split("、"):
        code, label = pair.split("→")
        # Convert code to the correct data type (int here—adjust if your codes are strings)
        mapping_dict[int(code.strip())] = label.strip()
    
    label_mapping[col_name] = mapping_dict

Step 2: Bulk Apply Mappings to Your Raw Data #

Now use the label_mapping dictionary to batch-process all relevant columns in df_data. We’ll only process columns that exist in both the mapping and your raw data, and keep original values for any unrecognized codes:

# Iterate through each mapped column and apply the transformation
for col, code_map in label_mapping.items():
    if col in df_data.columns:
        # Map codes to labels, keep original values if no match exists
        df_data[col] = df_data[col].map(code_map).fillna(df_data[col])

Key Notes #

• Data Type Matching: Make sure the data type of codes in df_data matches the keys in your mapping dictionary (e.g., if df_data['Age'] has integers, your mapping keys should be integers, not strings).
• Efficiency: This method uses pandas' vectorized map() operation per column, which is way faster than looping through individual rows or manually handling each column.
• Data Safety: The fillna(df_data[col]) ensures you don’t lose data if there are codes not present in your label mapping—those values stay as-is.

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