2024 年高教社杯全国大学生数学建模竞赛 C 题 农作物的种植策略（完整代码）

第一问代码：

（1） 

import pandas as pd
import numpy as np
from scipy.optimize import linprog
import random

# 读取四个表格的数据
file_1 = '2023年的种植数据与销售量.xlsx'
file_2 = '各作物聚合后销售量与价格.xlsx'
file_3 = '各作物适合种植的地块类型与季别.xlsx'
file_4 = '乡村的现有耕地.xlsx'

# 加载表格
df1 = pd.read_excel(file_1)
df2 = pd.read_excel(file_2)
df3 = pd.read_excel(file_3)
df4 = pd.read_excel(file_4)

# 提取2023年各作物的销售量
sales_2023 = df2.set_index('作物名称')['销售量/斤'].to_dict()

# 提取地块和作物信息
land_types = df4['地块名称'].unique().tolist()

# 根据df3筛选适合第一季和第二季的作物
def get_suitable_crops(season):
    if season == '第一季':
        suitable_crops = df3[df3[f'水浇地{season}'] == 1]['作物名称'].tolist() + \
                         df3[df3[f'普通大棚{season}'] == 1]['作物名称'].tolist() + \
                         df3[df3[f'智慧大棚{season}'] == 1]['作物名称'].tolist() + \
                         df3[df3[f'平旱地'] == 1]['作物名称'].tolist() + \
                         df3[df3[f'梯田'] == 1]['作物名称'].tolist() + \
                         df3[df3[f'山坡地'] == 1]['作物名称'].tolist() + \
                         df3[df3[f'水浇地'] == 1]['作物名称'].tolist()
    else:
        suitable_crops = df3[df3[f'水浇地{season}'] == 1]['作物名称'].tolist() + \
                         df3[df3[f'普通大棚{season}'] == 1]['作物名称'].tolist() + \
                         df3[df3[f'智慧大棚{season}'] == 1]['作物名称'].tolist()
    
    return suitable_crops

suitable_crops_first_season = get_suitable_crops('第一季')
suitable_crops_second_season = get_suitable_crops('第二季')

# 过滤不在suitable_crops中的作物
crops_first_season = [crop for crop in df1['作物名称_x'].unique() if crop in suitable_crops_first_season]
crops_second_season = [crop for crop in df1['作物名称_x'].unique() if crop in suitable_crops_second_season]

# 第二季地块
land_types_second_season = ['D1', 'D2', 'D3', 'D4', 'D5', 'D6', 'D7', 'D8', 
                            'E1', 'E2', 'E3', 'E4', 'E5', 'E6', 'E7', 'E8', 'E9', 'E10', 'E11', 'E12', 'E13', 'E14', 'E15', 'E16', 
                            'F1', 'F2', 'F3', 'F4']

# 随机选择作物的数量,减少计算量
num_crops_to_select = 9

# 为第一季和第二季创建地块与作物的映射
def create_land_crop_mapping(land_types, crops):
    land_crop_mapping = {}
    for land in land_types:
        if len(crops) > num_crops_to_select:
            selected_crops = random.sample(crops, num_crops_to_select)
        else:
            selected_crops = crops  # 如果作物少于9种，全部选中
        land_crop_mapping[land] = selected_crops
    return land_crop_mapping

# 第一季
land_crop_mapping_first_season = create_land_crop_mapping(land_types, crops_first_season)

# 第二季
land_crop_mapping_second_season = create_land_crop_mapping(land_types_second_season, crops_second_season)

