基于列名前缀配对相乘求和的Pandas实现问题

Hey Jessica, let's solve this problem efficiently—since your dataset is large, we’ll skip slow Python loops and use Pandas’ optimized vectorized operations instead. Here’s how to get your desired total column:

Step-by-Step Implementation #

First, let’s start with your sample data, then apply the solution that works for large, unordered datasets:

import pandas as pd

# Your sample dataset setup
d = {
 'measure1_x': [10,12,20,30,21],
 'measure2_x':[11,12,10,3,3],
 'measure3_x':[10,0,12,1,1],
 'measure1_y': [1,2,2,3,1],
 'measure2_y':[1,1,1,3,3],
 'measure3_y':[1,0,2,1,1]
}
df = pd.DataFrame(d)
df = df.reindex(columns=[
'measure1_x','measure2_x', 'measure3_x','measure1_y','measure2_y','measure3_y'
])

# Efficient way to compute the total column
# 1. Collect all _x columns and their matching _y counterparts
x_columns = [col for col in df.columns if col.endswith('_x')]
y_columns = [col.replace('_x', '_y') for col in x_columns]

# 2. Multiply each x-y pair and sum the results row-wise
df['total'] = sum(df[x_col] * df[y_col] for x_col, y_col in zip(x_columns, y_columns))

print(df)

What This Does #

• Identify Pairs: We first grab all columns ending with _x, then generate their matching _y column names by replacing the suffix. This works even if columns are in any order.
• Multiply & Sum: Using a generator expression, we multiply each paired column set, then sum all those products to get the total value for every row.

Alternative (Group-Based Approach) #

If you prefer to group columns by their shared prefix (like measure1), this method also works great:

# Extract prefixes by removing _x/_y suffixes
prefixes = df.columns.str.replace(r'_[xy]$', '', regex=True)

# Group columns by prefix, multiply pairs, then sum across groups
df['total'] = df.groupby(prefixes, axis=1).apply(lambda group: group.iloc[:,0] * group.iloc[:,1]).sum(axis=1)

Why This Beats Loops #

Both methods use Pandas’ vectorized operations, which run on optimized C backend code—way faster than manual Python loops, especially for large datasets with hundreds/thousands of columns.

Output #

Either approach will give you exactly the result you want:

measure1_x  measure2_x  measure3_x  measure1_y  measure2_y  measure3_y  total
0          10          11          10           1           1           1     31
1          12          12           0           2           1           0     36
2          20          10          12           2           1           2     74
3          30           3           1           3           3           1    100
4          21           3           1           1           3           1     31

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