Changeset 11541

Timestamp:

11/12/14 15:58:32 (10 years ago)

Author:

bburlacu

Message:

#2098: Updated SymbolicDiscriminantFunctionClassificationSolutionExcelExporter (added the missing functionality).

Location:

trunk/sources

Files:

• HeuristicLab.Problems.DataAnalysis.Symbolic.Classification.Views/3.4/SymbolicDiscriminantFunctionClassificationSolutionExcelExporter.cs (modified) (6 diffs)
• HeuristicLab.Problems.DataAnalysis.Symbolic.Views/3.4/Exporters/SymbolicSolutionExcelExporter.cs (modified) (5 diffs)

trunk/sources/HeuristicLab.Problems.DataAnalysis.Symbolic.Classification.Views/3.4/SymbolicDiscriminantFunctionClassificationSolutionExcelExporter.cs

@@ -6 +6 @@
 using HeuristicLab.Problems.DataAnalysis.Symbolic.Views;
 using OfficeOpenXml;
+using OfficeOpenXml.Drawing.Chart;
 
 namespace HeuristicLab.Problems.DataAnalysis.Symbolic.Classification.Views {
   public class SymbolicDiscriminantFunctionClassificationSolutionExcelExporter : SymbolicSolutionExcelExporter {
-    protected override void ExportChart(string fileName, ISymbolicDataAnalysisSolution solution, string formula) {
-      ExportChart(fileName, solution as ISymbolicClassificationSolution, formula);
-    }
-
-    private void ExportChart(string fileName, ISymbolicClassificationSolution solution, string formula) {
+    public override void Export(IDataAnalysisSolution solution, string fileName) {
+      var symbDiscriminantSolution = solution as SymbolicDiscriminantFunctionClassificationSolution;
+      if (symbDiscriminantSolution == null) throw new NotSupportedException("This solution cannot be exported to Excel");
+      var formatter = new SymbolicDataAnalysisExpressionExcelFormatter();
+      var formula = formatter.Format(symbDiscriminantSolution.Model.SymbolicExpressionTree, solution.ProblemData.Dataset);
+      ExportChart(fileName, symbDiscriminantSolution, formula);
+    }
+
+    private void ExportChart(string fileName, SymbolicDiscriminantFunctionClassificationSolution solution, string formula) {
       FileInfo newFile = new FileInfo(fileName);
       if (newFile.Exists) {
@@ -35 +40 @@
 
         ExcelWorksheet chartsWorksheet = package.Workbook.Worksheets.Add("Charts");
-        AddCharts(chartsWorksheet);
+        AddCharts(chartsWorksheet, solution);
         package.Workbook.Properties.Title = "Excel Export";
         package.Workbook.Properties.Author = "HEAL";
@@ -42 +47 @@
         package.Save();
       }
-    }
-
-    protected void WriteEstimatedWorksheet(ExcelWorksheet estimatedWorksheet, ExcelWorksheet datasetWorksheet, string[] formulaParts, ISymbolicDataAnalysisSolution solution) {
-      WriteEstimatedWorksheet(estimatedWorksheet, datasetWorksheet, formulaParts, solution as SymbolicDiscriminantFunctionClassificationSolution);
-    }
-
-    protected override void FormatModelSheet(ExcelWorksheet modelWorksheet, ISymbolicDataAnalysisSolution solution, IEnumerable<string> formulaParts) {
-      FormatModelSheet(modelWorksheet, solution as SymbolicDiscriminantFunctionClassificationSolution, formulaParts);
     }
 
@@ -180 +177 @@
       estimatedWorksheet.Cells[1, 3].Value = "Estimated Values"; // C
       estimatedWorksheet.Cells[1, 4].Value = "Estimated Class Values"; // D
-      estimatedWorksheet.Cells[1, 6].Value = "Error"; // E
+      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, 1, 1, 10].AutoFitColumns();
+      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;
@@ -195 +199 @@
         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";
@@ -213 +236 @@
       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 AddCharts(ExcelWorksheet chartsWorksheet, DiscriminantFunctionClassificationSolution solution) {
+      List<char> columns = new List<char> { 'M', 'N', 'O', 'P', 'Q', 'R', 'S', 'T', 'U', 'V', 'W', 'X', 'Y', 'Z' };
+      var scatterPlot = chartsWorksheet.Drawings.AddChart("scatterPlot", eChartType.XYScatter);
+      scatterPlot.SetSize(800, 400);
+      scatterPlot.SetPosition(0, 0);
+      scatterPlot.Title.Text = "Scatter Plot";
+      var thresholds = solution.Model.Thresholds.Where(x => !double.IsInfinity(x)).ToList();
+      chartsWorksheet.Names.AddFormula("XKey", "OFFSET('Estimated Values'!$K$1,1,0, COUNTA('Estimated Values'!$K:$K)-1)");
+
+      for (int i = 0; i <= thresholds.Count; ++i) {
+        string header = "Class" + i;
+        chartsWorksheet.Names.AddFormula(header, String.Format("OFFSET('Estimated Values'!${0}$1, 1, 0, COUNTA('Estimated Values'!${0}:${0})-1)", columns[i]));
+        var series = scatterPlot.Series.Add(header, "XKey");
+        series.Header = header;
+
+        if (i < thresholds.Count) {
+          chartsWorksheet.Names.AddFormula("Threshold" + i, String.Format("OFFSET('Estimated Values'!${0}$1, 1, 0, COUNTA('Estimated Values'!${0}:${0})-1)", columns[i + thresholds.Count + 1]));
+          var s = scatterPlot.Series.Add("Threshold" + i, "XKey");
+          s.Header = "Threshold" + i;
+        }
+      }
     }
 
     // this method assumes that the thresholds list is sorted in ascending order
-    private string GenerateThresholdsFormula(List<double> thresholds) {
+    private static string GenerateThresholdsFormula(List<double> thresholds) {
       if (thresholds.Count == 1) {
         return String.Format("IF(J2 < {0}, 0, 1)", thresholds[0]);

trunk/sources/HeuristicLab.Problems.DataAnalysis.Symbolic.Views/3.4/Exporters/SymbolicSolutionExcelExporter.cs

@@ -44 +44 @@
     }
 
-    public void Export(IDataAnalysisSolution solution, string fileName) {
+    public virtual void Export(IDataAnalysisSolution solution, string fileName) {
       var symbSolution = solution as ISymbolicDataAnalysisSolution;
       if (symbSolution == null) throw new NotSupportedException("This solution cannot be exported to Excel");
@@ -52 +52 @@
     }
 
-    protected virtual void ExportChart(string fileName, ISymbolicDataAnalysisSolution solution, string formula) {
+    private void ExportChart(string fileName, ISymbolicDataAnalysisSolution solution, string formula) {
       FileInfo newFile = new FileInfo(fileName);
       if (newFile.Exists) {
@@ -85 +85 @@
     }
 
-    protected virtual void FormatModelSheet(ExcelWorksheet modelWorksheet, ISymbolicDataAnalysisSolution solution, IEnumerable<string> formulaParts) {
+    private void FormatModelSheet(ExcelWorksheet modelWorksheet, ISymbolicDataAnalysisSolution solution, IEnumerable<string> formulaParts) {
       int row = 1;
       modelWorksheet.Cells[row, 1].Value = "Model";
@@ -224 +224 @@
     }
 
-    protected virtual void AddCharts(ExcelWorksheet chartsWorksheet) {
+    private void AddCharts(ExcelWorksheet chartsWorksheet) {
       chartsWorksheet.Names.AddFormula("AllId", "OFFSET('Estimated Values'!$A$1,1,0, COUNTA('Estimated Values'!$A:$A)-1)");
       chartsWorksheet.Names.AddFormula("AllTarget", "OFFSET('Estimated Values'!$B$1,1,0, COUNTA('Estimated Values'!$B:$B)-1)");
@@ -260 +260 @@
     }
 
-    protected virtual void AddModelTreePicture(ExcelWorksheet modelWorksheet, ISymbolicDataAnalysisModel model) {
+    protected void AddModelTreePicture(ExcelWorksheet modelWorksheet, ISymbolicDataAnalysisModel model) {
       SymbolicExpressionTreeChart modelTreePicture = new SymbolicExpressionTreeChart();
       modelTreePicture.Tree = model.SymbolicExpressionTree;