private void ExportChart(string fileName, SymbolicDiscriminantFunctionClassificationSolution solution, string formula)
{
FileInfo newFile = new FileInfo(fileName);
if (newFile.Exists)
{
newFile.Delete();
newFile = new FileInfo(fileName);
}
var formulaParts = formula.Split(new string[] { Environment.NewLine }, StringSplitOptions.None);
using (ExcelPackage package = new ExcelPackage(newFile)) {
ExcelWorksheet modelWorksheet = package.Workbook.Worksheets.Add("Model");
FormatModelSheet(modelWorksheet, solution, formulaParts);
ExcelWorksheet datasetWorksheet = package.Workbook.Worksheets.Add("Dataset");
WriteDatasetToExcel(datasetWorksheet, solution.ProblemData);
ExcelWorksheet inputsWorksheet = package.Workbook.Worksheets.Add("Inputs");
WriteInputSheet(inputsWorksheet, datasetWorksheet, formulaParts.Skip(2), solution.ProblemData.Dataset);
ExcelWorksheet estimatedWorksheet = package.Workbook.Worksheets.Add("Estimated Values");
WriteEstimatedWorksheet(estimatedWorksheet, datasetWorksheet, formulaParts, solution);
ExcelWorksheet chartsWorksheet = package.Workbook.Worksheets.Add("Charts");
AddCharts(chartsWorksheet, solution);
package.Workbook.Properties.Title = "Excel Export";
package.Workbook.Properties.Author = "HEAL";
package.Workbook.Properties.Comments = "Excel export of a symbolic data analysis solution from HeuristicLab";
package.Save();
}
}
private void ExportChart(string fileName, SymbolicDiscriminantFunctionClassificationSolution solution, string formula) {
FileInfo newFile = new FileInfo(fileName);
if (newFile.Exists) {
newFile.Delete();
newFile = new FileInfo(fileName);
}
var formulaParts = formula.Split(new string[] { Environment.NewLine }, StringSplitOptions.None);
using (ExcelPackage package = new ExcelPackage(newFile)) {
ExcelWorksheet modelWorksheet = package.Workbook.Worksheets.Add("Model");
FormatModelSheet(modelWorksheet, solution, formulaParts);
ExcelWorksheet datasetWorksheet = package.Workbook.Worksheets.Add("Dataset");
WriteDatasetToExcel(datasetWorksheet, solution.ProblemData);
ExcelWorksheet inputsWorksheet = package.Workbook.Worksheets.Add("Inputs");
WriteInputSheet(inputsWorksheet, datasetWorksheet, formulaParts.Skip(2), solution.ProblemData.Dataset);
ExcelWorksheet estimatedWorksheet = package.Workbook.Worksheets.Add("Estimated Values");
WriteEstimatedWorksheet(estimatedWorksheet, datasetWorksheet, formulaParts, solution);
ExcelWorksheet chartsWorksheet = package.Workbook.Worksheets.Add("Charts");
AddCharts(chartsWorksheet, solution);
package.Workbook.Properties.Title = "Excel Export";
package.Workbook.Properties.Author = "HEAL";
package.Workbook.Properties.Comments = "Excel export of a symbolic data analysis solution from HeuristicLab";
package.Save();
}
}
public static IClassificationSolution CreateLinearDiscriminantAnalysisSolution(IClassificationProblemData problemData)
{
var dataset = problemData.Dataset;
string targetVariable = problemData.TargetVariable;
IEnumerable <string> allowedInputVariables = problemData.AllowedInputVariables;
IEnumerable <int> rows = problemData.TrainingIndices;
int nClasses = problemData.ClassNames.Count();
double[,] inputMatrix = AlglibUtil.PrepareInputMatrix(dataset, allowedInputVariables.Concat(new string[] { targetVariable }), rows);
if (inputMatrix.Cast <double>().Any(x => double.IsNaN(x) || double.IsInfinity(x)))
{
throw new NotSupportedException("Linear discriminant analysis does not support NaN or infinity values in the input dataset.");
}
// change class values into class index
int targetVariableColumn = inputMatrix.GetLength(1) - 1;
List <double> classValues = problemData.ClassValues.OrderBy(x => x).ToList();
for (int row = 0; row < inputMatrix.GetLength(0); row++)
{
inputMatrix[row, targetVariableColumn] = classValues.IndexOf(inputMatrix[row, targetVariableColumn]);
}
int info;
double[] w;
alglib.fisherlda(inputMatrix, inputMatrix.GetLength(0), allowedInputVariables.Count(), nClasses, out info, out w);
if (info < 1)
{
throw new ArgumentException("Error in calculation of linear discriminant analysis solution");
}
ISymbolicExpressionTree tree = new SymbolicExpressionTree(new ProgramRootSymbol().CreateTreeNode());
ISymbolicExpressionTreeNode startNode = new StartSymbol().CreateTreeNode();
tree.Root.AddSubtree(startNode);
ISymbolicExpressionTreeNode addition = new Addition().CreateTreeNode();
startNode.AddSubtree(addition);
int col = 0;
foreach (string column in allowedInputVariables)
{
VariableTreeNode vNode = (VariableTreeNode) new HeuristicLab.Problems.DataAnalysis.Symbolic.Variable().CreateTreeNode();
vNode.VariableName = column;
vNode.Weight = w[col];
addition.AddSubtree(vNode);
col++;
}
var model = LinearDiscriminantAnalysis.CreateDiscriminantFunctionModel(tree, new SymbolicDataAnalysisExpressionTreeInterpreter(), problemData, rows);
SymbolicDiscriminantFunctionClassificationSolution solution = new SymbolicDiscriminantFunctionClassificationSolution(model, (IClassificationProblemData)problemData.Clone());
return(solution);
}
public static IClassificationSolution CreateLinearDiscriminantAnalysisSolution(IClassificationProblemData problemData)
{
var dataset = problemData.Dataset;
string targetVariable = problemData.TargetVariable;
IEnumerable <string> allowedInputVariables = problemData.AllowedInputVariables;
IEnumerable <int> rows = problemData.TrainingIndices;
int nClasses = problemData.ClassNames.Count();
var doubleVariableNames = allowedInputVariables.Where(dataset.VariableHasType <double>).ToArray();
var factorVariableNames = allowedInputVariables.Where(dataset.VariableHasType <string>).ToArray();
double[,] inputMatrix = dataset.ToArray(doubleVariableNames.Concat(new string[] { targetVariable }), rows);
var factorVariables = dataset.GetFactorVariableValues(factorVariableNames, rows);
var factorMatrix = dataset.ToArray(factorVariables, rows);
inputMatrix = factorMatrix.HorzCat(inputMatrix);
if (inputMatrix.Cast <double>().Any(x => double.IsNaN(x) || double.IsInfinity(x)))
{
throw new NotSupportedException("Linear discriminant analysis does not support NaN or infinity values in the input dataset.");
}
// change class values into class index
int targetVariableColumn = inputMatrix.GetLength(1) - 1;
List <double> classValues = problemData.ClassValues.OrderBy(x => x).ToList();
for (int row = 0; row < inputMatrix.GetLength(0); row++)
{
inputMatrix[row, targetVariableColumn] = classValues.IndexOf(inputMatrix[row, targetVariableColumn]);
}
int info;
double[] w;
alglib.fisherlda(inputMatrix, inputMatrix.GetLength(0), inputMatrix.GetLength(1) - 1, nClasses, out info, out w);
if (info < 1)
{
throw new ArgumentException("Error in calculation of linear discriminant analysis solution");
}
var nFactorCoeff = factorMatrix.GetLength(1);
var tree = LinearModelToTreeConverter.CreateTree(factorVariables, w.Take(nFactorCoeff).ToArray(),
doubleVariableNames, w.Skip(nFactorCoeff).Take(doubleVariableNames.Length).ToArray());
var model = CreateDiscriminantFunctionModel(tree, new SymbolicDataAnalysisExpressionTreeLinearInterpreter(), problemData, rows);
SymbolicDiscriminantFunctionClassificationSolution solution = new SymbolicDiscriminantFunctionClassificationSolution(model, (IClassificationProblemData)problemData.Clone());
return(solution);
}
private void FormatModelSheet(ExcelWorksheet modelWorksheet, SymbolicDiscriminantFunctionClassificationSolution solution, IEnumerable <string> formulaParts)
{
int row = 1;
modelWorksheet.Cells[row, 1].Value = "Model";
modelWorksheet.Cells[row, 2].Value = solution.Name;
foreach (var part in formulaParts)
{
modelWorksheet.Cells[row, 4].Value = part;
row++;
}
row = 2;
modelWorksheet.Cells[row, 1].Value = "Model Depth";
modelWorksheet.Cells[row, 2].Value = solution.Model.SymbolicExpressionTree.Depth;
row++;
modelWorksheet.Cells[row, 1].Value = "Model Length";
modelWorksheet.Cells[row, 2].Value = solution.Model.SymbolicExpressionTree.Length;
row += 2;
var thresholds = solution.Model.Thresholds.ToList();
// skip first (-inf) and last (+inf) thresholds
for (int i = 0; i < thresholds.Count; ++i)
{
if (double.IsInfinity(thresholds[i]) || double.IsNaN(thresholds[i]))
{
continue;
}
modelWorksheet.Cells[row, 1].Value = "Threshold " + i;
modelWorksheet.Cells[row, 2].Value = thresholds[i];
++row;
}
row++;
modelWorksheet.Cells[row, 1].Value = "Estimation Limits Lower";
modelWorksheet.Cells[row, 2].Value = Math.Max(solution.Model.LowerEstimationLimit, -9.99999999999999E+307); // minimal value supported by excel
modelWorksheet.Names.Add("EstimationLimitLower", modelWorksheet.Cells[row, 2]);
modelWorksheet.Cells[row, 2].Style.Numberformat.Format = "0.000E+00";
row++;
modelWorksheet.Cells[row, 1].Value = "Estimation Limits Upper";
modelWorksheet.Cells[row, 2].Value = Math.Min(solution.Model.UpperEstimationLimit, 9.99999999999999E+307); // maximal value supported by excel
modelWorksheet.Names.Add("EstimationLimitUpper", modelWorksheet.Cells[row, 2]);
modelWorksheet.Cells[row, 2].Style.Numberformat.Format = "0.000E+00";
row += 2;
modelWorksheet.Cells[row, 1].Value = "Trainings Partition Start";
modelWorksheet.Cells[row, 2].Value = solution.ProblemData.TrainingPartition.Start;
modelWorksheet.Names.Add(TRAININGSTART, modelWorksheet.Cells[row, 2]);
row++;
modelWorksheet.Cells[row, 1].Value = "Trainings Partition End";
modelWorksheet.Cells[row, 2].Value = solution.ProblemData.TrainingPartition.End;
modelWorksheet.Names.Add(TRAININGEND, modelWorksheet.Cells[row, 2]);
row++;
modelWorksheet.Cells[row, 1].Value = "Test Partition Start";
modelWorksheet.Cells[row, 2].Value = solution.ProblemData.TestPartition.Start;
modelWorksheet.Names.Add(TESTSTART, modelWorksheet.Cells[row, 2]);
row++;
modelWorksheet.Cells[row, 1].Value = "Test Partition End";
modelWorksheet.Cells[row, 2].Value = solution.ProblemData.TestPartition.End;
modelWorksheet.Names.Add(TESTEND, modelWorksheet.Cells[row, 2]);
row += 2;
string excelTrainingTarget = Indirect("B", true);
string excelTrainingEstimated = Indirect("C", true);
string excelTrainingClassValues = Indirect("D", true);
string excelTrainingMeanError = Indirect("F", true);
string excelTrainingMSE = Indirect("G", true);
string excelTestTarget = Indirect("B", false);
string excelTestEstimated = Indirect("C", false);
string excelTestClassValues = Indirect("D", false);
string excelTestMeanError = Indirect("F", false);
string excelTestMSE = Indirect("G", false);
modelWorksheet.Cells[row, 1].Value = "Accuracy (training)";
modelWorksheet.Cells[row, 2].Formula = string.Format("SUMPRODUCT(({0}={1})*1)/COUNT({0})", excelTrainingClassValues, excelTrainingTarget);
modelWorksheet.Cells[row, 2].Style.Numberformat.Format = "0.000";
row++;
modelWorksheet.Cells[row, 1].Value = "Accuracy (test)";
modelWorksheet.Cells[row, 2].Formula = string.Format("SUMPRODUCT(({0}={1})*1)/COUNT({0})", excelTestClassValues, excelTestTarget);
modelWorksheet.Cells[row, 2].Style.Numberformat.Format = "0.000";
row++;
modelWorksheet.Cells[row, 1].Value = "Pearson's R² (training)";
modelWorksheet.Cells[row, 2].Formula = string.Format("POWER(PEARSON({0},{1}),2)", excelTrainingTarget, excelTrainingEstimated);
modelWorksheet.Cells[row, 2].Style.Numberformat.Format = "0.000";
row++;
modelWorksheet.Cells[row, 1].Value = "Pearson's R² (test)";
modelWorksheet.Cells[row, 2].Formula = string.Format("POWER(PEARSON({0},{1}),2)", excelTestTarget, excelTestEstimated);
modelWorksheet.Cells[row, 2].Style.Numberformat.Format = "0.000";
row++;
modelWorksheet.Cells[row, 1].Value = "Mean Squared Error (training)";
modelWorksheet.Cells[row, 2].Formula = string.Format("AVERAGE({0})", excelTrainingMSE);
modelWorksheet.Names.Add("TrainingMSE", modelWorksheet.Cells[row, 2]);
modelWorksheet.Cells[row, 2].Style.Numberformat.Format = "0.000E+00";
row++;
modelWorksheet.Cells[row, 1].Value = "Mean Squared Error (test)";
modelWorksheet.Cells[row, 2].Formula = string.Format("AVERAGE({0})", excelTestMSE);
modelWorksheet.Names.Add("TestMSE", modelWorksheet.Cells[row, 2]);
modelWorksheet.Cells[row, 2].Style.Numberformat.Format = "0.000E+00";
row++;
modelWorksheet.Cells[row, 1].Value = "Normalized Gini Coefficient (training)";
modelWorksheet.Cells[row, 2].Formula = string.Format("AVERAGE({0})", excelTrainingMeanError);
modelWorksheet.Cells[row, 2].Style.Numberformat.Format = "0.000E+00";
row++;
modelWorksheet.Cells[row, 1].Value = "Normalized Gini Coefficient (test)";
modelWorksheet.Cells[row, 2].Formula = string.Format("AVERAGE({0})", excelTestMeanError);
modelWorksheet.Cells[row, 2].Style.Numberformat.Format = "0.000E+00";
row++;
modelWorksheet.Cells["A1:B" + row].AutoFitColumns();
AddModelTreePicture(modelWorksheet, solution.Model);
}
private void WriteEstimatedWorksheet(ExcelWorksheet estimatedWorksheet, ExcelWorksheet datasetWorksheet, string[] formulaParts, SymbolicDiscriminantFunctionClassificationSolution solution)
{
string preparedFormula = PrepareFormula(formulaParts);
int rows = solution.ProblemData.Dataset.Rows;
estimatedWorksheet.Cells[1, 1].Value = "Id"; // A
estimatedWorksheet.Cells[1, 2].Value = "Target Variable"; // B
estimatedWorksheet.Cells[1, 3].Value = "Estimated Values"; // C
estimatedWorksheet.Cells[1, 4].Value = "Estimated Class Values"; // D
estimatedWorksheet.Cells[1, 6].Value = "Error"; // F
estimatedWorksheet.Cells[1, 7].Value = "Squared Error"; // G
estimatedWorksheet.Cells[1, 9].Value = "Unbounded Estimated Values"; // I
estimatedWorksheet.Cells[1, 10].Value = "Bounded Estimated Values"; // J
estimatedWorksheet.Cells[1, 11].Value = "Random Key"; // K
var thresholds = solution.Model.Thresholds.Where(x => !double.IsInfinity(x)).ToList();
var thresholdsFormula = GenerateThresholdsFormula(thresholds);
const int columnIndex = 13; // index of beginning columns for class values
for (int i = 0; i <= thresholds.Count; ++i)
{
estimatedWorksheet.Cells[1, i + columnIndex].Value = "Class " + i;
if (i < thresholds.Count)
{
estimatedWorksheet.Cells[1, i + columnIndex + thresholds.Count + 1].Value = "Threshold " + i;
}
}
estimatedWorksheet.Cells[1, 1, 1, 10].AutoFitColumns();
int targetIndex = solution.ProblemData.Dataset.VariableNames.ToList().FindIndex(x => x.Equals(solution.ProblemData.TargetVariable)) + 1;
for (int i = 0; i < rows; i++)
{
estimatedWorksheet.Cells[i + 2, 1].Value = i;
estimatedWorksheet.Cells[i + 2, 2].Formula = datasetWorksheet.Cells[i + 2, targetIndex].FullAddress; // target values
estimatedWorksheet.Cells[i + 2, 9].Formula = string.Format(preparedFormula, i + 2); // formula (estimated) values
string condition = string.Empty;
string rowRef = "C" + (i + 2);
int nClasses = thresholds.Count + 1;
for (int j = columnIndex; j < columnIndex + nClasses; ++j)
{
int idx = j - columnIndex + 5; // row index for the threshold values
if (j == columnIndex)
{
condition = rowRef + " < Model!$B$" + idx;
}
else if (j > columnIndex && j < columnIndex + thresholds.Count)
{
condition = "AND(" + rowRef + "> Model!$B$" + (idx - 1) + ", " + rowRef + " < Model!$B$" + idx + ")";
}
else if (j == columnIndex + thresholds.Count)
{
condition = rowRef + " > Model!$B$" + (idx - 1);
}
estimatedWorksheet.Cells[i + 2, j].Formula = "IF(" + condition + ", " + rowRef + ", #N/A)";
if (j < columnIndex + thresholds.Count)
{
estimatedWorksheet.Cells[i + 2, j + nClasses].Formula = "Model!$B$" + idx;
}
}
}
estimatedWorksheet.Cells["B2:B" + (rows + 1)].Style.Numberformat.Format = "0.000";
estimatedWorksheet.Cells["C2:C" + (rows + 1)].Formula = "J2";
estimatedWorksheet.Cells["C2:C" + (rows + 1)].Style.Numberformat.Format = "0.000";
estimatedWorksheet.Cells["D2:D" + (rows + 1)].Formula = thresholdsFormula;
estimatedWorksheet.Cells["D2:D" + (rows + 1)].Style.Numberformat.Format = "0.0";
estimatedWorksheet.Cells["F2:F" + (rows + 1)].Formula = "B2 - C2";
estimatedWorksheet.Cells["F2:F" + (rows + 1)].Style.Numberformat.Format = "0.0";
estimatedWorksheet.Cells["G2:G" + (rows + 1)].Formula = "POWER(F2, 2)";
estimatedWorksheet.Cells["G2:G" + (rows + 1)].Style.Numberformat.Format = "0.000";
estimatedWorksheet.Cells["I2:I" + (rows + 1)].Style.Numberformat.Format = "0.000";
estimatedWorksheet.Cells["J2:J" + (rows + 1)].Formula = "IFERROR(IF(I2 > Model!EstimationLimitUpper, Model!EstimationLimitUpper, IF(I2 < Model!EstimationLimitLower, Model!EstimationLimitLower, I2)), AVERAGE(Model!EstimationLimitLower, Model!EstimationLimitUpper))";
estimatedWorksheet.Cells["J2:J" + (rows + 1)].Style.Numberformat.Format = "0.000";
estimatedWorksheet.Cells["K2:K" + (rows + 1)].Formula = "RAND()";
estimatedWorksheet.Cells["K2:K" + (rows + 1)].Style.Numberformat.Format = "0.000";
}
private void FormatModelSheet(ExcelWorksheet modelWorksheet, SymbolicDiscriminantFunctionClassificationSolution solution, IEnumerable<string> formulaParts) {
int row = 1;
modelWorksheet.Cells[row, 1].Value = "Model";
modelWorksheet.Cells[row, 2].Value = solution.Name;
foreach (var part in formulaParts) {
modelWorksheet.Cells[row, 4].Value = part;
row++;
}
row = 2;
modelWorksheet.Cells[row, 1].Value = "Model Depth";
modelWorksheet.Cells[row, 2].Value = solution.Model.SymbolicExpressionTree.Depth;
row++;
modelWorksheet.Cells[row, 1].Value = "Model Length";
modelWorksheet.Cells[row, 2].Value = solution.Model.SymbolicExpressionTree.Length;
row += 2;
var thresholds = solution.Model.Thresholds.ToList();
// skip first (-inf) and last (+inf) thresholds
for (int i = 0; i < thresholds.Count; ++i) {
if (double.IsInfinity(thresholds[i]) || double.IsNaN(thresholds[i]))
continue;
modelWorksheet.Cells[row, 1].Value = "Threshold " + i;
modelWorksheet.Cells[row, 2].Value = thresholds[i];
++row;
}
row++;
modelWorksheet.Cells[row, 1].Value = "Estimation Limits Lower";
modelWorksheet.Cells[row, 2].Value = Math.Max(solution.Model.LowerEstimationLimit, -9.99999999999999E+307); // minimal value supported by excel
modelWorksheet.Names.Add("EstimationLimitLower", modelWorksheet.Cells[row, 2]);
modelWorksheet.Cells[row, 2].Style.Numberformat.Format = "0.000E+00";
row++;
modelWorksheet.Cells[row, 1].Value = "Estimation Limits Upper";
modelWorksheet.Cells[row, 2].Value = Math.Min(solution.Model.UpperEstimationLimit, 9.99999999999999E+307); // maximal value supported by excel
modelWorksheet.Names.Add("EstimationLimitUpper", modelWorksheet.Cells[row, 2]);
modelWorksheet.Cells[row, 2].Style.Numberformat.Format = "0.000E+00";
row += 2;
modelWorksheet.Cells[row, 1].Value = "Trainings Partition Start";
modelWorksheet.Cells[row, 2].Value = solution.ProblemData.TrainingPartition.Start;
modelWorksheet.Names.Add(TRAININGSTART, modelWorksheet.Cells[row, 2]);
row++;
modelWorksheet.Cells[row, 1].Value = "Trainings Partition End";
modelWorksheet.Cells[row, 2].Value = solution.ProblemData.TrainingPartition.End;
modelWorksheet.Names.Add(TRAININGEND, modelWorksheet.Cells[row, 2]);
row++;
modelWorksheet.Cells[row, 1].Value = "Test Partition Start";
modelWorksheet.Cells[row, 2].Value = solution.ProblemData.TestPartition.Start;
modelWorksheet.Names.Add(TESTSTART, modelWorksheet.Cells[row, 2]);
row++;
modelWorksheet.Cells[row, 1].Value = "Test Partition End";
modelWorksheet.Cells[row, 2].Value = solution.ProblemData.TestPartition.End;
modelWorksheet.Names.Add(TESTEND, modelWorksheet.Cells[row, 2]);
row += 2;
string excelTrainingTarget = Indirect("B", true);
string excelTrainingEstimated = Indirect("C", true);
string excelTrainingClassValues = Indirect("D", true);
string excelTrainingMeanError = Indirect("F", true);
string excelTrainingMSE = Indirect("G", true);
string excelTestTarget = Indirect("B", false);
string excelTestEstimated = Indirect("C", false);
string excelTestClassValues = Indirect("D", false);
string excelTestMeanError = Indirect("F", false);
string excelTestMSE = Indirect("G", false);
modelWorksheet.Cells[row, 1].Value = "Accuracy (training)";
modelWorksheet.Cells[row, 2].Formula = string.Format("SUMPRODUCT(({0}={1})*1)/COUNT({0})", excelTrainingClassValues, excelTrainingTarget);
modelWorksheet.Cells[row, 2].Style.Numberformat.Format = "0.000";
row++;
modelWorksheet.Cells[row, 1].Value = "Accuracy (test)";
modelWorksheet.Cells[row, 2].Formula = string.Format("SUMPRODUCT(({0}={1})*1)/COUNT({0})", excelTestClassValues, excelTestTarget);
modelWorksheet.Cells[row, 2].Style.Numberformat.Format = "0.000";
row++;
modelWorksheet.Cells[row, 1].Value = "Pearson's R² (training)";
modelWorksheet.Cells[row, 2].Formula = string.Format("POWER(PEARSON({0},{1}),2)", excelTrainingTarget, excelTrainingEstimated);
modelWorksheet.Cells[row, 2].Style.Numberformat.Format = "0.000";
row++;
modelWorksheet.Cells[row, 1].Value = "Pearson's R² (test)";
modelWorksheet.Cells[row, 2].Formula = string.Format("POWER(PEARSON({0},{1}),2)", excelTestTarget, excelTestEstimated);
modelWorksheet.Cells[row, 2].Style.Numberformat.Format = "0.000";
row++;
modelWorksheet.Cells[row, 1].Value = "Mean Squared Error (training)";
modelWorksheet.Cells[row, 2].Formula = string.Format("AVERAGE({0})", excelTrainingMSE);
modelWorksheet.Names.Add("TrainingMSE", modelWorksheet.Cells[row, 2]);
modelWorksheet.Cells[row, 2].Style.Numberformat.Format = "0.000E+00";
row++;
modelWorksheet.Cells[row, 1].Value = "Mean Squared Error (test)";
modelWorksheet.Cells[row, 2].Formula = string.Format("AVERAGE({0})", excelTestMSE);
modelWorksheet.Names.Add("TestMSE", modelWorksheet.Cells[row, 2]);
modelWorksheet.Cells[row, 2].Style.Numberformat.Format = "0.000E+00";
row++;
modelWorksheet.Cells[row, 1].Value = "Normalized Gini Coefficient (training)";
modelWorksheet.Cells[row, 2].Formula = string.Format("AVERAGE({0})", excelTrainingMeanError);
modelWorksheet.Cells[row, 2].Style.Numberformat.Format = "0.000E+00";
row++;
modelWorksheet.Cells[row, 1].Value = "Normalized Gini Coefficient (test)";
modelWorksheet.Cells[row, 2].Formula = string.Format("AVERAGE({0})", excelTestMeanError);
modelWorksheet.Cells[row, 2].Style.Numberformat.Format = "0.000E+00";
row++;
modelWorksheet.Cells["A1:B" + row].AutoFitColumns();
AddModelTreePicture(modelWorksheet, solution.Model);
}
private void WriteEstimatedWorksheet(ExcelWorksheet estimatedWorksheet, ExcelWorksheet datasetWorksheet, string[] formulaParts, SymbolicDiscriminantFunctionClassificationSolution solution) {
string preparedFormula = PrepareFormula(formulaParts);
int rows = solution.ProblemData.Dataset.Rows;
estimatedWorksheet.Cells[1, 1].Value = "Id"; // A
estimatedWorksheet.Cells[1, 2].Value = "Target Variable"; // B
estimatedWorksheet.Cells[1, 3].Value = "Estimated Values"; // C
estimatedWorksheet.Cells[1, 4].Value = "Estimated Class Values"; // D
estimatedWorksheet.Cells[1, 6].Value = "Error"; // F
estimatedWorksheet.Cells[1, 7].Value = "Squared Error"; // G
estimatedWorksheet.Cells[1, 9].Value = "Unbounded Estimated Values"; // I
estimatedWorksheet.Cells[1, 10].Value = "Bounded Estimated Values"; // J
estimatedWorksheet.Cells[1, 11].Value = "Random Key"; // K
var thresholds = solution.Model.Thresholds.Where(x => !double.IsInfinity(x)).ToList();
var thresholdsFormula = GenerateThresholdsFormula(thresholds);
const int columnIndex = 13; // index of beginning columns for class values
for (int i = 0; i <= thresholds.Count; ++i) {
estimatedWorksheet.Cells[1, i + columnIndex].Value = "Class " + i;
if (i < thresholds.Count)
estimatedWorksheet.Cells[1, i + columnIndex + thresholds.Count + 1].Value = "Threshold " + i;
}
estimatedWorksheet.Cells[1, 1, 1, 10].AutoFitColumns();
int targetIndex = solution.ProblemData.Dataset.VariableNames.ToList().FindIndex(x => x.Equals(solution.ProblemData.TargetVariable)) + 1;
for (int i = 0; i < rows; i++) {
estimatedWorksheet.Cells[i + 2, 1].Value = i;
estimatedWorksheet.Cells[i + 2, 2].Formula = datasetWorksheet.Cells[i + 2, targetIndex].FullAddress; // target values
estimatedWorksheet.Cells[i + 2, 9].Formula = string.Format(preparedFormula, i + 2); // formula (estimated) values
string condition = string.Empty;
string rowRef = "C" + (i + 2);
int nClasses = thresholds.Count + 1;
for (int j = columnIndex; j < columnIndex + nClasses; ++j) {
int idx = j - columnIndex + 5; // row index for the threshold values
if (j == columnIndex) {
condition = rowRef + " < Model!$B$" + idx;
} else if (j > columnIndex && j < columnIndex + thresholds.Count) {
condition = "AND(" + rowRef + "> Model!$B$" + (idx - 1) + ", " + rowRef + " < Model!$B$" + idx + ")";
} else if (j == columnIndex + thresholds.Count) {
condition = rowRef + " > Model!$B$" + (idx - 1);
}
estimatedWorksheet.Cells[i + 2, j].Formula = "IF(" + condition + ", " + rowRef + ", #N/A)";
if (j < columnIndex + thresholds.Count)
estimatedWorksheet.Cells[i + 2, j + nClasses].Formula = "Model!$B$" + idx;
}
}
estimatedWorksheet.Cells["B2:B" + (rows + 1)].Style.Numberformat.Format = "0.000";
estimatedWorksheet.Cells["C2:C" + (rows + 1)].Formula = "J2";
estimatedWorksheet.Cells["C2:C" + (rows + 1)].Style.Numberformat.Format = "0.000";
estimatedWorksheet.Cells["D2:D" + (rows + 1)].Formula = thresholdsFormula;
estimatedWorksheet.Cells["D2:D" + (rows + 1)].Style.Numberformat.Format = "0.0";
estimatedWorksheet.Cells["F2:F" + (rows + 1)].Formula = "B2 - C2";
estimatedWorksheet.Cells["F2:F" + (rows + 1)].Style.Numberformat.Format = "0.0";
estimatedWorksheet.Cells["G2:G" + (rows + 1)].Formula = "POWER(F2, 2)";
estimatedWorksheet.Cells["G2:G" + (rows + 1)].Style.Numberformat.Format = "0.000";
estimatedWorksheet.Cells["I2:I" + (rows + 1)].Style.Numberformat.Format = "0.000";
estimatedWorksheet.Cells["J2:J" + (rows + 1)].Formula = "IFERROR(IF(I2 > Model!EstimationLimitUpper, Model!EstimationLimitUpper, IF(I2 < Model!EstimationLimitLower, Model!EstimationLimitLower, I2)), AVERAGE(Model!EstimationLimitLower, Model!EstimationLimitUpper))";
estimatedWorksheet.Cells["J2:J" + (rows + 1)].Style.Numberformat.Format = "0.000";
estimatedWorksheet.Cells["K2:K" + (rows + 1)].Formula = "RAND()";
estimatedWorksheet.Cells["K2:K" + (rows + 1)].Style.Numberformat.Format = "0.000";
}
