使用Pandas/NumPy读取Excel后，数组转float64遇lmfit类型错误

我在循环中导入多个Excel文件，提取一列数据转为float64数组供lmfit使用，代码如下：

isolated_peak_df = pd.read_excel(path + r'/Residuals_{}.xlsx'.format(i), header=None)
isolated_peak = [a for a in isolated_peak_df.transpose().iloc[0].loc[0:50]]
isolated_peak = np.array(isolated_peak)

该数组能正常用于其他操作，但在lmfit中执行数学运算时触发错误：

TypeError: Cannot cast array data from dtype('O') to dtype('float64') according to the rule 'safe'

我试过多种常规方法：

• 在np.array()中指定dtype='float64'
• 用astype('float')转换DataFrame
• 使用pd.to_numeric处理数组
  均无效，且已确认数组尺寸匹配、数据均为数值类型。

奇怪的是，把lmfit参数初始值降低几个数量级后，错误消失：

freeParams = Parameters()
freeParams.add("x", value = 5 * (10 ** 20), vary=True)
freeParams.add("y", value = 5 * (10 ** 15), vary=True)

已知float64的存储范围是2.2E-308至1.7E+308，5e15或5e20显然在范围内，疑惑为何会触发错误。

完整测试代码：

epsfcn=0.01
ftol=1.e-10
xtol=1.e-10
max_nfev=300

for i in absorption.index:
    isolated_peak_df = pd.read_excel(path + r'/Residuals_{}.xlsx'.format(i), header=None)
    isolated_peak = [a for a in isolated_peak_df.transpose().iloc[0].loc[0:50]]
    isolated_peak = np.array(isolated_peak)
    
    def calc_residual(freeParams, isolated_peak):
        residual = isolated_peak[5:22] - np.zeros(17)
        return residual

    mini = minimize(calc_residual, freeParams, args=(isolated_peak,), epsfcn=epsfcn, ftol=ftol, xtol=xtol, max_nfev=max_nfev, calc_covar=True, nan_policy="omit")

注：测试代码未使用x、y参数，最终会纳入计算。

完整报错栈：

TypeError                                 Traceback (most recent call last)
Cell In[202], line 19
     16     residual = isolated_peak[5:22] - np.zeros(17)
     17     return residual
---> 19 mini = minimize(calc_residual, freeParams, args=(isolated_peak,), epsfcn=epsfcn, ftol=ftol, xtol=xtol, max_nfev=max_nfev, calc_covar=True, nan_policy="omit")

File ~/opt/anaconda3/lib/python3.9/site-packages/lmfit/minimizer.py:2600, in minimize(fcn, params, method, args, kws, iter_cb, scale_covar, nan_policy, reduce_fcn, calc_covar, max_nfev, **fit_kws)
   2460 """Perform the minimization of the objective function.
   2461 
   2462 The minimize function takes an objective function to be minimized,
   (...)
   2594 
   2595 """
   2596 fitter = Minimizer(fcn, params, fcn_args=args, fcn_kws=kws,
   2597                    iter_cb=iter_cb, scale_covar=scale_covar,
   2598                    nan_policy=nan_policy, reduce_fcn=reduce_fcn,
   2599                    calc_covar=calc_covar, max_nfev=max_nfev, **fit_kws)
-> 2600 return fitter.minimize(method=method)

File ~/opt/anaconda3/lib/python3.9/site-packages/lmfit/minimizer.py:2369, in Minimizer.minimize(self, method, params, **kws)
   2366         if (key.lower().startswith(user_method) or
   2367                 val.lower().startswith(user_method)):
   2368             kwargs['method'] = val
-> 2369 return function(**kwargs)

File ~/opt/anaconda3/lib/python3.9/site-packages/lmfit/minimizer.py:1693, in Minimizer.leastsq(self, params, max_nfev, **kws)
   1691 result.call_kws = lskws
   1692 try:
-> 1693     lsout = scipy_leastsq(self.__residual, variables, **lskws)
   1694 except AbortFitException:
   1695     pass

File ~/opt/anaconda3/lib/python3.9/site-packages/scipy/optimize/_minpack_py.py:426, in leastsq(func, x0, args, Dfun, full_output, col_deriv, ftol, xtol, gtol, maxfev, epsfcn, factor, diag)
    424     if maxfev == 0:
    425         maxfev = 200*(n + 1)
-> 426     retval = _minpack._lmdif(func, x0, args, full_output, ftol, xtol,
    427                              gtol, maxfev, epsfcn, factor, diag)
    428 else:
    429     if col_deriv:

TypeError: Cannot cast array data from dtype('O') to dtype('float64') according to the rule 'safe'

1. 彻底清理数组类型
   Excel导入时可能存在隐藏的非数值元素（如空单元格、字符串格式数值、不可见字符），常规转换未彻底处理。建议简化提取逻辑并强制清理：

   # 读取时填充空值，直接提取目标列并强制转float64
   isolated_peak_df = pd.read_excel(path + r'/Residuals_{}.xlsx'.format(i), header=None).fillna(0)
   isolated_peak = isolated_peak_df.iloc[0:51, 0].astype('float64').values
   

   避免用列表推导保留原Series的object类型元素，直接用astype+values生成纯float64数组。

2. 参数初始值触发问题的本质
   当参数初始值过大时，lmfit内部计算梯度或缩放时会启动严格类型检查，此时数组中隐性的object元素（哪怕单个）就会触发转换错误；参数值较小时，内部逻辑可能跳过部分严格检查，误差被掩盖。

3. 其他排查优化点

   • 用print(isolated_peak.dtype)确认数组类型，np.isnan(isolated_peak).any()排查是否存在NaN
   • 将calc_residual函数定义移到循环外，避免重复定义引发的潜在问题
   • 尝试method='nelder'等其他优化方法，缩小问题范围

内容的提问来源于stack exchange，提问作者ttoshiro