How to Replace Substring in df1[Column1] with df2[Column2] if df1[Column1] contains df2[Column1]?

To replace substrings in a pandas DataFrame column based on matches with another DataFrame, you should use str.contains to identify rows and str.replace for the substitution. This vectorized approach avoids inefficient Python loops by leveraging pandas' optimized C backend. The process involves creating a boolean mask from the lookup table and applying the replacement only where the condition is met, ensuring both speed and correctness.

The Inefficient Loop Approach

Many beginners attempt to solve this problem using nested loops or the apply method with a lambda function that iterates through every row of the second DataFrame. While this might work for tiny datasets, it results in O(N*M) complexity, which causes severe performance degradation as data grows. For example, if df1 has 100,000 rows and df2 has 1,000 patterns, you are performing 100 million operations in pure Python. This approach also often ignores sort order or index alignment, leading to subtle bugs where replacements happen on the wrong rows due to implicit index matching errors.

The Vectorized Solution Using str.contains

The robust solution relies on pandas string methods, specifically str.contains and str.replace. These methods operate on entire columns at once. First, you need to ensure your lookup data in df2 is clean and ready for pattern matching. Then, you can iterate through the patterns in df2, but because each iteration processes a full column vector rather than individual cells, it remains significantly faster than cell-by-cell loops. Start by extracting the search terms and replacement values from df2. Let's assume df2['Column1'] holds the substring to find and df2['Column2'] holds the new value. You must handle potential regular expression special characters in the search terms to prevent errors. Use re.escape if you are treating the search strings as literal text rather than regex patterns. Here is the step-by-step logic:

1. Prepare the Lookup Data: Ensure df2 does not contain duplicates that might cause ambiguous replacements. Sort df2 by the length of the search string in descending order if overlapping patterns exist, so longer matches are prioritized.
2. Create a Copy: Always work on a copy of df1['Column1'] to avoid SettingWithCopyWarning and to preserve the original data.
3. Iterate and Replace: Loop through the rows of df2. For each row, check if the target column contains the substring using str.contains.
4. Apply Replacement: Where the condition is true, use str.replace to swap the old substring with the new one.

import pandas as pd
import re

# Sample DataFrames
df1 = pd.DataFrame({'Text': ['apple pie', 'banana split', 'cherry apple']})
df2 = pd.DataFrame({
    'Search': ['apple', 'banana'],
    'Replace': ['fruit', 'citrus']
})

# Create a copy to modify
result = df1['Text'].copy()

# Iterate through lookup table
for _, row in df2.iterrows():
    pattern = re.escape(row['Search']) # Escape regex special chars
    replacement = row['Replace']
    
    # Check if the pattern exists in the current series
    mask = result.str.contains(pattern, na=False)
    
    # Only replace where the mask is True
    result[mask] = result[mask].str.replace(pattern, replacement, regex=True)

df1['Replaced_Text'] = result

This method is efficient because str.contains and str.replace are vectorized operations. Although we still loop over df2, the inner operation is handled by pandas in C, making it orders of magnitude faster than a Python-level loop over df1. If df2 is very large, consider compiling a single regex pattern using | (OR operator) to perform all replacements in one pass, but be cautious of greedy matching issues.

Handling Overlapping Patterns

A common pitfall occurs when one search term is a substring of another. For instance, if you want to replace "cat" with "dog" and "catalog" with "book", replacing "cat" first will break "catalog". To fix this, sort df2 by the length of the search string in descending order. This ensures that longer, more specific patterns are replaced before shorter, generic ones. This strategy prevents partial matches from interfering with complete words. For more complex string manipulations, such as converting fields to arrays or removing specific endings, see Treating String Field Values and Converting Them to Arrays. This technique complements substring replacement by allowing further structural changes to your data after the initial substitution.

Performance Optimization Tips

If your dataset is massive, even the vectorized loop might be slow. In such cases, consider using regex compilation. You can build a single compiled regex pattern from all entries in df2. However, this requires careful handling of replacement groups if you need dynamic replacement values. Alternatively, merge df1 and df2 using a left join on the extracted substring, but this is only feasible if the substring extraction is deterministic and unique. For most standard use cases, the str.contains mask approach provides the best balance of readability and performance. Another related technique is finding and deleting strings at the end of lines, which uses similar regex principles. Read How to Find and Delete a String at the End of a Line to understand how regex anchors can refine your substring targeting.

Frequently Asked Questions

Why is my replacement not working for some rows?

This usually happens because the search string contains regex special characters like dots or brackets. Always use re.escape() on your search terms from df2 to treat them as literal strings. Additionally, check for whitespace differences or case sensitivity mismatches between the two DataFrames.

Can I do this without looping through df2?

Yes, you can combine all patterns into a single regex using the pipe character (|). However, this makes dynamic replacement values difficult because str.replace expects a single replacement string or a callable function. If you need dynamic replacements, the loop over df2 is the safest and most readable approach.

What if df1 and df2 have different indices?

Index alignment is not required for this method because we are operating on the string content itself, not the index labels. The str.contains method checks the actual text values, so mismatched indices will not cause errors. Just ensure you are referencing the correct column names.

How do I handle null values in the column?

Pandas string methods return NaN for null inputs by default. When using str.contains, set na=False to ensure nulls are treated as False, preventing them from being selected for replacement. This keeps your original null values intact while processing only valid strings.

Is there a limit to the number of replacements?

There is no hard limit, but performance may degrade if df2 contains thousands of unique patterns. For very large lookup tables, consider using a dictionary-based approach or merging strategies instead of iterative string replacement. Also, ensure your regex engine supports the complexity of the combined patterns if you choose to optimize further.