Python_仓库使用货拉拉物流运费计算2

在上一篇  《Python_仓库使用货拉拉物流运费计算1   》的基础上更细一步计算运费

根据货拉拉的收费标准进行阶梯计算

根据物料的长宽高、重量去匹配车型

import pandas as pd

# 读取数据
df_111 = pd.read_excel('订单明细.xlsx')
df_highway_fee = pd.read_excel(广州到全国城市距离及高速费.xlsx')

# 物料表（示例）
data_material = [
    {'物料': 'A', '长/m': 0.365, '宽/m': 0.305, '高/m': 0.15, '毛重/t': 0.00009},
    {'物料': 'B', '长/m': 0.367, '宽/m': 0.25, '高/m': 0.16, '毛重/t': 0.01064}
]
df_material = pd.DataFrame(data_material)

# 车型载重和载方范围信息以及阶梯价格和起步公里数
data_fee = [
    {'车型': '中面', '载重下限': 0, '载重上限': 1.2, '载方下限': 0, '载方上限': 6.1, '车长': 3.2, '车宽': 1.6, '起步价': 73.66, '起步公里数': 7, '价格区间': [(99999, 5.08)]},
    {'车型': '17米5', '载重下限': 0, '载重上限': 35.0, '载方下限': 0, '载方上限': 137.2, '车长': 17.5, '车宽': 2.8, '起步价': 994.5, '起步公里数': 15, '价格区间': [(15, 0), (30, 15.56), (50, 18.72), (80, 0.53), (200, 9.95), (300, 8.78), (400, 8.19), (2000, 7.61), (99999, 6.44)]}
]
df_fee = pd.DataFrame(data_fee)

# 路费阶梯算法（根据车型单独配置的起步公里数）
def calculate_lala_fee(start_price, price_steps, distance, start_km):
    if pd.isnull(distance):
        return None
    fee = start_price
    remaining_distance = max(distance - start_km, 0)  # 超出各车型起步公里数的部分
    for step_limit, step_price in price_steps:
        if remaining_distance <= 0:
            break
        step_distance = min(remaining_distance, step_limit)
        fee += step_distance * step_price
        remaining_distance -= step_distance
    return fee

# 合并数据
merged_df = df_111.merge(df_material, left_on='产品代码', right_on='物料简称', how='left')
merged_df = merged_df.rename(columns={'毛重/t': '毛重(吨)'})
merged_df['总毛重(吨)'] = merged_df['毛重(吨)'] * merged_df['实发数量']

# 汇总每单总体积、总重量和产品明细

# 汇总每单的产品明细
def get_product_summary(x):
    return '+'.join(x['产品代码'] + '*' + x['实发数量'].astype(str))

product_summary = merged_df.groupby('单据编号').apply(get_product_summary).reset_index(name='产品明细')

# 汇总每单总体积和总重量
order_summary = merged_df.groupby('单据编号').agg({
    '发货单位（方or吨）': 'sum',
    '总毛重(吨)': 'sum',
    '到货省': 'first',
    '到货市': 'first'
}).reset_index().rename(columns={'发货单位（方or吨）': '总体积(方)', '总毛重(吨)': '总重量(吨)'})

# 合并产品明细
order_summary = order_summary.merge(product_summary, on='单据编号', how='left').rename(columns={'发货单位（方or吨）': '总体积(方)', '总毛重(吨)': '总重量(吨)'})

# 匹配车型
def match_vehicle(row):
    remain_weight = row['总重量(吨)']
    remain_volume = row['总体积(方)']
    vehicle_plan = []

    while remain_weight > 0 or remain_volume > 0:
        suitable = df_fee[(df_fee['载重上限'] >= remain_weight) & (df_fee['载方上限'] >= remain_volume)].sort_values(by='起步价')
        if suitable.empty:
            suitable = df_fee.sort_values(by='起步价', ascending=False).head(1)
        vehicle = suitable.iloc[0]
        vehicle_plan.append(vehicle['车型'] + '*1')
        remain_weight -= vehicle['载重上限']
        remain_volume -= vehicle['载方上限']

    return '+'.join(vehicle_plan)

order_summary['车型'] = order_summary.apply(match_vehicle, axis=1)

# 计算公里数和高速费
def get_distance_and_fee(row):
    match = df_highway_fee[(df_highway_fee['省'] == row['到货省']) & (df_highway_fee['地级市'] == row['到货市'])]
    if not match.empty:
        return match.iloc[0]['距离（公里）'], match.iloc[0]['高速费（元）']
    return None, None

order_summary[['公里数', '高速费']] = order_summary.apply(get_distance_and_fee, axis=1, result_type='expand')

# 计算路费和总费用
def calc_fee(row):
    if pd.isnull(row['车型']):
        return pd.Series([None, None])

    total_fee = 0
    plan = row['车型'].split('+')

    for v in plan:
        if '*' not in v:
            continue
        car, num = v.split('*')
        num = int(num)
        vehicle = df_fee[df_fee['车型'] == car]
        if vehicle.empty:
            continue
        vehicle = vehicle.iloc[0]
        fee = calculate_lala_fee(vehicle['起步价'], vehicle['价格区间'], row['公里数'], vehicle['起步公里数'])
        if fee is None:
            fee = 0
        total_fee += (fee * num)

    total_fee += row['高速费'] + total_fee * 0.4 if pd.notnull(total_fee) else None

    return pd.Series([total_fee / (1 + 0.4), total_fee])

order_summary[['路费', '总费用']] = order_summary.apply(calc_fee, axis=1)


# 导出结果
order_summary.to_excel('订单费用计算结果.xlsx', index=False)