用机器学习中的线性拟合方法对不同距离下的亮度进行预测

import numpy as np
from sklearn.model_selection import train_test_split
from sklearn.preprocessing import PolynomialFeatures
from sklearn.linear_model import LinearRegression
from sklearn.metrics import mean_squared_error
import matplotlib.pyplot as plt
import pandas as pd

# 指定文件路径和工作表名（sheet_name参数指定工作簿）
file_path = r'2025_1031_1000nit.xlsx'  # 替换为您的文件路径
sheet_name = 'Sheet1'  # 指定工作表名称（例如"Sheet1"）

# 读取指定区域：usecols定义列范围（A到C列），skiprows和nrows定义行范围（跳过0行，读取10行）
df = pd.read_excel(
    file_path,
    sheet_name=sheet_name,  # 指定工作簿
    usecols='A:K',  # 指定列范围（A到C列）
    skiprows=12,  # 起始行偏移量（0表示从第1行开始）
    nrows=51  # 读取的行数（例如读取前10行）
)

# 输出DataFrame数据（区域内容）
print("读取的数据区域：")
print(df)

step = 6
num = int(round(len(df)/step))
para_lists = []
for i in range(3,4):
    if i ==0:
        X = np.array(df.iloc[0:step, 0])
        y = np.array(df.iloc[0:step, 4])
    elif i == num-1:
        X = np.array(df.iloc[(num-1)*step:, 0])
        y = np.array(df.iloc[(num-1)*step:, 4])
    else:
        X = np.array(df.iloc[i*step-1:(i+1)*step,0])
        y = np.array(df.iloc[i*step-1:(i+1)*step,4])

    X = X.reshape(-1, 1)
    X_train, X_test, y_train, y_test = train_test_split(X, y, test_size=0.1)
    # 特征工程
    poly = PolynomialFeatures(degree=2, include_bias=False)
    X_poly_train = poly.fit_transform(X_train)
    print("X_poly_train:", X_poly_train)
    X_poly_test = poly.transform(X_test)
    # 模型训练
    model = LinearRegression()
    model.fit(X_poly_train, y_train)
    # 评估
    y_pred = model.predict(X_poly_test)
    train_score = model.score(X_poly_train, y_train)
    test_score = model.score(X_poly_test, y_test)
    mse = mean_squared_error(y_test, y_pred)

    print(f"测试集MSE: {mse:.4f}")
    print(f"R2_train_score:{train_score:.4f}")
    print(f"R2_test_score:{test_score:.4f}")

    print(f"模型系数a: {model.coef_[1]}")
    print(f"模型系数b: {model.coef_[0]}")
    print(f"截距 c：{model.intercept_}")

    para_dict = {"a": model.coef_[1], "b": model.coef_[0], "c": model.intercept_, "mse": mse,
                 "R2_train_score": train_score, "R2_test_score": test_score}
    para_lists.append(para_dict)

paras = pd.DataFrame(para_lists)
print(paras)
with pd.ExcelWriter(r"F:QHY600_距离亮度关系研究_自动聚焦_lxh_20250425paras.xlsx", engine='openpyxl', mode='a',if_sheet_exists="replace") as writer:
    paras.to_excel(writer, sheet_name="para", startrow=0, startcol=5, index=False, float_format="%.6f")