# 定义优化函数
def optimize_land_crop(land_crop_mapping, crops, season, year):
    # 初始化决策变量和目标函数：优化目标是最大化净收入，并最小化因超产导致的滞销损失。
    decision_vars = {}
    objective_coeffs = []

    for land, selected_crops in land_crop_mapping.items():
        for crop in selected_crops:
            decision_vars[(crop, land)] = 0

            land_type = df4[df4['地块名称'] == land]['地块类型'].values[0]
            yield_data = df1[(df1['作物名称_x'] == crop) & (df1['地块类型'] == land_type)]['亩产量/斤'].values
            price_data = df2[df2['作物名称'] == crop]['销售单价/(元/斤)'].values
            cost_data = df1[(df1['作物名称_x'] == crop) & (df1['地块类型'] == land_type)]['种植成本/(元/亩)'].values

            if len(yield_data) > 0 and len(price_data) > 0 and len(cost_data) > 0:
                yield_per_acre = yield_data[0]
                price_per_unit = price_data[0]
                cost_per_acre = cost_data[0]
                
                # 获取2023年作物销售量
                sales_2023_volume = sales_2023.get(crop, 0)
                
                # 计算实际销售收入
                net_revenue = (yield_per_acre * price_per_unit) - cost_per_acre
                
                # 计算滞销损失
                excess_volume = max(0, yield_per_acre - sales_2023_volume)
                wastage_loss = excess_volume * price_per_unit
                
                # 将净收入和滞销损失合并到目标函数中
                objective_coeffs.append(net_revenue - wastage_loss)
            else:
                objective_coeffs.append(0)

    objective_coeffs = np.array(objective_coeffs) * -1

    # 重新构建约束条件
    A_ub = []
    b_ub = []

    # 约束条件1：总面积约束
    for land in land_crop_mapping.keys():
        constraint = np.zeros(len(decision_vars))
        for i, (crop, land_name) in enumerate(decision_vars.keys()):
            if land_name == land:
                constraint[i] = 1
        A_ub.append(constraint)
        b_ub.append(df4[df4['地块名称'] == land]['地块面积/亩'].values[0])

    # 约束条件2：最小种植面积约束
    for land in land_crop_mapping.keys():
        min_area = 0.1 * df4[df4['地块名称'] == land]['地块面积/亩'].values[0]
        for crop in land_crop_mapping[land]:
            constraint = np.zeros(len(decision_vars))
            for i, (crop_name, land_name) in enumerate(decision_vars.keys()):
                if crop_name == crop and land_name == land:
                    constraint[i] = -1
            A_ub.append(constraint)
            b_ub.append(-min_area)

    # 约束条件3：三年内至少种植一次豆类作物
    beans_crops = df3[df3['作物类型'].str.contains('粮食（豆类）')]['作物名称'].tolist()
    for land in land_crop_mapping.keys():
        constraint = np.zeros(len(decision_vars))
        for i, (crop, land_name) in enumerate(decision_vars.keys()):
            if land_name == land and crop in beans_crops:
                constraint[i] = -1
        A_ub.append(constraint)
        b_ub.append(0)  # 三年内种植豆类作物的约束（具体实施时可能需要调整）

    A_ub = np.array(A_ub)
    b_ub = np.array(b_ub)

    # 优化模型
    result = linprog(c=objective_coeffs, A_ub=A_ub, b_ub=b_ub, method='highs')

    # 构建优化结果的表格输出
    if result.success:
        optimal_areas = result.x
        solution = {}
        
        for i, (crop, land) in enumerate(decision_vars.keys()):
            if land not in solution:
                solution[land] = {}
            solution[land][crop] = optimal_areas[i]

        # 确保结果表格中包含所有作物
        all_crops = sorted(set(df2['作物名称'].to_list()))  # 所有作物的集合（去重并排序）
        
        # 构建结果表格
        results = pd.DataFrame(columns=['年', '季别', '地块名'] + all_crops)
        
        for land, crop_areas in solution.items():
            season_data = {'年': year, '季别': season, '地块名': land}
            for crop in all_crops:
                season_data[crop] = crop_areas.get(crop, 0)  # 如果该作物不在该地块中，则面积为0
            
            results = results._append(season_data, ignore_index=True)

        return results
    else:
        print(f"{year}年{season}优化失败，无法生成结果表格。")
        return None

# 迭代计算 2024~2030 年的结果
years = list(range(2024, 2031))
all_results = []

for year in years:
    # 第一季
    results_first_season = optimize_land_crop(land_crop_mapping_first_season, crops_first_season, '第一季', year)
    # 第二季
    results_second_season = optimize_land_crop(land_crop_mapping_second_season, crops_second_season, '第二季', year)

    # 合并结果
    if results_first_season is not None and results_second_season is not None:
        combined_results = pd.concat([results_first_season, results_second_season], ignore_index=True)
        all_results.append(combined_results)

# 将所有结果写入Excel文件
if all_results:
    final_results = pd.concat(all_results, ignore_index=True)
    final_results.to_excel('2024至2030年农作物种植方案.xlsx', index=None)

（2） 

import pulp
import pandas as pd
import numpy as np
from scipy.optimize import linprog
import random

# 读取四个表格的数据
file_1 = '2023年的种植数据与销售量.xlsx'
file_2 = '各作物聚合后销售量与价格.xlsx'
file_3 = '各作物适合种植的地块类型与季别.xlsx'
file_4 = '乡村的现有耕地.xlsx'

