import openpyxl
import pandas as pd
import numpy as np
import matplotlib.pyplot as plt
from sklearn.preprocessing import PolynomialFeatures, StandardScaler
from sklearn.linear_model import LinearRegression
from sklearn.metrics import r2_score
from sklearn.model_selection import train_test_split
import optuna
from scipy.optimize import minimize_scalar

# Функция загрузки данных из Excel
def load_data_from_excel(excel_file, columns_to_check):
    workbook = openpyxl.load_workbook(excel_file, data_only=True)
    sheet = workbook.active

    numeric_data = []

    for row in sheet.iter_rows(min_row=1):
        row_values = []
        is_full_numeric = True

        for col in columns_to_check:
            cell_value = row[col - 1].value
            if isinstance(cell_value, (int, float)):
                row_values.append(cell_value)
            else:
                is_full_numeric = False
                break

        if is_full_numeric:
            numeric_data.append(row_values)

    df = pd.DataFrame(numeric_data)
    # Удаление первой строки
    df = df.drop(index=0).reset_index(drop=True)
    return df

# Функция сортировки данных по температуре и расходу
def sort_data_by_temperature_and_flow_rate(df, temperature_index, flow_rate_index):
    return df.sort_values(by=[temperature_index, flow_rate_index])

# Функция получения уникальных значений температуры
def get_unique_temperatures(df, temperature_index):
    return df.iloc[:, temperature_index].unique()

# Функция поиска номинального значения температуры
def find_nominal_temperature(unique_temperatures):
    print(f"Уникальные значения температуры: {unique_temperatures}")
    nominal_temperature = float(input("Введите номинальное значение температуры обратной сетевой воды (например, 70): "))
    return nominal_temperature

# Функция расчета дельт
def calculate_deltas(df, temperature_index, power_index, nominal_temperature):
    df = df.copy()
    df['Дельта'] = 0.0
    nominal_subset = df[df.iloc[:, temperature_index] == nominal_temperature]

    unique_temperatures = get_unique_temperatures(df, temperature_index)

    for temp in unique_temperatures:
        if temp == nominal_temperature:
            continue

        subset = df[df.iloc[:, temperature_index] == temp]
        if len(subset) != len(nominal_subset):
            print(f"Warning: Length mismatch between Tоб={temp} и Tоб={nominal_temperature}. Skipping this segment.")
            continue

        df.loc[subset.index, 'Дельта'] = subset.iloc[:, power_index].values - nominal_subset.iloc[:, power_index].values

    filtered_df = df[df.iloc[:, temperature_index] != nominal_temperature]

    return filtered_df

# Измененная функция нормализации дельт для поддержки индивидуальных целевых значений
def normalize_deltas(df, temperature_index, delta_index, target_values, tolerance=1e-6):
    df = df.copy()
    unique_temperatures = get_unique_temperatures(df, temperature_index)
    first_deltas = {}

    for temp in unique_temperatures:
        subset = df[df.iloc[:, temperature_index] == temp]
        if not subset.empty:
            first_delta = subset.iloc[0, delta_index]
            first_deltas[temp] = first_delta

    df['Модифицированное значение'] = 0.0
    for temp in unique_temperatures:
        first_delta = first_deltas[temp]
        target_value = target_values.get(temp, None)
        if target_value is None:
            print(f"Целевое значение для температуры {temp} не задано.")
            continue

        if abs(first_delta) < tolerance:
            k = 0.0
        else:
            k = target_value / first_delta

        df.loc[df.iloc[:, temperature_index] == temp, 'Модифицированное значение'] = df.loc[df.iloc[:, temperature_index] == temp, 'Дельта'] * k

    return df

# Новая функция для получения коэффициентов регрессии для каждой температуры
def get_regression_coefficients(df, temperature_index):
    temp_to_coef = {}
    unique_temperatures = get_unique_temperatures(df, temperature_index)

    for temp in unique_temperatures:
        subset = df[df.iloc[:, temperature_index] == temp]
        X = subset['Дельта'].values.reshape(-1, 1)
        y = subset['Модифицированное значение'].values

        # Проверяем, что у нас достаточно данных для построения модели
        if len(X) > 1:
            model = LinearRegression()
            model.fit(X, y)
            slope = model.coef_[0]
            temp_to_coef[temp] = slope
        else:
            print(f"Недостаточно данных для построения модели для температуры {temp}")

    return temp_to_coef

