如何在Pandas中按门店与尺码累计扣减库存订单数量？

问题背景 #

现有两个Pandas DataFrame：df_1存储订单数据，df_2存储各门店的库存数据。当前已通过门店位置和尺码左连接两个DataFrame，并实现了用初始库存直接扣除对应订单数量，但需要改为按门店位置+尺码分组，持续累计扣减——即前一个订单扣减后的库存作为下一个订单的初始库存。

原始数据 #

订单数据（df_1）

import pandas as pd

df_1 = pd.DataFrame(
    columns=['size', 'ordered_by', 'shop_location', 'order_quantity'],
    data=[['L', 'Tom', 'London', 1], 
          ['M', 'Alice', 'Manchester', 1],
          ['S', 'Alice', 'Manchester', 1],
          ['S', 'Georgia', 'Newcastle', 1], 
          ['L', 'Bart', 'Manchester', 3], 
          ['M', 'Bob', 'Manchester', 1], 
          ['L', 'Toby', 'London', 2]]
)

库存数据（df_2）

df_2 = pd.DataFrame(
    columns=['size', 'shop_location', 'stock', 'price'],
    data=[['S', 'London', '5', '7.99'],
          ['M', 'London', '9', '7.99'],
          ['L', 'London', '3', '8.99'],
          ['XL', 'London', '7', '8.99'],
          ['S', 'Manchester', '2', '7.99'],
          ['M', 'Manchester', '2', '7.99'],
          ['L', 'Manchester', '15', '8.99'],
          ['XL', 'Manchester', '8', '8.99'],
          ['S', 'Newcastle', '2', '7.99'],
          ['M', 'Newcastle', '11', '7.99'],
          ['L', 'Newcastle', '4', '8.99'],
          ['XL', 'Newcastle', '1', '8.99']]
)

当前实现及问题 #

当前左连接后直接用初始库存减订单量的代码：

df_merge = pd.merge(
    df_1,
    df_2,
    how='left',
    left_on=['shop_location', 'size'],
    right_on=['shop_location','size']
)

df_merge['stock'] = df_merge['stock'].astype(int) - df_merge['order_quantity'].astype(int)

此方法的问题是：同一门店同一尺码的所有订单都用初始库存扣减，而非累计扣减（如曼彻斯特M码的第二个订单库存未在前一个扣减后的基础上继续减少）。

解决方案 #

核心思路：按门店位置+尺码分组，计算每组内的累计订单量，用该组的初始库存减去累计订单量，得到实时库存。

实现代码 #

# 合并数据并转换数据类型
df_merge = pd.merge(
    df_1,
    df_2,
    how='left',
    left_on=['shop_location', 'size'],
    right_on=['shop_location','size']
)
df_merge['stock'] = df_merge['stock'].astype(int)
df_merge['order_quantity'] = df_merge['order_quantity'].astype(int)
df_merge['price'] = df_merge['price'].astype(float)

# 按门店+尺码分组，计算累计订单量
df_merge['cumulative_orders'] = df_merge.groupby(['shop_location', 'size'])['order_quantity'].cumsum()

# 计算实时库存：初始库存 - 累计订单量
df_merge['stock'] = df_merge.groupby(['shop_location', 'size'])['stock'].transform('first') - df_merge['cumulative_orders']

# 可选：删除辅助列
df_merge = df_merge.drop('cumulative_orders', axis=1)

print(df_merge)

运行结果 #

size ordered_by shop_location  order_quantity  stock  price
0    L        Tom        London               1      2   8.99
1    M      Alice    Manchester               1      1   7.99
2    S      Alice    Manchester               1      1   7.99
3    S    Georgia     Newcastle               1      1   7.99
4    L       Bart    Manchester               3     12   8.99
5    M        Bob    Manchester               1      0   7.99
6    L       Toby        London               2      0   8.99

可以看到，曼彻斯特门店的M码第二个订单库存已变为0，符合累计扣减的需求。

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