高级字符串拆分：如何从商品订单字符串中分离商品与对应价格

问题说明 #

CSV中basket_items字段的结构规则：

• 整单商品用英文逗号分隔
• 单个商品用-作为分隔符，最后一段固定为价格，存在口味的商品会在商品名和价格之间多一段口味值
  原代码仅完成了整单维度的逗号拆分，未对单个商品做字段拆解、格式转换，无法直接得到商品与价格的匹配关系。

给出的CSV样例数据结构：

timestamp,store,customer_name,basket_items,total_price,cash_or_card
06/06/2022 09:00,Chesterfield,Stephanie Neyhart,"Large Flat white - 2.45, Large Flavoured iced latte - Vanilla - 3.25, Large Flavoured iced latte - Hazelnut - 3.25",8.95,CASH
06/06/2022 09:02,Chesterfield,Donna Marley,"Large Flavoured iced latte - Hazelnut - 3.25, Regular Latte - 2.15, Large Flavoured iced latte - Vanilla - 3.25",8.65,CARD

实现代码 #

版本1：按订单维度输出结构化商品列表 #

处理后每笔订单对应一个字典，包含订单基础信息和结构化的商品明细，商品字段区分商品名、口味、价格：

import pandas as pd

# 读取并清洗数据
df = pd.read_csv("team1-project/example_transactions.csv")
df = df.dropna().reset_index(drop=True)

order_list = []
for _, row in df.iterrows():
    # 拆分整单为单个商品，去除前后多余空格
    raw_goods = [item.strip() for item in row["basket_items"].split(",")]
    goods_detail = []
    for good in raw_goods:
        parts = [p.strip() for p in good.split(" - ")]
        price = float(parts[-1])
        if len(parts) == 3:
            # 带口味的商品
            goods_detail.append({
                "base_name": parts[0],
                "flavor": parts[1],
                "full_name": f"{parts[0]} - {parts[1]}",
                "price": price
            })
        elif len(parts) == 2:
            # 无口味商品
            goods_detail.append({
                "base_name": parts[0],
                "flavor": None,
                "full_name": parts[0],
                "price": price
            })
    order_list.append({
        "timestamp": row["timestamp"],
        "store": row["store"],
        "customer": row["customer_name"],
        "pay_type": row["cash_or_card"],
        "total": float(row["total_price"]),
        "goods": goods_detail
    })

版本2：打平为商品明细DataFrame #

如果后续需要做销量、销售额统计，直接打平为单商品一行的表结构更方便：

item_records = []
for _, row in df.iterrows():
    raw_goods = [item.strip() for item in row["basket_items"].split(",")]
    for good in raw_goods:
        parts = [p.strip() for p in good.split(" - ")]
        record = {
            "timestamp": row["timestamp"],
            "store": row["store"],
            "price": float(parts[-1])
        }
        if len(parts) == 3:
            record["base_name"] = parts[0]
            record["flavor"] = parts[1]
        else:
            record["base_name"] = parts[0]
            record["flavor"] = None
        item_records.append(record)

# 转为DataFrame可直接做分组统计
item_df = pd.DataFrame(item_records)

处理效果 #

单个商品结构化后的结果示例：

{
  "base_name": "Large Flavoured iced latte",
  "flavor": "Hazelnut",
  "full_name": "Large Flavoured iced latte - Hazelnut",
  "price": 3.25
}

可以直接通过full_name作为唯一键匹配对应价格，也可以通过base_name+flavor的组合做商品去重、分类统计。

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