按累计总价拆分DataFrame：处理超1000美元单品的分组问题

我有一个包含item、Quantity、unit Price、Total Price列的DataFrame，需要将其拆分为多个子DataFrame，要求每个子DataFrame的累计总价≥1000美元。若存在Total Price超过1000美元的单品，需拆分其数量：取部分数量凑满当前组的1000美元后，剩余部分归入下一组。

我尝试了以下代码，但运行出现错误：

import pandas as pd

# Assume your dataframe is called "df"

df = df.sort_values(by='total price', ascending=False) # sort the dataframe by total price in descending order

# Create an empty list to store the groups

groups = []

# Initialize a variable to track the cumulative total price

cumulative_total_price = 0

# Initialize a list to store the current group

current_group = pd.DataFrame(columns=["item", "quantity", "unit price", "total price"])

# Iterate through each row in the dataframe

for index, row in df.iterrows():

    if cumulative_total_price + row['total price'] > 1000:

        # If adding the current row's total price to the cumulative total price exceeds 1000,
        # find the quantity that doesn't exceed the cumulative total price
        remaining_price = 1000 - cumulative_total_price

        remaining_quantity = int(remaining_price / row['unit price'])

        # add the remaining quantity of the item to the current group
        current_group = current_group.append({'item': row['item'], 'quantity': remaining_quantity, 'unit price': row['unit price'], 'total price': remaining_quantity * row['unit price']}, ignore_index=True)

        groups.append(current_group)

        # update the cumulative total price
        cumulative_total_price = 0

        # reset the current group
        current_group = pd.DataFrame(columns=["item", "quantity", "unit price", "total price"])

        # add the remaining quantity of the item to the next group
        current_group = current_group.append({'item': row['item'], 'quantity': row['quantity'] - remaining_quantity, 'unit price': row['unit price'], 'total price': (row['quantity'] - remaining_quantity) * row['unit price']}, ignore_index=True)

        cumulative_total_price = (row['quantity'] - remaining_quantity) * row['unit price']

    else:

        # If adding the current row's total price to the cumulative total price does not exceed 1000,
        # add the row to the current group and update the cumulative total price
        current_group = current_group.append({'item': row['item'], 'quantity': row['quantity'], 'unit price': row['unit price'], 'total price': row['total price']}, ignore_index=True)

        cumulative_total_price += row['total price']

# Add the last group to the list of groups

groups.append(current_group)

错误原因分析 #

1. 列名不匹配：原DataFrame的列名是Total Price（带空格和大写），但代码中使用小写无空格的total price，会触发KeyError。
2. append()方法已弃用：pandas 2.0+版本中DataFrame.append()已被移除，继续使用会直接报错。
3. 数量拆分逻辑缺陷：用int()直接取整可能导致当前组累计总价未达到1000美元，且未处理单价无法整除剩余金额的情况。
4. 最后一组未校验：若最后一组累计总价不足1000美元，不符合需求要求。

修正后的代码 #

import pandas as pd
import math

# 统一列名为小写下划线，避免空格和大小写问题
df.columns = ['item', 'quantity', 'unit_price', 'total_price']
# 按总价降序排序，优先处理高价商品
df = df.sort_values(by='total_price', ascending=False).reset_index(drop=True)

groups = []
current_cumulative = 0
current_group = []

for _, row in df.iterrows():
    item = row['item']
    qty = row['quantity']
    unit_price = row['unit_price']
    total = row['total_price']

    # 当前组加整行总价仍不足1000，直接加入
    if current_cumulative + total <= 1000:
        current_group.append(row.to_dict())
        current_cumulative += total
    else:
        # 拆分当前商品凑满当前组到1000
        needed = 1000 - current_cumulative
        # 向上取整确保凑够金额，同时不超过原有数量
        split_qty = math.ceil(needed / unit_price)
        split_qty = min(split_qty, qty)
        
        # 生成拆分后的当前组条目
        split_item = {
            'item': item,
            'quantity': split_qty,
            'unit_price': unit_price,
            'total_price': split_qty * unit_price
        }
        current_group.append(split_item)
        # 将当前组存入列表
        groups.append(pd.DataFrame(current_group))
        
        # 处理剩余数量
        remaining_qty = qty - split_qty
        if remaining_qty > 0:
            # 剩余部分作为新组的初始条目
            remaining_item = {
                'item': item,
                'quantity': remaining_qty,
                'unit_price': unit_price,
                'total_price': remaining_qty * unit_price
            }
            current_group = [remaining_item]
            current_cumulative = remaining_qty * unit_price
        else:
            # 无剩余则重置当前组
            current_group = []
            current_cumulative = 0

# 处理最后一组：确保累计金额≥1000
if current_group:
    if current_cumulative >= 1000:
        groups.append(pd.DataFrame(current_group))
    elif groups:
        # 最后一组不足1000，合并到前一个有效组
        last_group = groups.pop()
        combined = pd.concat([last_group, pd.DataFrame(current_group)], ignore_index=True)
        groups.append(combined)

# 示例：输出所有分组
for i, group in enumerate(groups, 1):
    print(f"第{i}组，累计总价：{group['total_price'].sum():.2f}美元")
    print(group)
    print("-" * 50)

内容的提问来源于stack exchange，提问作者osama Abd-Elmohsen