Changeset 11923

Timestamp:

02/05/15 18:51:07 (9 years ago)

Author:

mkommend

Message:

#2098: Merged r11523, r11524, r11525, r11541, r11827 and r11828 into stable.

Location:

stable

Files:

• . (modified) (1 prop)
• HeuristicLab.Problems.DataAnalysis.Symbolic (modified) (1 prop)
• HeuristicLab.Problems.DataAnalysis.Symbolic.Classification.Views (modified) (1 prop)
• HeuristicLab.Problems.DataAnalysis.Symbolic.Classification.Views/3.4/HeuristicLab.Problems.DataAnalysis.Symbolic.Classification.Views-3.4.csproj (modified) (2 diffs)
• HeuristicLab.Problems.DataAnalysis.Symbolic.Classification.Views/3.4/Plugin.cs.frame (modified) (1 diff)
• HeuristicLab.Problems.DataAnalysis.Symbolic.Classification.Views/3.4/SymbolicDiscriminantFunctionClassificationSolutionExcelExporter.cs (copied) (copied from trunk/sources/HeuristicLab.Problems.DataAnalysis.Symbolic.Classification.Views/3.4/SymbolicDiscriminantFunctionClassificationSolutionExcelExporter.cs) (7 diffs)
• HeuristicLab.Problems.DataAnalysis.Symbolic.Classification.Views/3.4/SymbolicDiscriminantFunctionClassificationSolutionView.cs (modified) (2 diffs)
• HeuristicLab.Problems.DataAnalysis.Symbolic.Views (modified) (1 prop)
• HeuristicLab.Problems.DataAnalysis.Symbolic.Views/3.4/Exporters/SymbolicSolutionExcelExporter.cs (modified) (8 diffs)
• HeuristicLab.Problems.DataAnalysis.Symbolic/3.4/Formatters/SymbolicDataAnalysisExpressionExcelFormatter.cs (modified) (1 diff)

stable/HeuristicLab.Problems.DataAnalysis.Symbolic.Classification.Views/3.4/HeuristicLab.Problems.DataAnalysis.Symbolic.Classification.Views-3.4.csproj

@@ -99 +99 @@
   </PropertyGroup>
   <ItemGroup>
+    <Reference Include="EPPlus-3.1.3, Version=3.1.3.0, Culture=neutral, PublicKeyToken=ea159fdaa78159a1, processorArchitecture=MSIL">
+      <SpecificVersion>False</SpecificVersion>
+      <HintPath>..\..\bin\EPPlus-3.1.3.dll</HintPath>
+      <Private>False</Private>
+    </Reference>
     <Reference Include="System" />
     <Reference Include="System.Core">
@@ -122 +127 @@
       <DependentUpon>InteractiveSymbolicClassificationSolutionSimplifierView.cs</DependentUpon>
     </Compile>
+    <Compile Include="SymbolicDiscriminantFunctionClassificationSolutionExcelExporter.cs" />
     <Compile Include="SymbolicClassificationSolutionView.cs">
       <SubType>UserControl</SubType>

stable/HeuristicLab.Problems.DataAnalysis.Symbolic.Classification.Views/3.4/Plugin.cs.frame

@@ -32 +32 @@
   [PluginDependency("HeuristicLab.Core", "3.3")]
   [PluginDependency("HeuristicLab.Core.Views", "3.3")]
+  [PluginDependency("HeuristicLab.Data", "3.3")]
   [PluginDependency("HeuristicLab.Encodings.SymbolicExpressionTreeEncoding", "3.4")]
+  [PluginDependency("HeuristicLab.EPPlus", "3.1")]
   [PluginDependency("HeuristicLab.MainForm", "3.3")]
   [PluginDependency("HeuristicLab.MainForm.WindowsForms", "3.3")]

stable/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";
@@ -204 +227 @@
       estimatedWorksheet.Cells["D2:D" + (rows + 1)].Style.Numberformat.Format = "0.0";
 
-      estimatedWorksheet.Cells["F2:F" + (rows + 1)].Formula = "B2 - D2";
+      estimatedWorksheet.Cells["F2:F" + (rows + 1)].Formula = "B2 - C2";
       estimatedWorksheet.Cells["F2:F" + (rows + 1)].Style.Numberformat.Format = "0.0";
 