# 加载表格
df1 = pd.read_excel(file_1)
df2 = pd.read_excel(file_2)
df3 = pd.read_excel(file_3)
df4 = pd.read_excel(file_4)

# 提取地块和作物信息
land_types = df4['地块名称'].unique().tolist()

# 根据df3筛选适合第一季和第二季的作物
def get_suitable_crops(season):
    if season=='第一季':
        suitable_crops = df3[df3[f'水浇地{season}'] == 1]['作物名称'].tolist() + \
                     df3[df3[f'普通大棚{season}'] == 1]['作物名称'].tolist() + \
                     df3[df3[f'智慧大棚{season}'] == 1]['作物名称'].tolist()+ \
                    df3[df3[f'平旱地'] == 1]['作物名称'].tolist() + \
                     df3[df3[f'梯田'] == 1]['作物名称'].tolist() + \
                     df3[df3[f'山坡地'] == 1]['作物名称'].tolist()+ \
                    df3[df3[f'水浇地'] == 1]['作物名称'].tolist()
    else:
        suitable_crops = df3[df3[f'水浇地{season}'] == 1]['作物名称'].tolist() + \
                     df3[df3[f'普通大棚{season}'] == 1]['作物名称'].tolist() + \
                     df3[df3[f'智慧大棚{season}'] == 1]['作物名称'].tolist()   
    
    return suitable_crops

suitable_crops_first_season = get_suitable_crops('第一季')
suitable_crops_second_season = get_suitable_crops('第二季')

# 过滤不在suitable_crops中的作物
crops_first_season = [crop for crop in df1['作物名称_x'].unique() if crop in suitable_crops_first_season]
crops_second_season = [crop for crop in df1['作物名称_x'].unique() if crop in suitable_crops_second_season]

# 第二季地块
land_types_second_season = ['D1', 'D2', 'D3', 'D4', 'D5', 'D6', 'D7', 'D8', 
                            'E1', 'E2', 'E3', 'E4', 'E5', 'E6', 'E7', 'E8', 'E9', 'E10', 'E11', 'E12', 'E13', 'E14', 'E15', 'E16', 
                            'F1', 'F2', 'F3', 'F4']

# 随机选择作物的数量,减少计算量
num_crops_to_select = 9

# 为第一季和第二季创建地块与作物的映射
def create_land_crop_mapping(land_types, crops):
    land_crop_mapping = {}
    for land in land_types:
        if len(crops) > num_crops_to_select:
            selected_crops = random.sample(crops, num_crops_to_select)
        else:
            selected_crops = crops  # 如果作物少于9种，全部选中
        land_crop_mapping[land] = selected_crops
    return land_crop_mapping

# 第一季
land_crop_mapping_first_season = create_land_crop_mapping(land_types, crops_first_season)

# 第二季
land_crop_mapping_second_season = create_land_crop_mapping(land_types_second_season, crops_second_season)

