如何高效实现DataFrame列值匹配并扩展数据行（规避双重循环）

Absolutely right to avoid double loops here—with 5k and 25k rows, that's 125 million iterations which would crawl in Python. Instead, leverage pandas' built-in vectorized operations (like merge) which are optimized in C and will handle this dataset in milliseconds.

First, let's define your sample DataFrames to work with:

import pandas as pd

# First DataFrame with City/Country
df_locations = pd.DataFrame({
    'City': ['NY', 'LA', 'Paris', 'Roma'],
    'Country': ['US', 'US', 'France', 'Italy']
})

# Second DataFrame with Place/Score/ID_ref
df_scores = pd.DataFrame({
    'Place': ['Paris', 'US', 'Italy'],
    'Score': ['+1', '+5', '-8'],
    'ID_ref': ['0010', '1000', '3020']
})

The Optimal Approach: Two Merges + Concatenation #

Your goal is to match rows where either the City matches Place OR the Country matches Place. We can do this with two separate merges (one for each match condition) then combine the results:

# Merge on City <-> Place
match_city = pd.merge(df_locations, df_scores, left_on='City', right_on='Place', how='inner')

# Merge on Country <-> Place
match_country = pd.merge(df_locations, df_scores, left_on='Country', right_on='Place', how='inner')

# Combine both result sets and reset index
expanded_df = pd.concat([match_city, match_country]).reset_index(drop=True)

# Optional: Reorder columns to match your desired output
expanded_df = expanded_df[['City', 'Country', 'Place', 'Score', 'ID_ref']]

Result #

This gives you the expanded DataFrame you need:

Why This Works Better Than Loops #

• Pandas merge uses optimized, vectorized operations under the hood (no Python-level loops), so it's orders of magnitude faster for large datasets.
• It's cleaner and more maintainable—you avoid messy nested loops and manual row appends.

Bonus: Handle Duplicates (If Needed) #

If some rows might match both City and Place (e.g., a city named "US" in country "US"), you can drop duplicates with:

expanded_df = expanded_df.drop_duplicates()

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