如何基于contains匹配Account字段合并两个DataFrame（非完全匹配）

Hey, I get it—being stuck on this partial match merge for a week is super frustrating. Regular merge/join only handles exact matches, so let's walk through two solid solutions to get your desired df3 using pandas:

First, let's recreate your DataFrames #

First, let's set up the sample data you provided to test the code:

import pandas as pd

# df1 as per your example
df1 = pd.DataFrame({
    'Account': ['B36363', 'G47281', 'H46291'],
    'ID': ['2019001', '2019002;2018101', '2019001'],
    'Name': ['John', 'Alice;Emma', 'John']
})

# df2 as per your example
df2 = pd.DataFrame({
    'Account': ['B36363-0', '01_G47281', 'X_H46291', 'II_G47281'],
    'Col_B': ['text_b1', 'text_b2', 'text_b3', 'text_b4'],
    'Col_C': ['text_c1', 'text_c2', 'text_c3', 'text_C4']
})

Method 1: Iterate through df1 and match rows in df2 #

This method is straightforward and efficient if df1 doesn't have too many rows:

# Initialize empty result DataFrame
df3 = pd.DataFrame()

# Loop through each row in df1
for _, df1_row in df1.iterrows():
    target_account = df1_row['Account']
    # Filter df2 rows where Account contains the target from df1
    matched_df2_rows = df2[df2['Account'].str.contains(target_account)]
    # Attach ID and Name from df1 to the matched rows
    matched_df2_rows = matched_df2_rows.assign(
        ID=df1_row['ID'],
        Name=df1_row['Name']
    )
    # Append to the result
    df3 = pd.concat([df3, matched_df2_rows], ignore_index=True)

# Reorder columns to match your desired df3 (optional but clean)
df3 = df3[['Account', 'Col_B', 'Col_C', 'ID', 'Name']]

print(df3)

Method 2: Cross Join + Filter (No Loops) #

If you prefer a loop-free approach, use a cross join to get all possible combinations, then filter for partial matches. Note: This works best for smaller datasets (since cross joins create a lot of rows temporarily):

# Perform a cross join to get all df2-df1 combinations
cross_joined = df2.merge(df1, how='cross')

# Keep only rows where df2's Account contains df1's Account
filtered = cross_joined[
    cross_joined.apply(lambda row: row['Account_x'].contains(row['Account_y']), axis=1)
]

# Clean up column names and order
df3 = filtered.rename(columns={'Account_x': 'Account'}).drop('Account_y', axis=1)
df3 = df3[['Account', 'Col_B', 'Col_C', 'ID', 'Name']]

print(df3)

Notes on Both Methods #

• Both methods will return exactly the df3 you described.
• If a single df2 Account matches multiple df1 Accounts (unlikely in your case), both methods will retain all matching pairs—you can add a .drop_duplicates() if needed.
• For very large datasets, Method 1 is usually more memory-friendly since it avoids creating a full cross join.

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