# 定义优化函数
def optimize_land_crop(land_crop_mapping, crops, season, year):
    # 初始化决策变量和目标函数
    decision_vars = {}
    objective_coeffs = []
    slack_vars = {}  # 存储超出部分的滞销收入
    '''目标函数调整：计算超出部分的收入，考虑按50%降价处理。

    计算每种作物的总净收入。
    计算超出部分的滞销收入（按2023年销售价格的50%）。
    目标函数是总净收入加上滞销收入。'''
    for land, selected_crops in land_crop_mapping.items():
        for crop in selected_crops:
            decision_vars[(crop, land)] = 0

            land_type = df4[df4['地块名称'] == land]['地块类型'].values[0]
            yield_data = df1[(df1['作物名称_x'] == crop) & (df1['地块类型'] == land_type)]['亩产量/斤'].values
            price_data = df2[df2['作物名称'] == crop]['销售单价/(元/斤)'].values
            cost_data = df1[(df1['作物名称_x'] == crop) & (df1['地块类型'] == land_type)]['种植成本/(元/亩)'].values
            sale_2023 = df2[df2['作物名称'] == crop]['销售量/斤'].values
            if len(sale_2023) > 0:
                sale_2023 = sale_2023[0]
            else:
                sale_2023 = 0

            if len(yield_data) > 0 and len(price_data) > 0 and len(cost_data) > 0:
                yield_per_acre = yield_data[0]
                price_per_unit = price_data[0]
                cost_per_acre = cost_data[0]
                reduced_price = price_per_unit * 0.5
                net_revenue = (yield_per_acre * price_per_unit - cost_per_acre)
                reduced_revenue = max(0, (yield_per_acre - sale_2023) * reduced_price)  # 滞销收入
                objective_coeffs.append(net_revenue + reduced_revenue)  # 目标函数：总净收入加上滞销收入
            else:
                objective_coeffs.append(0)

    objective_coeffs = np.array(objective_coeffs) * -1

    # 重新构建约束条件
    A_ub = []
    b_ub = []

    # 约束条件1：总面积约束
    for land in land_crop_mapping.keys():
        constraint = np.zeros(len(decision_vars))
        for i, (crop, land_name) in enumerate(decision_vars.keys()):
            if land_name == land:
                constraint[i] = 1
        A_ub.append(constraint)
        b_ub.append(df4[df4['地块名称'] == land]['地块面积/亩'].values[0])

    # 约束条件2：最小种植面积约束
    for land in land_crop_mapping.keys():
        min_area = 0.1 * df4[df4['地块名称'] == land]['地块面积/亩'].values[0]
        for crop in land_crop_mapping[land]:
            constraint = np.zeros(len(decision_vars))
            for i, (crop_name, land_name) in enumerate(decision_vars.keys()):
                if crop_name == crop and land_name == land:
                    constraint[i] = -1
            A_ub.append(constraint)
            b_ub.append(-min_area)

    # 约束条件3：三年内至少种植一次豆类作物
    beans_crops = df3[df3['作物类型'].str.contains('粮食（豆类）')]['作物名称'].tolist()
    for land in land_crop_mapping.keys():
        constraint = np.zeros(len(decision_vars))
        for i, (crop, land_name) in enumerate(decision_vars.keys()):
            if land_name == land and crop in beans_crops:
                constraint[i] = -1
        A_ub.append(constraint)
        b_ub.append(0)  # 三年内种植豆类作物的约束（具体实施时可能需要调整）

    A_ub = np.array(A_ub)
    b_ub = np.array(b_ub)

    # 优化模型
    result = linprog(c=objective_coeffs, A_ub=A_ub, b_ub=b_ub, method='highs')

    # 构建优化结果的表格输出
    if result.success:
        optimal_areas = result.x
        solution = {}
        
        for i, (crop, land) in enumerate(decision_vars.keys()):
            if land not in solution:
                solution[land] = {}
            solution[land][crop] = optimal_areas[i]

        # 确保结果表格中包含所有作物
        all_crops = sorted(set(df2['作物名称'].to_list()))  # 所有作物的集合（去重并排序）
        
        # 构建结果表格
        results = pd.DataFrame(columns=['年', '季别', '地块名'] + all_crops)
        
        for land, crop_areas in solution.items():
            season_data = {'年': year, '季别': season, '地块名': land}
            for crop in all_crops:
                season_data[crop] = crop_areas.get(crop, 0)  # 如果该作物不在该地块中，则面积为0
            
            results = results._append(season_data, ignore_index=True)

        return results
    else:
        print(f"{year}年{season}优化失败，无法生成结果表格。")
        return None

# 迭代计算 2024~2030 年的结果
years = list(range(2024, 2031))
all_results = []

for year in years:
    # 第一季
    results_first_season = optimize_land_crop(land_crop_mapping_first_season, crops_first_season, '第一季', year)
    # 第二季
    results_second_season = optimize_land_crop(land_crop_mapping_second_season, crops_second_season, '第二季', year)

    # 合并结果
    if results_first_season is not None and results_second_season is not None:
        combined_results = pd.concat([results_first_season, results_second_season], ignore_index=True)
        all_results.append(combined_results)

第二问代码：

import pulp
import pandas as pd
import numpy as np
from scipy.optimize import linprog
import random

# 读取四个表格的数据
file_1 = '2023年的种植数据与销售量.xlsx'
file_2 = '各作物聚合后销售量与价格.xlsx'
file_3 = '各作物适合种植的地块类型与季别.xlsx'
file_4 = '乡村的现有耕地.xlsx'

# 加载表格
df1 = pd.read_excel(file_1)
df2 = pd.read_excel(file_2)
df3 = pd.read_excel(file_3)
df4 = pd.read_excel(file_4)

# 定义随机生成函数
def random_range(value, percent):
    return value * (1 + np.random.uniform(-percent, percent))