@@ -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]);

stable/HeuristicLab.Problems.DataAnalysis.Symbolic.Classification.Views/3.4/SymbolicDiscriminantFunctionClassificationSolutionView.cs

@@ -25 +25 @@
 using System.Windows.Forms;
 using HeuristicLab.MainForm;
-using HeuristicLab.Problems.DataAnalysis.Symbolic.Views;
 using HeuristicLab.Problems.DataAnalysis.Views;
 
@@ -54 +53 @@
 
     private void exportButton_Click(object sender, EventArgs e) {
-      var exporter = new SymbolicSolutionExcelExporter();
+      var exporter = new SymbolicDiscriminantFunctionClassificationSolutionExcelExporter();
       exportFileDialog.Filter = exporter.FileTypeFilter;
       if (exportFileDialog.ShowDialog(this) == DialogResult.OK) {

stable/HeuristicLab.Problems.DataAnalysis.Symbolic.Views/3.4/Exporters/SymbolicSolutionExcelExporter.cs

@@ -30 +30 @@
 namespace HeuristicLab.Problems.DataAnalysis.Symbolic.Views {
   public class SymbolicSolutionExcelExporter : IDataAnalysisSolutionExporter {
-    private const string TRAININGSTART = "TrainingStart";
-    private const string TRAININGEND = "TrainingEnd";
-    private const string TESTSTART = "TestStart";
-    private const string TESTEND = "TestEnd";
+    protected const string TRAININGSTART = "TrainingStart";
+    protected const string TRAININGEND = "TrainingEnd";
+    protected const string TESTSTART = "TestStart";
+    protected const string TESTEND = "TestEnd";
 
 
@@ -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");
@@ -216 +216 @@
     }
 
-    private string Indirect(string column, bool training) {
+    protected string Indirect(string column, bool training) {
       if (training) {
         return string.Format("INDIRECT(\"'Estimated Values'!{0}\"&{1}+2&\":{0}\"&{2}+1)", column, TRAININGSTART, TRAININGEND);
@@ -260 +260 @@
     }
 
-    private void AddModelTreePicture(ExcelWorksheet modelWorksheet, ISymbolicDataAnalysisModel model) {
+    protected void AddModelTreePicture(ExcelWorksheet modelWorksheet, ISymbolicDataAnalysisModel model) {
       SymbolicExpressionTreeChart modelTreePicture = new SymbolicExpressionTreeChart();
       modelTreePicture.Tree = model.SymbolicExpressionTree;
@@ -289 +289 @@
       estimatedWorksheet.Cells[1, 1, 1, 10].AutoFitColumns();
 
+      // fill in id, target variable and unbounded estimated values
       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;
-        estimatedWorksheet.Cells[i + 2, 9].Formula = string.Format(preparedFormula, i + 2);
+        estimatedWorksheet.Cells[i + 2, 1].Value = i; // id
+        estimatedWorksheet.Cells[i + 2, 2].Formula = datasetWorksheet.Cells[i + 2, targetIndex].FullAddress; // target variable
+        estimatedWorksheet.Cells[i + 2, 9].Formula = string.Format(preparedFormula, i + 2); // unbounded estimated values
       }
       estimatedWorksheet.Cells["B2:B" + (rows + 1)].Style.Numberformat.Format = "0.000";
@@ -313 +314 @@
     }
 
-    private string PrepareFormula(string[] formulaParts) {
+    protected string PrepareFormula(string[] formulaParts) {
       string preparedFormula = formulaParts[0];
       foreach (var part in formulaParts.Skip(2)) {
@@ -323 +324 @@
     }
 
-    private void WriteInputSheet(ExcelWorksheet inputsWorksheet, ExcelWorksheet datasetWorksheet, IEnumerable<string> list, Dataset dataset) {
+    protected void WriteInputSheet(ExcelWorksheet inputsWorksheet, ExcelWorksheet datasetWorksheet, IEnumerable<string> list, Dataset dataset) {
       //remark the performance of EPPlus drops dramatically 
       //if the data is not written row wise (from left to right) due the internal indices used.
@@ -337 +338 @@
     }
 
-    private void WriteDatasetToExcel(ExcelWorksheet datasetWorksheet, IDataAnalysisProblemData problemData) {
+    protected void WriteDatasetToExcel(ExcelWorksheet datasetWorksheet, IDataAnalysisProblemData problemData) {
       //remark the performance of EPPlus drops dramatically 
       //if the data is not written row wise (from left to right) due the internal indices used.

stable/HeuristicLab.Problems.DataAnalysis.Symbolic/3.4/Formatters/SymbolicDataAnalysisExpressionExcelFormatter.cs

@@ -203 +203 @@
         stringBuilder.Append(FormatRecursively(node.GetSubtree(1)));
         stringBuilder.Append(",0))");
+      } else if (symbol is IfThenElse) {
+        stringBuilder.Append("IF(");
+        stringBuilder.Append("(" + FormatRecursively(node.GetSubtree(0)) + " ) > 0");
+        stringBuilder.Append(",");
+        stringBuilder.Append(FormatRecursively(node.GetSubtree(1)));
+        stringBuilder.Append(",");
+        stringBuilder.Append(FormatRecursively(node.GetSubtree(2)));
+        stringBuilder.Append(")");
+      } else if (symbol is VariableCondition) {
+        VariableConditionTreeNode variableConditionTreeNode = node as VariableConditionTreeNode;
+        double threshold = variableConditionTreeNode.Threshold;
+        double slope = variableConditionTreeNode.Slope;
+        string p = "(1 / (1 + EXP(-" + slope.ToString(CultureInfo.InvariantCulture) + " * (" + GetColumnToVariableName(variableConditionTreeNode.VariableName) + "-" + threshold.ToString(CultureInfo.InvariantCulture) + "))))";
+        stringBuilder.Append("((");
+        stringBuilder.Append(FormatRecursively(node.GetSubtree(0)));
+        stringBuilder.Append("*");
+        stringBuilder.Append(p);
+        stringBuilder.Append(") + (");
+        stringBuilder.Append(FormatRecursively(node.GetSubtree(1)));
+        stringBuilder.Append("*(");
+        stringBuilder.Append("1 - " + p + ")");
+        stringBuilder.Append("))");
+      } else if (symbol is Xor) {
+        stringBuilder.Append("IF(");
+        stringBuilder.Append("XOR(");
+        stringBuilder.Append("(" + FormatRecursively(node.GetSubtree(0)) + ") > 0,");
+        stringBuilder.Append("(" + FormatRecursively(node.GetSubtree(1)) + ") > 0");
+        stringBuilder.Append("), 1.0, -1.0)");
+      } else if (symbol is Or) {
+        stringBuilder.Append("IF(");
+        stringBuilder.Append("OR(");
+        stringBuilder.Append("(" + FormatRecursively(node.GetSubtree(0)) + ") > 0,");
+        stringBuilder.Append("(" + FormatRecursively(node.GetSubtree(1)) + ") > 0");
+        stringBuilder.Append("), 1.0, -1.0)");
+      } else if (symbol is And) {
+        stringBuilder.Append("IF(");
+        stringBuilder.Append("AND(");
+        stringBuilder.Append("(" + FormatRecursively(node.GetSubtree(0)) + ") > 0,");
+        stringBuilder.Append("(" + FormatRecursively(node.GetSubtree(1)) + ") > 0");
+        stringBuilder.Append("), 1.0, -1.0)");
+      } else if (symbol is Not) {
+        stringBuilder.Append("IF(");
+        stringBuilder.Append(FormatRecursively(node.GetSubtree(0)));
+        stringBuilder.Append(" > 0, -1.0, 1.0)");
+      } else if (symbol is GreaterThan) {
+        stringBuilder.Append("IF((");
+        stringBuilder.Append(FormatRecursively(node.GetSubtree(0)));
+        stringBuilder.Append(") > (");
+        stringBuilder.Append(FormatRecursively(node.GetSubtree(1)));
+        stringBuilder.Append("), 1.0, -1.0)");
+      } else if (symbol is LessThan) {
+        stringBuilder.Append("IF((");
+        stringBuilder.Append(FormatRecursively(node.GetSubtree(0)));
+        stringBuilder.Append(") < (");
+        stringBuilder.Append(FormatRecursively(node.GetSubtree(1)));
+        stringBuilder.Append("), 1.0, -1.0)");
       } else {
         throw new NotImplementedException("Excel export of " + node.Symbol + " is not implemented.");