基于条件对不同形状的Pandas DataFrame执行指定列乘法运算

Hey there! Let's break down why your current code isn't working and get you the exact result you want.

First, let's recap your data and goal:
You have two DataFrames, df_A (with columns id, alt, a, b, c, d, e) and df_B (with id, alt, a, b, c). You want to multiply the a, b, c columns in df_A with the corresponding values from df_B where the alt values match, leaving d and e untouched.

Your Original Data #

df_A:

df_B:

Why Your Code Failed #

The main issue with your code is chained indexing (df_C[list].loc[df_C["alt"]==i]), which creates a copy of the DataFrame slice instead of modifying the original df_C. Any assignment you make here only affects the copy, not the actual DataFrame you're trying to update. Additionally, converting df_B to a numpy array can cause broadcasting issues since each alt group in df_A has multiple rows while df_B has one row per alt.

Solution 1: Merge + Broadcast (Clean & Straightforward) #

This approach merges the multiplier values from df_B into df_A, then performs the multiplication, and cleans up the temporary columns:

import pandas as pd

# Recreate your DataFrames (you can skip this if you already have them)
df_A = pd.DataFrame({
    'id': [0, 1, 2, 3, 4],
    'alt': ['ICV', 'ICV', 'BEV', 'ICV', 'BEV'],
    'a': [0.2, 1.0, 3.2, 2.0, 2.0],
    'b': [1.0, 1.0, 1.0, 1.0, 1.0],
    'c': [0.2, 0.2, 0.2, 0.2, 0.2],
    'd': [0, 0, 1, 0, 1],
    'e': [1, 0, 0, 0, 1]
})

df_B = pd.DataFrame({
    'id': [0, 1],
    'alt': ['ICV', 'BEV'],
    'a': [0.1, 0.2],
    'b': [0.3, 0.4],
    'c': [0.5, 0.6]
})

# Step 1: Rename df_B's columns to avoid conflicts when merging
df_b_mapped = df_B.rename(columns={'a': 'a_mult', 'b': 'b_mult', 'c': 'c_mult'})[['alt', 'a_mult', 'b_mult', 'c_mult']]

# Step 2: Merge the multipliers into df_A using 'alt' as the key
df_merged = df_A.merge(df_b_mapped, on='alt', how='left')

# Step 3: Perform the multiplication for columns a, b, c
df_C = df_merged.copy()
df_C['a'] = df_merged['a'] * df_merged['a_mult']
df_C['b'] = df_merged['b'] * df_merged['b_mult']
df_C['c'] = df_merged['c'] * df_merged['c_mult']

# Step 4: Remove temporary multiplier columns
df_C = df_C.drop(['a_mult', 'b_mult', 'c_mult'], axis=1)

print(df_C)

Solution 2: GroupBy + Direct Assignment (Closer to Your Original Approach) #

If you prefer working with groups, this method uses groupby and avoids chained indexing by using .loc to modify the original DataFrame:

cols_to_multiply = ['a', 'b', 'c']
df_C = df_A.copy()

# Create a dictionary mapping alt values to their multipliers from df_B
alt_multipliers = df_B.set_index('alt')[cols_to_multiply].to_dict('index')

# Iterate over each alt group in df_C
for alt, group in df_C.groupby('alt'):
    # Get the multiplier values for this alt
    multipliers = alt_multipliers[alt]
    # Use .loc to directly update the relevant rows and columns in df_C
    df_C.loc[group.index, cols_to_multiply] = group[cols_to_multiply] * multipliers

print(df_C)

Expected Output #

Both solutions will produce your desired df_C:

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