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['预订商品限额'] selected_products = df_product[df_product['已分配数量'] > 0]['商品编号'].tolist() print(f'会员编号：{member_id}，已分配数量：{allocated_quantity}，预订商品限额：{member_limit}，挑选到的商品编号：{selected_products}')