Pandas如何根据指定列的值对对应数量的列进行求和？

To compute the heldInventory column by summing the first n sales columns (where n is the value in weeksInventory for each row) efficiently without loops, use this vectorized approach leveraging pandas cumulative sums and numpy's indexing:

import pandas as pd
import numpy as np

# Sample DataFrame
data = {
    'product': ['Product 1', 'Product 2', 'Product 3', 'Product 4'],
    'weeksInventory': [1, 2, 3, 4],
    'wk1Sales': [25, 18, 5, 38],
    'wk2Sales': [31, 16, 4, 42],
    'wk3Sales': [28, 12, 2, 48],
    'wk4Sales': [32, 14, 3, 41],
    'wk5Sales': [33, 19, 4, 45],
    'wk6Sales': [27, 17, 6, 44]
}
df = pd.DataFrame(data)

# Step 1: Extract all sales columns
sales_cols = df.filter(like='wkSales')

# Step 2: Compute cumulative sums along each row
cumulative_sales = sales_cols.cumsum(axis=1)

# Step 3: Get the index of the cumulative sum we need (weeksInventory - 1, 0-based)
target_indices = df['weeksInventory'] - 1

# Step 4: Use numpy to select the correct cumulative sum value for each row
df['heldInventory'] = np.take_along_axis(
    cumulative_sales.to_numpy(),
    target_indices.to_numpy()[:, np.newaxis],
    axis=1
).ravel()

print(df)

How This Works: #

• Cumulative Sum Calculation: cumsum(axis=1) computes the running total of sales columns for each row. For example, the third row's cumulative sums are [5, 9, 11, 14, 18, 24].
• Index Selection: Subtract 1 from weeksInventory to convert it to a 0-based index matching the cumulative sum columns. For weeksInventory=3, we pick the 2nd index (value 11), which is the sum of the first 3 sales columns.
• Vectorized Indexing: np.take_along_axis efficiently selects the correct value from each row of the cumulative sum array, avoiding slow row-wise loops.

Why Your Original Code Failed: #

The line df.iloc[:,2:(3 + df['weeksInventory'])].sum(axis=1) doesn't work because iloc expects scalar values for slice bounds. You can't pass a Series (which has a different value per row) as the end of the slice—this would require a different slice for every row, which iloc doesn't support directly.

Alternative (Less Efficient) Approach Using apply: #

If you prefer a pandas-only solution (without numpy), you can use apply, though it's slower for large datasets:

df['heldInventory'] = df.apply(
    lambda row: row.loc['wk1Sales':f'wk{row.weeksInventory}Sales'].sum(),
    axis=1
)

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