1111555

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

# 读取表格的数据
fe1 = '2023年的种植数据和销售量.xlsx'
fe2 = '各作物归类后销售量与价格.xlsx'
fe3 = '各作物适宜种植的地块类型与季别.xlsx'
fe4 = '耕地.xlsx'

# 加载表格
re1 = pd.read_excel(fe1)
re2 = pd.read_excel(fe2)
re3 = pd.read_excel(fe3)
re4 = pd.read_excel(fe4)

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

# 根据re3筛选适合第一季的作物
suitableCropsFirstSeason = re3[re3['水浇地第一季'] == 1]['作物名称'].tolist() + \
                           re3[re3['普通大棚第一季'] == 1]['作物名称'].tolist() + \
                           re3[re3['智慧大棚第一季'] == 1]['作物名称'].tolist()

# 根据re3筛选适合第二季的作物
suitableCropsSecondSeason = re3[re3['水浇地第二季'] == 1]['作物名称'].tolist() + \
                            re3[re3['普通大棚第二季'] == 1]['作物名称'].tolist() + \
                            re3[re3['智慧大棚第二季'] == 1]['作物名称'].tolist()

# 过滤不在suitableCropsFirstSeason中的作物
cropsFirstSeason = [crop for crop in re1['作物名称_x'].unique() if crop in suitableCropsFirstSeason]
cropsSecondSeason = [crop for crop in re1['作物名称_x'].unique() if crop in suitableCropsSecondSeason]

# 第二季地块
landTypesSecondSeason = ['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 createLandCropMapping(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 = createLandCropMapping(landTypes, cropsFirstSeason)

# 第二季
land_crop_mapping_second_season = createLandCropMapping(landTypesSecondSeason, cropsSecondSeason)


# 定义优化函数
def optimizeLandCrop(land_crop_mapping, crops, season):
    # 初始化决策变量和目标函数
    decision_vars_limited = {}
    objective_coeffs_limited = []

    for land, selected_crops in land_crop_mapping.items():
        for crops in selected_crops:
            decision_vars_limited[(crops, land)] = 0

            land_type = re4[re4['地块名称'] == land]['地块类型'].values[0]
            yield_data = re1[(re1['作物名称_x'] == crops) & (re1['地块类型'] == land_type)]['亩产量/斤'].values
            price_data = re2[re2['作物名称'] == crops]['销售单价/(元/斤)'].values
            cost_data = re1[(re1['作物名称_x'] == crops) & (re1['地块类型'] == 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_limited.append(net_revenue)
            else:
                objective_coeffs_limited.append(0)

    objective_coeffs_limited = np.array(objective_coeffs_limited) * -1

    # 重新构建约束条件
    A_ub_limited = []
    b_ub_limited = []

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

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

    A_ub_limited = np.array(A_ub_limited)
    b_ub_limited = np.array(b_ub_limited)

    # 优化模型
    result_limited = linprog(c=objective_coeffs_limited, A_ub=A_ub_limited, b_ub=b_ub_limited, method='highs')

    # 构建优化结果的表格输出
    if result_limited.success:
        optimal_areas_limited = result_limited.x
        solution_limited = {}

        for i, (crop, land) in enumerate(decision_vars_limited.keys()):
            if land not in solution_limited:
                solution_limited[land] = {}
            solution_limited[land][crop] = optimal_areas_limited[i]

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

        # 构建结果表格

        for land, crop_areas in solution_limited.items():
            season_data = {'季别': season, '地块名': land}
            for crop in all_crops:
                season_data[crop] = crop_areas.get(crop, 0)  # 如果该作物不在该地块中，则面积为0
                results_limited = results_limited.append(season_data, ignore_index=True)
        return results_limited
    else:
        print(f"{season} 优化失败，无法生成结果表格。")
        return None


# 优化第一季
results_first_season = optimizeLandCrop(land_crop_mapping_first_season, cropsFirstSeason, '第一季')

# 优化第二季
results_second_season = optimizeLandCrop(land_crop_mapping_second_season, cropsSecondSeason, '第二季')

# 合并结果
if results_first_season is not None and results_second_season is not None:
    # 在两个数据框中添加季节列
    results_first_season['季别'] = '第一季'
    results_second_season['季别'] = '第二季'

    # 合并两个数据框
    combinedResults = pd.concat([results_first_season, results_second_season], ignore_index=True)

    combinedResults.to_excel('2024--2030年农作物种植方案.xlsx')