Engee 文档
Notebook

从 Excel 中读取数据并建立模型

需要分析的数据往往不是来自抽象的存储库或数据库，而是一些表格格式文件。在某些行业，首选格式是 CSV，而在其他情况下，数据是在标准表格编辑器中创建的。

在本演示中，我们将学习如何从 Excel 电子表格中加载数据并绘制图表，然后从中建立模型。

读取整个文件

对于大多数命令，我们都需要以下已内置到环境中的库。我们只需指定要使用这些库命名空间中的命令即可。

In [ ]: 
Pkg.add(["Interpolations", "XLSX", "GLM", "Optim"])
In [ ]: 
using XLSX, DataFrames

一个 Excel 文件可能包含多个电子表格。大多数情况下，我们只使用其中的第一张表，它被称为Лист1 或Sheet1 （但也可能被称为其他名称）。

读取整个文件将返回一个包含多个表格的对象，每个表格都在单独的工作表上。

In [ ]: 
xf = XLSX.readxlsx( "table_simple_example.xlsx" )
Out[0]:
XLSXFile("table_simple_example.xlsx") containing 1 Worksheet
            sheetname size          range        
-------------------------------------------------
                Лист1 4x3           A1:C4

我们可以通过显示工作表列表再次看到这一点。

In [ ]: 
XLSX.sheetnames(xf)
Out[0]:
1-element Vector{String}:
 "Лист1"

如果已经使用readxlsx 命令打开了文件，则可以使用附加命令分隔所需的工作表。

In [ ]: 
sh = xf["Лист1"]
Out[0]:
4×3 XLSX.Worksheet: ["Лист1"](A1:C4)

或者使用更快的命令。

从表格中读取单张纸

您可以使用标准库命令XLSX 单独读取每个工作表。

In [ ]: 
XLSX.readtable( "table_simple_example.xlsx", "Лист1" )
Out[0]:
XLSX.DataTable(Any[Any[1, 4, 7], Any[2, 5, 8], Any[3, 6, 9]], [:a, :b, :c], Dict(:a => 1, :b => 2, :c => 3))

您可以将表格表单输入DataFrame 格式，并使用该库的方法处理数据。

In [ ]: 
DataFrame( XLSX.readtable( "table_simple_example.xlsx", "Лист1" ) )
Out[0]:

3 rows × 3 columns

abc
AnyAnyAny
1123
2456
3789
In [ ]: 
xdf = Float32.(DataFrame( XLSX.readtable( "table_simple_example.xlsx", "Лист1" ) ))
Out[0]:

3 rows × 3 columns

abc
Float32Float32Float32
11.02.03.0
24.05.06.0
37.08.09.0

但当数据代表一组对象及其属性列表时，这样做才有意义。有时，表格只包含一个数字矩阵，这时最好使用标准数据类型Matrix 来处理。

In [ ]: 
xm = XLSX.readdata( "table_simple_example.xlsx", "Лист1", "A2:C4" )
Out[0]:
3×3 Matrix{Any}:
 1  2  3
 4  5  6
 7  8  9

选择正确的数据类型可以简化表格或矩阵的进一步处理。

In [ ]: 
gr()
wireframe( xm )
Out[0]:

在新的计算网格上计算数据（插值）

现在，我们已经掌握了在某个坐标系中定义的数据表，我们可以使用样条曲线对其进行插值并建立模型。

In [ ]: 
xm = vec( Float32.( XLSX.readdata("table_matrix_example.xlsx", "Лист1", "B1:D1") ))
ym = vec( Float32.( XLSX.readdata("table_matrix_example.xlsx", "Лист1", "A2:A4") ))
zm = Float32.( XLSX.readdata("table_matrix_example.xlsx", "Лист1", "B2:D4") )

wireframe( xm, ym, zm )
Out[0]:
In [ ]: 
using Interpolations

# Переводим описание системы координат из векторов в набор диапазонов
xSrc = range( minimum(xm), maximum(xm), step=xm[2]-xm[1] )
ySrc = range( minimum(ym), maximum(ym), step=ym[2]-ym[1] )

# Строим модель, которая предскажет нам значения на новой сетке
cubInt = cubic_spline_interpolation( (xSrc, ySrc), zm )

# Задаем новую расчетную сетку
xRange = range( minimum(xm), maximum(xm), 50 )
yRange = range( minimum(ym), maximum(ym), 50 )
zIntRes = cubInt(xRange, yRange)

# Визуализируем результат
surface( xRange, yRange, zIntRes, fillalpha=0.2 )
wireframe!( xRange, yRange, zIntRes )
Out[0]:

我们已经使用三次样条曲线进行了插值。如果原始数据点位于一个坐标系中，但 X 和 Y 缺口不规则，我们就只能通过线性插值（或 "近邻 "模型）来建立模型。

函数逼近和最小搜索

在对数据进行直观分析后，您可以猜测哪个函数可以方便地逼近数据。这需要一些经验和与数据的交互式工作。

假设我们已经验证了二次函数可以很好地表示数据。让我们构建一个多项式，以便更准确地找到输出参数的预期值，使所研究过程的目标值最小。

In [ ]: 
using GLM

