Pandas中从列提取数据并生成新值的优化方案咨询

Hey there! Let's tackle your Pandas optimization needs step by step—since you're new to coding (welcome aboard!), I'll break this down clearly so it's easy to follow.

First, let's recap your starting point to make sure we're aligned:

Your original raw Col A data looks like this:

Col A
Learn English Lit
Go Mathematics
Start Science
Learn Science
Go English
Start Math
Learn Math
Go Biology
Start English
Learn Science, Math
Learn Math, English

You’ve already written code to extract and standardize interests, but want to fix two key pain points:

1. Improve how action keywords (Learn/Go/Start) map to their target columns (Col Learn/Col Go/Col Start)
2. Handle rows with multiple interests (like "Learn Science, Math") by appending all matches as comma-separated values instead of overwriting existing data

1. Optimize Keyword-Interest Matching Logic #

Your current approach uses scattered loc assignments that overwrite values. Instead, we’ll build structured mappings and use regex to extract all keyword-interest pairs in one pass, making the logic scalable and easy to maintain.

Here’s the refined code for this step:

import pandas as pd
import re

# Load your dataset
df = pd.read_csv('subjects.csv')

# Step 1: Define core mappings (easy to update later!)
# Standardize interest synonyms to a single name
interest_standardize = {
    'English Lit': 'English',
    'Mathematics': 'Maths',
    'Biology': 'Science',
    'Math': 'Maths'
}
# Map action keywords to their target columns
keyword_to_col = {
    'Learn': 'Col Learn',
    'Go': 'Col Go',
    'Start': 'Col Start'
}
# Abbreviate interests for concise output
interest_abbr = {
    'English': 'E',
    'Maths': 'M',
    'Science': 'S'
}

# Step 2: Extract all (keyword, interest) pairs from Col A
# Regex pattern to catch keywords + any valid interest (original or standardized)
valid_interests = list(interest_standardize.keys()) + list(interest_standardize.values())
pattern = rf'\b({"|".join(keyword_to_col.keys())})\b.*?\b({"|".join(valid_interests)})\b'
# Extract matches as a list of tuples per row
df['raw_matches'] = df['Col A'].str.findall(pattern, flags=re.IGNORECASE)

# Step 3: Standardize interests in the matches
def clean_match(match_tuple):
    keyword, interest = match_tuple
    # Standardize the interest name, keep keyword as-is
    standardized_interest = interest_standardize.get(interest.strip(), interest.strip())
    return (keyword.strip(), standardized_interest)

df['clean_matches'] = df['raw_matches'].apply(lambda x: [clean_match(t) for t in x])

2. Implement Multi-Value Column Append (No Overwriting) #

Now we’ll process the cleaned matches to populate your target columns with comma-separated values, ensuring we never overwrite existing data—only append new matches.

Add this code after the previous step:

# Initialize target columns if they don't exist
for col in keyword_to_col.values():
    if col not in df.columns:
        df[col] = ""

# Function to populate columns with comma-separated values
def populate_target_cols(row):
    # Group matches by their keyword to map to the right column
    keyword_groups = {}
    for keyword, interest in row['clean_matches']:
        keyword_groups.setdefault(keyword, []).append(interest_abbr[interest])
    
    # Build and append the formatted strings to each column
    for keyword, target_col in keyword_to_col.items():
        if keyword in keyword_groups:
            # Format as "Learn S, Learn M"
            formatted_entries = ', '.join([f"{keyword} {abbr}" for abbr in keyword_groups[keyword]])
            # Append if column has existing data, else set it
            if row[target_col]:
                row[target_col] = f"{row[target_col]}, {formatted_entries}"
            else:
                row[target_col] = formatted_entries
    return row

# Apply the function to every row
df = df.apply(populate_target_cols, axis=1)

# Optional: Drop temporary match columns if you don't need them
df = df.drop(['raw_matches', 'clean_matches'], axis=1)

Example Output #

For a row with Col A = "Learn Science, Math, Biology. Go English", your output will look like this:

Quick Tips for New Coders #

• Test regex patterns with small samples first (e.g., re.findall(pattern, "Learn Science, Math")) to verify matches
• Use print(df['clean_matches'].head()) to check if your cleaned matches are correct before populating columns
• Keep mappings in dictionaries like we did—this makes it trivial to add new keywords, interests, or synonyms later without rewriting core logic

内容的提问来源于stack exchange，提问作者Umar.H