如何通过前缀匹配两个DataFrame为df1新增marker列？

Hey there! Let's ditch those messy nested loops and solve this problem the pandas way—efficiently and cleanly.

First, let's recap what you need: add a marker column to df1 by matching the prefix of BSI values with initial values from df2, then pull the corresponding marker for cartopy styling.

Step 1: Setup Your Sample Data #

First, let's define the sample DataFrames properly in code:

import pandas as pd

# Sample df1
df1 = pd.DataFrame({
    'BSI': ['AA-010', 'AA-030', 'AA-180', 'AA-200', 'AA-220'],
    'Shelter_Number': ['1085', '3690', '279', '2018', '3301'],
    'Location': [
        'SUSSEX (N SIDE) & RIDEAU FALLS',
        'SUSSEX (E SIDE) & ALEXANDER NS',
        'CRICHTON (E SIDE) & BEECHWOOD FS',
        'BEECHWOOD (S SIDE) & CHARLEVOIX NS',
        'BEECHWOOD (S SIDE) & MAISONNEUVE NS'
    ],
    'Latitude': [45.439571, 45.442795, 45.439556, 45.441154, 45.442188],
    'Longitude': [-75.695694, -75.692322, -75.676849, -75.673622, -75.671356]
})

# Sample df2
df2 = pd.DataFrame({
    'initial': ['AA', 'AB', 'AC', 'AD', 'AE'],
    'marker': ['bo', 'bv', 'b^', 'b<', 'b>']
})

Step 2: Efficient Solutions (No Nested Loops!) #

Nested loops are slow, error-prone, and not how pandas is designed to work. Here are two better approaches:

Approach 1: Dictionary Mapping + apply

First, convert df2 into a lookup dictionary for fast access, then use apply to check each BSI value:

# Create a lookup dict: initial -> marker
marker_lookup = df2.set_index('initial')['marker'].to_dict()

# Function to find matching marker
def get_marker(bsi_value):
    for prefix in marker_lookup.keys():
        if bsi_value.startswith(prefix):
            return marker_lookup[prefix]
    return None  # Return None if no match found

# Add the marker column to df1
df1['marker'] = df1['BSI'].apply(get_marker)

Approach 2: Vectorized Extraction + Merge (Even Faster!)

If your initial values follow a consistent pattern (like 2 uppercase letters at the start of BSI), use string extraction and merge—this is the fastest method for large datasets:

# Extract the prefix from BSI (adjust regex if your prefixes vary in length)
df1['initial'] = df1['BSI'].str.extract(r'^([A-Z]{2})')

# Merge with df2 to get the marker, then clean up
df1 = df1.merge(df2, on='initial', how='left').drop('initial', axis=1)

Step 3: Result #

Either method will give you the desired output:

BSI Shelter_Number                                      Location   Latitude  Longitude marker
0  AA-010           1085              SUSSEX (N SIDE) & RIDEAU FALLS  45.439571  -75.695694     bo
1  AA-030           3690            SUSSEX (E SIDE) & ALEXANDER NS  45.442795  -75.692322     bo
2  AA-180            279           CRICHTON (E SIDE) & BEECHWOOD FS  45.439556  -75.676849     bo
3  AA-200           2018        BEECHWOOD (S SIDE) & CHARLEVOIX NS  45.441154  -75.673622     bo
4  AA-220           3301       BEECHWOOD (S SIDE) & MAISONNEUVE NS  45.442188  -75.671356     bo

Why Your Nested Loop Failed #

Most likely, you ran into scope issues or were trying to modify df1 rows directly during iteration (which doesn't work because iterrows() returns copies, not references). Nested loops also perform terribly with large datasets—these pandas-native methods are way more efficient.

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