# Функция оптимизации целевого значения для достижения требуемого коэффициента регрессии
def optimize_target_value(temp_match, df, temperature_index, delta_index, coef_base, tolerance=1e-6):
    df = df.copy()
    subset = df[df.iloc[:, temperature_index] == temp_match].copy()
    if subset.empty:
        print(f"Данные для температуры {temp_match} не найдены.")
        return None, None

    first_delta = subset.iloc[0, delta_index]
    if abs(first_delta) < tolerance:
        print(f"Первое значение 'Дельта' для температуры {temp_match} слишком мало.")
        return None, None

    # Определяем целевую функцию для оптимизации
    def objective(target_value):
        k = target_value / first_delta
        subset['Модифицированное значение'] = subset['Дельта'] * k
        X = subset['Дельта'].values.reshape(-1, 1)
        y = subset['Модифицированное значение'].values

        if len(X) < 2:
            return float('inf')

        model = LinearRegression()
        model.fit(X, y)
        coef_match = model.coef_[0]
        return abs(coef_match - coef_base)

    # Выполняем оптимизацию
    res = minimize_scalar(objective, bounds=(0.01, 10), method='bounded')
    optimal_target_value = res.x
    k = optimal_target_value / first_delta

    # Обновляем 'Модифицированное значение' в основном датафрейме
    df.loc[df.iloc[:, temperature_index] == temp_match, 'Модифицированное значение'] = df.loc[df.iloc[:, temperature_index] == temp_match, 'Дельта'] * k

    return optimal_target_value, k

# Функция сохранения данных
def save_data(df, output_file):
    df.to_csv(output_file, index=False)

# Целевая функция для Optuna
def objective(trial, Gt_train, Gt_test, mod_train, mod_test):
    degree = trial.suggest_int('degree', 1, 5)  # Ограничиваем степень полинома для стабильности

    poly = PolynomialFeatures(degree=degree)
    Gt_train_poly = poly.fit_transform(Gt_train)
    Gt_test_poly = poly.transform(Gt_test)

    model = LinearRegression()
    model.fit(Gt_train_poly, mod_train)

    mod_pred = model.predict(Gt_test_poly)

    r2 = r2_score(mod_test, mod_pred)

    return -r2

# Функция удаления выбросов
def remove_outliers_iqr(df, column_name):
    Q1 = df[column_name].quantile(0.25)
    Q3 = df[column_name].quantile(0.75)
    IQR = Q3 - Q1

    lower_bound = Q1 - 1.5 * IQR
    upper_bound = Q3 + 1.5 * IQR

    return df[(df[column_name] >= lower_bound) & (df[column_name] <= upper_bound)]

# Функция поиска пар температур, имеющих общие дельты
def find_temperature_pairs(pressure_temp_data):
    """
    Находит соответствующие пары температур для одинаковых дельт между разными давлениями.

    :param pressure_temp_data: словарь, где ключ — давление, значение — словарь с номинальной температурой и списком температур
    :return: словарь, где ключ — дельта, значение — список кортежей (давление, температура)
    """
    # Словарь для хранения температур по дельтам
    delta_to_temps = {}

    # Вычисляем дельты и собираем температуры по дельтам
    for pressure, data in pressure_temp_data.items():
        nominal = data['nominal']
        temperatures = data['temperatures']
        deltas = [int(round(temp - nominal)) for temp in temperatures]
        data['deltas'] = deltas  # Сохраняем дельты обратно в данные

        for delta, temp in zip(deltas, temperatures):
            if delta not in delta_to_temps:
                delta_to_temps[delta] = []
            delta_to_temps[delta].append((pressure, temp))

    # Ищем дельты, которые присутствуют в нескольких давлениях
    result = {}
    for delta, temps in delta_to_temps.items():
        if len(temps) > 1:
            result[delta] = temps

    # Сортируем результаты по дельтам
    sorted_result = {delta: result[delta] for delta in sorted(result)}

    # Выводим результат
    print("Соответствующие пары температур для общих дельт:")
    for delta, temps in sorted_result.items():
        print(f"{delta}: {temps}")

    return sorted_result

