Python Pandas：基于经纬度和信誉值分配商品

import pandas as pd import math

for jishu in range(1, 32): # 加载商品信息表和会员信息表 product_info = pd.read_excel('D:\pythonProject3\商品信息\商品打包.xlsx') member_info = pd.read_excel('D:\pythonProject3\打包问题\会员信息.xlsx', sheet_name=f'会员信息{jishu}')

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

# 初始化会员挑选到商品的数量
member_info['挑选商品数量'] = 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
                # 将商品的数量减去已分配的数量
                product_info.at[j, '打包数量'] -= quantity
            else:
                break

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

# 将剩余的商品分配给信誉度高且未满限额的会员
remaining_quantity = product_info['打包数量'].sum()
while remaining_quantity > 0:
    # 获取信誉度最高且未满限额的会员
    selected_member = member_info[(member_info['挑选商品数量'] < member_info['预订商品限额']) & (member_info['信誉值'] == member_info['信誉值'].max())].iloc[0]
    selected_member_index = selected_member.name

    # 获取会员的GPS经纬度和预订商品限额
    member_latitude = selected_member['会员GPS纬度']
    member_longitude = selected_member['会员GPS经度']
    limit = selected_member['预订商品限额']

    # 遍历剩余的商品
    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
                # 将商品的数量减去已分配的数量
                product_info.at[j, '打包数量'] -= quantity
                remaining_quantity -= quantity
            else:
                break

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

# 输出每个会员挑选到商品的数量
print(member_info['挑选商品数量'])
# member_info.to_excel("D:\pythonProject3\打包问题\会员信息 - 副本.xlsx", index=False, columns=['会员编号', '会员GPS纬度', '会员GPS经度', '信誉值', '预订商品比例', '挑选商品数量'])
outfile = r'D:\pythonProject3\打包问题\会员信息 - 副本.xlsx'
with pd.ExcelWriter(outfile, mode='a', engine='openpyxl') as writer:
    member_info.to_excel(writer, columns=['会员编号', '会员GPS纬度', '会员GPS经度', '信誉值', '预订商品比例',
                                          '挑选商品数量'], sheet_name=f'会员信息{jishu}', index=False)