Python实现基于距离的商品打包分配算法

import pandas as pd
import math

# 读取商品信息
product_df = pd.read_excel('D:\\pythonProject3\\商品信息\\商品打包.xlsx')
product_df = product_df[['商品编号', '新商品GPS纬度', '新商品GPS经度', '打包数量']]

# 读取会员信息
member_df = pd.read_excel('D:\\pythonProject3\\打包问题\\会员信息.xlsx')
member_df = member_df[['会员编号', '会员GPS纬度', '会员GPS经度', '预订商品限额']]

# 初始化结果数据框
result_df = pd.DataFrame(columns=['商品编号', '会员编号', '打包数量'])

# 遍历会员
for index, member in member_df.iterrows():
    member_id = member['会员编号']
    member_lat = member['会员GPS纬度']
    member_lng = member['会员GPS经度']
    member_limit = member['预订商品限额']
    
    # 计算会员与商品的距离
    product_df['距离'] = product_df.apply(lambda row: math.sqrt((row['新商品GPS纬度'] - member_lat)**2 + (row['新商品GPS经度'] - member_lng)**2), axis=1)
    
    # 按距离进行排序
    product_df = product_df.sort_values(by='距离')
    
    # 筛选距离小于0.1的商品
    available_products = product_df[product_df['距离'] <= 0.1]
    
    # 初始化会员已选商品数量和已选商品编号列表
    selected_quantity = 0
    selected_product_ids = []
    
    # 遍历商品
    for index, product in available_products.iterrows():
        product_id = product['商品编号']
        product_quantity = product['打包数量']
        
        # 判断商品是否已被选
        if product_id in selected_product_ids:
            continue
        
        # 判断商品是否超过限额
        if selected_quantity + product_quantity > member_limit:
            continue
        
        # 更新已选商品数量和已选商品编号列表
        selected_quantity += product_quantity
        selected_product_ids.append(product_id)
        
        # 添加结果到结果数据框
        result_df = result_df.append({'商品编号': product_id, '会员编号': member_id, '打包数量': product_quantity}, ignore_index=True)
        
        # 判断已选商品数量是否达到限额
        if selected_quantity == member_limit:
            break
    
    # 从可用商品列表中移除已选商品
    product_df = product_df[~product_df['商品编号'].isin(selected_product_ids)]
    
    # 输出每个会员挑选到商品的数量和会员的预订商品限额
    print(f'会员编号: {member_id}, 挑选到的商品数量: {selected_quantity}, 预订商品限额: {member_limit}')

# 输出每个商品被分配到哪个会员
product_group = result_df.groupby('商品编号')
for product_id, group in product_group:
    members = group['会员编号'].unique()
    print(f'商品编号: {product_id}, 分配给会员: {members}')

# 输出商品数量为挑选的商品的对应的'打包数量'之和
product_quantity_group = result_df.groupby('商品编号')['打包数量'].sum()
for product_id, quantity in product_quantity_group.items():
    print(f'商品编号: {product_id}, 商品数量: {quantity}')