Python基于地理位置的商品打包算法

import pandas as pd
import numpy as np
import math

# 初始化结果列表
output_results = []

for jishu in range(1, 33):
    # 读取商品信息
    goods_df = pd.read_excel('D:\pythonProject3\商品信息\商品打包.xlsx')
    goods_df = goods_df[['新商品GPS纬度', '新商品GPS经度', '商品编号', '打包数量']]
    goods_df = goods_df.dropna()

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

    # 初始化会员挑选结果
    results = []

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

        # 初始化会员挑选的商品数量和商品编号
        selected_quantity = 0
        selected_goods = []

        # 遍历商品列表
        for index, good in goods_df.iterrows():
            good_id = good['商品编号']
            good_lat = good['新商品GPS纬度']
            good_lon = good['新商品GPS经度']
            good_quantity = good['打包数量']

            # 计算会员和商品之间的距离
            distance = math.sqrt((member_lat - good_lat) ** 2 + (member_lon - good_lon) ** 2)

            # 如果距离小于等于0.1并且商品数量加上已选商品数量不超过会员限额
            if distance <= 0.1 and selected_quantity + good_quantity <= member_limit:
                # 更新已选商品数量和商品编号
                selected_quantity += good_quantity
                selected_goods.append(good_id)

        # 添加会员挑选结果到结果列表
        results.append({'会员编号': member_id, '预订商品限额': member_limit, '挑选商品数量': selected_quantity, '挑选商品编号': selected_goods})

    # 输出结果
    for result in results:
        output_results.append(result)

# 将结果转换为DataFrame
output_df = pd.DataFrame(output_results)

# 将结果写入Excel文件的同一列
output_path = r'D:\pythonProject3\打包问题\结果.xlsx'
with pd.ExcelWriter(output_path, mode='a', engine='openpyxl') as writer:
    output_df.to_excel(writer, sheet_name='会员信息', index=False, startrow=1, header=False)