# Новая функция для получения температур с соответствиями
def get_temperatures_with_matches(delta_to_temps_pressures, base_pressure):
    """
    Получает словарь давлений к множеству температур, которые имеют соответствие с базовым давлением.
    """
    pressure_to_matched_temperatures = {}
    for delta, temps in delta_to_temps_pressures.items():
        pressures_present = [pt for pt, temp in temps]
        if base_pressure in pressures_present:
            for pt, temp in temps:
                if pt not in pressure_to_matched_temperatures:
                    pressure_to_matched_temperatures[pt] = set()
                pressure_to_matched_temperatures[pt].add(temp)
    return pressure_to_matched_temperatures

# Обновленная функция построения графиков с учетом соответствующих температур
def main_plot(data_list, pt_values, pressure_to_matched_temperatures):
    fig, (ax_gt, ax_delta) = plt.subplots(1, 2, figsize=(14, 6), gridspec_kw={'width_ratios': [1, 1]})

    for idx, (data, pt) in enumerate(zip(data_list, pt_values)):
        print(f"Обработка давления Pt = {pt}")
        if pt == pt_values[0]:
            matched_temperatures = data['Tоб'].unique()  # Для базового давления используем все температуры
        else:
            matched_temperatures = pressure_to_matched_temperatures.get(pt, set())

        # Фильтруем данные, оставляя только необходимые температуры
        data_matched = data[data['Tоб'].isin(matched_temperatures)]

        if data_matched.empty:
            print(f"Нет данных с соответствующими температурами для Pt={pt}")
            continue

        # График зависимости модифицированного значения от Gт
        Gt = data_matched['Gт'].values.reshape(-1, 1)
        mod_value = data_matched['Модифицированное значение'].values

        # Нормировка данных
        scaler = StandardScaler()
        Gt_scaled = scaler.fit_transform(Gt)

        Gt_train, Gt_test, mod_train, mod_test = train_test_split(Gt_scaled, mod_value, test_size=0.2, random_state=42)

        study = optuna.create_study(direction='minimize')
        study.optimize(lambda trial: objective(trial, Gt_train, Gt_test, mod_train, mod_test), n_trials=50)

        best_degree = study.best_params['degree']
        best_r2 = -study.best_value

        poly = PolynomialFeatures(degree=best_degree)
        Gt_poly = poly.fit_transform(Gt_scaled)

        model = LinearRegression()
        model.fit(Gt_poly, mod_value)

        mod_value_pred = model.predict(Gt_poly)

        # Преобразуем обратно для построения графика
        Gt_original = scaler.inverse_transform(Gt_scaled)
        order = np.argsort(Gt_original.flatten())

        ax_gt.scatter(data_matched['Gт'], data_matched['Модифицированное значение'], label=f'Исходные данные Pt={pt}', color=plt.cm.tab10(idx % 10), alpha=0.6)
        ax_gt.plot(Gt_original[order].flatten(), mod_value_pred[order], '-', label=f'Poly deg {best_degree}, R²: {best_r2:.2f}, Pt={pt}', color=plt.cm.tab10(idx % 10))

        # Вывод уравнения полиномиальной регрессии
        coef = model.coef_[::-1]
        intercept = model.intercept_
        equation = f"y = {intercept:.4f} "
        for i, c in enumerate(coef[1:], start=1):
            equation += f"+ ({c:.15f}) * x^{i} "
        print(f"Уравнение полинома для графика 'Модифицированное значение' от 'Gт' (степень {best_degree}), Pt={pt}:")
        print(equation)

        # График зависимости модифицированного значения от Дельта
        unique_tob_values = data_matched['Tоб'].unique()

        for tob_value in unique_tob_values:
            df_filtered = data_matched[data_matched['Tоб'] == tob_value]
            df_filtered = df_filtered.sort_values(by='Gт')

            X = df_filtered['Дельта'].values.reshape(-1, 1)
            y = df_filtered['Модифицированное значение'].values

            if len(X) > 1:
                model = LinearRegression()
                model.fit(X, y)

                slope = model.coef_[0]
                intercept = model.intercept_

                ax_delta.plot(df_filtered['Дельта'], df_filtered['Модифицированное значение'], label=f'Tоб: {tob_value}, Pt={pt}')

                print(f"Уравнение для Tоб {tob_value}, Pt={pt}: y = {slope:.4f} * x + {intercept:.10f}")
            else:
                print(f"Недостаточно данных для построения модели для Tоб {tob_value}, Pt={pt}")

    ax_gt.set_title('Зависимость f(Pот, Gтурб)')
    ax_gt.set_xlabel('Gт')
    ax_gt.set_ylabel('Модифицированное значение')
    ax_gt.legend()
    ax_gt.grid(True)  # Добавляем сетку

    ax_delta.set_title('Зависимость f(dNe, dtоб)')
    ax_delta.set_xlabel('Дельта')
    ax_delta.set_ylabel('Модифицированное значение')
    ax_delta.legend()
    ax_delta.grid(True)  # Добавляем сетку

    plt.tight_layout()
    plt.show()

