基于Python Pandas的商品分配算法优化

python/nimport pandas as pd/nimport math/n/nfor jishu in range(1, 32):/n # 加载商品信息表和会员信息表/n product_info = pd.read_excel('D://pythonProject3//商品信息//商品打包.xlsx')/n member_info = pd.read_excel('D://pythonProject3//打包问题//会员信息.xlsx', sheet_name=f'会员信息{jishu}')/n/n # 对商品信息表按照经纬度排序/n product_info.sort_values(by=['新商品GPS经度', '新商品GPS纬度'], inplace=True)/n/n # 初始化会员挑选到商品的数量/n member_info['挑选商品数量'] = 0/n/n # 遍历每个会员/n for i in range(len(member_info)):/n member = member_info.iloc[i]/n/n # 获取会员的GPS经纬度/n member_latitude = member['会员GPS纬度']/n member_longitude = member['会员GPS经度']/n/n # 获取会员的预订商品限额/n limit = member['预订商品限额']/n/n # 初始化商品数量和总距离/n selected_quantity = 0/n total_distance = 0/n/n # 遍历每个商品/n for j in range(len(product_info)):/n product = product_info.iloc[j]/n/n # 获取商品的GPS经纬度和打包数量/n product_latitude = product['新商品GPS纬度']/n product_longitude = product['新商品GPS经度']/n quantity = product['打包数量']/n/n # 计算商品与会员之间的距离/n distance = math.sqrt(/n (member_latitude - product_latitude) ** 2 + (member_longitude - product_longitude) ** 2)/n/n # 判断商品与会员之间的距离是否满足要求/n if distance <= 1:/n # 判断挑选该商品是否会超过预订商品限额/n if selected_quantity + quantity <= limit:/n # 更新商品数量和总距离/n selected_quantity += quantity/n total_distance += distance/n else:/n break/n/n # 更新会员挑选到商品的数量/n member_info.at[i, '挑选商品数量'] = selected_quantity/n/n # 分配剩余商品给信誉度高且未满限额的会员/n remaining_quantity = product_info['打包数量'].sum() - member_info['挑选商品数量'].sum()/n while remaining_quantity > 0:/n # 按照信誉度降序、限额升序排序/n member_info.sort_values(by=['信誉值', '预订商品限额'], ascending=[False, True], inplace=True)/n /n # 找到第一个还有剩余限额的会员/n for i in range(len(member_info)):/n member = member_info.iloc[i]/n if member['挑选商品数量'] < member['预订商品限额']:/n # 计算可以分配的数量/n available_quantity = member['预订商品限额'] - member['挑选商品数量']/n / # 如果剩余数量小于等于可分配数量，则全部分配/n if remaining_quantity <= available_quantity:/n member_info.at[i, '挑选商品数量'] += remaining_quantity/n remaining_quantity = 0/n break/n # 否则分配可分配数量，并继续分配剩余数量/n else:/n member_info.at[i, '挑选商品数量'] += available_quantity/n remaining_quantity -= available_quantity/n # 如果遍历完所有会员还有剩余，则分配给信誉度最高的会员/n if remaining_quantity > 0:/n member_info.at[0, '挑选商品数量'] += remaining_quantity/n remaining_quantity = 0/n/n # 输出每个会员挑选到商品的数量/n print(member_info['挑选商品数量'])/n # member_info.to_excel('D:////pythonProject3////打包问题////会员信息 - 副本4.xlsx', index=False, columns=['会员编号', '会员GPS纬度', '会员GPS经度', '信誉值', '预订商品比例', '挑选商品数量'])/n outfile = r'D://pythonProject3//打包问题//会员信息 - 副本4.xlsx'/n with pd.ExcelWriter(outfile, mode='a', engine='openpyxl') as writer:/n member_info.to_excel(writer, columns=['会员编号', '会员GPS纬度', '会员GPS经度', '信誉值', '预订商品比例',/n '挑选商品数量'], sheet_name=f'会员信息{jishu}', index=False)/n