import pandas as pd import numpy as np from scipy.optimize import minimize

读取Excel文件

data = pd.read_excel(r'D:\pythonProject3\会员信息\附件二：会员信息数据.xlsx')

计算距离函数

def calculate_distance(lat1, lon1, lat2, lon2): return np.sqrt((lat1 - lat2)**2 + (lon1 - lon2)**2)

筛选满足条件的商品价格

def filter_prices(q1, q2, r): if r < 0.1: y = (0.17074800590158568 * q1 * (q26.0973538046874465)) / (r(-7.64598152989214)) if y > 0.0002065832682235432: return q1 return None

获取会员周围商品的数量并排序

def get_sorted_product_counts(member_lat, member_lon): product_counts = {} for index, row in data.iterrows(): product_lat = row['商品GPS纬度'] product_lon = row['商品GPS经度'] distance = calculate_distance(member_lat, member_lon, product_lat, product_lon) if distance < 0.1: if row['商品名称'] not in product_counts: product_counts[row['商品名称']] = 0 product_counts[row['商品名称']] += 1 sorted_product_counts = sorted(product_counts.items(), key=lambda x: x[1]) return sorted_product_counts

优化计算最小价格

def optimize_price(member_lat, member_lon, q2, sorted_product_counts): prices = {} for product, count in sorted_product_counts: q1_initial = 0.1 r = calculate_distance(member_lat, member_lon, data.loc[data['商品名称'] == product, '商品GPS纬度'].values[0], data.loc[data['商品名称'] == product, '商品GPS经度'].values[0]) result = minimize(lambda q1: -filter_prices(q1[0], q2, r), q1_initial, bounds=[(0, None)]) prices[product] = result.x[0] return prices

获取会员位置信息

member_lat = data['会员GPS纬度'].values[0] member_lon = data['会员GPS经度'].values[0]

获取预订任务限额

q2 = data['预订任务限额'].values[0]

获取会员周围商品的数量并排序

sorted_product_counts = get_sorted_product_counts(member_lat, member_lon)

优化计算最小价格

prices = optimize_price(member_lat, member_lon, q2, sorted_product_counts)

输出每个商品及其对应价格

for product, price in prices.items(): print(f"商品：{product}，价格：{price}")