if __name__ == "__main__":
    excel_file = '/content/test_data.xlsx'
    final_output_file = 'final_output_with_delta.csv'

    temperature_index = 9  # Индекс колонки 'Tоб'
    flow_rate_index = 0    # Индекс колонки 'Gт'
    power_index = 2        # Индекс колонки 'Ne'
    pressure_index = 11    # Индекс колонки 'Pt'

    # Загрузка данных из Excel файла
    df = load_data_from_excel(excel_file, [2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12,
                                           13, 14, 15, 16, 17, 18, 19, 20, 21,
                                           22, 23, 24, 25, 26, 27, 28])

    # Округление давления до десятых долей
    df['Pt_rounded'] = df.iloc[:, pressure_index].round(1)

    # Получение уникальных значений давления
    unique_pt_values = df['Pt_rounded'].unique()
    print(f"Уникальные значения давления: {unique_pt_values}")

    data_list = []
    pt_values = []
    pressure_temp_data = {}  # Для хранения температур и номинальных температур

    for pt in unique_pt_values:
        print(f"\nОбработка давления Pt = {pt}")
        df_pt = df[df['Pt_rounded'] == pt].copy().drop(columns=['Pt_rounded'])

        # Сортировка данных
        sorted_df = sort_data_by_temperature_and_flow_rate(df_pt, temperature_index, flow_rate_index)

        # Находжение уникальных значений температуры
        unique_temperatures = get_unique_temperatures(sorted_df, temperature_index)

        # Поиск номинального значения температуры
        nominal_temperature = find_nominal_temperature(unique_temperatures)

        # Сохраняем данные для функции поиска пар температур
        pressure_temp_data[pt] = {
            'nominal': nominal_temperature,
            'temperatures': sorted(unique_temperatures.tolist())
        }

        # Расчет дельты
        result_df = calculate_deltas(sorted_df, temperature_index, power_index, nominal_temperature)

        # Запрос целевых значений для первого давления
        if len(data_list) == 0:
            target_values = {}
            for temp in get_unique_temperatures(result_df, temperature_index):
                target_value = float(input(f"Введите целевое значение для температуры {temp} (например, 1.0): "))
                target_values[temp] = target_value

            # Нормализация дельты
            normalized_df = normalize_deltas(result_df, temperature_index, result_df.columns.get_loc('Дельта'), target_values)
            # Удаление выбросов по столбцу 'Модифицированное значение'
            cleaned_df = remove_outliers_iqr(normalized_df, 'Модифицированное значение')

            # Автоматическое переименование столбцов после создания новых столбцов
            column_names = [
                'Gт', 'Qт', 'Ne', 'qe', 'Gcн', 'Ipв', 'Toхл', 'Qот', 'w',
                'Tоб', 'Tпр', 'Pt', 'Pдиаф', 'Psн', 'Pk', 'Gк',
                'G:Д03', 'G:С29', 'T:Д03', 'P:Д03', 'P:Ч13', 'T:Ч13',
                'P:Ч17', 'P:Ч20', 'P:Ч29', 'T:Р27', 'T:ДФ', 'Дельта', 'Модифицированное значение'
            ]

            # Проверяем, совпадает ли количество столбцов
            if len(column_names) == cleaned_df.shape[1]:
                cleaned_df.columns = column_names
            else:
                print(f"Количество столбцов в DataFrame ({cleaned_df.shape[1]}) не соответствует ожидаемому ({len(column_names)}). Автоматическое переименование невозможно.")
                continue

            # Сохранение результата
            output_file = f'final_output_with_delta_Pt_{pt}.csv'
            save_data(cleaned_df, output_file)
            print(f"Обработка давления Pt = {pt} завершена.")

            data_list.append(cleaned_df)
            pt_values.append(pt)

            # Получаем коэффициенты регрессии для первого давления
            temp_base_to_coef = get_regression_coefficients(cleaned_df, temperature_index)

        else:
            # Для остальных давлений
            # Расчет дельты уже выполнен выше

            # Инициализируем столбец 'Модифицированное значение'
            result_df['Модифицированное значение'] = 0.0
            delta_index = result_df.columns.get_loc('Дельта')
            tolerance = 1e-6

            # Получаем пары температур для общих дельт
            delta_to_temps_pressures = find_temperature_pairs(pressure_temp_data)

            # Словарь целевых значений для температур
            target_values = {}

            for delta, temp_pressure_list in delta_to_temps_pressures.items():
                # Ищем соответствующие температуры для текущего давления и первого давления
                temp_base = None
                temp_match = None
                for pressure, temp in temp_pressure_list:
                    if pressure == pt:
                        temp_match = temp
                    elif pressure == pt_values[0]:
                        temp_base = temp

                if temp_base and temp_match:
                    coef_base = temp_base_to_coef.get(temp_base, None)
                    if coef_base is None:
                        print(f"Коэффициент регрессии для температуры {temp_base} не найден.")
                        continue

                    # Оптимизируем целевое значение для temp_match
                    optimal_target_value, k = optimize_target_value(
                        temp_match, result_df, temperature_index, delta_index, coef_base, tolerance
                    )
                    if optimal_target_value is not None:
                        target_values[temp_match] = optimal_target_value
                        print(f"Оптимальное целевое значение для температуры {temp_match} при Pt={pt}: {optimal_target_value:.4f}, коэффициент k: {k:.4f}")
                    else:
                        print(f"Не удалось оптимизировать целевое значение для температуры {temp_match} при Pt={pt}")
                else:
                    print(f"Нет соответствующей температуры для дельты {delta} при давлении {pt}")

            # Нормализуем дельты с найденными целевыми значениями
            normalized_df = normalize_deltas(result_df, temperature_index, delta_index, target_values)
            # Удаление выбросов по столбцу 'Модифицированное значение'
            cleaned_df = remove_outliers_iqr(normalized_df, 'Модифицированное значение')

            # Автоматическое переименование столбцов после создания новых столбцов
            column_names = [
                'Gт', 'Qт', 'Ne', 'qe', 'Gcн', 'Ipв', 'Toхл', 'Qот', 'w',
                'Tоб', 'Tпр', 'Pt', 'Pдиаф', 'Psн', 'Pk', 'Gк',
                'G:Д03', 'G:С29', 'T:Д03', 'P:Д03', 'P:Ч13', 'T:Ч13',
                'P:Ч17', 'P:Ч20', 'P:Ч29', 'T:Р27', 'T:ДФ', 'Дельта', 'Модифицированное значение'
            ]

            # Проверяем, совпадает ли количество столбцов
            if len(column_names) == cleaned_df.shape[1]:
                cleaned_df.columns = column_names
            else:
                print(f"Количество столбцов в DataFrame ({cleaned_df.shape[1]}) не соответствует ожидаемому ({len(column_names)}). Автоматическое переименование невозможно.")
                continue

            # Сохранение результата
            output_file = f'final_output_with_delta_Pt_{pt}.csv'
            save_data(cleaned_df, output_file)
            print(f"Обработка давления Pt = {pt} завершена.")

            data_list.append(cleaned_df)
            pt_values.append(pt)

    # Вызов функции для поиска пар температур
    delta_to_temps_pressures = find_temperature_pairs(pressure_temp_data)
    # Получение температур с соответствиями для каждого давления
    pressure_to_matched_temperatures = get_temperatures_with_matches(delta_to_temps_pressures, pt_values[0])

    # Построение графиков с учетом температурных соответствий
    main_plot(data_list, pt_values, pressure_to_matched_temperatures)
