将拟合的曲线保存为xlsx文件，并保存参数

将拟合的曲线保存为xlsx文件，并保存参数

import pandas as pd
import numpy as np
import matplotlib.pyplot as plt
from scipy.optimize import curve_fit
import math

def plot_data(x, y):
    # 绘制原始数据的散点图
    plt.scatter(x, y)
    plt.xlabel('x')
    plt.ylabel('y')
    plt.legend()
    plt.show()
    
    
def fit_func(x, a, b,c,d,e):
#     return a*x +b
    return a*x**4 +b*x**3+c*x**2+d*x+e

def fit_data(x, y):

    # 使用Levenberg-Marquardt算法拟合
    popt, _ = curve_fit(fit_func, x, y)

    # 绘制原始数据和拟合曲线
    plt.scatter(x, y, label='Original Data')
    plt.plot(x, fit_func(x, *popt), label='Nonlinear Fit')

    # 计算预测误差
    y_pred = fit_func(x, *popt)
    residuals = y_pred - y
    mae = np.mean(np.abs(residuals))
    
    print("Mean Absolute Error (MAE):", mae)
    
    plt.xlabel('x')
    plt.ylabel('y')
    plt.legend()
    plt.show()

    # 提取拟合参数
#     a, b= popt
    a, b,c,d,e= popt

    # 打印拟合参数
    print("a =", a)
    print("b =", b)
    print("c =", c)
    print("d =", d)
    print("e =", e)

    # 创建包含原始数据、预测数据和残差的 DataFrame
    result_df = pd.DataFrame({
        'x': x,
        'y': y,
        '预测值': y_pred,
        '绝对误差': residuals
#         'mae':mae
    })

    # 将 DataFrame 保存到 Excel 文件
    result_df.to_excel('path.xlsx', index=False)
    # 创建包含拟合参数的 DataFrame
    parameters = pd.DataFrame({'Parameters': ['a', 'b', 'c', 'd','e'], 'Values': popt})

#     # 如果需要，也可以保存拟合参数到 Excel
#     with pd.ExcelWriter('path.xlsx') as writer:
#         pd.DataFrame({'Parameters': ['a', 'b', 'c', 'd'], 'Values': popt}).to_excel(writer, index=False)
    with pd.ExcelWriter('path.xlsx') as writer:
        result_df.to_excel(writer, sheet_name='Data and Predictions', index=False)
        parameters.to_excel(writer, sheet_name='Fitting Parameters', index=False)

# 读取CSV文件
data = pd.read_csv('data.csv')

# 提取前两行数据
row1 = data.iloc[147]
# 选择特定的列
i= 39
columns = [f'Amp_{0.5*n+1}' for n in range(0, i)]

print(row1)
# print(row2[columns])
# 绘制散点图

    
x = range(i) 
x = np.array(x)  # 将x转换为数组类型
y = row1[columns]
#y = row2[columns]

plot_data(x,y)
fit_data(x, y)