如何基于子串匹配实现Pandas DataFrame的合并？

Hey there! Let's figure out how to speed up that substring-based matching between your massive DataFrames. Your current approach works but is way too slow for 42 million rows, so let's dive into more efficient solutions tailored to your needs (matching when df1['code'] either exactly matches or contains any df2['code'] as a substring).

Understanding the Core Problem #

Your original code loops through every code in df2 and scans df1 each time—this is an O(N*M) operation (42M * 4000 = 168 billion checks), which is why it's crawling. We need to reduce this to a single pass over df1 instead.

Solution 1: Precompiled Regular Expression (Simplest, No Extra Libraries) #

We can bundle all df2['code'] values into a single regex pattern, then scan df1 once to find matches. This cuts the operation down to O(N) time.

import re
import pandas as pd

# Step 1: Create a regex pattern with escaped codes (to handle special characters)
# Use unique() to avoid redundant patterns from duplicate codes in df2
pattern = '|'.join(re.escape(code) for code in df2['code'].unique())
match_regex = re.compile(pattern)

# Step 2: Check all df1 codes in one pass
df1['is_match'] = df1['code'].str.contains(match_regex, na=False)

# Step 3: Filter and format the result
result = df1[df1['is_match']][['code', 'id']].rename(columns={'code': 'code1'})

Why this works:

• The regex engine checks each df1['code'] against all df2 codes in a single scan, instead of looping through each df2 code separately.
• Using re.escape() ensures codes with special regex characters (like . or *) don't break the pattern.

Solution 2: Trie Tree (Faster for Prefix-Specific Matches) #

If your matching is specifically prefix-based (like O008 matching O0080 because it's the start of the string), a trie (prefix tree) will be even faster than regex. It reduces the number of checks per string to the length of the code itself.

We'll use the pygtrie library for this (install with pip install pygtrie):

import pygtrie
import pandas as pd

# Step 1: Build a trie from df2's codes
trie = pygtrie.StringTrie()
for code in df2['code'].unique():
    trie[code] = True  # Value doesn't matter, we just need existence

# Step 2: Define a function to check for exact matches or prefix matches
def matches_trie(code):
    # Check exact match first
    if code in trie:
        return True
    # Check if any prefix of the code exists in the trie
    for idx in range(1, len(code)):
        if code[:idx] in trie:
            return True
    return False

# Step 3: Apply the function to df1 (use swifter for faster apply on large data)
# Install swifter with pip install swifter
import swifter
df1['is_match'] = df1['code'].swifter.apply(matches_trie)

# Step 4: Filter and format the result
result = df1[df1['is_match']][['code', 'id']].rename(columns={'code': 'code1'})

Why this works:

• A trie stores codes as a tree of characters, so checking for prefixes is extremely fast—no need to compare against every df2 code for each df1 entry.

Solution 3: Distributed Processing (For Extremely Large Data) #

If 42 million rows are too big for your machine's memory, use a distributed framework like Dask or Spark to handle the matching across multiple cores/nodes.

Example with Spark:

from pyspark.sql import SparkSession
import re

# Initialize Spark session
spark = SparkSession.builder.appName("SubstringMatch").getOrCreate()

# Convert Pandas DataFrames to Spark DataFrames
sdf1 = spark.createDataFrame(df1)
sdf2 = spark.createDataFrame(df2)

# Build regex pattern
pattern = '|'.join(re.escape(code) for code in df2['code'].unique())

# Perform the match and join
matched = sdf1.filter(sdf1.code.rlike(pattern))
result = matched.select('code', 'id').withColumnRenamed('code', 'code1')

# Convert back to Pandas if needed
result_pd = result.toPandas()

Key Notes for Optimization #

1. Deduplicate df2 codes: Always use unique() when building patterns/tries—duplicates just add unnecessary work.
2. Prefix vs. Substring: If you only need prefix matches (not any substring), modify the regex to ^('|'.join(...)) to make matching even faster (the regex engine stops checking once it finds a prefix match).
3. Vectorization: Avoid apply() when possible—str.contains() is vectorized and faster for most cases. Use swifter if you must use apply() on large data, as it automatically chooses the fastest execution method.

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