# X0 = np.array(df.iloc[0:step, 0])  # 0~6 5~12  11~17 16~23 22~27  26~32  31~37 36~42  41~47 46~50
# X1 = np.array(df.iloc[step-1:step-1+step+1, 0])
# X2 = np.array(df.iloc[2*step-1:2*step-1+step+1, 0])
# X3 = np.array(df.iloc[3*step-1:3*step-1+step+1, 0])
# X4 = np.array(df.iloc[4*step-1:4*step-1+step+1, 0])
# X5 = np.array(df.iloc[5*step-1:5*step-1+step+1, 0])
# X6 = np.array(df.iloc[6*step-1:6*step-1+step+1, 0])
# X7 = np.array(df.iloc[7*step-1:7*step-1+step+1, 0])
# X8 = np.array(df.iloc[8*step-1:, 0])
#
#
# X_list = [X0, X1, X2, X3, X4, X5, X6, X7, X8]
#
# y0 = np.array(df.iloc[0:6, 4])  # 0~6 5~12  11~17 16~23 22~27  26~32  31~37 36~42  41~47 46~50
# y1 = np.array(df.iloc[5:12, 4])
# y2 = np.array(df.iloc[11:18, 4])
# y3 = np.array(df.iloc[17:24, 4])
# y4 = np.array(df.iloc[23:30, 4])
# y5 = np.array(df.iloc[29:36, 4])
# y6 = np.array(df.iloc[35:42, 4])
# y7 = np.array(df.iloc[41:48, 4])
# y8 = np.array(df.iloc[47:50, 4])
#
#
# y_list = [y0, y1, y2, y3, y4, y5, y6, y7, y8]
# para_list = []
# for i in range(len(X_list)):
#     X = X_list[i]
#     X = X.reshape(-1, 1)
#     y = y_list[i]
#     X_train, X_test, y_train, y_test = train_test_split(X, y, test_size=0.2)
#     # 特征工程
#     poly = PolynomialFeatures(degree=2, include_bias=False)
#     X_poly_train = poly.fit_transform(X_train)
#     print("X_poly_train:", X_poly_train)
#     X_poly_test = poly.transform(X_test)
#     # 模型训练
#     model = LinearRegression()
#     model.fit(X_poly_train, y_train)
#     # 评估
#     y_pred = model.predict(X_poly_test)
#     train_score = model.score(X_poly_train, y_train)
#     test_score = model.score(X_poly_test, y_test)
#     mse = mean_squared_error(y_test, y_pred)
#
#     print(f"测试集MSE: {mse:.4f}")
#     print(f"R2_train_score:{train_score:.4f}")
#     print(f"R2_test_score:{test_score:.4f}")
#
#     print(f"模型系数a: {model.coef_[1]}")
#     print(f"模型系数b: {model.coef_[0]}")
#     print(f"截距 c：{model.intercept_}")
#
#     para_dict = {"a": model.coef_[1], "b": model.coef_[0], "c": model.intercept_, "mse": mse,
#                  "R2_train_score": train_score, "R2_test_score": test_score}
#     para_list.append(para_dict)
# paras = pd.DataFrame(para_list)
# print(paras)
# with pd.ExcelWriter(file_path, engine='openpyxl', mode='a',if_sheet_exists="replace") as writer:
#     paras.to_excel(writer, sheet_name="para", startrow=0, startcol=5, index=False, float_format="%.6f")

# plt.scatter(X, y, color='blue', label='原始数据')
# plt.plot(X, model.predict(poly.transform(X)),
#          color='red', label='多项式回归')
# plt.legend()
# plt.show()

# 数据准备
# X = np.array([100,103,106,109,112,115,118,120])
# print(X)
# X = X.reshape(-1, 1)
#
# y = np.array([0.936478719,0.932740471,0.929883461,0.92980169,0.927730649,0.925983172,0.924672565,0.92475111])
# X_train, X_test, y_train, y_test = train_test_split(X, y, test_size=0.2)
#
# # 特征工程
# poly = PolynomialFeatures(degree=2, include_bias=False)
# X_poly_train = poly.fit_transform(X_train)
# print("X_poly_train:", X_poly_train)
# X_poly_test = poly.transform(X_test)
#
# # 模型训练
# model = LinearRegression()
# model.fit(X_poly_train, y_train)
# # 评估
# y_pred = model.predict(X_poly_test)
# train_score = model.score(X_poly_train, y_train)
# test_score = model.score(X_poly_test, y_test)
# mse = mean_squared_error(y_test, y_pred)
#
# print(f"测试集MSE: {mse:.4f}")
# print(f"R2_train_score:{train_score:.4f}")
# print(f"R2_test_score:{test_score:.4f}")
#
# print(f"模型系数a: {model.coef_[1]}")
# print(f"模型系数b: {model.coef_[0]}")
# print(f"截距 c：{model.intercept_}")
# plt.scatter(X, y, color='blue', label='原始数据')
# plt.plot(X, model.predict(poly.transform(X)),
#          color='red', label='多项式回归')
# plt.legend()
# plt.show()