# 生成销售量、亩产量、种植成本和销售价格的随机值
def generate_random_parameters(crop, year, crop_type):
    # 获取2023年的数据
    yield_data = df1[(df1['作物名称_x'] == crop)]['亩产量/斤'].values[0]
    price_data = df2[df2['作物名称'] == crop]['销售单价/(元/斤)'].values[0]
    cost_data = df1[(df1['作物名称_x'] == crop)]['种植成本/(元/亩)'].values[0]
    sales_volume_2023 = df2[df2['作物名称'] == crop]['销售量/斤'].values[0]

    # 计算未来年份的销售量、亩产量、种植成本和销售价格
    if crop_type == '粮食':
        sales_volume = sales_volume_2023 * (1 + np.random.uniform(0.05, 0.1)) ** (year - 2023)
    else:
        sales_volume = sales_volume_2023 * (1 + np.random.uniform(-0.05, 0.05)) ** (year - 2023)

    yield_per_acre = random_range(yield_data, 0.1)
    cost_per_acre = cost_data * (1 + 0.05) ** (year - 2023)
    
    if crop_type == '粮食':
        price_per_unit = price_data
    elif crop_type == '蔬菜':
        price_per_unit = price_data * (1 + 0.05) ** (year - 2023)
    else:
        price_decrease = 0.01 * np.random.uniform(1, 5) if crop != '羊肚菌' else 0.05
        price_per_unit = price_data * (1 - price_decrease) ** (year - 2023)
    
    return sales_volume, yield_per_acre, cost_per_acre, price_per_unit

# 根据df3筛选适合第一季和第二季的作物
def get_suitable_crops(season):
    if season == '第一季':
        suitable_crops = df3[df3[f'水浇地{season}'] == 1]['作物名称'].tolist() + \
                         df3[df3[f'普通大棚{season}'] == 1]['作物名称'].tolist() + \
                         df3[df3[f'智慧大棚{season}'] == 1]['作物名称'].tolist() + \
                         df3[df3[f'平旱地'] == 1]['作物名称'].tolist() + \
                         df3[df3[f'梯田'] == 1]['作物名称'].tolist() + \
                         df3[df3[f'山坡地'] == 1]['作物名称'].tolist() + \
                         df3[df3[f'水浇地'] == 1]['作物名称'].tolist()
    else:
        suitable_crops = df3[df3[f'水浇地{season}'] == 1]['作物名称'].tolist() + \
                         df3[df3[f'普通大棚{season}'] == 1]['作物名称'].tolist() + \
                         df3[df3[f'智慧大棚{season}'] == 1]['作物名称'].tolist()
    
    return suitable_crops

suitable_crops_first_season = get_suitable_crops('第一季')
suitable_crops_second_season = get_suitable_crops('第二季')

# 过滤不在suitable_crops中的作物
crops_first_season = [crop for crop in df1['作物名称_x'].unique() if crop in suitable_crops_first_season]
crops_second_season = [crop for crop in df1['作物名称_x'].unique() if crop in suitable_crops_second_season]

# 提取地块和作物信息
land_types = df4['地块名称'].unique().tolist()

# 第二季地块
land_types_second_season = ['D1', 'D2', 'D3', 'D4', 'D5', 'D6', 'D7', 'D8', 
                            'E1', 'E2', 'E3', 'E4', 'E5', 'E6', 'E7', 'E8', 'E9', 'E10', 'E11', 'E12', 'E13', 'E14', 'E15', 'E16', 
                            'F1', 'F2', 'F3', 'F4']

# 随机选择作物的数量,减少计算量
num_crops_to_select = 9

# 为第一季和第二季创建地块与作物的映射
def create_land_crop_mapping(land_types, crops):
    land_crop_mapping = {}
    for land in land_types:
        if len(crops) > num_crops_to_select:
            selected_crops = random.sample(crops, num_crops_to_select)
        else:
            selected_crops = crops  # 如果作物少于9种，全部选中
        land_crop_mapping[land] = selected_crops
    return land_crop_mapping

# 第一季
land_crop_mapping_first_season = create_land_crop_mapping(land_types, crops_first_season)

# 第二季
land_crop_mapping_second_season = create_land_crop_mapping(land_types_second_season, crops_second_season)

