Free cookie consent management tool by TermsFeed Policy Generator

source: stable/HeuristicLab.Problems.DataAnalysis.Symbolic/3.4/Formatters/SymbolicDataAnalysisExpressionExcelFormatter.cs @ 17870

Last change on this file since 17870 was 17181, checked in by swagner, 5 years ago

#2875: Merged r17180 from trunk to stable
File size: 14.9 KB
Line 
1#region License Information
2/* HeuristicLab
3 * Copyright (C) Heuristic and Evolutionary Algorithms Laboratory (HEAL)
4 *
5 * This file is part of HeuristicLab.
6 *
7 * HeuristicLab is free software: you can redistribute it and/or modify
8 * it under the terms of the GNU General Public License as published by
9 * the Free Software Foundation, either version 3 of the License, or
10 * (at your option) any later version.
11 *
12 * HeuristicLab is distributed in the hope that it will be useful,
13 * but WITHOUT ANY WARRANTY; without even the implied warranty of
14 * MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the
15 * GNU General Public License for more details.
16 *
17 * You should have received a copy of the GNU General Public License
18 * along with HeuristicLab. If not, see <http://www.gnu.org/licenses/>.
19 */
20#endregion
21
22using System;
23using System.Collections.Generic;
24using System.Globalization;
25using System.Linq;
26using System.Text;
27using HEAL.Attic;
28using HeuristicLab.Common;
29using HeuristicLab.Core;
30using HeuristicLab.Encodings.SymbolicExpressionTreeEncoding;
31
32namespace HeuristicLab.Problems.DataAnalysis.Symbolic {
33  [Item("Excel String Formatter", "String formatter for string representations of symbolic data analysis expressions in Excel syntax.")]
34  [StorableType("46C46897-9C92-4CF1-81C9-700732700DD3")]
35  public sealed class SymbolicDataAnalysisExpressionExcelFormatter : NamedItem, ISymbolicExpressionTreeStringFormatter {
36    [StorableConstructor]
37    private SymbolicDataAnalysisExpressionExcelFormatter(StorableConstructorFlag _) : base(_) { }
38    private SymbolicDataAnalysisExpressionExcelFormatter(SymbolicDataAnalysisExpressionExcelFormatter original, Cloner cloner) : base(original, cloner) { }
39    public SymbolicDataAnalysisExpressionExcelFormatter()
40      : base() {
41      Name = ItemName;
42      Description = ItemDescription;
43    }
44    public override IDeepCloneable Clone(Cloner cloner) {
45      return new SymbolicDataAnalysisExpressionExcelFormatter(this, cloner);
46    }
47    private string GetExcelColumnName(int columnNumber) {
48      int dividend = columnNumber;
49      string columnName = String.Empty;
50
51      while (dividend > 0) {
52        int modulo = (dividend - 1) % 26;
53        columnName = System.Convert.ToChar(65 + modulo) + columnName;
54        dividend = (int)((dividend - modulo) / 26);
55      }
56
57      return columnName;
58    }
59
60    private readonly Dictionary<string, string> variableNameMapping = new Dictionary<string, string>();
61    private int currentVariableIndex = 0;
62    private string GetColumnToVariableName(string varName) {
63      if (!variableNameMapping.ContainsKey(varName)) {
64        currentVariableIndex++;
65        variableNameMapping.Add(varName, GetExcelColumnName(currentVariableIndex));
66      }
67      return string.Format("${0}1", variableNameMapping[varName]);
68    }
69    public string Format(ISymbolicExpressionTree symbolicExpressionTree) {
70      return Format(symbolicExpressionTree, null);
71    }
72
73
74    public string Format(ISymbolicExpressionTree symbolicExpressionTree, IDataset dataset) {
75      if (dataset != null)
76        return FormatWithMapping(symbolicExpressionTree, CalculateVariableMapping(symbolicExpressionTree, dataset));
77      else return FormatWithMapping(symbolicExpressionTree, new Dictionary<string, string>());
78    }
79
80    public string FormatWithMapping(ISymbolicExpressionTree symbolicExpressionTree, Dictionary<string, string> variableNameMapping) {
81      foreach (var kvp in variableNameMapping) this.variableNameMapping.Add(kvp.Key, kvp.Value);
82      var stringBuilder = new StringBuilder();
83
84      stringBuilder.Append("=");
85      stringBuilder.Append(FormatRecursively(symbolicExpressionTree.Root));
86
87      foreach (var variable in this.variableNameMapping) {
88        stringBuilder.AppendLine();
89        stringBuilder.Append(variable.Key + " = " + variable.Value);
90      }
91      return stringBuilder.ToString();
92    }
93
94    private Dictionary<string, string> CalculateVariableMapping(ISymbolicExpressionTree tree, IDataset dataset) {
95      var mapping = new Dictionary<string, string>();
96      int inputIndex = 0;
97      var usedVariables = tree.IterateNodesPrefix().OfType<IVariableTreeNode>().Select(v => v.VariableName).Distinct().ToArray();
98      foreach (var variable in dataset.VariableNames) {
99        if (!usedVariables.Contains(variable)) continue;
100        inputIndex++;
101        mapping[variable] = GetExcelColumnName(inputIndex);
102      }
103      return mapping;
104    }
105
106    private string FormatRecursively(ISymbolicExpressionTreeNode node) {
107      ISymbol symbol = node.Symbol;
108      StringBuilder stringBuilder = new StringBuilder();
109
110      if (symbol is ProgramRootSymbol) {
111        stringBuilder.AppendLine(FormatRecursively(node.GetSubtree(0)));
112      } else if (symbol is StartSymbol)
113        return FormatRecursively(node.GetSubtree(0));
114      else if (symbol is Addition) {
115        stringBuilder.Append("(");
116        for (int i = 0; i < node.SubtreeCount; i++) {
117          if (i > 0) stringBuilder.Append("+");
118          stringBuilder.Append(FormatRecursively(node.GetSubtree(i)));
119        }
120        stringBuilder.Append(")");
121      } else if (symbol is Absolute) {
122        stringBuilder.Append($"ABS({FormatRecursively(node.GetSubtree(0))})");
123      } else if (symbol is AnalyticQuotient) {
124        stringBuilder.Append($"({FormatRecursively(node.GetSubtree(0))}) / SQRT(1 + POWER({FormatRecursively(node.GetSubtree(1))}, 2))");
125      } else if (symbol is Average) {
126        stringBuilder.Append("(1/(");
127        stringBuilder.Append(node.SubtreeCount);
128        stringBuilder.Append(")*(");
129        for (int i = 0; i < node.SubtreeCount; i++) {
130          if (i > 0) stringBuilder.Append("+");
131          stringBuilder.Append("(");
132          stringBuilder.Append(FormatRecursively(node.GetSubtree(i)));
133          stringBuilder.Append(")");
134        }
135        stringBuilder.Append("))");
136      } else if (symbol is Constant) {
137        ConstantTreeNode constantTreeNode = node as ConstantTreeNode;
138        stringBuilder.Append(constantTreeNode.Value.ToString(CultureInfo.InvariantCulture));
139      } else if (symbol is Cosine) {
140        stringBuilder.Append("COS(");
141        stringBuilder.Append(FormatRecursively(node.GetSubtree(0)));
142        stringBuilder.Append(")");
143      } else if (symbol is Cube) {
144        stringBuilder.Append($"POWER({FormatRecursively(node.GetSubtree(0))}, 3)");
145      } else if (symbol is CubeRoot) {
146        var arg_expr = FormatRecursively(node.GetSubtree(0));
147        stringBuilder.Append($"IF({arg_expr} < 0, -POWER(-{arg_expr}, 1/3), POWER({arg_expr}, 1/3))");
148      } else if (symbol is Division) {
149        if (node.SubtreeCount == 1) {
150          stringBuilder.Append("1/(");
151          stringBuilder.Append(FormatRecursively(node.GetSubtree(0)));
152          stringBuilder.Append(")");
153        } else {
154          stringBuilder.Append(FormatRecursively(node.GetSubtree(0)));
155          stringBuilder.Append("/(");
156          for (int i = 1; i < node.SubtreeCount; i++) {
157            if (i > 1) stringBuilder.Append("*");
158            stringBuilder.Append(FormatRecursively(node.GetSubtree(i)));
159          }
160          stringBuilder.Append(")");
161        }
162      } else if (symbol is Exponential) {
163        stringBuilder.Append("EXP(");
164        stringBuilder.Append(FormatRecursively(node.GetSubtree(0)));
165        stringBuilder.Append(")");
166      } else if (symbol is Square) {
167        stringBuilder.Append("POWER(");
168        stringBuilder.Append(FormatRecursively(node.GetSubtree(0)));
169        stringBuilder.Append(",2)");
170      } else if (symbol is SquareRoot) {
171        stringBuilder.Append("SQRT(");
172        stringBuilder.Append(FormatRecursively(node.GetSubtree(0)));
173        stringBuilder.Append(")");
174      } else if (symbol is Logarithm) {
175        stringBuilder.Append("LN(");
176        stringBuilder.Append(FormatRecursively(node.GetSubtree(0)));
177        stringBuilder.Append(")");
178      } else if (symbol is Multiplication) {
179        for (int i = 0; i < node.SubtreeCount; i++) {
180          if (i > 0) stringBuilder.Append("*");
181          stringBuilder.Append(FormatRecursively(node.GetSubtree(i)));
182        }
183      } else if (symbol is Sine) {
184        stringBuilder.Append("SIN(");
185        stringBuilder.Append(FormatRecursively(node.GetSubtree(0)));
186        stringBuilder.Append(")");
187      } else if (symbol is Subtraction) {
188        stringBuilder.Append("(");
189        if (node.SubtreeCount == 1) {
190          stringBuilder.Append("-");
191          stringBuilder.Append(FormatRecursively(node.GetSubtree(0)));
192        } else {
193          stringBuilder.Append(FormatRecursively(node.GetSubtree(0)));
194          for (int i = 1; i < node.SubtreeCount; i++) {
195            stringBuilder.Append("-");
196            stringBuilder.Append(FormatRecursively(node.GetSubtree(i)));
197          }
198        }
199        stringBuilder.Append(")");
200      } else if (symbol is Tangent) {
201        stringBuilder.Append("TAN(");
202        stringBuilder.Append(FormatRecursively(node.GetSubtree(0)));
203        stringBuilder.Append(")");
204      } else if (symbol is HyperbolicTangent) {
205        stringBuilder.Append("TANH(");
206        stringBuilder.Append(FormatRecursively(node.GetSubtree(0)));
207        stringBuilder.Append(")");
208      } else if (symbol is Variable) {
209        VariableTreeNode variableTreeNode = node as VariableTreeNode;
210        stringBuilder.Append(variableTreeNode.Weight.ToString(CultureInfo.InvariantCulture));
211        stringBuilder.Append("*");
212        stringBuilder.Append(GetColumnToVariableName(variableTreeNode.VariableName));
213      } else if (symbol is BinaryFactorVariable) {
214        var binFactorNode = node as BinaryFactorVariableTreeNode;
215        stringBuilder.AppendFormat("IF({0}=\"{1}\", {2}, 0)",
216          GetColumnToVariableName(binFactorNode.VariableName),
217          binFactorNode.VariableValue,
218          binFactorNode.Weight.ToString(CultureInfo.InvariantCulture)
219          );
220      } else if (symbol is FactorVariable) {
221        var factorNode = node as FactorVariableTreeNode;
222        var values = factorNode.Symbol.GetVariableValues(factorNode.VariableName).ToArray();
223        var w = factorNode.Weights;
224        // create nested if
225        for (int i = 0; i < values.Length; i++) {
226          stringBuilder.AppendFormat("IF({0}=\"{1}\", {2}, ",
227            GetColumnToVariableName(factorNode.VariableName),
228            values[i],
229            w[i].ToString(CultureInfo.InvariantCulture));
230        }
231        stringBuilder.Append("\"\""); // return empty string on unknown value
232        stringBuilder.Append(')', values.Length); // add closing parenthesis
233      } else if (symbol is Power) {
234        stringBuilder.Append("POWER(");
235        stringBuilder.Append(FormatRecursively(node.GetSubtree(0)));
236        stringBuilder.Append(",ROUND(");
237        stringBuilder.Append(FormatRecursively(node.GetSubtree(1)));
238        stringBuilder.Append(",0))");
239      } else if (symbol is Root) {
240        stringBuilder.Append("(");
241        stringBuilder.Append(FormatRecursively(node.GetSubtree(0)));
242        stringBuilder.Append(")^(1 / ROUND(");
243        stringBuilder.Append(FormatRecursively(node.GetSubtree(1)));
244        stringBuilder.Append(",0))");
245      } else if (symbol is IfThenElse) {
246        stringBuilder.Append("IF(");
247        stringBuilder.Append("(" + FormatRecursively(node.GetSubtree(0)) + " ) > 0");
248        stringBuilder.Append(",");
249        stringBuilder.Append(FormatRecursively(node.GetSubtree(1)));
250        stringBuilder.Append(",");
251        stringBuilder.Append(FormatRecursively(node.GetSubtree(2)));
252        stringBuilder.Append(")");
253      } else if (symbol is VariableCondition) {
254        VariableConditionTreeNode variableConditionTreeNode = node as VariableConditionTreeNode;
255        if (!variableConditionTreeNode.Symbol.IgnoreSlope) {
256          double threshold = variableConditionTreeNode.Threshold;
257          double slope = variableConditionTreeNode.Slope;
258          string p = "(1 / (1 + EXP(-" + slope.ToString(CultureInfo.InvariantCulture) + " * (" +
259                     GetColumnToVariableName(variableConditionTreeNode.VariableName) + "-" +
260                     threshold.ToString(CultureInfo.InvariantCulture) + "))))";
261          stringBuilder.Append("((");
262          stringBuilder.Append(FormatRecursively(node.GetSubtree(0)));
263          stringBuilder.Append("*");
264          stringBuilder.Append(p);
265          stringBuilder.Append(") + (");
266          stringBuilder.Append(FormatRecursively(node.GetSubtree(1)));
267          stringBuilder.Append("*(");
268          stringBuilder.Append("1 - " + p + ")");
269          stringBuilder.Append("))");
270        } else {
271          stringBuilder.AppendFormat(CultureInfo.InvariantCulture, "(IF({0} <= {1}, {2}, {3}))",
272            GetColumnToVariableName(variableConditionTreeNode.VariableName),
273            variableConditionTreeNode.Threshold,
274            FormatRecursively(node.GetSubtree(0)),
275            FormatRecursively(node.GetSubtree(1))
276            );
277        }
278      } else if (symbol is Xor) {
279        stringBuilder.Append("IF(");
280        stringBuilder.Append("XOR(");
281        stringBuilder.Append("(" + FormatRecursively(node.GetSubtree(0)) + ") > 0,");
282        stringBuilder.Append("(" + FormatRecursively(node.GetSubtree(1)) + ") > 0");
283        stringBuilder.Append("), 1.0, -1.0)");
284      } else if (symbol is Or) {
285        stringBuilder.Append("IF(");
286        stringBuilder.Append("OR(");
287        stringBuilder.Append("(" + FormatRecursively(node.GetSubtree(0)) + ") > 0,");
288        stringBuilder.Append("(" + FormatRecursively(node.GetSubtree(1)) + ") > 0");
289        stringBuilder.Append("), 1.0, -1.0)");
290      } else if (symbol is And) {
291        stringBuilder.Append("IF(");
292        stringBuilder.Append("AND(");
293        stringBuilder.Append("(" + FormatRecursively(node.GetSubtree(0)) + ") > 0,");
294        stringBuilder.Append("(" + FormatRecursively(node.GetSubtree(1)) + ") > 0");
295        stringBuilder.Append("), 1.0, -1.0)");
296      } else if (symbol is Not) {
297        stringBuilder.Append("IF(");
298        stringBuilder.Append(FormatRecursively(node.GetSubtree(0)));
299        stringBuilder.Append(" > 0, -1.0, 1.0)");
300      } else if (symbol is GreaterThan) {
301        stringBuilder.Append("IF((");
302        stringBuilder.Append(FormatRecursively(node.GetSubtree(0)));
303        stringBuilder.Append(") > (");
304        stringBuilder.Append(FormatRecursively(node.GetSubtree(1)));
305        stringBuilder.Append("), 1.0, -1.0)");
306      } else if (symbol is LessThan) {
307        stringBuilder.Append("IF((");
308        stringBuilder.Append(FormatRecursively(node.GetSubtree(0)));
309        stringBuilder.Append(") < (");
310        stringBuilder.Append(FormatRecursively(node.GetSubtree(1)));
311        stringBuilder.Append("), 1.0, -1.0)");
312      } else {
313        throw new NotImplementedException("Excel export of " + node.Symbol + " is not implemented.");
314      }
315      return stringBuilder.ToString();
316    }
317  }
318}
Note: See TracBrowser for help on using the repository browser.