Python Pandas：匹配DataFrame列内容并实现多表关联输出

Got it, let's figure out how to get the joined results you need! Your original code only filters df2, but we can tweak it to pull in matching records from df1 too. Here's a step-by-step breakdown:

First, Let's Define the DataFrames (for clarity) #

import pandas as pd

df1 = pd.DataFrame({
    'vid': [1125, 1127, 1128, 1129],
    'vbull': ['RHSA:2017:3200', 'RHSA:2017:3205', 'RHSA:2017:3208', 'RHSA:2017:3209']
})

df2 = pd.DataFrame({
    'kbid': [2401, 2402, 2403, 2404, 2405],
    'vdesc': [
        'This contains details for RHSA:2017:3205',
        'This contains details for RHSA:2017:3206',
        'This contains details forRHSA:2017:3207',
        'This contains details for RHSA:2017:3208',
        'This contains details for RHSA:2017:3200'
    ]
})

Method 1: Extract Matched vbull from df2 Then Merge #

This is the most efficient approach for most cases:

1. Use regex to extract the matching vbull value from df2's vdesc field
2. Merge the two DataFrames on the vbull column to bring in df1's vid

# Create a regex pattern from df1's vbull values
bull_pattern = '|'.join(df1.vbull)

# Extract the matching vbull from df2's vdesc (adds a new vbull column to df2)
df2['vbull'] = df2['vdesc'].str.extract(f'({bull_pattern})', expand=False)

# Inner join df1 and df2 to keep only matching records
result = pd.merge(df1, df2, on='vbull', how='inner')

# Reorder columns to match your expected output (optional)
result = result[['vid', 'vbull', 'kbid', 'vdesc']]
print(result)

Output: #

vid          vbull  kbid                                            vdesc
0  1125  RHSA:2017:3200  2405  This contains details for RHSA:2017:3200
1  1127  RHSA:2017:3205  2401  This contains details for RHSA:2017:3205
2  1128  RHSA:2017:3208  2404  This contains details for RHSA:2017:3208

Method 2: Iterate Through df1 and Match Rows #

If you prefer a more explicit approach (good for small datasets):

matches = []
for _, row in df1.iterrows():
    # Find all df2 rows where vdesc contains the current vbull
    matched_df2_rows = df2[df2['vdesc'].str.contains(row['vbull'])]
    # Add df1's vid and vbull to these rows
    matched_df2_rows['vid'] = row['vid']
    matched_df2_rows['vbull'] = row['vbull']
    matches.append(matched_df2_rows)

# Combine all matches into one DataFrame
result = pd.concat(matches).reset_index(drop=True)
result = result[['vid', 'vbull', 'kbid', 'vdesc']]
print(result)

Key Notes #

• If your vbull values contain regex special characters (like . or *), use re.escape() to avoid matching issues:

  import re
  bull_pattern = '|'.join(re.escape(bull) for bull in df1.vbull)
  

• If a single vdesc has multiple matching vbull values, use str.extractall() instead of str.extract() to capture all matches, then reshape the data before merging.

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