# 定义目标函数和约束条件
def optimize_land_crop(land_crop_mapping, crops, season, year):
    # 初始化决策变量和目标函数
    decision_vars = {}
    objective_coeffs = []
    A_ub = []
    b_ub = []

    for land, selected_crops in land_crop_mapping.items():
        for crop in selected_crops:
            decision_vars[(crop, land)] = 0

            # 获取当前作物的参数
            crop_type = df3[df3['作物名称'] == crop]['作物类型'].values[0]
            sales_volume, yield_per_acre, cost_per_acre, price_per_unit = generate_random_parameters(crop, year, crop_type)
            
            # 计算超产部分按50%降价的销售收入
            sales_volume_2023 = df2[df2['作物名称'] == crop]['销售量/斤'].values[0]
            excess_volume = sales_volume - sales_volume_2023
            if excess_volume > 0:
                discounted_price = 0.5 * df2[df2['作物名称'] == crop]['销售单价/(元/斤)'].values[0]
                net_revenue = (sales_volume_2023 * price_per_unit - cost_per_acre) + (excess_volume * discounted_price - cost_per_acre)
            else:
                net_revenue = sales_volume * price_per_unit - cost_per_acre

            objective_coeffs.append(-net_revenue)  # 目标函数是最大化净利润

    objective_coeffs = np.array(objective_coeffs)
    
    # 重新构建约束条件
    for land in land_crop_mapping.keys():
        constraint = np.zeros(len(decision_vars))
        for i, (crop, land_name) in enumerate(decision_vars.keys()):
            if land_name == land:
                constraint[i] = 1
        A_ub.append(constraint)
        b_ub.append(df4[df4['地块名称'] == land]['地块面积/亩'].values[0])
    
    for land in land_crop_mapping.keys():
        min_area = 0.1 * df4[df4['地块名称'] == land]['地块面积/亩'].values[0]
        for crop in land_crop_mapping[land]:
            constraint = np.zeros(len(decision_vars))
            for i, (crop_name, land_name) in enumerate(decision_vars.keys()):
                if crop_name == crop and land_name == land:
                    constraint[i] = -1
            A_ub.append(constraint)
            b_ub.append(-min_area)
    
    beans_crops = df3[df3['作物类型'].str.contains('粮食（豆类）')]['作物名称'].tolist()
    for land in land_crop_mapping.keys():
        constraint = np.zeros(len(decision_vars))
        for i, (crop, land_name) in enumerate(decision_vars.keys()):
            if land_name == land and crop in beans_crops:
                constraint[i] = -1
        A_ub.append(constraint)
        b_ub.append(0)
    
    A_ub = np.array(A_ub)
    b_ub = np.array(b_ub)

    # 优化模型
    result = linprog(c=objective_coeffs, A_ub=A_ub, b_ub=b_ub, method='highs')

    # 构建优化结果的表格输出
    if result.success:
        optimal_areas = result.x
        solution = {}
        
        for i, (crop, land) in enumerate(decision_vars.keys()):
            if land not in solution:
                solution[land] = {}
            solution[land][crop] = optimal_areas[i]
        
        all_crops = sorted(set(df2['作物名称'].to_list()))
        
        results = pd.DataFrame(columns=['年', '季别', '地块名'] + all_crops)
        
        for land, crop_areas in solution.items():
            season_data = {'年': year, '季别': season, '地块名': land}
            for crop in all_crops:
                season_data[crop] = crop_areas.get(crop, 0)
            
            results = results._append(season_data, ignore_index=True)

        return results
    else:
        print(f"{year}年{season}优化失败，无法生成结果表格。")
        return None

# 迭代计算 2024~2030 年的结果
years = list(range(2024, 2031))
all_results = []

for year in years:
    results_first_season = optimize_land_crop(land_crop_mapping_first_season, crops_first_season, '第一季', year)
    results_second_season = optimize_land_crop(land_crop_mapping_second_season, crops_second_season, '第二季', year)

    if results_first_season is not None and results_second_season is not None:
        combined_results = pd.concat([results_first_season, results_second_season], ignore_index=True)
        all_results.append(combined_results)

# 将所有结果写入Excel文件
if all_results:
    final_results = pd.concat(all_results, ignore_index=True)
    final_results.to_excel('2024至2030年农作物种植方案Q2.xlsx', index=None)

第三问代码：

import pulp
import pandas as pd
import numpy as np
from scipy.optimize import linprog
import random

# 读取四个表格的数据
file_1 = '2023年的种植数据与销售量.xlsx'
file_2 = '各作物聚合后销售量与价格.xlsx'
file_3 = '各作物适合种植的地块类型与季别.xlsx'
file_4 = '乡村的现有耕地.xlsx'

