如何优化跨数据集值替换的低效代码以提升运行效率？

Hey there, the issue with your current code is that nested iterrows() loops are extremely inefficient for large datasets. Each iteration in Python carries overhead, and with two nested loops, you’re looking at O(n*m) time complexity—this gets painfully slow as your datasets grow. Let’s fix this with vectorized pandas operations, which are optimized to handle these tasks in a fraction of the time.

Here’s the Optimized Approach #

We’ll use pandas’ built-in merge and where functions, which are designed for exactly these kinds of matching and replacement scenarios:

1. Extract the release year from df's release_date
   First, we need a consistent year column to align with df1's release_year:

   df['release_year'] = df['release_date'].dt.year
   

2. Merge datasets to pull in matching mean values
   We’ll do a left join on the two key columns (production_companies and release_year) to bring in the mean values from df1 where there’s a match:

   # Merge df with df1, keeping only relevant columns from df1
   merged_df = df.merge(
       df1[['production_companies', 'release_year', 'mean']],
       left_on=['production_companies', 'release_year'],
       right_on=['production_companies', 'release_year'],
       how='left'
   )
   

3. Replace 0 budgets with matched mean values
   Use where to retain the original budget if it’s non-zero, otherwise use the merged mean value:

   df['budget'] = df['budget'].where(df['budget'] != 0, merged_df['mean'])
   

Why This Is Way Faster #

• Vectorized Operations: Pandas handles merging and value replacement using optimized C-based operations instead of slow Python loops. This cuts runtime drastically, especially for large datasets.
• Efficient Joins: We avoid the O(n*m) complexity by leveraging pandas’ optimized join algorithms, which are built to handle large data efficiently.

Alternative: Lookup Mapper Approach #

If you prefer a more direct lookup method, you can create a mapper from df1 and use it to fill values:

# Create a mapper using (production_companies, release_year) as keys
budget_mapper = df1.set_index(['production_companies', 'release_year'])['mean']

# Fill 0 budgets using the mapper
mask = df['budget'] == 0
df.loc[mask, 'budget'] = df.loc[mask].apply(
    lambda row: budget_mapper.get((row['production_companies'], row['release_year']), row['budget']),
    axis=1
)

This is still faster than your original code, but the merge approach is generally more readable and efficient for most use cases.

Quick Notes #

• Ensure production_companies is formatted consistently in both datasets (same case, no trailing spaces, etc.) to avoid missed matches.
• If no match exists in df1 for a row, the merged mean will be NaN—you can add fillna() to handle these cases (e.g., merged_df['mean'].fillna(df['budget']) to keep the original 0 if no match is found).

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