好的，我们将基于提供的数据和需求，构建一个Python模型，以解决基站抢修任务的优先级排序、资源匹配和路径规划问题。下面是一个完整的Python模型，包括了数据读取、优先级排序、资源分配和路径规划等功能。 ### 步骤 1: 导入必要的库 ```python import pandas as pd from geopy.distance import geodesic import numpy as np from scipy.optimize import linear_sum_assignment from datetime import datetime, timedelta ``` ### 步骤 2: 加载数据 ```python # 加载驻点、站点和发电机信息 depot_df = pd.read_excel('驻点、站点、发电机信息上报-0812.xlsx', sheet_name='驻点') site_df = pd.read_excel('驻点、站点、发电机信息上报-0812.xlsx', sheet_name='站点') generator_df = pd.read_excel('驻点、站点、发电机信息上报-0812.xlsx', sheet_name='发电机') # 显示数据结构 print(depot_df.head()) print(site_df.head()) print(generator_df.head()) ``` ### 步骤 3: 计算优先级 ```python def calculate_priority(site_row): # 核心基站优先 if site_row['优先级'] == 1: return 1000 - site_row['电池续航时间'] else: return 500 - site_row['电池续航时间'] # 添加优先级列 site_df['优先级值'] = site_df.apply(calculate_priority, axis=1) ``` ### 步骤 4: 资源匹配 ```python # 过滤出满足功率要求的发电机 def filter_generators(site_row): required_power = 8 * 1.2 # 8KW + 20%冗余 return generator_df[(generator_df['功率'] >= required_power)] # 匹配发电机 site_df['可用发电机'] = site_df.apply(lambda row: filter_generators(row), axis=1) # 匹配电工班组（假设有10个班组） teams = [f'Team {i+1}' for i in range(10)] team_assignments = {team: [] for team in teams} # 分配任务给班组 for _, row in site_df.iterrows(): assigned_team = min(team_assignments.items(), key=lambda x: len(x[1]))[0] team_assignments[assigned_team].append(row['站点名称']) ``` ### 步骤 5: 路径规划 ```python def calculate_distance(coord1, coord2): return geodesic(coord1, coord2).km def optimize_route(depot, sites): # 创建距离矩阵 distances = np.zeros((len(sites) + 1, len(sites) + 1)) for i, site_i in enumerate([depot] + sites): for j, site_j in enumerate([depot] + sites): if i != j: distances[i][j] = calculate_distance( (site_i['纬度'], site_i['经度']), (site_j['纬度'], site_j['经度']) ) # 使用匈牙利算法优化路径 row_ind, col_ind = linear_sum_assignment(distances) # 返回优化后的路径顺序 return [sites[i-1] for i in col_ind[1:]] # 对每个班组进行路径优化 optimized_routes = {} for team, assignments in team_assignments.items(): if assignments: relevant_sites = site_df[site_df['站点名称'].isin(assignments)] depot = depot_df.iloc[0] # 假设所有班组从同一个驻点出发 optimized_routes[team] = optimize_route(depot, relevant_sites.to_dict('records')) ``` ### 步骤 6: 输出结果 ```python # 打印每个班组的任务列表及其路径 for team, route in optimized_routes.items(): print(f"Team: {team}") for site in route: print(f" -> {site['站点名称']} (优先级: {site['优先级']}, 距离: {site['distance_to_depot']:.2f} km)") print() # 将结果保存为Excel文件 output_df = [] for team, route in optimized_routes.items(): for site in route: output_df.append({ '班组': team, '站点名称': site['站点名称'], '优先级': site['优先级'], '距离': site.get('distance_to_depot', 'N/A') }) pd.DataFrame(output_df).to_excel('任务分配与路径规划结果.xlsx', index=False) ``` ### 知识点详解 - **加权排序算法**：用于根据多个条件（如基站重要性和电池续航时间）计算任务的优先级。 - **线性指派问题（匈牙利算法）**：用于解决分配问题，确保最小化总成本或时间。 - **地理空间计算**：使用`geopy`库计算地理坐标间的距离，辅助路径规划。 这个模型可以作为一个起点，您可以根据实际需求进一步调整和完善。如果有任何问题或需要改进的地方，请随时告诉我！