# 加载表格
df1 = pd.read_excel(file_1)
df2 = pd.read_excel(file_2)
df3 = pd.read_excel(file_3)
df4 = pd.read_excel(file_4)

# 提取地块和作物信息
land_types = df4['地块名称'].unique().tolist()

# 根据df3筛选适合第一季和第二季的作物
def get_suitable_crops(season):
    if season == '第一季':
        suitable_crops = df3[df3[f'水浇地{season}'] == 1]['作物名称'].tolist() + \
                         df3[df3[f'普通大棚{season}'] == 1]['作物名称'].tolist() + \
                         df3[df3[f'智慧大棚{season}'] == 1]['作物名称'].tolist() + \
                         df3[df3[f'平旱地'] == 1]['作物名称'].tolist() + \
                         df3[df3[f'梯田'] == 1]['作物名称'].tolist() + \
                         df3[df3[f'山坡地'] == 1]['作物名称'].tolist() + \
                         df3[df3[f'水浇地'] == 1]['作物名称'].tolist()
    else:
        suitable_crops = df3[df3[f'水浇地{season}'] == 1]['作物名称'].tolist() + \
                         df3[df3[f'普通大棚{season}'] == 1]['作物名称'].tolist() + \
                         df3[df3[f'智慧大棚{season}'] == 1]['作物名称'].tolist()
    return suitable_crops

suitable_crops_first_season = get_suitable_crops('第一季')
suitable_crops_second_season = get_suitable_crops('第二季')

# 过滤不在suitable_crops中的作物
crops_first_season = [crop for crop in df1['作物名称_x'].unique() if crop in suitable_crops_first_season]
crops_second_season = [crop for crop in df1['作物名称_x'].unique() if crop in suitable_crops_second_season]

# 第二季地块
land_types_second_season = ['D1', 'D2', 'D3', 'D4', 'D5', 'D6', 'D7', 'D8',
                            'E1', 'E2', 'E3', 'E4', 'E5', 'E6', 'E7', 'E8', 'E9', 'E10', 'E11', 'E12', 'E13', 'E14', 'E15', 'E16',
                            'F1', 'F2', 'F3', 'F4']

# 随机选择作物的数量,减少计算量
num_crops_to_select = 9

# 为第一季和第二季创建地块与作物的映射
def create_land_crop_mapping(land_types, crops):
    land_crop_mapping = {}
    for land in land_types:
        if len(crops) > num_crops_to_select:
            selected_crops = random.sample(crops, num_crops_to_select)
        else:
            selected_crops = crops  # 如果作物少于9种，全部选中
        land_crop_mapping[land] = selected_crops
    return land_crop_mapping

# 第一季
land_crop_mapping_first_season = create_land_crop_mapping(land_types, crops_first_season)

# 第二季
land_crop_mapping_second_season = create_land_crop_mapping(land_types_second_season, crops_second_season)

# 相关性矩阵
correlation_matrix = {
    '粮食': {'cost_price': -0.201, 'cost_quantity': 0.231, 'price_quantity': -0.509},
    '粮食（豆类）': {'cost_price': -0.697, 'cost_quantity': 0.428, 'price_quantity': -0.224},
    '蔬菜': {'cost_price': 0.282, 'cost_quantity': -0.224, 'price_quantity': -0.408},
    '蔬菜（豆类）': {'cost_price': 0.520, 'cost_quantity': -0.116, 'price_quantity': 0.166},
    '食用菌': {'cost_price': 0.505, 'cost_quantity': -0.109, 'price_quantity': -0.823}
}

# 定义生成随机参数的函数
def generate_random_parameters(crop, year):
    base_sales_volume = df2[df2['作物名称'] == crop]['销售量/斤'].values[0]
    base_yield = df1[(df1['作物名称_x'] == crop)]['亩产量/斤'].values[0]
    base_cost = df1[(df1['作物名称_x'] == crop)]['种植成本/(元/亩)'].values[0]
    base_price = df2[df2['作物名称'] == crop]['销售单价/(元/斤)'].values[0]

    if crop in correlation_matrix:
        correlation = correlation_matrix[crop]
    else:
        correlation = {'cost_price': 0, 'cost_quantity': 0, 'price_quantity': 0}

    # 销售量调整
    sales_volume_growth = np.random.uniform(0.95, 1.05) if crop not in ['小麦', '玉米'] else np.random.uniform(1.05, 1.10)
    sales_volume = base_sales_volume * (1 + sales_volume_growth)

    # 亩产量调整
    yield_variation = np.random.uniform(0.9, 1.1)
    yield_per_acre = base_yield * yield_variation

    # 种植成本调整
    cost_increase = np.random.uniform(0.05, 0.1)
    cost_per_acre = base_cost * (1 + cost_increase)

    # 销售价格调整
    if crop in ['食用菌', '羊肚菌']:
        price_decrease = np.random.uniform(0.01, 0.05) if crop == '食用菌' else 0.05
        price_per_unit = base_price * (1 - price_decrease)
    else:
        price_increase = np.random.uniform(0.05, 0.1)
        price_per_unit = base_price * (1 + price_increase)

    return sales_volume, yield_per_acre, cost_per_acre, price_per_unit

