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文件读取的数据进行插值和近似，以及如何保存结果。