import pulp
import pandas as pd
# --------------------------
# 1. 数据准备(修正读取文件部分)
# --------------------------
# 读取 Excel 文件
excel_file = pd.ExcelFile("D:\\2024年建模问题\\CUMCM2024Problems\\C题\\4.xlsx")
# 获取指定工作表中的数据
crops = excel_file.parse('Sheet1')
crops = crops[["作物编号", "作物名称", "作物类型", "种植成本/(元/亩)", "单价", "亩产量/斤"]]
crops = crops.rename(columns={
"作物编号": "id",
"作物名称": "name",
"作物类型": "type",
"种植成本/(元/亩)": "cost",
"单价": "price",
"亩产量/斤": "yield_per_acre"
})
lands_data = excel_file.parse('Sheet2')
if set(["id", "type", "area"]).issubset(lands_data.columns):
lands = lands_data[["id", "type", "area"]]
else:
# 尝试使用中文列名
lands_data = lands_data.rename(columns={
"地块编号": "id",
"地块类型": "type",
"地块面积/亩": "area"
})
lands = lands_data[["id", "type", "area"]]
expected_sales_data = excel_file.parse('Sheet3')
expected_sales = dict(zip(expected_sales_data.iloc[:, 0], expected_sales_data.iloc[:, 1]))
# 创建作物类型字典以便快速查找
crop_type_dict = crops.set_index('id')['type'].to_dict()
# --------------------------
# 2. 变量定义(新增0-1变量解决相乘问题)
# --------------------------
model = pulp.LpProblem("Crop_Profit", pulp.LpMaximize)
land_ids = lands["id"].tolist()
crop_ids = crops["id"].tolist()
seasons = [1, 2] # 两个季节
# 核心变量:种植面积
x = pulp.LpVariable.dicts("PlantArea",
((i, j, k) for i in land_ids for j in crop_ids for k in seasons),
lowBound=0, cat=pulp.LpContinuous)
# 辅助变量:销售量和超销量
s = pulp.LpVariable.dicts("ActualSales",
((j, k) for j in crop_ids for k in seasons),
lowBound=0, cat=pulp.LpContinuous)
e = pulp.LpVariable.dicts("ExcessSales",
((j, k) for j in crop_ids for k in seasons),
lowBound=0, cat=pulp.LpContinuous)
# 0-1变量(解决变量相乘问题)
y1 = pulp.LpVariable.dicts("Y1", (land_ids, crop_ids), cat=pulp.LpBinary) # 粮食跨年不重茬
y2 = pulp.LpVariable.dicts("Y2", (land_ids, crop_ids), cat=pulp.LpBinary) # 蔬菜同季不重茬
y3 = pulp.LpVariable.dicts("Y3", (land_ids, crop_ids), cat=pulp.LpBinary) # 蔬菜跨年季不重茬
y4 = pulp.LpVariable.dicts("Y4", land_ids, cat=pulp.LpBinary) # 水稻与蔬菜不共存
# 大M常数(大于变量可能的最大值)
M = 10000 # 地块最大面积100亩,取足够大的值
# --------------------------
# 3. 目标函数
# --------------------------
# 总成本
total_cost = pulp.lpSum(
x[i, j, k] * crops.loc[crops["id"] == j, "cost"].values[0]
for i in land_ids for j in crop_ids for k in seasons
)
# 销售收入(通过s实现)
total_revenue = pulp.lpSum(
s[j, k] * crops.loc[crops["id"] == j, "price"].values[0]
for j in crop_ids for k in seasons
)
# 超销量收入(通过e实现)
excess_revenue = pulp.lpSum(
e[j, k] * crops.loc[crops["id"] == j, "price"].values[0] * 0.5
for j in crop_ids for k in seasons
)
model += total_revenue + excess_revenue - total_cost
# --------------------------
# 4. 约束条件(修正语法错误和逻辑问题)
# --------------------------
# 约束1:地块总面积限制
for i in land_ids:
land_area = lands.loc[lands["id"] == i, "area"].values[0]
model += pulp.lpSum(x[i, j, k] for j in crop_ids for k in seasons) <= land_area, \
f"Constraint1_Land_{i}"
# 约束2:单作物最小种植面积(≥地块1/2,若种植)
for i in land_ids:
land_area = lands.loc[lands["id"] == i, "area"].values[0]
for j in crop_ids:
for k in seasons:
# 使用大M法线性化
model += x[i, j, k] >= 0.5 * land_area - M * (1 - y2[i, j]), \
f"Constraint2_MinArea_{i}_{j}_{k}"
model += x[i, j, k] <= M * y2[i, j], \
f"Constraint2_MinArea_UB_{i}_{j}_{k}"
# 约束3:D类地块仅种1种作物
D_lands = lands.loc[lands["type"] == "D", "id"].tolist()
for i in D_lands:
# 使用指示变量求和
model += pulp.lpSum(y2[i, j] for j in crop_ids for k in seasons) <= 1, \
f"Constraint3_D_Single_{i}"
# 约束4a:粮食跨年不重茬(用y1替代x*y)
G_rice = [j for j in crop_ids if "G" in crop_type_dict[j] or j == 16] # 16是水稻
for i in land_ids:
for j in G_rice:
# 今年种植面积
current_year = pulp.lpSum(x[i, j, k] for k in seasons)
# 使用大M法线性化
model += current_year <= M * (1 - y1[i, j])
model += current_year >= 0.1 - M * y1[i, j] # 0.1是阈值,表示种植
# 约束4b:蔬菜同季不重茬(用y2替代x1*x2)
R_H = [j for j in crop_ids if "R" in crop_type_dict[j] or "H" in crop_type_dict[j]]
for i in land_ids:
for j in R_H:
# 若第一季种(x1>0),则第二季不能种
model += x[i, j, 2] <= M * (1 - y2[i, j])
model += x[i, j, 1] >= 0.1 - M * (1 - y2[i, j])
# 约束4c:蔬菜跨年季不重茬(用y3替代x2*x_next)
R_crops = [j for j in crop_ids if "R" in crop_type_dict[j]]
for i in land_ids:
for j in R_crops:
# 今年第二季种植面积
season2_area = x[i, j, 2]
# 使用大M法线性化
model += season2_area <= M * (1 - y3[i, j])
model += season2_area >= 0.1 - M * y3[i, j]
# 约束5:每块地必种豆类(3年至少1次)
M_crops = [j for j in crop_ids if "M" in crop_type_dict[j]]
for i in land_ids:
model += pulp.lpSum(x[i, j, k] for j in M_crops for k in seasons) >= 0.1, \
f"Constraint5_Bean_{i}"
# 约束6:G类粮食仅种在D类地块
non_D = [i for i in land_ids if lands.loc[lands["id"] == i, "type"].values[0] != "D"]
G_crops = [j for j in crop_ids if "G" in crop_type_dict[j]]
for i in non_D:
for j in G_crops:
for k in seasons:
model += x[i, j, k] == 0, f"Constraint6_G_OnlyD_{i}_{j}_{k}"
# 约束7:D类地块仅种G类粮食
for i in D_lands:
non_G = [j for j in crop_ids if j not in G_crops]
for j in non_G:
for k in seasons:
model += x[i, j, k] == 0, f"Constraint7_D_OnlyG_{i}_{j}_{k}"
# 约束8:水稻仅种在L类地块
non_L = [i for i in land_ids if lands.loc[lands["id"] == i, "type"].values[0] != "L"]
for i in non_L:
for k in seasons:
model += x[i, 16, k] == 0, f"Constraint8_Rice_OnlyL_{i}_{k}" # 水稻编号=16
# 约束9:水稻与蔬菜不共存(用y4替代rice_area*veg_area)
L_lands = lands.loc[lands["type"] == "L", "id"].tolist()
for i in L_lands:
rice_area = pulp.lpSum(x[i, 16, k] for k in seasons) # 水稻面积
veg_area = pulp.lpSum(x[i, j, k] for j in R_H for k in seasons) # 蔬菜面积
# 使用大M法线性化
model += rice_area <= M * (1 - y4[i])
model += veg_area <= M * y4[i]
# 约束10:L类地块蔬菜种植规则
H_crops = [j for j in crop_ids if "H" in crop_type_dict[j]]
for i in L_lands:
# 10a:第一季不种H类
model += pulp.lpSum(x[i, j, 1] for j in H_crops) == 0, f"Constraint10a_L_S1_NoH_{i}"
# 10b:第二季不种R类
R_crops = [j for j in crop_ids if "R" in crop_type_dict[j]]
model += pulp.lpSum(x[i, j, 2] for j in R_crops) == 0, f"Constraint10b_L_S2_NoR_{i}"
# 10c:H类仅种1种
model += pulp.lpSum(y2[i, j] for j in H_crops) <= 1, f"Constraint10c_H_Only1_{i}"
# 约束11:H类仅在L类第二季种植
non_L = [i for i in land_ids if i not in L_lands]
for i in non_L:
for j in H_crops:
for k in seasons:
model += x[i, j, k] == 0, f"Constraint11a_H_OnlyL_{i}_{j}_{k}"
for i in L_lands:
for j in H_crops:
model += x[i, j, 1] == 0, f"Constraint11b_H_OnlyS2_{i}_{j}"
# 约束12:N类地块种植规则
N_lands = lands.loc[lands["type"] == "N", "id"].tolist()
F_crops = [j for j in crop_ids if "F" in crop_type_dict[j]]
for i in N_lands:
# 12a:第一季不种H/F
model += pulp.lpSum(x[i, j, 1] for j in H_crops + F_crops) == 0, f"Constraint12a_N_S1_NoHF_{i}"
# 12b:第二季不种R/H
model += pulp.lpSum(x[i, j, 2] for j in R_crops + H_crops) == 0, f"Constraint12b_N_S2_NoRH_{i}"
# 约束13:F类仅在N类第二季种植
non_N = [i for i in land_ids if i not in N_lands]
for i in non_N:
for j in F_crops:
for k in seasons:
model += x[i, j, k] == 0, f"Constraint13a_F_OnlyN_{i}_{j}_{k}"
for i in N_lands:
for j in F_crops:
model += x[i, j, 1] == 0, f"Constraint13b_F_S1_No_{i}_{j}"
# 约束14:Z类地块规则
Z_lands = lands.loc[lands["type"] == "Z", "id"].tolist()
for i in Z_lands:
# 14a:禁种H类
for j in H_crops:
for k in seasons:
model += x[i, j, k] == 0, f"Constraint14a_Z_NoH_{i}_{j}_{k}"
# 14b:仅种R类和粮食
allowed_crops = R_crops + G_rice
for j in crop_ids:
if j not in allowed_crops:
for k in seasons:
model += x[i, j, k] == 0, f"Constraint14b_Z_OnlyR_{i}_{j}_{k}"
# 添加销售量和超销量约束
for j in crop_ids:
for k in seasons:
# 实际产量
actual_yield = pulp.lpSum(
x[i, j, k] * crops.loc[crops["id"] == j, "yield_per_acre"].values[0]
for i in land_ids
)
# 实际销售量不超过预期销量
model += s[j, k] <= expected_sales[j]
# 实际销售量不超过实际产量
model += s[j, k] <= actual_yield
# 超销量计算
model += e[j, k] >= actual_yield - s[j, k]
model += e[j, k] <= actual_yield
# --------------------------
# 5. 求解与输出
# --------------------------
# 使用更强大的求解器(如果可用)
solver = pulp.getSolver('PULP_CBC_CMD', timeLimit=600, msg=True) # 10分钟超时
model.solve(solver)
print(f"求解状态:{pulp.LpStatus[model.status]}")
if model.status == pulp.LpStatusOptimal:
print(f"最大总利润:{pulp.value(model.objective):.2f}元")
# 输出种植方案
print("\n最优种植方案(面积单位:亩):")
for i in land_ids:
print(f"\n地块 {i} ({lands.loc[lands['id'] == i, 'type'].values[0]}类):")
for k in seasons:
season_crops = []
for j in crop_ids:
if x[i, j, k].varValue > 1e-6:
crop_name = crops.loc[crops["id"] == j, "name"].values[0]
season_crops.append(f"{crop_name}: {x[i, j, k].varValue:.2f}亩")
if season_crops:
print(f" 第{k}季: {', '.join(season_crops)}")
# 输出销售情况
print("\n销售情况:")
for j in crop_ids:
crop_name = crops.loc[crops["id"] == j, "name"].values[0]
for k in seasons:
if s[j, k].varValue > 1e-6 or e[j, k].varValue > 1e-6:
print(f"{crop_name} 第{k}季: 正常销售 {s[j, k].varValue:.2f}斤, 超量销售 {e[j, k].varValue:.2f}斤")
else:
print("未找到最优解,请检查模型和约束条件")
不行啊
最新发布