Python DataFrame列值映射求助：多字段列匹配映射文件

Hey there! Let's work through this data matching problem together—this is a common task when dealing with unstructured string fields in pandas, so I'll walk you through a clear, step-by-step approach.

First, we need to pull out all the relevant tokens (like ABC, XYZ, A0) from the messy strings in Col1. The example you gave uses # and @ as separators, and we want to skip things like @1 (pure numeric values after @). A regex-based approach works best here for flexibility:

import pandas as pd
import re

# Assume your main dataset is stored in df_main
df_main['Col1_tokens'] = df_main['Col1'].apply(
    lambda x: [match.group() for match in re.finditer(r'[A-Za-z][A-Za-z0-9]*', x)]
)

This regex ([A-Za-z][A-Za-z0-9]*) grabs sequences that start with a letter followed by letters/numbers—perfect for filtering out the unwanted numeric-only tokens like 1 from your example.

Next, let's get your mapping data ready for quick lookups. Assuming your mapping CSV is loaded into df_mapping with columns token (the value to match, e.g., ABC) and mapped_value (the corresponding result), convert it to a dictionary for fast access:

# Drop duplicates first to avoid conflicting mappings
df_mapping_clean = df_mapping.drop_duplicates(subset='token')
# Convert to a dict: key = token, value = mapped_value
token_map = df_mapping_clean.set_index('token')['mapped_value'].to_dict()

Now we have two options depending on how you want your final output structured:

Option 1: Keep Mappings as a List per Row #

If you want to retain each original row and add a column with all mapped values in a list:

df_main['mapped_results'] = df_main['Col1_tokens'].apply(
    lambda tokens: [token_map.get(token, 'No Match') for token in tokens]
)

Using get() ensures that if a token isn't found in your mapping, it'll return 'No Match' (you can replace this with None or another placeholder if preferred).

Option 2: Expand Tokens into Separate Rows #

If you need each token to have its own row (while keeping other columns like col2 intact), use pandas' explode() method:

# Expand the tokens list into individual rows
df_expanded = df_main.explode('Col1_tokens').rename(columns={'Col1_tokens': 'token'})
# Merge with the mapping data to get the corresponding values
df_final = df_expanded.merge(df_mapping_clean, on='token', how='left')

The how='left' keeps all rows from your original dataset—tokens without a match will show NaN in the mapped value column.

• Case Sensitivity: If your mapping uses uppercase but Col1 has mixed cases (or vice versa), standardize everything first:

  # Convert tokens in Col1 to uppercase
  df_main['Col1_tokens'] = df_main['Col1'].apply(
      lambda x: [match.group().upper() for match in re.finditer(r'[A-Za-z][A-Za-z0-9]*', x)]
  )
  # Convert mapping tokens to uppercase too
  df_mapping_clean['token'] = df_mapping_clean['token'].str.upper()
  

• Duplicate Tokens: If Col1 has repeated tokens (e.g., ABC#ABC @1#A0), use set() to deduplicate before mapping:

  df_main['Col1_tokens'] = df_main['Col1'].apply(
      lambda x: list(set([match.group() for match in re.finditer(r'[A-Za-z][A-Za-z0-9]*', x)]))
  )
  

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