如何使用依赖于日期的实际值

Question

我正在分析市场上的销售情况，但不知道该如何使用一个成本价格，这个价格是根据我更改的日期来决定的。

举个例子：

1. 我在3月12日收到了一笔订单，商品A的成本价格是100美元。

2. 之后在3月20日，我把成本价格改成了110美元，也就是增加了10美元。

3. 接着，我在3月25日又收到了一笔订单，还是商品A。

4. 然后我做了一个报告，里面包含了3月份的所有销售情况。

所以，3月12日的订单，商品A的成本价格是100美元，而3月25日的订单，商品A的成本价格是110美元。

如果我没有更改成本价格，也就是价格变更日期这一列是空的，那么在报告中这个价格就不会变化，我会使用成本价格这一列的值。

如果需要的话，我可以重新构建成本价格的表格。

我的表格大概是这样的：

订单表：

orders_test = {
    'order_id': ['82514913-0085-1', '82514913-0085-1', '82514913-0090-1', '82514913-0090-1'],
    'article_id': ['99823WH2MO11093_34', '99823WH2MO11093_34', '99823WH2MO11093_34', '99823WH2MO11093_34'],
    'profit': [1042.91, 1042.91, 2000, 2000],
    'order_date': ['01-15-2024', '01-21-2024', '02-15-2024', '02-20-2024']
}
orders_test = pd.DataFrame(orders_test)
orders_test['order_date'] = pd.to_datetime(orders_test['order_date'])

成本价格表：

cost_price_test = {
    'article_id': ['99823WH2MO11093_34', '99823WH2MO11093_34', '99823WH2MO11093_34'],
    'cost_price': [100, 110, 120],
    'price_change_date': ['01-10-2024', '01-20-2024', '02-10-2024'],
    'new_cost_price': [110, 120, 100]
}

cost_price_test = pd.DataFrame(data)
cost_price_test['price_change_date'] = pd.to_datetime(cost_price_test['price_change_date'])

我想要的结果如下：

          order_id          article_id   profit order_date  cost_price
0  82514913-0085-1  99823WH2MO11093_34  1042.91 01-15-2024         100
1  82514913-0085-2  99823WH2MO11093_34  1042.91 01-21-2024         120
2  82514913-0090-3  99823WH2MO11093_34  2000.00 02-15-2024         100
3  82514913-0090-4  99823WH2MO11093_34  2000.00 02-20-2024         100

有人能帮我吗？谢谢！

Answer 1

我觉得你最开始给出的数据有问题，特别是在 order_id 这一列，因为这些值和你期望的结果不一样。而且，你期望结果中的 cost_price 列的值似乎也不符合你描述的逻辑。

如果我理解正确，并假设这些数据框最开始是这样的：

orders_test:
          order_id          article_id   profit order_date
0  82514913-0085-1  99823WH2MO11093_34  1042.91 2024-01-15
1  82514913-0085-2  99823WH2MO11093_34  1042.91 2024-01-21
2  82514913-0090-3  99823WH2MO11093_34  2000.00 2024-02-15
3  82514913-0090-4  99823WH2MO11093_34  2000.00 2024-02-20

cost_price_test:
           article_id  cost_price price_change_date  new_cost_price
0  99823WH2MO11093_34         100        2024-01-10             110
1  99823WH2MO11093_34         110        2024-01-20             120
2  99823WH2MO11093_34         120        2024-02-10             100

你可以按照以下步骤进行：

0. 如果 cost_price_test 的 new_cost_price 列有空值，可以选择用 cost_price 列的值来填充这些空值；
1. 用 how='left' 的方式合并这两个数据框；
2. 按照 order_date 进行排序；
3. 删除那些 order_date 早于 price_date_change 的行；
4. 根据 order_id 和 article_id 删除重复的行，只保留最后一行，因为它们是按 order_date 排序的。

cost_price_test["new_cost_price"] = cost_price_test["new_cost_price"].fillna(
    cost_price_test["cost_price"]
)

orders_test = pd.merge(orders_test, cost_price_test, how="left").sort_values(
    "order_date"
)
orders_test = orders_test.loc[
    orders_test["order_date"] >= orders_test["price_change_date"]
].drop_duplicates(["order_id", "article_id"], keep="last")
orders_test = orders_test.drop(columns=["cost_price", "price_change_date"]).rename(
    columns={"new_cost_price": "cost_price"}
)

最终结果将会是：

           order_id          article_id   profit order_date  cost_price
0   82514913-0085-1  99823WH2MO11093_34  1042.91 2024-01-15         110
4   82514913-0085-2  99823WH2MO11093_34  1042.91 2024-01-21         120
8   82514913-0090-3  99823WH2MO11093_34  2000.00 2024-02-15         100
11  82514913-0090-4  99823WH2MO11093_34  2000.00 2024-02-20         100

Answer 2

你可以这样使用 pd.merge_asof：

merged_df = pd.merge_asof(orders_test, 
                          cost_price_test, 
                          left_on='order_date', 
                          right_on='price_change_date', 
                          direction='backward', suffixes=('', '_y'))

然后根据日期选择正确的价格：

merged_df['new_cost'] = np.where(merged_df['order_date']<=merged_df['price_change_date'], 
                                 merged_df['cost_price'], 
                                 merged_df['new_cost_price'])

输出结果：

          order_id          article_id   profit order_date        article_id_y  cost_price price_change_date  new_cost_price  new_cost
0  82514913-0085-1  99823WH2MO11093_34  1042.91 2024-01-15  99823WH2MO11093_34         100        2024-01-10             110       110
1  82514913-0085-1  99823WH2MO11093_34  1042.91 2024-01-21  99823WH2MO11093_34         110        2024-01-20             120       120
2  82514913-0090-1  99823WH2MO11093_34  2000.00 2024-02-15  99823WH2MO11093_34         120        2024-02-10             100       100
3  82514913-0090-1  99823WH2MO11093_34  2000.00 2024-02-20  99823WH2MO11093_34         120        2024-02-10             100       100

最后，你可以对这个数据表进行列过滤，只显示你想要的列。

merged_df[['order_id', 'article_id', 'profit', 'order_date', 'new_cost']]

输出结果：

          order_id          article_id   profit order_date  new_cost
0  82514913-0085-1  99823WH2MO11093_34  1042.91 2024-01-15       110
1  82514913-0085-1  99823WH2MO11093_34  1042.91 2024-01-21       120
2  82514913-0090-1  99823WH2MO11093_34  2000.00 2024-02-15       100
3  82514913-0090-1  99823WH2MO11093_34  2000.00 2024-02-20       100