如何基于Order ID更新Pandas DataFrame中的订单商品列表

问题需求 #

基于order id定位DataFrame中的目标行，将新商品列表追加到该行的order items列原有列表中，实现订单商品的更新。

场景还原 #

创建订单的代码 #

order_info = {
    "order id": Order.counter,
    "order status": self.status,
    "order time": self.time_placed,
    "table": self.table_number,
    "order items": items
}

order_info_df = pd.DataFrame([order_info])
order_info_df

初始输出 #

order id order status     order time  table  order items
0         1       Placed  1683903935860    212  [leek, chicken]

期望更新结果 #

order id order status     order time  table                   order items
0         1       Placed  1683903935860    212  [leek, chicken, steak, beef]

其中待追加的新商品列表为：

new_items = ["steak", "beef"]

尝试过的无效代码 #

existing_df.loc[existing_df['order id'] == self.order_id, 'order items'] += pd.Series(new_items)

关联类代码 #

Order类 #

class Order:

    order_df = pd.DataFrame()
    file_created = False
    counter = 0

    def __init__(self, table_number,  status="Placed"):
        self.order_id = Order.counter
        self.table_number = table_number
        self.status = status
        self.time_placed = datetime.now()
        self.file_name = "order_data.json"
        Order.file_created = CreateFile.file_handler(
            self.file_name, Order.file_created)
        Order.counter += 1

    def create_order(self, items=[]):
        self.status = "Placed"
        self.time_placed = datetime.now()
        order_info = {
            "order id": Order.counter,
            "order status": self.status,
            "order time": self.time_placed,
            "table": self.table_number,
            "order items": items

        }

        Order.order_df = pd.DataFrame.from_records([order_info])
        CreateFile.save_file(Order.order_df, self.file_name)

    def update_order(self, new_items=[]):
        existing_df = CreateFile.load("order_data.json")
        row_index = existing_df.loc[existing_df["order id"]
                                    == self.order_id].index[0]

        existing_df.at[row_index, "order items"].extend(new_items)

        CreateFile.save_file(existing_df, self.file_name)

CreateFile类 #

class CreateFile:
    @staticmethod
    def create_file(file_name):
        if os.path.exists(file_name):
            return
        with open(file_name, "w") as file:
            pass

    @staticmethod
    def save_file(data, file_name):
        existing_data = CreateFile.load(file_name)
        updated_data = existing_data.copy()
        updated_data.update(data)
        updated_data = pd.concat([updated_data, data], ignore_index=True)
        CreateFile.write_dataframe_to_json(updated_data, file_name)

    @staticmethod
    def load(file_name):
        with open(file_name, 'r') as file:
            content = file.read()
            if content.strip() == '':
                df = pd.DataFrame()
                return df
            else:
                df = pd.read_json(content)
        return df

    @staticmethod
    def write_dataframe_to_json(df, file_name):
        with open(file_name, 'w') as file:
            file.write(df.to_json(orient='records', indent=4))

    @staticmethod
    def file_handler(file_name, bool_value):
        if not bool_value:
            CreateFile.create_file(file_name)
            bool_value = True
            return bool_value

问题根源与修正方案 #

核心问题点 #

1. 订单ID逻辑冲突：Order类中__init__先赋值self.order_id再自增counter，但create_order用当前counter作为订单ID，导致self.order_id与实际存储的订单ID不匹配，更新时无法找到目标行。
2. 列表追加方式错误：使用loc结合+=的方式无法正确实现列表追加，Pandas会将其视为元素级操作而非列表扩展。
3. 保存方法逻辑错误：CreateFile.save_file中的update和concat会导致重复添加行，而非更新原有数据。

分步修正 #

1. 修复订单ID生成逻辑

调整Order类的__init__和create_order，确保订单ID统一：

class Order:
    order_df = pd.DataFrame()
    file_created = False
    counter = 0

    def __init__(self, table_number,  status="Placed"):
        self.table_number = table_number
        self.status = status
        self.time_placed = datetime.now()
        self.file_name = "order_data.json"
        Order.file_created = CreateFile.file_handler(
            self.file_name, Order.file_created)

    def create_order(self, items=[]):
        self.status = "Placed"
        self.time_placed = datetime.now()
        # 生成唯一订单ID
        Order.counter += 1
        self.order_id = Order.counter
        order_info = {
            "order id": self.order_id,
            "order status": self.status,
            "order time": self.time_placed,
            "table": self.table_number,
            "order items": items
        }
        new_order_df = pd.DataFrame.from_records([order_info])
        # 加载现有数据并追加新订单
        existing_df = CreateFile.load(self.file_name)
        if existing_df.empty:
            Order.order_df = new_order_df
        else:
            Order.order_df = pd.concat([existing_df, new_order_df], ignore_index=True)
        CreateFile.save_file(Order.order_df, self.file_name)

2. 修正订单更新逻辑

使用apply实现列表追加，更健壮且不易出错：

def update_order(self, new_items=[]):
    existing_df = CreateFile.load("order_data.json")
    # 定位目标行并追加新商品
    mask = existing_df["order id"] == self.order_id
    existing_df.loc[mask, "order items"] = existing_df.loc[mask, "order items"].apply(lambda x: x + new_items)
    # 直接保存修改后的完整数据
    CreateFile.save_file(existing_df, self.file_name)

3. 修复文件保存逻辑

简化CreateFile.save_file，更新操作传入的已是修改后的完整DataFrame，无需额外合并：

@staticmethod
def save_file(data, file_name):
    CreateFile.write_dataframe_to_json(data, file_name)

经过以上修正，即可实现基于订单ID定位行，并将新商品列表正确追加到原有列表中，同时保证数据保存无重复。

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