# Подготовим данные: представим таблицу как список отдельных точек данных
xx = Float64.(repeat( collect(xSrc), outer = length(ySrc) ))
yy = Float64.(repeat( collect(ySrc), inner = length(xSrc) ))
zz = Float64.(vec(reshape(zm', 1, :)))

# Создадим объект DataFrame
data = DataFrame( X = xx, Y = yy, Z = zz )

# Построим модель с двумя входными параметрами
model = lm( @formula(Z ~ X + Y + X^2 + Y^2 + X*Y), data )
Out[0]:
StatsModels.TableRegressionModel{LinearModel{GLM.LmResp{Vector{Float64}}, GLM.DensePredChol{Float64, LinearAlgebra.CholeskyPivoted{Float64, Matrix{Float64}, Vector{Int64}}}}, Matrix{Float64}}

Z ~ 1 + X + Y + :(X ^ 2) + :(Y ^ 2) + X & Y

Coefficients:
──────────────────────────────────────────────────────────────────────────────
                  Coef.   Std. Error      t  Pr(>|t|)    Lower 95%   Upper 95%
──────────────────────────────────────────────────────────────────────────────
(Intercept)  26.1111     10.0158       2.61    0.0799  -5.76373     57.986
X            -2.2         0.96622     -2.28    0.1072  -5.27494      0.874942
Y            -0.433333    0.292604    -1.48    0.2352  -1.36453      0.497864
X ^ 2         0.0533333   0.0237268    2.25    0.1102  -0.0221761    0.128843
Y ^ 2         0.0133333   0.00593171   2.25    0.1102  -0.00554402   0.0322107
X & Y         0.01        0.0083887    1.19    0.3189  -0.0166966    0.0366966
──────────────────────────────────────────────────────────────────────────────
In [ ]: 
# Задаем новую расчетную сетку
dx = 2 * abs(maximum(xm) - minimum(xm))
dy = 2 * abs(maximum(ym) - minimum(ym))
xRange = range( minimum(xm) - dx, maximum(xm) + dx, 50 )
yRange = range( minimum(ym) - dy, maximum(ym) + dy, 20 )

# Визуализируем результат
xx = repeat( collect(xRange), outer = length(yRange) )
yy = repeat( collect(yRange), inner = length(xRange) )
zz = reshape( GLM.predict(model, (;X=xx,Y=yy)), length(xRange), :)

# Построим графики
surface( xRange, yRange, zz', fillalpha=0.2, xlabel="x", ylabel="y", zlabel="z" )
wireframe!( xRange, yRange, zz' )
Out[0]:

使用GLM 创建的模型接受点云作为输入（如您所见，我们使用的是一维向量形式的数据Vector ），并允许我们在不进行任何额外设置的情况下进行推断。

寻找最小点

这两个对象都可以让我们找到函数的最小点，我们可以从表格中得到函数的描述。

In [ ]: 
using Optim
opt = optimize( x->GLM.predict( model,(;X=[x[1]],Y=[x[2]]) )[1], [0.0,0.0] )
(x_min, y_min) = Optim.minimizer( opt )

surface( xRange, yRange, zz', xlabel="x", ylabel="y", zlabel="z", c=:viridis, zcolor=zz' )
scatter!( [x_min], [y_min], [GLM.predict( model,(;X=[x_min],Y=[y_min]) )[1]],
    camera = (30,50), legend=false, cbar=false )
Out[0]:

我们得到了一个标有最小值的图形。

将数据写入 Excel 文件

要将分析结果保存到另一个 Excel 电子表格中，请使用以下命令：

In [ ]: 
XLSX.openxlsx( "table_write_example_1.xlsx", mode="w" ) do xf
    
    # Поместим матрицу на первый лист
    XLSX.rename!( xf[1], "Лист1" )

    xw = reshape(xx, 1, :) # Сделаем матрицы из векторов
    for ind in CartesianIndices(xw)
        XLSX.setdata!( xf["Лист1"], XLSX.CellRef( ind[1], ind[2]+1 ), xw[ind])
    end

    yw = reshape(yy, :, 1)
    for ind in CartesianIndices(yw)
        XLSX.setdata!( xf["Лист1"], XLSX.CellRef( ind[1]+1, ind[2] ), yw[ind])
    end

    zw = zz
    for ind in CartesianIndices(zw)
        XLSX.setdata!( xf["Лист1"], XLSX.CellRef( ind[1]+1, ind[2]+1 ), zw[ind])
    end

    
    # На второй лист поместим DataFrame
    XLSX.addsheet!( xf, "Лист2" )
    XLSX.writetable!( xf["Лист2"], eachcol(xdf), names(xdf); anchor_cell=XLSX.CellRef("A1"))
    
end

或者使用更短的命令--将DataFrame 保存在一个表格中：

In [ ]: 
XLSX.writetable( "table_write_example_2.xlsx", xdf, overwrite=true )

要将Matrix 或Vector 对象保存为表格格式，使用CSV 格式更为常见。

结论

在本例中，我们学习了如何读取 Excel 电子表格格式的文件。我们将它们转换成了不同的格式：

  • 最适合分析表格数据的格式DataFrame *Matrix 格式，可将源表中的一组数字作为矩阵处理。

我们还演示了如何对从 Excel 文件读取的数据进行插值和近似计算，以及如何保存结果。