Код завершается с ошибкой:
IndexError                                Traceback (most recent call last)
<ipython-input-5-6726391b106d> in <cell line: 346>()
    354 
    355     # Загрузка данных из Excel файла
--> 356     df = load_data_from_excel(excel_file, [2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12,
    357                                            13, 14, 15, 16, 17, 18, 19, 20, 21,
    358                                            22, 23, 24, 25, 26, 27, 28])

<ipython-input-5-6726391b106d> in load_data_from_excel(excel_file, columns_to_check)
     22 
     23         for col in columns_to_check:
---> 24             cell_value = row[col - 1].value
     25             if isinstance(cell_value, (int, float)):
     26                 row_values.append(cell_value)

IndexError: tuple index out of range
При попытке прочитать данные:
	Gт	Qт	Ne	qe	Gcн	Iпв	Tохл	Qот	w	Tоб	Tпр	Pт	Pдиаф	Pсн	Pк	Gк	G:Д0  	P:Д0  	T:Д0  	G:Кнд 	P:Кнд 	T:Кнд 	P:Ч01 	G:Ч13 	G:Ч15 	G:Р18 
	т/ч	Гкал/ч	МВт		т/ч	ккал/кг	град	Гкал/ч	т/ч	град	град	кг/см2				т/ч	т/ч	кг/см2	град	т/ч	кг/см2	град	кг/см2	т/ч	т/ч	т/ч
	237.9606207	176.1848824	42.52285298	1180.661691	0	70.8895229	32	125.9797789	1663.621813	50	125.3784036	2.501036273	2.100354609	7.891841079	0.065102198	15	237.9606207	79.03	497.7	0	5.86	217.5	74.42106614	236.1141611	235.4121778	193.6979888
	232.4615878	172.184517	41.22527732	1189.747747	0	70.58370187	32	123.1368362	1623.758824	50	125.4849026	2.501750195	2.115127014	7.728309796	0.065102198	15	232.4615878	79.03	497.7	0	5.86	217.5	72.65624037	230.6284262	229.9264483	190.421559
	200.2655166	148.7306939	33.83456213	1254.97372	0	68.61712678	32	106.2692076	1392.610597	50	125.9521838	2.500123058	2.187978799	6.780869902	0.065102198	15	200.2655166	79.03	497.7	0	5.86	217.5	62.35896078	198.509472	197.8074882	169.9340578
	160.4380864	119.601312	25.11693919	1382.953149	0	65.81756201	32	84.86576189	1105.984608	50	126.3691648	2.498109502	2.274085658	5.646392541	0.065102198	15	160.4380864	79.03	497.7	0	5.86	217.5	49.70895306	158.7746994	158.0727059	142.0887233
	118.137455	88.48522711	16.18657822	1662.570048	0	62.28232986	32	61.57390698	799.4502695	50	126.6515408	2.498970646	2.360225436	4.514537799	0.065102198	15	118.137455	79.03	497.7	0	5.86	217.5	36.38901935	116.5672688	115.8652818	108.9886601