基于距离的商品分配算法：Python Pandas 实现

import pandas as pd
import numpy as np

# 读取商品信息
df_product = pd.read_excel('D:\pythonProject3\商品信息\商品打包.xlsx')
df_product['已分配数量'] = 0

# 读取会员信息
df_member = pd.read_excel('D:\pythonProject3\打包问题\会员信息.xlsx')
df_member['已分配数量'] = 0


# 计算距离
def calculate_distance(lat1, lon1, lat2, lon2):
    coords_1 = (lat1, lon1)
    coords_2 = (lat2, lon2)
    return geodesic(coords_1, coords_2).miles


# 遍历每个会员
for index, member in df_member.iterrows():
    member_id = member['会员编号']
    member_lat = member['会员GPS纬度']
    member_lon = member['会员GPS经度']
    member_limit = member['预订商品限额']

    # 选取距离最近的商品
    min_distance = np.inf
    selected_product = None

    for index, product in df_product.iterrows():
        product_id = product['商品编号']
        product_lat = product['新商品GPS纬度']
        product_lon = product['新商品GPS经度']
        product_quantity = product['打包数量']
        allocated_quantity = product['已分配数量']

        # 检查商品是否已被分配
        if allocated_quantity > 0:
            continue

        # 计算距离
        distance = ((product_lat - member_lat)**2+(product_lon-member_lon)**2)**0.5

        # 检查距离是否符合要求
        if distance <= 0.1 and distance < min_distance:
            min_distance = distance
            selected_product = product_id

    # 检查是否有可分配的商品
    if selected_product is not None:
        product = df_product[df_product['商品编号'] == selected_product].iloc[0]
        product_quantity = product['打包数量']
        allocated_quantity = product['已分配数量']

        # 检查商品数量是否超过会员限额
        if allocated_quantity + product_quantity <= member_limit:
            df_product.loc[df_product['商品编号'] == selected_product, '已分配数量'] += product_quantity
            df_member.loc[df_member['会员编号'] == member_id, '已分配数量'] += product_quantity
        else:
            # 选择最大化打包数量的商品组合
            remaining_limit = member_limit - allocated_quantity
            df_product_sorted = df_product[df_product['已分配数量'] == 0].sort_values('打包数量', ascending=False)
            for index, product in df_product_sorted.iterrows():
                product_id = product['商品编号']
                product_quantity = product['打包数量']
                allocated_quantity = product['已分配数量']

                if allocated_quantity > 0:
                    continue

                if allocated_quantity + product_quantity <= remaining_limit:
                    df_product.loc[df_product['商品编号'] == product_id, '已分配数量'] += product_quantity
                    df_member.loc[df_member['会员编号'] == member_id, '已分配数量'] += product_quantity
                    remaining_limit -= product_quantity
                else:
                    break

# 输出每个会员挑选到商品的数量和会员的预订商品限额
for index, member in df_member.iterrows():
    member_id = member['会员编号']
    allocated_quantity = member['已分配数量']
    member_limit = member['预订商品限额']
    print(f'会员编号：{member_id}，已分配数量：{allocated_quantity}，预订商品限额：{member_limit}')

# 输出每个商品被分到哪个会员
for index, product in df_product.iterrows():
    product_id = product['商品编号']
    allocated_quantity = product['已分配数量']
    print(f'商品编号：{product_id}，已分配数量：{allocated_quantity}')
    allocated_members = df_member[df_member['已分配数量'] >= product['打包数量']]
    for index, member in allocated_members.iterrows():
        member_id = member['会员编号']
        print(f'    分配给会员编号：{member_id}')
    print('\n')