如何对多层索引Pandas DataFrame外层索引行求和及筛选最优商品组合

一、优化前期数据处理（替代低效的iterrows） #

用矢量化操作替代循环，提升代码效率：

import pandas as pd
import numpy as np

# 示例数据
item1 = ['item 1', 'item 2', 'item 1', 'item 1', 'item 2']
seller1 = ['Seller 1', 'Seller 2', 'Seller 3', 'Seller 4', 'Seller 1']
price1 = [1.85, 1.94, 2.00, 2.00, 2.02]
shipping1 = [0.99, 0.99, 0.99, 2.99, 0.99]
freeship1 = [5, 5, 5, 50, 5]
countavailable1 = [1, 2, 2, 5, 2]
countneeded1 = [2, 1, 2, 2, 1]

df1 = pd.DataFrame({
    "Seller": seller1,
    "Item": item1,
    "Price": price1,
    "Shipping": shipping1,
    "Free Shipping Minimum": freeship1,
    "Count Available": countavailable1,
    "Count Needed": countneeded1
})

# 1. 判断是否满足所需数量
df1['Fulfills Count Needed'] = np.where(df1['Count Available'] >= df1['Count Needed'], 'Yes', 'No')

# 2. 计算商品总金额（取可提供数量与所需数量的最小值相乘）
df1['Price x Count'] = np.minimum(df1['Count Available'], df1['Count Needed']) * df1['Price']

二、按卖家分组计算总花费（正确处理运费与免邮） #

解决同一卖家运费重复计算的问题，先按卖家聚合商品总金额，再应用免邮规则：

# 按卖家分组，聚合核心字段
seller_group = df1.groupby('Seller').agg(
    Total_Goods_Price=('Price x Count', 'sum'),
    Shipping_Cost=('Shipping', 'first'),  # 假设同一卖家运费规则统一
    Free_Shipping_Min=('Free Shipping Minimum', 'first'),
    Item_Details=('Item', list),
    Item_Counts=('Price x Count', lambda x: list(x / df1.loc[x.index, 'Price']))  # 还原实际可购买数量
).reset_index()

# 计算实际运费：满足免邮门槛则免运费，否则收取基础运费
seller_group['Actual_Shipping'] = np.where(
    seller_group['Total_Goods_Price'] >= seller_group['Free_Shipping_Min'],
    0,
    seller_group['Shipping_Cost']
)

# 计算卖家总花费（商品金额+运费）
seller_group['Total_Cost'] = seller_group['Total_Goods_Price'] + seller_group['Actual_Shipping']

三、选取刚好满足需求的低价商品组合（贪心算法） #

采用贪心策略：优先选择能覆盖更多需求、单位成本更低的卖家，逐步补充直到满足所有需求：

# 1. 定义全局需求（可根据实际场景调整）
global_needs = {'item 1': 2, 'item 2': 2}

# 2. 计算每个卖家的需求覆盖量和单位成本
def calculate_contribution(row):
    total_contrib = 0
    total_units = 0
    # 遍历卖家的商品，计算能覆盖的需求数量
    for item, count in zip(row['Item_Details'], row['Item_Counts']):
        if item in global_needs:
            contrib = min(count, global_needs[item])
            total_contrib += contrib
            total_units += contrib
    # 计算单位成本（无可用商品则设为无穷大）
    unit_cost = row['Total_Cost'] / total_units if total_units > 0 else float('inf')
    return pd.Series([total_contrib, unit_cost], index=['Total_Contribution', 'Unit_Cost'])

seller_group[['Total_Contribution', 'Unit_Cost']] = seller_group.apply(calculate_contribution, axis=1)

# 3. 排序卖家：优先覆盖需求多的，其次单位成本低的
seller_sorted = seller_group.sort_values(by=['Total_Contribution', 'Unit_Cost'], ascending=[False, True])

# 4. 贪心选取卖家，直到满足所有需求
selected_sellers = []
remaining_needs = global_needs.copy()

for _, row in seller_sorted.iterrows():
    if all(v <= 0 for v in remaining_needs.values()):
        break
    
    seller_items = dict(zip(row['Item_Details'], row['Item_Counts']))
    purchase = {}
    purchase_goods_cost = 0
    
    # 计算本次从该卖家购买的商品数量和金额
    for item, need in remaining_needs.items():
        if need <= 0 or item not in seller_items:
            continue
        buy_count = min(seller_items[item], need)
        purchase[item] = buy_count
        remaining_needs[item] -= buy_count
        # 获取该商品单价，计算本次购买的商品成本
        item_price = df1[(df1['Seller'] == row['Seller']) & (df1['Item'] == item)]['Price'].iloc[0]
        purchase_goods_cost += item_price * buy_count
    
    # 计算本次订单的运费
    if purchase_goods_cost >= row['Free_Shipping_Min']:
        shipping_cost = 0
    else:
        shipping_cost = row['Shipping_Cost']
    total_order_cost = purchase_goods_cost + shipping_cost
    
    if purchase:
        selected_sellers.append({
            'Seller': row['Seller'],
            'Purchased_Items': purchase,
            'Order_Total_Cost': total_order_cost
        })

# 输出结果
print("Selected Seller Orders:")
for order in selected_sellers:
    print(f"Seller: {order['Seller']}, Purchased: {order['Purchased_Items']}, Total Cost: ${order['Order_Total_Cost']:.2f}")

total_overall = sum(order['Order_Total_Cost'] for order in selected_sellers)
print(f"\nOverall Total Cost: ${total_overall:.2f}")

示例输出 #

Selected Seller Orders:
Seller: Seller 1, Purchased: {'item 1': 1, 'item 2': 1}, Total Cost: $4.85
Seller: Seller 3, Purchased: {'item 1': 1}, Total Cost: $2.99
Seller: Seller 2, Purchased: {'item 2': 1}, Total Cost: $2.93

Overall Total Cost: $10.77

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