如何基于承运商费率在Python中实现货运成本自动计算？

我有一个记录商品信息的Pandas DataFrame，包含Region（区域）、UF（州）、Weight（重量）、Price of the Product（商品价格）列，每行对应一件待发货商品：

import pandas as pd

df = pd.DataFrame(
    data = [('Vila Velha', 'ES', 220, 2300),
            ('Leio', 'ES',12, 100),
            ('São Paulo', 'SP',12, 200),
            ('Lauro de Freitas', 'BA',5, 400),
            ('Fortaleza', 'CE',14, 500)
            ],
    columns=['Region','UF', 'Weight','Price of the Product']
)

还有一个记录各区域可用承运商的DataFrame（0代表无对应承运商）：

RegionT = ["Vila Velha","Leio","São Paulo","Lauro de Freitas","Fortaleza"]
TrUF = ['ES','ES','SP','BA','CE']
Tr1 = ['Evidência','Termaco','Termaco','Evidência', '0']
Tr2 = ['0','Leite Express','Leite Express','0', '0']
Tr3 = ['Sudoeste','0','0','0', '0']
Tr4 = ['0','0','0','0','0']

Transportadoras = pd.DataFrame(
    data = zip(RegionT,TrUF,Tr1,Tr2,Tr3,Tr4),
    columns=["Region","UF","Transp. 1","Transp. 2","Transp. 3","Transp. 4"]
)

另外有各承运商的费率表，以Sudoeste为例：

# Sudoeste 费率表
Location = ["Capital","Interior"]
UF = ['ES','RJ']
Minimum_shipping_up_to_10kg = [47.40,48.34]
MinShipping_bettwen_10_to20 = [57.40, 58.34]
MinShipping_bettwen_20_to30 = [67.40, 68.34]
Shipping_after_50kg = [0.710, 0.22]
Toll = [2.83,2.83]
GRIS = [0.12,0.12]
Tax = [3.36,8.52]

Sudoeste = pd.DataFrame(
    data = zip(Location,UF,Minimum_shipping_up_to_10kg,MinShipping_bettwen_10_to20,MinShipping_bettwen_20_to30,Shipping_after_50kg,Toll,GRIS,Tax),
    columns=["Location","UF","Minimun Shipping to 10","Bettwen 10 to 20","Bettwen 20 to 30","After 50","Toll","GRIS","Tax"]
)

以df第一行商品为例，重量220kg、价格2300，按Sudoeste费率计算货运成本的公式如下：

• 30kg以内最低运费：67.40
• 超出50kg部分运费：(220-30)*0.710
• 过路费：ROUND(220/100, 0)*2.83（对应Excel的ROUND函数）
• GRIS（按商品价格百分比）：2300*0.12%
• 税费：3.36
• 总成本：67.40 + (220-30)*0.710 + 2300*0.12% + ROUND(220/100,0)*2.83 +3.36 =214.08

需要实现：为每个商品的可用承运商分别计算货运成本，新增列展示结果。

步骤1：数据预处理与合并 #

先把商品表和承运商表按Region和UF合并，提取每个商品的可用承运商列表：

# 合并商品表和承运商表
merged_df = pd.merge(df, Transportadoras, on=['Region', 'UF'], how='left')

# 提取每个商品的可用承运商（排除标记为0的项）
merged_df['Available_Carriers'] = merged_df[['Transp. 1', 'Transp. 2', 'Transp. 3', 'Transp. 4']].apply(
    lambda x: [carrier for carrier in x if carrier != '0'], axis=1
)

步骤2：定义承运商成本计算函数 #

先实现Sudoeste的成本计算逻辑，后续可按相同模式扩展其他承运商：

def calculate_sudoeste_cost(row):
    # 获取对应UF的费率数据（默认取该UF的第一条费率记录，可根据实际区域调整）
    rate = Sudoeste[Sudoeste['UF'] == row['UF']].iloc[0]
    weight = row['Weight']
    price = row['Price of the Product']
    
    # 计算30kg以内的基础运费
    if weight <= 10:
        base_shipping = rate['Minimun Shipping to 10']
    elif 10 < weight <=20:
        base_shipping = rate['Bettwen 10 to 20']
    elif 20 < weight <=30:
        base_shipping = rate['Bettwen 20 to 30']
    else:
        base_shipping = rate['Bettwen 20 to 30']
    
    # 计算超出30kg部分的运费（按示例逻辑，从30kg开始计算超出）
    extra_shipping = 0
    if weight > 50:
        extra_shipping = (weight - 30) * rate['After 50']
    
    # 过路费：按重量每100kg为单位取整计算
    toll = round(weight / 100, 0) * rate['Toll']
    
    # GRIS：商品价格乘以GRIS百分比（转换为小数）
    gris = price * (rate['GRIS'] / 100)
    
    # 税费
    tax = rate['Tax']
    
    # 总成本
    total_cost = base_shipping + extra_shipping + toll + gris + tax
    return round(total_cost, 2)

# 其他承运商（如Termaco、Evidência）可参照上述逻辑，替换对应费率表和计算规则

步骤3：批量计算每个商品的承运商成本 #

遍历所有可能的承运商，为每个商品计算对应承运商的成本（无可用承运商则标记为None）：

# 遍历承运商列表，新增成本列
for carrier in ['Sudoeste', 'Termaco', 'Evidência', 'Leite Express']:
    merged_df[f'{carrier}_Cost'] = merged_df.apply(
        lambda row: calculate_sudoeste_cost(row) if carrier in row['Available_Carriers'] else None,
        axis=1
    )

# 查看核心结果
print(merged_df[['Region', 'UF', 'Weight', 'Price of the Product', 'Sudoeste_Cost', 'Termaco_Cost', 'Evidência_Cost', 'Leite Express_Cost']])

步骤4：（可选）整理结果 #

过滤掉全为None的无意义列，得到最终结果：

final_df = merged_df.dropna(axis=1, how='all')

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