Pandas Python中的SUMIFS公式

Question

Pandas Python中的SUMIFS公式

4

我在一家物流公司工作，我们为客户提供B2C交付服务。因此，我们有一个费率卡以表格形式列出交付/交易清单，包裹的重量和交付地点。

我看到这里回答了很多SUMIFS问题，但与我所需的非常不同。

第一个“交易数据框”包含“交易ID”，“重量”，“岛屿”，“类别”。

transaction   weight   island   category
1             0.3      luzon    regular
2             0.5      visayas  express
3             0.5      luzon    express
4             0.4      visayas  regular
5             1.7      visayas  regular
6             1.5      luzon    regular

第二个费率卡数据框包含类别、最小重量、最大重量和费用

category    island  min weight  max weight  fee
regular     luzon     0            0.5       30
regular     luzon     0.51         3.0       40
express     luzon     0            3.0       45
regular     visayas   0            0.5       50
regular     visayas   0.51         3.0       60
express     visayas   0            3.0       65

我希望能根据包裹重量和地址计算费用。结果应该是交易数据框。

transaction      weight      island    category       fee
1                 0.3        luzon      regular        30
2                 0.5       visayas     express        65
3                 0.5        luzon      express        45
4                 0.4       visayas     regular        50
5                 1.7       visayas     regular        60
6                 1.5        luzon      regular        40

以下是在 EXCEL 中计算 费用 的公式：

=SUMIFS(rate_card.fee, rate_card.min_weight <= transaction.weight, rate_card.max_weight >= transaction.weight, rate_card.island = transaction.island, rate_card.category = transaction.category)

我想使用Pandas在Python中复制这个特定的公式。

希望有人能够在一个月内为我的问题提供解决方案。

- lester paja

2个回答

1

注意：这不是一个推荐的解决方案（性能问题），可能更适用于创建测试以确保使用merge的更好版本按预期工作...

有以下准备工作：

import pandas as pd
from io import StringIO

transaction = pd.read_csv(StringIO("""
transaction   weight   island   category
1             0.3      luzon    regular
2             0.5      visayas  express
3             0.5      luzon    express
4             0.4      visayas  regular
5             1.7      visayas  regular
6             1.5      luzon    regular
"""), sep=r"\s+")

rate = pd.read_csv(StringIO("""
category    island  min_weight  max_weight  fee
regular     luzon     0            0.5       30
regular     luzon     0.51         3.0       40
express     luzon     0            3.0       45
regular     visayas   0            0.5       50
regular     visayas   0.51         3.0       60
express     visayas   0            3.0       65
"""), sep=r"\s+")

我们可以创建一个Pandas表达式来计算第一笔交易的费率总和：

rate[(rate.min_weight <= 0.3) &
     (rate.max_weight >= 0.3) &
     (rate.island=="luzon") &
     (rate.category=="regular")].fee.sum()

使用该表达式在列表推导式中为每一行进行操作，然后像这样将其赋值给一个新列：

transaction["fee"] = [
    rate[(rate.min_weight <= t.weight) &
         (rate.max_weight >= t.weight) &
         (rate.island == t.island) &
         (rate.category == t.category)].fee.sum()
    for t in transaction.itertuples()
]

- Aprillion

嗨，我尝试了给定的解决方案，它起作用了！非常感谢！我可以知道这个特定的行是做什么的吗：for _, t in transaction.iterrows() - lester paja

df.iterrows() 返回一个二元组 (index, data) 的可迭代对象，我使用序列解包将这个二元组分配给两个变量 _, t，其中第一个变量只是一个下划线，用作一个不会被使用的变量名的约定。 - Aprillion

阅读上述文档后，我简化了我的代码，使用了 df.itertuples()。 - Aprillion

1

这个答案是可行的，但是在pandas dataframe上进行迭代是应该避免的事情之一，因为我们在pandas和numpy中有向量化的解决方案。这里有一个很好的阅读材料。 - Erfan

网页内容由stack overflow 提供, 点击上面的

可以查看英文原文，
原文链接

- Erfan · Accepted Answer

这是在category和island上进行的merge，然后从transaction数据框中query出weight在min weight和max weight之间的内容：

new = transaction.merge(rate_card, on=['category', 'island'])\
                 .query('weight.between(`min weight`, `max weight`)')\
                 .sort_values('transaction')\
                 .drop(['min weight', 'max weight'], axis=1)

如果你的 pandas < 0.25.0 版本还不支持在 query 中使用反引号列选择，那么可以使用以下方式：

new = transaction.merge(rate_card, on=['category', 'island'])

new = new.loc[new['weight'].between(new['min weight'], new['max weight'])]\
         .sort_values('transaction')\
         .drop(['min weight', 'max weight'], axis=1)

输出

   transaction  weight   island category  fee
0            1     0.3    luzon  regular   30
4            2     0.5  visayas  express   65
5            3     0.5    luzon  express   45
6            4     0.4  visayas  regular   50
9            5     1.7  visayas  regular   60
3            6     1.5    luzon  regular   40

细节：第一次合并给我们以下结果：

transaction.merge(rate_card, on=['category', 'island'])

   transaction  weight   island category  min weight  max weight  fee
0            1     0.3    luzon  regular        0.00         0.5   30
1            1     0.3    luzon  regular        0.51         3.0   40
2            6     1.5    luzon  regular        0.00         0.5   30
3            6     1.5    luzon  regular        0.51         3.0   40
4            2     0.5  visayas  express        0.00         3.0   65
5            3     0.5    luzon  express        0.00         3.0   45
6            4     0.4  visayas  regular        0.00         0.5   50
7            4     0.4  visayas  regular        0.51         3.0   60
8            5     1.7  visayas  regular        0.00         0.5   50
9            5     1.7  visayas  regular        0.51         3.0   60

然后，我们筛选所有满足 weight = 在最小重量和最大重量之间 的行：

new = transaction.merge(rate_card, on=['category', 'island'])\
                 .query('weight.between(`min weight`, `max weight`)')

   transaction  weight   island category  min weight  max weight  fee
0            1     0.3    luzon  regular        0.00         0.5   30
3            6     1.5    luzon  regular        0.51         3.0   40
4            2     0.5  visayas  express        0.00         3.0   65
5            3     0.5    luzon  express        0.00         3.0   45
6            4     0.4  visayas  regular        0.00         0.5   50
9            5     1.7  visayas  regular        0.51         3.0   60

最后两个步骤是正确排序和删除不必要的列。