如何实现两个DataFrame间单个字符串与对应数值的映射替换？

Got it, let's walk through how to solve this problem step by step. You have two DataFrames: one mapping words to numerical values (DF1), and another with strings containing those words (DF2). We need to swap out each word in DF2's string column for its matching value from DF1.

Step 1: Set Up Your DataFrames (if you haven't already) #

First, let's replicate your sample data in pandas to work with:

import pandas as pd

# Create DF1 with word-value mappings
df1 = pd.DataFrame({
    'words': ['ABC', 'XYZ', 'DEF', 'GHI'],
    'value': [1.0, 2.0, 3.0, 4.0]
})

# Create DF2 with target strings
df2 = pd.DataFrame({
    'string': ['ABC DEF GHI', 'XYZ ABC DEF']
})

Step 2: Convert DF1 to a Mapping Dictionary #

We'll turn DF1 into a dictionary for quick lookups—this makes the replacement process efficient:

# Create a word-to-value dictionary
word_value_map = df1.set_index('words')['value'].to_dict()

This gives us the clean mapping: {'ABC': 1.0, 'XYZ': 2.0, 'DEF': 3.0, 'GHI': 4.0}

Step 3: Replace Words in DF2 #

We have two straightforward approaches here, depending on your exact use case:

Approach 1: Split & Join (Best for Space-Separated Words)

If your string column only has words separated by spaces (no special characters attached to words), this method is simple and fast:

# Split each string into words, map to values, then rejoin
df2['string'] = df2['string'].apply(
    lambda x: ' '.join(str(word_value_map[word]) for word in x.split())
)

Approach 2: Regular Expression Replace (Handles Edge Cases)

If words might be adjacent to other characters (or you want strict word boundary matching), use regex to target whole words only:

import re

# Build a regex pattern to match any of our target words (whole words only)
word_pattern = re.compile(r'\b(' + '|'.join(re.escape(word) for word in word_value_map.keys()) + r')\b')

# Define a replacement function to look up values
def replace_match(match):
    return str(word_value_map[match.group(0)])

# Apply the regex replacement to each string
df2['string'] = df2['string'].apply(lambda x: word_pattern.sub(replace_match, x))

Final Result #

After running either approach, your DF2 will look like this:

Bonus: Handle Missing Words #

If there's a chance some words in DF2 aren't in DF1, modify the split-join method to keep the original word instead of throwing an error:

df2['string'] = df2['string'].apply(
    lambda x: ' '.join(str(word_value_map.get(word, word)) for word in x.split())
)

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