# 定义优化函数
def optimize_land_crop(land_crop_mapping, crops, season, year):
    # 初始化目标函数系数
    objective_coeffs = []
    decision_vars = []

    for land, selected_crops in land_crop_mapping.items():
        for crop in selected_crops:
            decision_vars.append((crop, land))

            land_type = df4[df4['地块名称'] == land]['地块类型'].values[0]
            yield_data = df1[(df1['作物名称_x'] == crop) & (df1['地块类型'] == land_type)]['亩产量/斤'].values
            price_data = df2[df2['作物名称'] == crop]['销售单价/(元/斤)'].values
            cost_data = df1[(df1['作物名称_x'] == crop) & (df1['地块类型'] == land_type)]['种植成本/(元/亩)'].values

            if len(yield_data) > 0 and len(price_data) > 0 and len(cost_data) > 0:
                yield_per_acre = yield_data[0]
                price_per_unit = price_data[0]
                cost_per_acre = cost_data[0]
                net_revenue = (yield_per_acre * price_per_unit) - cost_per_acre
                objective_coeffs.append(net_revenue)
            else:
                objective_coeffs.append(0)

    objective_coeffs = np.array(objective_coeffs) * -1

    # 约束条件构建
    A_ub = []
    b_ub = []

    for land in land_crop_mapping.keys():
        constraint = np.zeros(len(decision_vars))
        for i, (crop, land_name) in enumerate(decision_vars):
            if land_name == land:
                constraint[i] = 1
        A_ub.append(constraint)
        b_ub.append(df4[df4['地块名称'] == land]['地块面积/亩'].values[0])

    for land in land_crop_mapping.keys():
        min_area = 0.1 * df4[df4['地块名称'] == land]['地块面积/亩'].values[0]
        for crop in land_crop_mapping[land]:
            constraint = np.zeros(len(decision_vars))
            for i, (crop_name, land_name) in enumerate(decision_vars):
                if crop_name == crop and land_name == land:
                    constraint[i] = -1
            A_ub.append(constraint)
            b_ub.append(-min_area)

    # 运行线性规划
    A_ub = np.array(A_ub)
    b_ub = np.array(b_ub)
    result = linprog(c=objective_coeffs, A_ub=A_ub, b_ub=b_ub, method='highs')

    if result.success:
        optimal_areas = result.x
        solution = {}
        
        for i, (crop, land) in enumerate(decision_vars):
            if land not in solution:
                solution[land] = {}
            solution[land][crop] = optimal_areas[i]

        # 确保结果表格中包含所有作物
        all_crops = sorted(set(df2['作物名称'].to_list()))  # 所有作物的集合（去重并排序）

        # 构建结果表格
        results = pd.DataFrame(columns=['年', '季别', '地块名'] + all_crops)

        for land, crop_areas in solution.items():
            season_data = {'年': year, '季别': season, '地块名': land}
            for crop in all_crops:
                season_data[crop] = crop_areas.get(crop, 0)  # 如果该作物不在该地块中，则面积为0
            
            results = results._append(season_data, ignore_index=True)

        return results
    else:
        print(f"{year}年{season}优化失败，无法生成结果表格。")
        return pd.DataFrame()

# 计算2024~2030年的结果
def calculate_results_for_years(start_year, end_year):
    results = {}
    for year in range(start_year, end_year + 1):
        first_season_result = optimize_land_crop(land_crop_mapping_first_season, crops_first_season, '第一季', year)
        second_season_result = optimize_land_crop(land_crop_mapping_second_season, crops_second_season, '第二季', year)
        combined_result = pd.concat([first_season_result, second_season_result], ignore_index=True)
        results[year] = combined_result
    return results

# 计算结果并保存
results = calculate_results_for_years(2024, 2030)