import pandas as pd import math from scipy.optimize import minimize

Read the Excel file

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

Get the location information of goods and members

goods_longitude = df['商品GPS经度'] goods_latitude = df['商品GPS纬度'] member_longitude = df['会员GPS经度'] member_latitude = df['会员GPS纬度']

Get the task quota and goods price

task_quota = df['预订任务限额'] goods_price = df['任务标价']

Calculate the distance between goods and members

def haversine(lat1, lon1, lat2, lon2): R = 6371 # Earth radius in kilometers dlat = math.radians(lat2 - lat1) dlon = math.radians(lon2 - lon1) a = math.sin(dlat / 2) * math.sin(dlat / 2) + math.cos(math.radians(lat1)) * math.cos(math.radians(lat2)) * math.sin(dlon / 2) * math.sin(dlon / 2) c = 2 * math.atan2(math.sqrt(a), math.sqrt(1 - a)) distance = R * c return distance

Calculate the distance between goods and members

distance = [] for i in range(len(df)): dist = haversine(goods_latitude[i], goods_longitude[i], member_latitude[i], member_longitude[i]) distance.append(dist)

Define the objective function

def objective(x): k, n, m = x y = (k * goods_price * (task_quotam)) / (distancen) return sum(y)

Set the initial values

x0 = [1, 1, 1]

Minimize the objective function

result = minimize(objective, x0)

Extract the minimum attraction factor 'y' and constants 'k', 'n', and 'm'

y_min = result.fun k_min, n_min, m_min = result.x

Output the formula with constants

print(f"Formula: y = ({k_min} * q1 * (q2^{m_min})) / (r^{n_min})") print(f"Minimum attraction factor y: {y_min}")