基于地理位置和信誉度的商品打包分配算法

import pandas as pd
import math

# 设置文件路径
product_info_path = 'D:\\pythonProject3\\商品信息\\商品打包.xlsx'
member_info_path = 'D:\\pythonProject3\\打包问题\\会员信息.xlsx'
output_path = 'D:\\pythonProject3\\打包问题\\会员信息 - 副本3.xlsx'

# 循环处理每个会员信息表
for jishu in range(1, 32):
    # 加载商品信息表和会员信息表
    product_info = pd.read_excel(product_info_path)
    member_info = pd.read_excel(member_info_path, sheet_name=f'会员信息{jishu}')

    # 对商品信息表按照经纬度排序
    product_info.sort_values(by=['新商品GPS经度', '新商品GPS纬度'], inplace=True)

    # 初始化会员挑选到商品的数量和完成率
    member_info['挑选商品数量'] = 0
    member_info['完成率'] = 0.0

    # 遍历每个会员
    for i in range(len(member_info)):
        member = member_info.iloc[i]

        # 获取会员的GPS经纬度
        member_latitude = member['会员GPS纬度']
        member_longitude = member['会员GPS经度']

        # 获取会员的预订商品限额
        limit = member['预订商品限额']

        # 初始化商品数量和总距离
        selected_quantity = 0
        total_distance = 0

        # 遍历每个商品
        for j in range(len(product_info)):
            product = product_info.iloc[j]

            # 获取商品的GPS经纬度和打包数量
            product_latitude = product['新商品GPS纬度']
            product_longitude = product['新商品GPS经度']
            quantity = product['打包数量']

            # 计算商品与会员之间的距离
            distance = math.sqrt((member_latitude - product_latitude) ** 2 + (member_longitude - product_longitude) ** 2)

            # 判断商品与会员之间的距离是否满足要求
            if distance <= 1:
                # 判断挑选该商品是否会超过预订商品限额
                if selected_quantity + quantity <= limit:
                    # 更新商品数量和总距离
                    selected_quantity += quantity
                    total_distance += distance
                else:
                    break

        # 更新会员挑选到商品的数量
        member_info.at[i, '挑选商品数量'] = selected_quantity

    # 分配剩余商品给信誉度高且未满限额的会员
    remaining_quantity = product_info['打包数量'].sum() - member_info['挑选商品数量'].sum()
    if remaining_quantity > 0:
        # 对会员按照信誉值从高到低排序
        member_info.sort_values(by=['信誉值'], ascending=False, inplace=True)

        # 遍历会员，分配剩余商品
        for i in range(len(member_info)):
            member = member_info.iloc[i]
            limit = member['预订商品限额']
            selected_quantity = member['挑选商品数量']

            # 判断会员是否未满限额
            if selected_quantity < limit:
                remaining_space = limit - selected_quantity

                # 判断剩余商品数量是否足够分配给该会员
                if remaining_quantity <= remaining_space:
                    member_info.at[i, '挑选商品数量'] += remaining_quantity
                    remaining_quantity = 0
                    break
                else:
                    member_info.at[i, '挑选商品数量'] += remaining_space
                    remaining_quantity -= remaining_space

    # 计算完成率
    completion_rate = member_info['挑选商品数量'].sum() / 835 * 0.84
    member_info['完成率'] = completion_rate

    # 输出每个会员挑选到商品的数量和完成率
    print(member_info[['会员编号', '挑选商品数量', '完成率']])

    # 保存结果到Excel文件
    with pd.ExcelWriter(output_path, mode='a', engine='openpyxl') as writer:
        member_info.to_excel(writer, columns=['会员编号', '会员GPS纬度', '会员GPS经度', '信誉值', '预订商品比例',
                                              '挑选商品数量', '完成率'], sheet_name=f'会员信息{jishu}', index=False)