如何在Python中基于列元素的子串合并Pandas DataFrame

Since your merge relies on matching a substring from DataFrame A's col1 (the 'a' prefix) to DataFrame B's col1, you can't use merge directly on the original columns. Instead, create a helper column in A to isolate the matching substring, then perform the merge. Here's how to do it step by step:

Step 1: Create a Helper Column in DataFrame A #

First, extract the common 'a' substring from A's col1 to use as the merge key. For your data, splitting the string on underscores and taking the first element works perfectly:

import pandas as pd

# Sample DataFrames
A = pd.DataFrame({
    'col1': ['a_low', 'a_low', 'a_high'],
    'col2': [5, 3, 4],
    'col3': [6, 10, 4]
})

B = pd.DataFrame({
    'col1': ['a'],
    'colB': [90]
})

# Add helper column to A
A['match_key'] = A['col1'].str.split('_').str[0]

This adds a match_key column to A with the value 'a' for all rows.

Step 2: Merge the DataFrames #

Now merge A and B using the helper column from A and col1 from B. Use how='left' to keep all rows from A (which matches your expected result):

merged_df = A.merge(B, left_on='match_key', right_on='col1', how='left')

Step 3: Clean Up the Result #

The merged DataFrame will have duplicate columns (col1_x from A, col1_y from B) and the helper column. Let's rename and drop these to match your expected output:

final_df = merged_df.rename(columns={'col1_x': 'col1'}).drop(['match_key', 'col1_y'], axis=1)

Final Result #

Running this code will give you exactly the output you want:

col1  col2  col3  colB
0   a_low     5     6    90
1   a_low     3    10    90
2  a_high     4     4    90

Alternatively, if you want to do this in a more concise one-liner (without keeping the intermediate helper column), you can use assign to create the helper column on the fly:

final_df = A.assign(match_key=A['col1'].str.split('_').str[0]) \
            .merge(B, left_on='match_key', right_on='col1', how='left') \
            .rename(columns={'col1_x': 'col1'}) \
            .drop(['match_key', 'col1_y'], axis=1)

That's it! This approach ensures all rows from A are matched with the corresponding value from B based on the substring.

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