Чтение данных из формата Excel и построение моделей

Открыть пример в Engee

Очень часто данные, которые нужно проанализировать, берутся не из каких-то абстрактных репозиториев или баз данных, а лежат в некотором файле табличного формата. В одних отраслях предпочтительным форматом является CSV, в других случаях данные создаются в стандартных табличных редакторах.

В этой демонстрации мы изучим, как загрузить и отобразить на графике данные из таблицы в формате Excel и построить по ним модель.

Чтение целого файла

Для большинства команд нам потребуются следующие библиотеки, которые уже встроены в окружение. Нам только остается обозначить, что мы собираемся использовать команды из пространства имён этих библиотек.

using XLSX, DataFrames

В одном файле Excel может находиться несколько листов с таблицами. Чаще всего мы пользуемся только первым из этих листов, который называется Лист1 или Sheet1 (но может называться иначе).

Прочтение всего файла вернёт нам объект с несколькими таблицами внутри, каждая из которых расположена на отдельном листе.

xf = XLSX.readxlsx( "table_simple_example.xlsx" )

XLSXFile("table_simple_example.xlsx") containing 1 Worksheet
            sheetname size          range
-------------------------------------------------
                Лист1 4x3           A1:C4

Мы можем снова в этом убедиться, отобразив список листов.

XLSX.sheetnames(xf)

1-element Vector{String}:
 "Лист1"

Если вы уже открыли файл командой readxlsx, то сможете отделить нужный лист при помощи дополнительной команды.

sh = xf["Лист1"]

4×3 XLSX.Worksheet: ["Лист1"](A1:C4)

Или можно воспользоваться командами, которые осуществляют этот процесс быстрее.

Чтение отдельного листа из таблицы

Стандартами командами библиотеки XLSX можно прочитать каждый лист по отдельности.

XLSX.readtable( "table_simple_example.xlsx", "Лист1" )

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 и обрабатывать данные средствами этой библиотеки.

DataFrame( XLSX.readtable( "table_simple_example.xlsx", "Лист1" ) )

3×3 DataFrame
 Row │ a    b    c
     │ Any  Any  Any
─────┼───────────────
   1 │ 1    2    3
   2 │ 4    5    6
   3 │ 7    8    9

xdf = Float32.(DataFrame( XLSX.readtable( "table_simple_example.xlsx", "Лист1" ) ))

3×3 DataFrame
 Row │ a        b        c
     │ Float32  Float32  Float32
─────┼───────────────────────────
   1 │     1.0      2.0      3.0
   2 │     4.0      5.0      6.0
   3 │     7.0      8.0      9.0

Но это имеет смысл, когда данные представляют из себя набор объектов и перечень их признаков. Иногда в таблице лежит просто матрица с числами, и тогда работать с ней лучше при помощи стандартного типа данных Matrix.

xm = XLSX.readdata("table_simple_example.xlsx", "Лист1", "A2:C4")

3×3 Matrix{Any}:
 1  2  3
 4  5  6
 7  8  9

Выбор правильного типа данных очень упрощает дальнейшую работу с таблицей или матрицей.

using Plots
wireframe( xm )

interactive-scripts/images/data_analysis_excel_table_to_model/d393f743249dae9a25d706d61f997b6e9746fbc2

Вычисление данных на новой расчетной сетке (интерполяция)

Теперь, в нашем распоряжении таблица данных, заданных в некоторой системе координат, мы можем выполнить их интерполяцию при помощи сплайна и построить модель.

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 )

interactive-scripts/images/data_analysis_excel_table_to_model/9366caef5e4a84029e4f1860931544816a187dae

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 )

interactive-scripts/images/data_analysis_excel_table_to_model/40f5f2693a8a80330e77f0d7c8c940d8987f687e

Мы выполнили интерполяцию кубическими сплайнами. Если бы исходные точки данных располагались бы в системе координат, но с не регулярными засечками по осям X и Y, то мы бы смогли построить только модель на основе линейной интерполяции (или модель "методом ближайших соседей").

Аппроксимация при помощи функции и поиск минимума

После визуального анализа данных вы можете выдвинуть предположение о том, какой функцией было бы удобно произвести аппроксимацию этих данных. Это требует некоторого опыта и интерактивной работы с данными.

Предположим, что мы убедились, что данные хорошо представимы при помощи квадратичной функции. Построим полином, который позволит нам более точно найти предполагаемые значения выходных параметров, в которых целевое значение изучаемого процесса минимально.

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 )

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
──────────────────────────────────────────────────────────────────────────────

# Задаем новую расчетную сетку
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( 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' )

interactive-scripts/images/data_analysis_excel_table_to_model/4982806978d4a2f3cb857a295037f426d1d56c78

Модели, созданные при помощи GLM принимают на вход облако точек (как вы видели, мы пользовались данными в форме одномерных векторов Vector) и позволяют выполнять экстраполяцию без каких-либо дополнительных настроек.

Нахождение точки минимума

Оба объекта позволяют нам найти точку минимума функции, описание которой мы получили из таблицы.

using Optim
opt = optimize( x->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], [predict( model,(;X=[x_min],Y=[y_min]) )[1]],
    camera = (30,50), legend=false, cbar=false )

interactive-scripts/images/data_analysis_excel_table_to_model/25899f5a9c43a226f1e3f5fe48fa0c51934ffaee

Мы получили график, где отмечен минимум.

Запись данных в файл Excel

Чтобы сохранить результаты анализа в другую таблицу Excel, воспользуемся следующей командой:

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 в таблице из одного листа:

XLSX.writetable( "table_write_example_2.xlsx", xdf )

Для сохранения объектов Matrix или Vector в табличный формат гораздо чаще используется формат CSV.

Заключение

В этом примере мы научились читать файлы в табличном формате Excel. Мы преобразовывали их к разным форматам:

формат DataFrame, который лучше всего подходит для анализа табличных данных
формат Matrix, который позволяет работать с группой чисел из исходной таблицы как с матрицей

Мы также продемонстрировали, как осуществлять интерполяцию и аппроксимацию данных, считанных из файла в формате Excel и как сохранить результаты.