/// <summary> /// Returns the range of data that exists in the row. Range will start at the parameter cell and end before the first empty column it encounters /// </summary> /// <param name="worksheet">Target worksheet</param> /// <param name="range">Singular cell in the desired column</param> /// <returns>Excel range</returns> public static Excel.Range GetRowRange(this Excel.Worksheet worksheet, string range) { if (worksheet.IsRange(range)) { if (worksheet.Range[range].IsSingularCell()) { int rangeRowNumber = worksheet.Range[range].Row; int lastColumn = worksheet.Range[range].Column; while (!string.IsNullOrEmpty(worksheet.Range[$"{WorksheetHelper.GetColumnName(lastColumn)}" + $"{rangeRowNumber}"].Value)) { lastColumn++; } return(worksheet.Range[$"{WorksheetHelper.GetColumnName(lastColumn - 1)}" + $"{rangeRowNumber}"]); } else { throw new Exception("Please ensure only one cell is referenced to indicate the range"); } } else { throw new Exception($"The range, {range}, does not exist on the current worksheet, {worksheet.Name}"); } }
public void createScatterPlot(List <Variable> variablesX, List <Variable> variablesY) { _Worksheet sheet = WorksheetHelper.NewWorksheet("Scatterplot"); int offsetY = 0; foreach (Variable variableX in variablesX) { int offsetX = 0; foreach (Variable variableY in variablesY) { var charts = (ChartObjects)sheet.ChartObjects(); var chartObject = charts.Add(offsetX * 300, offsetY * 200, 300, 200); var chart = chartObject.Chart; chart.ChartType = XlChartType.xlXYScatter; chart.ChartWizard(Title: "Scatterplot " + variableX.name + " vs " + variableY.name, HasLegend: false); var seriesCollection = (SeriesCollection)chart.SeriesCollection(); var series = seriesCollection.Add(); series.Values = variableX.getRange(); series.XValues = variableY.getRange(); series.MarkerStyle = XlMarkerStyle.xlMarkerStyleCircle; offsetX++; } offsetY++; } }
public bool CreateGraph(DataSet dataSet, List <bool> doInclude, bool doUseLabels, int labelsId) { // Create new lists (reference) for Data and labels var valuesArraysData = new List <Models.Data>(); var valuesArraysLabel = new List <Models.Data>(); // Create new sheet var sheet = WorksheetHelper.NewWorksheet("Time Series"); // variables for writing on sheet and writing of title int title = 1; int row = 5; sheet.Cells[title, 1] = "Time series plot"; // add data to the list for (var j = 0; j < dataSet.DataList.Count; j++) { if (!doInclude[j]) { continue; } valuesArraysData.Add(dataSet.DataList[j]); } // add labels to list valuesArraysLabel.Add(dataSet.DataList[labelsId]); // create array to put in labels string[] arrayLabels = new string[valuesArraysLabel[0].GetValuesList().Count]; // put in labels in array if label box is checked if (doUseLabels) { for (int i = 0; i < valuesArraysLabel[0].GetValuesList().Count; i++) { arrayLabels[i] = valuesArraysLabel[0].GetValuesList()[i].ToString() + " "; } } // else put in 1, 2, 3, ... else { for (int i = 0; i < valuesArraysLabel[0].GetValuesList().Count; i++) { arrayLabels[i] = Convert.ToString(i + 1); } } // loop all data sets and make Time series figure for (int i = 0; i < valuesArraysData.Count; i++) { // name figure and plot of figure string name = "Time series " + valuesArraysData[i].Name; CreateNewGraph(sheet, row, valuesArraysData[i].GetValuesArray(), arrayLabels, name); // make room for next figure row = row + 20; } return(true); }
public void createConfidence(List <Variable> variables, DataSet dataSet) { _Worksheet worksheet = WorksheetHelper.NewWorksheet("Confidence"); if (variables.Count == 1) { foreach (Variable variable in variables) { worksheet.Cells[1, 1] = "Conf. intervals (Proportion)"; worksheet.Cells[1, 2] = variable.name; worksheet.Cells[2, 1] = "Sample size"; worksheet.Cells[2, 2] = "=ROWS(" + dataSet.getWorksheet().Name + "!" + variable.Range + ")"; worksheet.Cells[3, 1] = "Sample proportion"; worksheet.Cells[3, 2] = "=COUNTIF(" + dataSet.getWorksheet().Name + "!" + variable.Range + "," + 0 + ") / " + (worksheet.Cells[2, 2] as Range).Value; PrintCategories(worksheet, 4, dataSet); } } else if (variables.Count == 2) { worksheet.Cells[1, 1] = "Conf. intervals (Proportion)"; worksheet.Cells[1, 2] = variables[0].name; worksheet.Cells[1, 3] = variables[1].name; worksheet.Cells[2, 1] = "Sample size"; worksheet.Cells[2, 2] = "=ROWS(" + dataSet.getWorksheet().Name + "!" + variables[0].Range + ")"; worksheet.Cells[2, 3] = "=ROWS(" + dataSet.getWorksheet().Name + "!" + variables[1].Range + ")"; worksheet.Cells[3, 1] = "Sample proportion"; worksheet.Cells[3, 2] = "=COUNTIF(" + dataSet.getWorksheet().Name + "!" + variables[0].Range + "," + 0 + ") / " + (worksheet.Cells[2, 2] as Range).Value; worksheet.Cells[3, 3] = "=COUNTIF(" + dataSet.getWorksheet().Name + "!" + variables[1].Range + "," + 0 + ") / " + (worksheet.Cells[2, 3] as Range).Value; PrintCategories2(worksheet, 4, dataSet); } }
/// <summary> /// Print the Summary Statistics to a new <see cref="_Worksheet"/>. /// </summary> /// <param name="dataSet">The <see cref="DataSet"/> which needs it Correlation and/or Covariance printed.</param> /// <param name="doInclude">A <see cref="List{T}"/> of <see cref="bool"/>s that corresponds to which <see cref="Models.Data"/> in the <see cref="DataSet.DataList"/> should be included.</param> /// <param name="doCalculate">A collection of <see cref="bool"/>s that indicate which summary statistic has to be calculated.</param> /// <param name="meanConfidenceLevel">(Optional) The confidence level for the <see cref="SummaryStatistics.Mean"/>. Default is 0.</param> /// <param name="standardDeviationConfidenceLevel">(Optional) The confidence level for the <see cref="SummaryStatistics.StandardDeviation"/>. Default is 0.</param> /// <param name="row">(Optional) The first row the data will be written to. Default is 1.</param> /// <param name="column">(Optional) The first column the data will be written to. Default is 1.</param> /// <param name="sheet">(Optional) An existing <see cref="_Worksheet"/>. A new one will be created when left blank. Default is null.</param> /// <param name="sheetName">(Optional) The name of the sheet. Default is 'One-Variable Summary'.</param> /// <param name="doIsNumericCheck">(Optional) Set to 'false' if the IsNumeric check should be suppressed. Default is true.</param> /// <returns>A <see cref="bool"/> that indicates if the print was successful or not.</returns> public bool Print(DataSet dataSet, List <bool> doInclude, SummaryStatisticsBool doCalculate, int meanConfidenceLevel = 0, int standardDeviationConfidenceLevel = 0, int row = 1, int column = 1, _Worksheet sheet = null, string sheetName = "One-Variable Summary", bool doIsNumericCheck = true) { // Save the variables as a field variables. _doCalculate = doCalculate; _column = column; // Check the input so everything is working like intended. var check = new CheckInput(dataSet, sheetName, doInclude, DefaultCheck.Numeric, doIsNumericCheck: doIsNumericCheck, doCalculate: doCalculate); if (!check.Successful) { return(false); } ValuesArray = check.Values; // Create a new sheet for the chart if none was provided. Sheet = sheet ?? WorksheetHelper.NewWorksheet(sheetName); // Print the categories to the sheet if there is at least 1 Set of Data selected. if (ValuesArray.Count > 0) { _row = row; PrintCategories(sheetName); } // Print the calculated variables for each DataSets Data. foreach (var data in ValuesArray) { _row = row; PrintValues(data, meanConfidenceLevel, standardDeviationConfidenceLevel); } return(true); }
/// <summary> /// Method for passing in validation to command rows /// </summary> /// <param name="commandType">Command type value</param> /// <param name="command">Command named range</param> /// <param name="options">Options named range</param> /// <param name="reference">Reference named range</param> private void AddRowValidation(string commandType, string command, string options = null, string reference = null) { Excel.Workbook workbook = Globals.ThisAddIn.Application.ActiveWorkbook; Excel.Worksheet worksheet = workbook.ActiveSheet; Excel.Range range = Globals.ThisAddIn.Application.ActiveCell; //Checks if commands page exists if (!workbook.WorksheetExists("Commands")) { MessageBox.Show("Please run 'Add Commands' and then try again."); } // Get row and next column indices var thisColumn = WorksheetHelper.GetColumnName(range.Column); var thisRow = range.Row; Excel.Range startRange = worksheet.Range[$"{thisColumn}" + $"{thisRow}"]; //Clears any previous validation on the row string tempColumn; for (int i = 0; i < 6; i++) { tempColumn = WorksheetHelper.GetColumnName(range.Column + i); worksheet.Range[$"{tempColumn}" + $"{thisRow}"].Validation.Delete(); } // Command type column range.Value = commandType; // Gets next column index thisColumn = WorksheetHelper.GetColumnName(range.Column + 1); // Command column range = worksheet.Range[$"{thisColumn}" + $"{thisRow}"]; range.AddDropDownList(command); // Gets next column index thisColumn = WorksheetHelper.GetColumnName(range.Column + 1); // Options column if (!string.IsNullOrEmpty(options)) { range = worksheet.Range[$"{thisColumn}" + $"{thisRow}"]; range.AddDropDownList(options); // Gets next column index thisColumn = WorksheetHelper.GetColumnName(range.Column + 1); if (!string.IsNullOrEmpty(reference)) { // Reference column range = worksheet.Range[$"{thisColumn}" + $"{thisRow}"]; range.AddDropDownList(nameof(ReferenceOptions)); } } }
public void ValidateColumnNames(ExcelWorksheet ws) { List <string> colNames = WorksheetHelper.GetColNamesInExcelSheet(ws, lastCol); foreach (var c in colsRequired) { if (!colNames.Contains(c)) { throw new ValidationException("Column name " + c + " doesn't exist in the excel file"); } } }
public bool checkInput(List <Variable> variablesX, List <Variable> variablesY, DataSet dataSet) { if (dataSet != null) { _Worksheet sheet = WorksheetHelper.NewWorksheet("Time Series Graph"); generateChart(variablesX, variablesY, sheet, dataSet); return(true); } else { MessageBox.Show("Please correct all fields to generate Time Series Graph", "Time Series Graph error"); } return(false); }
public bool checkInput(List <Variable> variables, bool rdbAllObservations, bool rdbObservationsInRange, DataSet dataSet, string uiTextBox_StopIndex, string uiTextBox_StartIndex, bool rdbPlotAllObservations, bool rdbPlotObservationsInRange, string uiTextBox_PlotStopIndex, string uiTextBox_PlotStartIndex) { int startindex = Convert.ToInt16(uiTextBox_StartIndex); int stopindex = Convert.ToInt16(uiTextBox_StopIndex); int plotstartindex = Convert.ToInt16(uiTextBox_PlotStartIndex); int plotstopindex = Convert.ToInt16(uiTextBox_PlotStopIndex); if ((rdbAllObservations || rdbObservationsInRange) && (rdbPlotAllObservations || rdbPlotObservationsInRange) && (dataSet != null) && (startindex <= stopindex) && (startindex >= 0 && stopindex >= 0) && (plotstartindex <= plotstopindex) && (plotstartindex >= 0 && plotstopindex >= 0)) { offset = 320; if (rdbAllObservations) { startindex = 0; stopindex = dataSet.rangeSize() - 1; } if (rdbObservationsInRange && stopindex >= dataSet.rangeSize()) { stopindex = dataSet.rangeSize() - 1; } if (rdbPlotAllObservations) { plotstartindex = 0; plotstopindex = dataSet.rangeSize() - 1; } if (rdbPlotObservationsInRange && plotstopindex >= dataSet.rangeSize()) { plotstopindex = dataSet.rangeSize() - 1; } _Worksheet sheet = WorksheetHelper.NewWorksheet("XR Chart"); generateXRChart(variables, startindex, stopindex, plotstartindex, plotstopindex, dataSet, offset, sheet); return(true); } else { MessageBox.Show("Please correct all fields to generate X/R-Chart", "XR-Chart error"); } return(false); }
public bool checkInput(List <Variable> variablesX, DataSet dataSet) { string ran = variablesX[0].Range.ToString(); Array arr = dataSet.getWorksheet().Range[ran].Value; if (dataSet != null) { _Worksheet sheet = WorksheetHelper.NewWorksheet("Autocorrelation"); generateChart(arr, variablesX, sheet, dataSet); return(true); } else { MessageBox.Show("Please correct all fields to generate Autocorrelation", "Autocorrelation error"); } return(false); }
public void createHistograms(List <Variable> variables) { _Worksheet worksheet = WorksheetHelper.NewWorksheet("Histogram"); int row = 1; int counter = 0; // Create the Histogram. foreach (Variable variable in variables) { PrintCategories(worksheet, row++, 1); PrintVariables(worksheet, row++, counter++, variable); if (row < 15 * counter) { row = 15 * counter; } } }
public bool checkInput(bool rdbAllObservations, bool rdbObservationsInRange, DataSet dataSet, string uiTextBox_StopIndex, string uiTextBox_StartIndex, bool rdbPlotAllObservations, bool rdbPlotRange, string uiTextbox_PlotStopIndex, string uiTextbox_PlotStartIndex) { int startindex = Convert.ToInt16(uiTextBox_StartIndex); int stopindex = Convert.ToInt16(uiTextBox_StopIndex); int plotstartindex = Convert.ToInt16(uiTextbox_PlotStartIndex); int plotstopindex = Convert.ToInt16(uiTextbox_PlotStopIndex); if ((rdbAllObservations || rdbObservationsInRange) && (dataSet != null) && (startindex <= stopindex) && (startindex >= 0 && stopindex >= 0) && (plotstartindex <= plotstopindex) && (plotstartindex >= 0 && plotstopindex >= 0)) { if (rdbAllObservations) { startindex = 0; stopindex = dataSet.amountOfVariables() - 2; } if (rdbObservationsInRange && stopindex >= dataSet.amountOfVariables()) { stopindex = dataSet.amountOfVariables() - 2; } if (rdbPlotAllObservations) { plotstartindex = 0; plotstopindex = dataSet.amountOfVariables() - 2; } if (rdbPlotRange && plotstopindex >= dataSet.amountOfVariables()) { plotstopindex = dataSet.amountOfVariables() - 2; } _Worksheet sheet = WorksheetHelper.NewWorksheet("P Chart"); generatePChart(startindex, stopindex, dataSet, sheet, plotstartindex, plotstopindex); return(true); } else { MessageBox.Show("Please correct all fields to generate P-Chart", "P-Chart error"); } return(false); }
public bool checkInput(DataSet dataSet, string uiTextBox_LowerLimit, string uiTextBox_UpperLimit) { int LSL = Convert.ToInt16(uiTextBox_LowerLimit); int USL = Convert.ToInt16(uiTextBox_UpperLimit); if ((dataSet != null) && (LSL < USL)) { _Worksheet sheet = WorksheetHelper.NewWorksheet("Process Capability"); generateProcessCapability(LSL, USL, dataSet, sheet); return(true); } else { MessageBox.Show("Please correct all fields to generate X/R-Chart", "XR-Chart error"); } return(false); }
public bool checkInput(List <Variable> variables, DataSet dataSet, string uiTextBox_LowerLimit, string uiTextBox_UpperLimit) { double LSL = Convert.ToDouble(uiTextBox_LowerLimit); double USL = Convert.ToDouble(uiTextBox_UpperLimit); if ((dataSet != null) && (LSL < USL)) { _Worksheet sheet = WorksheetHelper.NewWorksheet("Process Capability"); generateProcessCapability(variables, LSL, USL, dataSet, sheet); return(true); } else { MessageBox.Show("Please correct all fields to generate X/R-Chart, LSL should be smaller than USL", "XR-Chart error"); } return(false); }
/// <summary> /// Print the Summary Statistics to a new <see cref="_Worksheet"/>. /// </summary> /// <param name="dataSet">The <see cref="DataSet"/> which needs it Correlation and/or Covariance printed.</param> /// <param name="doIncludeCategory">A <see cref="List{T}"/> of <see cref="bool"/>s that corresponds to which <see cref="Models.Data"/> in the <see cref="DataSet.DataList"/> should be included as the category.</param> /// <param name="doIncludeValue">A <see cref="List{T}"/> of <see cref="bool"/>s that corresponds to which <see cref="Models.Data"/> in the <see cref="DataSet.DataList"/> should be included as the value.</param> /// <param name="doCalculate">A collection of <see cref="bool"/>s that indicate which summary statistic has to be calculated.</param> /// <param name="meanConfidenceLevel">(Optional) The confidence level for the <see cref="SummaryStatistics.Mean"/>. Default is 0.</param> /// <param name="standardDeviationConfidenceLevel">(Optional) The confidence level for the <see cref="SummaryStatistics.StandardDeviation"/>. Default is 0.</param> /// <param name="row">(Optional) The first row the data will be written to. Default is 1.</param> /// <param name="column">(Optional) The first column the data will be written to. Default is 1.</param> /// <param name="sheet">(Optional) An existing <see cref="_Worksheet"/>. A new one will be created when left blank. Default is null.</param> /// <param name="sheetName">(Optional) The name of the sheet. Default is 'One-Variable Summary'.</param> /// <param name="doIsNumericCheck">(Optional) Set to 'false' if the IsNumeric check should be suppressed. Default is true.</param> /// <returns>A <see cref="bool"/> that indicates if the print was successful or not.</returns> public bool Print(DataSet dataSet, List <bool> doIncludeCategory, List <bool> doIncludeValue, SummaryStatisticsBool doCalculate, int meanConfidenceLevel = 0, int standardDeviationConfidenceLevel = 0, int row = 1, int column = 1, _Worksheet sheet = null, string sheetName = "One-Variable Summary", bool doIsNumericCheck = true) { // Save the variables as a field variables. _doCalculate = doCalculate; _column = column; // Check the input so everything is working like intended. var check = new CheckInput(dataSet, sheetName, doIncludeCategory, DefaultCheck.Nonnumeric, -1, doIncludeValue, DefaultCheck.Numeric, 1, doCalculate, isCategory: true, doIsNumericCheck: doIsNumericCheck); if (!check.Successful) { return(false); } var categories = check.Categories; ValuesArray = check.Values; // Create a new sheet for the chart if none was provided. Sheet = sheet ?? WorksheetHelper.NewWorksheet(sheetName); // Create a new DataSet. dataSet = DataSet.GetByCategories(Sheet, categories[0], ValuesArray, row, _column + 1); // Create an array that holds the Sets of Data that need a Summary Statistic. ValuesArray = dataSet.DataList; // Print the categories to the sheet if there is at least 1 Set of Data selected. if (ValuesArray.Count > 0) { _row = dataSet.Range.Row + dataSet.Range.Rows.Count; PrintCategories(sheetName); } // Print the calculated variables for each DataSets Data. foreach (var valuesArray in ValuesArray) { _row = dataSet.Range.Row + dataSet.Range.Rows.Count; PrintValues(valuesArray, meanConfidenceLevel, standardDeviationConfidenceLevel); } return(true); }
/// <summary> /// Print the categories to the <see cref="_Worksheet"/>. /// </summary> /// <param name="title">The category title.</param> private void PrintCategories(string title) { // Create a new sheet for the chart. if (_sheet == null) { _sheet = WorksheetHelper.NewWorksheet(title); } else { _sheet.Rename(Regex.Replace(_sheet.Name, @"[^A-Za-z]+", "") + " & " + title); } // Write the title to the sheet. _sheet.Cells[_row++, _column] = title; // Write the categories to she sheet. foreach (var valuesArray in _valuesArray) { _sheet.Cells[_row++, _column] = valuesArray.Name; } }
private async void btnView_Click(object sender, EventArgs e) { Excel.Worksheet sheet = (Excel.Worksheet)_sheets[view.SelectedSheet]; if (sheet == null) { return; } view.EndColumnName = ExcelLimits.GetEndColumnName(view.EndColumnName); var endColName = view.EndColumnName; stopwatch.Restart(); SetDataReadState(string.Empty); var data = await Task.Factory.StartNew(() => WorksheetHelper.GetRowContent(sheet, view.DataRowIndex, endColName)); stopwatch.Stop(); var dataElapsed = stopwatch.Elapsed; stopwatch.Start(); ShowData(data, dataGridView); stopwatch.Stop(); var desc = $"总计行数:{data.Count},数据解析耗时:{dataElapsed},总计耗时:{stopwatch.Elapsed}"; SetDataReadState(desc); }
public void SaveToDB(ExcelWorksheet ws) { DataTable dt = WorksheetHelper.WorksheetToDT(ws, colsRequired, firstRow, lastRow, firstCol, lastCol); DataTable manipulatedDt = ManipulatedDT(dt); using (SqlConnection conn = new SqlConnection(_config)) { using (SqlCommand command = new SqlCommand()) { command.Connection = conn; command.CommandText = "SP_BulkInsertBrandChannel"; command.Parameters.Add("@brandchannel", SqlDbType.Structured); command.Parameters["@brandchannel"].TypeName = "UDT_BrandChannels"; command.CommandType = CommandType.StoredProcedure; command.Parameters["@brandchannel"].Value = manipulatedDt; conn.Open(); command.ExecuteNonQuery(); conn.Close(); } } }
public void createOneWayAnova(List <Variable> variables) { _Worksheet sheet = WorksheetHelper.NewWorksheet("One-Way ANOVA"); sheet.Cells[1, 1] = "ANOVA Summary"; sheet.Cells[2, 1] = "Total Sample Size"; sheet.Cells[3, 1] = "Grand Mean"; sheet.Cells[4, 1] = "Pooled Std Dev"; sheet.Cells[5, 1] = "Pooled Variance"; sheet.Cells[6, 1] = "Number of Samples"; sheet.Cells[7, 1] = "Confidence Level"; sheet.Cells[9, 1] = "ANOVA Sample Stats"; sheet.Cells[10, 1] = "Sample Size"; sheet.Cells[11, 1] = "Sample Mean"; sheet.Cells[12, 1] = "Sample Std Dev"; sheet.Cells[13, 1] = "Sample Variance"; sheet.Cells[14, 1] = "Pooling Weight"; sheet.Cells[17, 1] = "One-Way ANOVA Table"; sheet.Cells[18, 1] = "Between Variation"; sheet.Cells[19, 1] = "Within Variation"; sheet.Cells[20, 1] = "Total Variation"; sheet.Cells[23, 1] = "Confidence Interval Tests"; sheet.Cells[16, 2] = "Sum of"; sheet.Cells[17, 2] = "Squares"; sheet.Cells[16, 3] = "Degrees of"; sheet.Cells[17, 3] = "Freedom"; sheet.Cells[16, 4] = "Mean"; sheet.Cells[17, 4] = "Squares"; sheet.Cells[17, 5] = "F-Ratio"; sheet.Cells[17, 6] = "p-Value"; sheet.Cells[22, 2] = "Difference"; sheet.Cells[23, 2] = " of Means"; int col = 1; foreach (Variable variable in variables) { col++; var range = variable.getRange().Address(true, true, true); sheet.Cells[9, col] = variable.name; sheet.WriteFunction(10, col, "COUNT(" + range + ")"); sheet.WriteFunction(11, col, "AVERAGE(" + range + ")"); sheet.WriteFunction(12, col, "STDEV.S(" + range + ")"); sheet.WriteFunction(13, col, "VAR.S(" + range + ")"); } sheet.WriteFunction(2, 2, "SUM(" + AddressConverter.CellAddress(10, 2, false, false) + ":" + AddressConverter.CellAddress(10, col, false, false) + ")"); sheet.WriteFunction(3, 2, "SUMPRODUCT(" + AddressConverter.CellAddress(10, 2, false, false) + ":" + AddressConverter.CellAddress(10, col, false, false) + "," + AddressConverter.CellAddress(11, 2, false, false) + ":" + AddressConverter.CellAddress(11, col, false, false) + ")/" + AddressConverter.CellAddress(2, 2, false, false)); sheet.Cells[6, 2] = variables.Count; sheet.Cells[7, 2] = model.confidenceLevel; ((Range)sheet.Cells[7, 2]).NumberFormat = "0.00%"; col = 1; foreach (Variable variable in variables) { col++; sheet.WriteFunction(14, col, "(" + AddressConverter.CellAddress(10, col, false, false) + "-1)/(B2-B6)"); } sheet.WriteFunction(5, 2, "SUMPRODUCT(" + AddressConverter.CellAddress(13, 2, false, false) + ":" + AddressConverter.CellAddress(13, col, false, false) + "," + AddressConverter.CellAddress(14, 2, false, false) + ":" + AddressConverter.CellAddress(14, col, false, false) + ")"); sheet.WriteFunction(4, 2, "SQRT(" + AddressConverter.CellAddress(5, 2, false, false) + ")"); sheet.WriteFunction(18, 2, "SUMPRODUCT(" + AddressConverter.CellAddress(10, 2, false, false) + ":" + AddressConverter.CellAddress(10, col, false, false) + ",(" + AddressConverter.CellAddress(11, 2, false, false) + ":" + AddressConverter.CellAddress(11, col, false, false) + "-B3)^2)"); sheet.WriteFunction(19, 2, "(B2-" + variables.Count + ")*B5"); sheet.WriteFunction(20, 2, "B18+B19"); sheet.Cells[18, 3] = variables.Count - 1; sheet.WriteFunction(19, 3, "B2-" + variables.Count); sheet.WriteFunction(20, 3, "C18+C19"); sheet.WriteFunction(18, 4, "B18/C18"); sheet.WriteFunction(19, 4, "B19/C19"); sheet.WriteFunction(18, 5, "D18/D19"); sheet.WriteFunction(18, 6, "F.DIST.RT(E18,C18,C19)"); int row = 24; int c = 0; for (int i = 0; i < variables.Count; i++) { for (int j = i + 1; j < variables.Count; j++) { c++; Variable var1 = variables[i]; Variable var2 = variables[j]; sheet.Cells[row, 1] = var1.name + " - " + var2.name; sheet.WriteFunction(row, 2, AddressConverter.CellAddress(11, i + 2, false, false) + "-" + AddressConverter.CellAddress(11, j + 2, false, false)); col = 3; if (model.noCorrection) { sheet.WriteFunction(row, col++, AddressConverter.CellAddress(row, 2, false, false) + "-(ABS(T.INV((1-B7)/2,C19)))*SQRT(D19*(1/" + AddressConverter.CellAddress(10, i + 2, false, false) + "+1/" + AddressConverter.CellAddress(10, j + 2, false, false) + "))"); sheet.WriteFunction(row, col++, AddressConverter.CellAddress(row, 2, false, false) + "+(ABS(T.INV((1-B7)/2,C19)))*SQRT(D19*(1/" + AddressConverter.CellAddress(10, i + 2, false, false) + "+1/" + AddressConverter.CellAddress(10, j + 2, false, false) + "))"); } if (model.bonferroni) { sheet.WriteFunction(row, col++, AddressConverter.CellAddress(row, 2, false, false) + "-(ABS(T.INV(((1-B7)/(B6*(B6-1)/2))/2,C19)))*SQRT(D19*(1/" + AddressConverter.CellAddress(10, i + 2, false, false) + "+1/" + AddressConverter.CellAddress(10, j + 2, false, false) + "))"); sheet.WriteFunction(row, col++, AddressConverter.CellAddress(row, 2, false, false) + "+(ABS(T.INV(((1-B7)/(B6*(B6-1)/2))/2,C19)))*SQRT(D19*(1/" + AddressConverter.CellAddress(10, i + 2, false, false) + "+1/" + AddressConverter.CellAddress(10, j + 2, false, false) + "))"); } if (model.scheffe) { sheet.WriteFunction(row, col++, AddressConverter.CellAddress(row, 2, false, false) + "-SQRT((B6-1)*F.INV.RT(1-B7,B6-1,C19))*SQRT(D19*(1/" + AddressConverter.CellAddress(10, i + 2, false, false) + "+1/" + AddressConverter.CellAddress(10, j + 2, false, false) + "))"); sheet.WriteFunction(row, col, AddressConverter.CellAddress(row, 2, false, false) + "+SQRT((B6-1)*F.INV.RT(1-B7,B6-1,C19))*SQRT(D19*(1/" + AddressConverter.CellAddress(10, i + 2, false, false) + "+1/" + AddressConverter.CellAddress(10, j + 2, false, false) + "))"); } row++; } } col = 3; if (model.noCorrection) { sheet.Cells[22, col] = "No Correction"; sheet.Range[AddressConverter.CellAddress(22, col, false, false), AddressConverter.CellAddress(22, col + 1, false, false)].Merge(); sheet.Cells[23, col] = "Lower"; sheet.Cells[23, col + 1] = "Upper"; sheet.Range[AddressConverter.CellAddress(23, col, false, false), AddressConverter.CellAddress(23, col + 1, false, false)].Borders[XlBordersIndex.xlEdgeBottom].LineStyle = XlLineStyle.xlDouble; sheet.Range[AddressConverter.CellAddress(24, col, false, false), AddressConverter.CellAddress(23 + c, col, false, false)].Borders[XlBordersIndex.xlEdgeLeft].LineStyle = XlLineStyle.xlDot; col += 2; } if (model.bonferroni) { sheet.Cells[22, col] = "Bonferroni"; sheet.Range[AddressConverter.CellAddress(22, col, false, false), AddressConverter.CellAddress(22, col + 1, false, false)].Merge(); sheet.Cells[23, col] = "Lower"; sheet.Cells[23, col + 1] = "Upper"; sheet.Range[AddressConverter.CellAddress(23, col, false, false), AddressConverter.CellAddress(23, col + 1, false, false)].Borders[XlBordersIndex.xlEdgeBottom].LineStyle = XlLineStyle.xlDouble; sheet.Range[AddressConverter.CellAddress(24, col, false, false), AddressConverter.CellAddress(23 + c, col, false, false)].Borders[XlBordersIndex.xlEdgeLeft].LineStyle = XlLineStyle.xlDot; col += 2; } if (model.scheffe) { sheet.Cells[22, col] = "Scheffe"; sheet.Range[AddressConverter.CellAddress(22, col, false, false), AddressConverter.CellAddress(22, col + 1, false, false)].Merge(); sheet.Cells[23, col] = "Lower"; sheet.Cells[23, col + 1] = "Upper"; sheet.Range[AddressConverter.CellAddress(23, col, false, false), AddressConverter.CellAddress(23, col + 1, false, false)].Borders[XlBordersIndex.xlEdgeBottom].LineStyle = XlLineStyle.xlDouble; sheet.Range[AddressConverter.CellAddress(24, col, false, false), AddressConverter.CellAddress(23 + c, col, false, false)].Borders[XlBordersIndex.xlEdgeLeft].LineStyle = XlLineStyle.xlDot; } ((Range)sheet.Cells[1, 1]).EntireColumn.AutoFit(); ((Range)sheet.Cells[1, 2]).EntireColumn.AutoFit(); ((Range)sheet.Cells[1, 3]).EntireColumn.AutoFit(); ((Range)sheet.Cells[1, 4]).EntireColumn.AutoFit(); ((Range)sheet.Cells[1, 5]).EntireColumn.AutoFit(); ((Range)sheet.Cells[1, 6]).EntireColumn.AutoFit(); ((Range)sheet.Cells[1, 7]).EntireColumn.AutoFit(); ((Range)sheet.Cells[1, 8]).EntireColumn.AutoFit(); sheet.Range["B1", "J200"].Cells.HorizontalAlignment = XlHAlign.xlHAlignCenter; sheet.Range["A1", "B1"].Borders[XlBordersIndex.xlEdgeBottom].LineStyle = XlLineStyle.xlDouble; sheet.Range["A9", AddressConverter.CellAddress(9, variables.Count + 1, false, false)].Borders[XlBordersIndex.xlEdgeBottom].LineStyle = XlLineStyle.xlDouble; sheet.Range["A17", "F17"].Borders[XlBordersIndex.xlEdgeBottom].LineStyle = XlLineStyle.xlDouble; sheet.Range["A23", "B17"].Borders[XlBordersIndex.xlEdgeBottom].LineStyle = XlLineStyle.xlDouble; sheet.Range["B3", "B5"].NumberFormat = "0.0000"; sheet.Range["B11", AddressConverter.CellAddress(14, variables.Count + 1, false, false)].NumberFormat = "0.000"; sheet.Range["B18", "B20"].NumberFormat = "0.0000"; sheet.Range["D18", "E19"].NumberFormat = "0.0000"; sheet.Range["B24", AddressConverter.CellAddress(23 + c, 2, false, false)].NumberFormat = "0.0000"; sheet.Range["C24", AddressConverter.CellAddress(23 + c, 8, false, false)].NumberFormat = "0.000000"; sheet.Range["C24", AddressConverter.CellAddress(23 + c, 8, false, false)].Cells.HorizontalAlignment = XlHAlign.xlHAlignRight; Globals.ExcelAddIn.Application.ActiveWindow.DisplayGridlines = false; }
public bool Print(DataSet dataSet, List <bool> doInclude, SummaryStatisticsBool doCalculate, bool doOptimizeParameters, bool doUseLabels, int labelsId, int numberOfForecasts, int numberOfHoldouts, int seasonalPeriod, int span, string level, string trend, string seasonality) { _doOptimizeParameters = doOptimizeParameters; _numberOfForecasts = numberOfForecasts; _numberOfHoldouts = numberOfHoldouts; _seasonalPeriod = seasonalPeriod; _span = span; if (!double.TryParse(level, out _level)) { MessageBox.Show("The Level is not a valid number.", "Forecast", MessageBoxButtons.OK, MessageBoxIcon.Error); return(false); } if (!double.TryParse(trend, out _trend)) { MessageBox.Show("The Trend is not a valid number.", "Forecast", MessageBoxButtons.OK, MessageBoxIcon.Error); return(false); } if (!double.TryParse(seasonality, out _seasonality)) { MessageBox.Show("The Seasonality is not a valid number.", "Forecast", MessageBoxButtons.OK, MessageBoxIcon.Error); return(false); } // Create new lists (reference) for Data and labels var valuesArraysData = new List <Models.Data>(); var valuesArraysLabel = new List <Models.Data>(); // Create new sheet var sheet = WorksheetHelper.NewWorksheet("Forecast"); // add data to the data list for (var j = 0; j < dataSet.DataList.Count; j++) { if (!doInclude[j]) { continue; } valuesArraysData.Add(dataSet.DataList[j]); } // add labels to labels list valuesArraysLabel.Add(dataSet.DataList[labelsId]); // variables for writing on sheet and writing of title int title = 1; int forcastingConstants = 3; int summaryName = forcastingConstants + 5; int row = summaryName + 5; sheet.Cells[title, 1] = "Forecast "; sheet.Cells[forcastingConstants, 1] = "Forecasting Constants"; sheet.Cells[summaryName, 1] = "Summary"; sheet.Cells[summaryName + 1, 1] = "Mean Absolute Error"; sheet.Cells[summaryName + 2, 1] = "Root Mean Squared Error"; sheet.Cells[summaryName + 3, 1] = "Mean Absolute Percentage Error"; sheet.Cells[row, 1] = "Label"; sheet.Cells[row, 2] = valuesArraysData[0].Name; // variables to count number of forecast, holdouts and data var nForecasts = Convert.ToDouble(numberOfForecasts); var nHoldouts = Convert.ToDouble(numberOfHoldouts); var nData = Convert.ToDouble(valuesArraysData[0].GetValuesList().Count); // create array to put in labels string[] arrayLabels = new string[valuesArraysLabel[0].GetValuesList().Count + Convert.ToInt16(nForecasts)]; // put in labels in array if label box is checked and print out labels in excel if (doUseLabels) { for (int i = 0; i < valuesArraysLabel[0].GetValuesList().Count; i++) { arrayLabels[i] = valuesArraysLabel[0].GetValuesList()[i].ToString() + " "; sheet.Cells[row + 1 + i, 1] = arrayLabels[i]; } } // else put in 1, 2, 3, ... and print out labels in excel else { for (int i = 0; i < valuesArraysLabel[0].GetValuesList().Count; i++) { arrayLabels[i] = Convert.ToString(i + 1); sheet.Cells[row + 1 + i, 1] = arrayLabels[i]; } } // Add labels for forecast and print out labels in excel double differenceLabels = Convert.ToDouble(arrayLabels[arrayLabels.Length - 1 - Convert.ToInt16(nForecasts)]) - Convert.ToDouble(arrayLabels[arrayLabels.Length - 2 - Convert.ToInt16(nForecasts)]); for (int i = 0; i < nForecasts; i++) { int arrayPosition = valuesArraysLabel[0].GetValuesList().Count + i; arrayLabels[arrayPosition] = Convert.ToString(Convert.ToDouble(arrayLabels[arrayPosition - 1]) + differenceLabels); sheet.Cells[row + arrayPosition + 1, 1] = arrayLabels[arrayPosition]; } // Print out Data in excel for (int i = 0; i < valuesArraysData[0].GetValuesList().Count; i++) { sheet.Cells[row + 1 + i, 2] = valuesArraysData[0].GetValuesList()[i]; } // Plot next figure below the data and the forecast int rowFigure = row + valuesArraysLabel[0].GetValuesList().Count + Convert.ToInt16(nForecasts) + 2; // Calculate moving average forecast if box is checked if (doCalculate.MovingAverage) { EvaluateMovingAverage(valuesArraysData[0].GetValuesArray(), row, sheet); string name = "Forecast Moving Average: " + valuesArraysData[0].Name; var rangeLabels = sheet.Range[sheet.Cells[row + 1, 1], sheet.Cells[row + nData + nForecasts, 1]]; var rangeData = sheet.Range[sheet.Cells[row + 1, 2], sheet.Cells[row + nData + nForecasts, 2]]; var rangeForecast = sheet.Range[sheet.Cells[row + 1, 3], sheet.Cells[row + nData + nForecasts, 3]]; new TimeSeriesGraph().CreateNewGraph(sheet, rowFigure, rangeData, rangeForecast, rangeLabels, name); } // Calculate exponential smoothing (simple) forecast if box is checked if (doCalculate.SimpleExponentialSmoothing) { CalculateSimple(valuesArraysData[0].GetValuesArray(), row, sheet); string name = "Forecast Exponential smoothing (Simple): " + valuesArraysData[0].Name; var rangeLabels = sheet.Range[sheet.Cells[row + 1, 1], sheet.Cells[row + nData + nForecasts, 1]]; var rangeData = sheet.Range[sheet.Cells[row + 1, 2], sheet.Cells[row + nData + nForecasts, 2]]; var rangeForecast = sheet.Range[sheet.Cells[row + 1, 4], sheet.Cells[row + nData + nForecasts, 4]]; new TimeSeriesGraph().CreateNewGraph(sheet, rowFigure, rangeData, rangeForecast, rangeLabels, name); } // Calculate exponential smoothing (holt) forecast if box is checked if (doCalculate.HoltsExponentialSmoothing) { CalculateHolt(valuesArraysData[0].GetValuesArray(), row, sheet); string name = "Forecast Exponential smoothing (Holt): " + valuesArraysData[0].Name; var rangeLabels = sheet.Range[sheet.Cells[row + 1, 1], sheet.Cells[row + nData + nForecasts, 1]]; var rangeData = sheet.Range[sheet.Cells[row + 1, 2], sheet.Cells[row + nData + nForecasts, 2]]; var rangeForecast = sheet.Range[sheet.Cells[row + 1, 5], sheet.Cells[row + nData + nForecasts, 5]]; new TimeSeriesGraph().CreateNewGraph(sheet, rowFigure, rangeData, rangeForecast, rangeLabels, name); } // Calculate exponential smoothing (winters) forecast if box is checked if (doCalculate.WintersExponentialSmoothing) { CalculateWinter(valuesArraysData[0].GetValuesArray(), row, sheet); string name = "Forecast Exponential smoothing (Winter): " + valuesArraysData[0].Name; var rangeLabels = sheet.Range[sheet.Cells[row + 1, 1], sheet.Cells[row + nData + nForecasts, 1]]; var rangeData = sheet.Range[sheet.Cells[row + 1, 2], sheet.Cells[row + nData + nForecasts, 2]]; var rangeForecast = sheet.Range[sheet.Cells[row + 1, 6], sheet.Cells[row + nData + nForecasts, 6]]; new TimeSeriesGraph().CreateNewGraph(sheet, rowFigure, rangeData, rangeForecast, rangeLabels, name); } return(true); }
public bool checkInput(DataSet dataSet, List <Variable> independentVariables, Variable dependentVariable) { if (dataSet == null || independentVariables.Count() == 0) { MessageBox.Show(" Please correct all fields to perform logistic regression. Make sure that only one independent variable is selected."); return(false); } // declare function to use function of excel var functions = Globals.ExcelAddIn.Application.WorksheetFunction; var sheet = WorksheetHelper.NewWorksheet("Logistic Regression"); var matrixX = Matrix <double> .Build.Dense(dataSet.rangeSize(), independentVariables.Count + 1); for (var i = 0; i < dataSet.rangeSize(); i++) { matrixX[i, 0] = 1; for (var j = 0; j < independentVariables.Count; j++) { matrixX[i, j + 1] = dataSet.getValuesArray(independentVariables[j])[i]; } } var matrixY = Matrix <double> .Build.Dense(dataSet.rangeSize(), 1); for (var i = 0; i < dataSet.rangeSize(); i++) { matrixY[i, 0] = dataSet.getValuesArray(dependentVariable)[i]; } // calculate theta iteratively using newton's method, starting from zero matrix var startTheta = Matrix <double> .Build.Dense(independentVariables.Count + 1, 1); var theta = NewtonsMethod(matrixX, matrixY, startTheta); // calculate classification matrix var classificationMatrix = CalculateClassificationMatrix(matrixX, matrixY, theta); // calculate summary classification var summaryClassificationPercentages = SummaryClassification(classificationMatrix); var expThetaX = theta.Transpose() * matrixX.Transpose(); var matrixV = Matrix <double> .Build.Dense(dataSet.rangeSize(), dataSet.rangeSize()); for (var i = 0; i < dataSet.rangeSize(); i++) { var prob = 1 / (1 + Math.Exp(-expThetaX[0, i])); matrixV[i, i] = prob * (1 - prob); } var matrixCovar = (matrixX.Transpose() * matrixV * matrixX).Inverse(); // locations of tables const int title = 1; const int summaryName = 3; const int name = summaryName + 6; const int betaCoeffName = 2; const int stdErrorName = betaCoeffName + 1; const int waldValueName = stdErrorName + 1; const int pValueName = waldValueName + 1; const int lowerLimitName = pValueName + 1; const int upperLimitName = lowerLimitName + 1; var classificationMatrixName = name + 3 + independentVariables.Count; var summaryClassificationName = classificationMatrixName + 4; var dataName = summaryClassificationName + 5; var probName = independentVariables.Count + 2; // names of variables on sheet sheet.Cells[title, 1] = "Logistic Regression"; sheet.Cells[summaryName, 1] = "Summary"; sheet.Cells[summaryName + 1, 1] = "Null Deviance"; sheet.Cells[summaryName + 2, 1] = "Model Deviance"; sheet.Cells[summaryName + 3, 1] = "Improvement"; sheet.Cells[summaryName + 4, 1] = "P Value"; sheet.Cells[name + 1, 1] = "Constant"; sheet.Cells[name, betaCoeffName] = "Beta Coefficient"; sheet.Cells[name, stdErrorName] = "Standard Error"; sheet.Cells[name, waldValueName] = "Wald Value"; sheet.Cells[name, pValueName] = "p Value"; sheet.Cells[name, lowerLimitName] = "Lower Limit (95%)"; sheet.Cells[name, upperLimitName] = "Upper Limit (95%)"; ((Range)sheet.Cells[name + 1, 1]).EntireColumn.AutoFit(); ((Range)sheet.Cells[name, betaCoeffName]).EntireColumn.AutoFit(); ((Range)sheet.Cells[name, stdErrorName]).EntireColumn.AutoFit(); ((Range)sheet.Cells[name, waldValueName]).EntireColumn.AutoFit(); ((Range)sheet.Cells[name, pValueName]).EntireColumn.AutoFit(); ((Range)sheet.Cells[name, lowerLimitName]).EntireColumn.AutoFit(); ((Range)sheet.Cells[name, upperLimitName]).EntireColumn.AutoFit(); for (var i = 0; i < independentVariables.Count; i++) { sheet.Cells[name + i + 2, 1] = independentVariables[i].name; } sheet.Cells[classificationMatrixName, 1] = "Classification Matrix"; sheet.Cells[classificationMatrixName, 2] = "1"; sheet.Cells[classificationMatrixName, 3] = "0"; sheet.Cells[classificationMatrixName, 4] = "Percentage Correct"; sheet.Cells[classificationMatrixName + 1, 1] = "1"; sheet.Cells[classificationMatrixName + 2, 1] = "0"; sheet.Cells[summaryClassificationName, 1] = "Classification Summary"; sheet.Cells[summaryClassificationName, 2] = "Percentage"; sheet.Cells[summaryClassificationName + 1, 1] = "Correct"; sheet.Cells[summaryClassificationName + 2, 1] = "Base"; sheet.Cells[summaryClassificationName + 3, 1] = "Improvement"; sheet.Cells[summaryClassificationName + 1, 2] = summaryClassificationPercentages[0]; sheet.Cells[summaryClassificationName + 2, 2] = summaryClassificationPercentages[1]; sheet.Cells[summaryClassificationName + 3, 2] = summaryClassificationPercentages[2]; ((Range)sheet.Cells[summaryClassificationName, 1]).EntireColumn.AutoFit(); ((Range)sheet.Cells[9, 1]).EntireRow.AutoFit(); sheet.Cells[dataName, 1] = ""; for (var i = 0; i < independentVariables.Count; i++) { sheet.Cells[dataName, 2 + i] = independentVariables[i].name; } sheet.Cells[dataName, probName] = "Probability"; sheet.Cells[dataName, probName + 1] = "Forecast"; sheet.Cells[dataName, probName + 2] = "Class"; for (var i = 0; i < dataSet.rangeSize(); i++) { sheet.Cells[dataName + 1 + i, 1] = i + 1; for (var j = 0; j < independentVariables.Count; j++) { sheet.Cells[dataName + 1 + i, 2 + j] = matrixX[i, j + 1]; } var prob = 1 / (1 + Math.Exp(-expThetaX[0, i])); sheet.Cells[dataName + 1 + i, probName] = prob; if (prob > 0.5) { sheet.Cells[dataName + 1 + i, probName + 1] = 1; } else { sheet.Cells[dataName + 1 + i, probName + 1] = 0; } sheet.Cells[dataName + 1 + i, probName + 2] = matrixY[i, 0]; } double dF = dataSet.rangeSize() - independentVariables.Count() - 1; // print out beta coefficients and std error of coefficient for (var i = 0; i < theta.RowCount; i++) { sheet.Cells[name + i + 1, betaCoeffName] = theta[i, 0]; var stdError = Math.Sqrt(matrixCovar[i, i]); sheet.Cells[name + i + 1, stdErrorName] = stdError; sheet.Cells[name + i + 1, waldValueName] = theta[i, 0] / stdError; var pValue = functions.TDist(Math.Abs(theta[i, 0] / stdError), dF, 2); sheet.Cells[name + i + 1, pValueName] = pValue; var confidenceConstant = 1.96; var lower = theta[i, 0] - stdError * confidenceConstant; var upper = theta[i, 0] + stdError * confidenceConstant; sheet.Cells[name + i + 1, lowerLimitName] = lower; sheet.Cells[name + i + 1, upperLimitName] = upper; } //print out classification matrix sheet.Cells[classificationMatrixName + 1, 2] = classificationMatrix[0]; sheet.Cells[classificationMatrixName + 1, 3] = classificationMatrix[1]; var n0 = Convert.ToDouble(classificationMatrix[0]) + Convert.ToDouble(classificationMatrix[1]); sheet.Cells[classificationMatrixName + 1, 4] = Convert.ToDouble(classificationMatrix[0]) / (Convert.ToDouble(classificationMatrix[0]) + Convert.ToDouble(classificationMatrix[1])); sheet.Cells[classificationMatrixName + 2, 2] = classificationMatrix[2]; sheet.Cells[classificationMatrixName + 2, 3] = classificationMatrix[3]; var n1 = Convert.ToDouble(classificationMatrix[2]) + Convert.ToDouble(classificationMatrix[3]); sheet.Cells[classificationMatrixName + 2, 4] = Convert.ToDouble(classificationMatrix[3]) / (Convert.ToDouble(classificationMatrix[2]) + Convert.ToDouble(classificationMatrix[3])); // summary measures var nullDeviance = 2 * (-n0 * Math.Log(n0 / (n0 + n1)) - n1 * Math.Log(n1 / (n0 + n1))); double modelDeviance = 0; for (var i = 0; i < dataSet.rangeSize(); i++) { var prob = 1 / (1 + Math.Exp(-expThetaX[0, i])); double yValue = matrixY[i, 0]; modelDeviance = modelDeviance + yValue * Math.Log(1 / prob) + (1 - yValue) * Math.Log(1 / (1 - prob)); } modelDeviance = modelDeviance * 2; sheet.Cells[summaryName + 1, 2] = nullDeviance; sheet.Cells[summaryName + 2, 2] = modelDeviance; sheet.Cells[summaryName + 3, 2] = nullDeviance - modelDeviance; sheet.Cells[summaryName + 4, 2] = "P Value"; return(true); }
public void createBoxWhiskerPlot(List <Variable> variables) { _Worksheet sheet = WorksheetHelper.NewWorksheet("Box-Whisker Plot"); var excel = Globals.ExcelAddIn.Application; List <double> yValues = new List <double>(); // To center the mean and outliers, the secondary Y-Axis has to be divided in sections. Each Box-Whisker Plot has a section above and below so if 1 Box-Whisker Plot is needed, the dots will be at 50% of the max. secondary Y-Axis value. If 2 Box-Whisker Plots are needed, the dots will be at 25% for the 1st and 75% for the 2nd of the max. secondary Y-Axis value. var section = 1.0 / (2 * variables.Count); for (var i = 0; i < variables.Count; i++) { yValues.Add((2 * (i + 1) - 1) * section); } // Create the chart. var charts = (ChartObjects)sheet.ChartObjects(); var chartObject = charts.Add(0, 0, 500, 125 * variables.Count); var chart = chartObject.Chart; chart.ChartType = XlChartType.xlBarStacked; chart.ChartWizard(Title: "Box-Whisker Plot", HasLegend: false); var seriesCollection = (SeriesCollection)chart.SeriesCollection(); // Add the Quartile 1 to the chart. double[] values = new double[variables.Count]; for (int i = 0; i < variables.Count; i++) { values[i] = excel.Evaluate("=QUARTILE.INC(" + variables[i].getRange().Address(true, true, true) + ",1)"); } var series = seriesCollection.NewSeries(); series.Values = values; series.ChartType = XlChartType.xlBarStacked; series.Format.Fill.Solid(); series.Format.Fill.Transparency = 1; // Add the lower halve to the chart. for (int i = 0; i < variables.Count; i++) { values[i] = excel.Evaluate("=MEDIAN(" + variables[i].getRange().Address(true, true, true) + ")-QUARTILE.INC(" + variables[i].getRange().Address(true, true, true) + ",1)"); } series = seriesCollection.NewSeries(); series.Values = values; series.ChartType = XlChartType.xlBarStacked; series.Format.Fill.Solid(); series.Format.Fill.Transparency = 1; series.Border.LineStyle = XlLineStyle.xlContinuous; series.Format.Line.Weight = 1.5f; // Add the upper halve to the chart. for (int i = 0; i < variables.Count; i++) { values[i] = excel.Evaluate("=QUARTILE.INC(" + variables[i].getRange().Address(true, true, true) + ",3)-MEDIAN(" + variables[i].getRange().Address(true, true, true) + ")"); } series = seriesCollection.NewSeries(); series.Values = values; series.ChartType = XlChartType.xlBarStacked; series.Format.Fill.Solid(); series.Format.Fill.Transparency = 1; series.Border.LineStyle = XlLineStyle.xlContinuous; series.Format.Line.Weight = 1.5f; // Set the Categories Axis. List <string> headers = new List <string>(); foreach (Variable variable in variables) { headers.Add(variable.name); } ((Axis)chart.Axes(XlAxisType.xlCategory)).CategoryNames = headers.ToArray(); // Add both the minus and plus whiskers to the chart. for (int i = 0; i < variables.Count; i++) { var range = variables[i].getRange().Address(true, true, true); double quartile1 = excel.Evaluate("=QUARTILE.INC(" + range + ",1)"); double minimum = excel.Evaluate("=MIN(" + range + ")"); double quartile3 = excel.Evaluate("=QUARTILE.INC(" + range + ",3)"); double interquartileRange = quartile3 - quartile1; values[i] = quartile1 - minimum <= 1.5 * interquartileRange ? quartile1 - minimum : 1.5 * interquartileRange; } ((Series)chart.SeriesCollection(1)).ErrorBar(XlErrorBarDirection.xlY, XlErrorBarInclude.xlErrorBarIncludeMinusValues, XlErrorBarType.xlErrorBarTypeCustom, 0, values); ((Series)chart.SeriesCollection(1)).ErrorBars.Format.Line.Weight = 1.5f; for (int i = 0; i < variables.Count; i++) { var range = variables[i].getRange().Address(true, true, true); double quartile1 = excel.Evaluate("=QUARTILE.INC(" + range + ",1)"); double maximum = excel.Evaluate("=MAX(" + range + ")"); double quartile3 = excel.Evaluate("=QUARTILE.INC(" + range + ",3)"); double interquartileRange = quartile3 - quartile1; values[i] = maximum - quartile3 <= 1.5 * interquartileRange ? maximum - quartile3 : 1.5 * interquartileRange; } ((Series)chart.SeriesCollection(3)).ErrorBar(XlErrorBarDirection.xlY, XlErrorBarInclude.xlErrorBarIncludePlusValues, XlErrorBarType.xlErrorBarTypeCustom, values, 0); ((Series)chart.SeriesCollection(3)).ErrorBars.Format.Line.Weight = 1.5f; // Add the means to the chart as a Scatterplot and change the layout. series = seriesCollection.Add(); series.ChartType = XlChartType.xlXYScatter; series.Name = "Mean"; series.Values = yValues.ToArray(); for (int i = 0; i < variables.Count; i++) { values[i] = excel.Evaluate("=AVERAGE(" + variables[i].getRange().Address(true, true, true) + ")"); } series.XValues = values; series.MarkerStyle = XlMarkerStyle.xlMarkerStyleX; series.MarkerForegroundColor = (int)XlRgbColor.rgbDarkBlue; // Add all the outliers to the chart as a Scatterplot and change the layout. List <List <double> > listOutliers = new List <List <double> >(); List <List <double> > listY = new List <List <double> >(); foreach (Variable variable in variables) { List <double> outliers = new List <double>(); for (var i = 1; i <= variable.getRange().Rows.Count; i++) { try { var range = variable.getRange().Address(true, true, true); double quartile1 = excel.Evaluate("=QUARTILE.INC(" + range + ",1)"); double quartile3 = excel.Evaluate("=QUARTILE.INC(" + range + ",3)"); double interquartileRange = quartile3 - quartile1; double value = (double)((Range)excel.Evaluate("=INDEX(" + range + "," + i + ")")).Value2; if (value < quartile1 - 1.5 * interquartileRange) { outliers.Add(value); } if (value > quartile3 + 1.5 * interquartileRange) { outliers.Add(value); } } catch { } } listOutliers.Add(outliers); } var sect = 1.0 / (2 * variables.Count); int count = 0; foreach (List <double> lst in listOutliers) { List <double> yOutliers = new List <double>(); double val = (2 * (count + 1) - 1) * sect; for (var i = 0; i < lst.Count; i++) { yOutliers.Add(val); } count = count + 1; listY.Add(yOutliers); } count = 0; foreach (List <double> outl in listOutliers) { if (outl.Count > 0) { series = seriesCollection.Add(); series.ChartType = XlChartType.xlXYScatter; series.Name = "Outliers"; series.Values = listY[count].ToArray(); series.XValues = outl.ToArray(); series.MarkerStyle = XlMarkerStyle.xlMarkerStyleSquare; series.MarkerBackgroundColor = (int)XlRgbColor.rgbDarkRed; series.MarkerForegroundColor = (int)XlRgbColor.rgbDarkRed; } count = count + 1; } // Hide the secondary axis and set max value. ((Axis)chart.Axes(XlAxisType.xlValue, XlAxisGroup.xlSecondary)).MaximumScale = 1; chart.HasAxis[XlAxisType.xlValue, XlAxisGroup.xlSecondary] = false; }
public bool checkInput(DataSet dataSet, List <Variable> independentVariables, Variable dependentVariable, string probability, string misclassification0, string misclassification1) { if (dataSet == null || independentVariables.Count() == 0) { MessageBox.Show(" Please correct all fields to perform logistic regression. Make sure that only one independent variable is selected."); return(false); } // declare function to use function of excel var functions = Globals.ExcelAddIn.Application.WorksheetFunction; var sheet = WorksheetHelper.NewWorksheet("Discriminant Analysis"); var matrixX = Matrix <double> .Build.Dense(dataSet.rangeSize(), independentVariables.Count); for (var i = 0; i < dataSet.rangeSize(); i++) { for (var j = 0; j < independentVariables.Count; j++) { matrixX[i, j] = dataSet.getValuesArray(independentVariables[j])[i]; } } var matrixY = Matrix <double> .Build.Dense(dataSet.rangeSize(), 1); for (var i = 0; i < dataSet.rangeSize(); i++) { matrixY[i, 0] = dataSet.getValuesArray(dependentVariable)[i]; } // Tables location const int setupName = 3; const int summaryName = setupName + 4; const int functionName = summaryName + 4; var matrixName = functionName + independentVariables.Count + 2; var classificationName = matrixName + 4; var covarsName = classificationName + 5; var cutOffName = covarsName + independentVariables.Count + 2; var dataName = cutOffName + 3; var discrimName = independentVariables.Count + 2; // write tables sheet.Cells[1, 1] = "Discriminant Analysis"; sheet.Cells[setupName, 1] = "Setup"; sheet.Cells[setupName, 2] = "Prior Probability of 0"; sheet.Cells[setupName, 3] = "Misclassication cost: 0 as 1"; sheet.Cells[setupName, 4] = "Misclassication cost: 1 as 0"; ((Range)sheet.Cells[setupName, 1]).EntireColumn.AutoFit(); ((Range)sheet.Cells[setupName, 2]).EntireColumn.AutoFit(); ((Range)sheet.Cells[setupName, 3]).EntireColumn.AutoFit(); ((Range)sheet.Cells[setupName, 4]).EntireColumn.AutoFit(); sheet.Cells[setupName + 1, 2] = probability; sheet.Cells[setupName + 1, 3] = misclassification0; sheet.Cells[setupName + 1, 4] = misclassification1; sheet.Cells[summaryName, 1] = "Sample Summary"; sheet.Cells[summaryName + 1, 1] = "0"; sheet.Cells[summaryName + 2, 1] = "1"; sheet.Cells[summaryName, 2] = "Sample Size"; for (var i = 0; i < independentVariables.Count; i++) { sheet.Cells[summaryName, 3 + i] = "Mean " + independentVariables[i].name; } sheet.Cells[functionName, 1] = "Discriminant Function"; sheet.Cells[functionName, 2] = "Coefficient"; for (var i = 0; i < independentVariables.Count; i++) { sheet.Cells[functionName + 1 + i, 1] = independentVariables[i].name; } sheet.Cells[matrixName, 1] = "Classification Matrix"; sheet.Cells[matrixName + 1, 1] = "0"; sheet.Cells[matrixName + 2, 1] = "1"; sheet.Cells[matrixName, 2] = "0"; sheet.Cells[matrixName, 3] = "1"; sheet.Cells[matrixName, 4] = "Correct"; sheet.Cells[classificationName, 1] = "Classification Summary"; sheet.Cells[classificationName + 1, 1] = "Correct"; sheet.Cells[classificationName + 2, 1] = "Base"; sheet.Cells[classificationName + 3, 1] = "Improvement"; sheet.Cells[covarsName, 1] = "Covariance Matrix"; for (var i = 0; i < independentVariables.Count; i++) { sheet.Cells[covarsName + 1 + i, 1] = independentVariables[i].name; sheet.Cells[covarsName, 2 + i] = independentVariables[i].name; } sheet.Cells[cutOffName, 1] = "Cut off "; sheet.Cells[dataName, 1] = "Data"; for (var i = 0; i < independentVariables.Count; i++) { sheet.Cells[dataName, 2 + i] = independentVariables[i].name; } sheet.Cells[dataName, discrimName] = "Discriminant"; sheet.Cells[dataName, discrimName + 1] = "Class"; sheet.Cells[dataName, discrimName + 2] = "Analysis"; var mean = new double[independentVariables.Count]; var mean0 = Matrix <double> .Build.Dense(independentVariables.Count, 1); var mean1 = Matrix <double> .Build.Dense(independentVariables.Count, 1); // mean for all X for (var i = 0; i < independentVariables.Count; i++) { mean[i] = matrixX.Column(i).Average(); } var n = dataSet.rangeSize(); var n0 = 0; var n1 = 0; var total0 = new double[independentVariables.Count]; var total1 = new double[independentVariables.Count]; //mean for X_0 and X_1, total elements n_0 and n_1 for (var i = 0; i < dataSet.rangeSize(); i++) { if (matrixY[i, 0] == 0) { n0++; for (var j = 0; j < independentVariables.Count; j++) { total0[j] = total0[j] + matrixX[i, j]; } } else { n1++; for (var j = 0; j < independentVariables.Count; j++) { total1[j] = total1[j] + matrixX[i, j]; } } } sheet.Cells[summaryName + 1, 2] = n0; sheet.Cells[summaryName + 2, 2] = n1; for (var i = 0; i < independentVariables.Count; i++) { mean0[i, 0] = total0[i] / Convert.ToDouble(n0); mean1[i, 0] = total1[i] / Convert.ToDouble(n1); sheet.Cells[summaryName + 1, 3 + i] = mean0[i, 0]; sheet.Cells[summaryName + 2, 3 + i] = mean1[i, 0]; } // create X_average matrix var matrixXAver = Matrix <double> .Build.Dense(dataSet.rangeSize(), independentVariables.Count); for (var i = 0; i < dataSet.rangeSize(); i++) { if (matrixY[i, 0] == 0) { for (var j = 0; j < independentVariables.Count; j++) { matrixXAver[i, j] = mean0[j, 0]; } } else { for (var j = 0; j < independentVariables.Count; j++) { matrixXAver[i, j] = mean1[j, 0]; } } } var matrixXTransposed = matrixX.Transpose() * matrixX; var matrixXAverTransposed = matrixXAver.Transpose() * matrixXAver; var matrixS = Matrix <double> .Build.Dense(matrixXTransposed.RowCount, matrixXTransposed.ColumnCount); for (var i = 0; i < matrixS.RowCount; i++) { for (var j = 0; j < matrixS.ColumnCount; j++) { matrixS[i, j] = (matrixXTransposed[i, j] - matrixXAverTransposed[i, j]) / (n - independentVariables.Count); sheet.Cells[covarsName + 1 + i, 2 + j] = matrixS[i, j]; } } var matrixSInv = matrixS.Inverse(); var matrixResult = matrixSInv * (mean0 - mean1); for (var i = 0; i < matrixResult.RowCount; i++) { sheet.Cells[functionName + 1 + i, 2] = matrixResult[i, 0]; } var z0 = mean0.Transpose() * matrixResult; var z1 = mean1.Transpose() * matrixResult; var cutOff = (z0[0, 0] + z1[0, 0]) / 2 + Math.Log(Convert.ToDouble(misclassification1) * (1 - Convert.ToDouble(probability)) / Convert.ToDouble(misclassification0) / Convert.ToDouble(probability)); sheet.Cells[cutOffName + 1, 1] = cutOff; var correctClass0 = 0; var correctClass1 = 0; for (var i = 0; i < dataSet.rangeSize(); i++) { sheet.Cells[dataName + 1 + i, 1] = i + 1; double discrim = 0; for (var j = 0; j < independentVariables.Count; j++) { sheet.Cells[dataName + 1 + i, j + 2] = matrixX[i, j]; discrim = discrim + matrixX[i, j] * matrixResult[j, 0]; } sheet.Cells[dataName + 1 + i, discrimName] = discrim; sheet.Cells[dataName + 1 + i, discrimName + 1] = matrixY[i, 0]; if (discrim > cutOff) { sheet.Cells[dataName + 1 + i, discrimName + 2] = 0; if (matrixY[i, 0] == 0) { correctClass0++; } } else { sheet.Cells[dataName + 1 + i, discrimName + 2] = 1; if (matrixY[i, 0] == 1) { correctClass1++; } } } sheet.Cells[matrixName + 1, 2] = correctClass0; sheet.Cells[matrixName + 1, 3] = n0 - correctClass0; sheet.Cells[matrixName + 2, 3] = correctClass1; sheet.Cells[matrixName + 2, 2] = n1 - correctClass1; sheet.Cells[matrixName + 1, 4] = Convert.ToDouble(correctClass0) / Convert.ToDouble(n0); sheet.Cells[matrixName + 2, 4] = Convert.ToDouble(correctClass1) / Convert.ToDouble(n1); var correct = Convert.ToDouble(correctClass0 + correctClass1) / Convert.ToDouble(n); double basis; if (Convert.ToDouble(n0) * 2 > Convert.ToDouble(n)) { basis = Convert.ToDouble(n0) / Convert.ToDouble(n); } else { basis = Convert.ToDouble(n1) / Convert.ToDouble(n); } var improvement = (correct - basis) / (1 - basis); sheet.Cells[classificationName + 1, 2] = correct; sheet.Cells[classificationName + 2, 2] = basis; sheet.Cells[classificationName + 3, 2] = improvement; return(true); }
// vanaf hier copy van oude code public void Print(DataSet dataSet, List <Variable> variables, bool rdbMovingAverage, bool rdbSimpleExponentialSmoothing, bool rdbHoltsExponentialSmoothing, bool rdbWintersExponentialSmoothing, bool doOptimizeParameters, int numberOfForecasts, int numberOfHoldouts, int seasonalPeriod, int span, string level, string trend, string seasonality) { // Create new sheet var sheet = WorksheetHelper.NewWorksheet("Forecast"); // use variables _doOptimizeParameters = doOptimizeParameters; _numberOfForecasts = numberOfForecasts; _numberOfHoldouts = numberOfHoldouts; _seasonalPeriod = seasonalPeriod; _span = span; // variables for writing on sheet and writing of title int title = 1; int forcastingConstants = 3; int summaryName = forcastingConstants + 5; int row = summaryName + 5; sheet.Cells[title, 1] = "Forecast "; sheet.Cells[forcastingConstants, 1] = "Forecasting Constants"; sheet.Cells[summaryName, 1] = "Summary"; sheet.Cells[summaryName + 1, 1] = "Mean Absolute Error"; sheet.Cells[summaryName + 2, 1] = "Root Mean Squared Error"; sheet.Cells[summaryName + 3, 1] = "Mean Absolute Percentage Error"; ((Range)sheet.Cells[row, 1]).EntireColumn.AutoFit(); // variables to count number of forecast, holdouts and data var nForecasts = Convert.ToDouble(numberOfForecasts); var nHoldouts = Convert.ToDouble(numberOfHoldouts); var nData = Convert.ToDouble(dataSet.rangeSize()); int column = 2; foreach (Variable variable in variables) { row = 1; double[,] array = RangeHelper.To2DDoubleArray(variable.getRange()); double[] array2 = new double[array.Length]; for (int i = 0; i < array.Length; i++) { array2[i] = array[i, 0]; // hier moet nog iets komen waardoor het mogelijk wordt om variabelen in rijen te verwerken } //Print data column++; sheet.Cells[row++, column] = variable.name; for (int i = 0; i < array2.Length; i++) { sheet.Cells[row++, column] = array2[i]; } row = 1; column++; // Plot next figure below the data and the forecast int rowFigure = row + variables.Count + Convert.ToInt16(nForecasts) + 2; // Calculate moving average forecast if box is checked if (rdbMovingAverage) { EvaluateMovingAverage(array2, row, column, sheet); string name = "Forecast Moving Average: " + variable.name; //var rangeLabels = sheet.Range[sheet.Cells[row + 1, column], sheet.Cells[row + nData + nForecasts, column]]; var rangeData = sheet.Range[sheet.Cells[row + 2, column - 1], sheet.Cells[row + nData + nForecasts, column - 1]]; var rangeForecast = sheet.Range[sheet.Cells[row + 2, column], sheet.Cells[row + nData + nForecasts, column]]; new TimeSeriesGraph().CreateNewGraph2(sheet, rowFigure, rangeData, rangeForecast, name); } // Calculate exponential smoothing (simple) forecast if box is checked if (rdbSimpleExponentialSmoothing) { CalculateSimple(array2, row, sheet, column); string name = "Forecast Exponential smoothing (Simple): " + variable.name; //var rangeLabels = sheet.Range[sheet.Cells[row + 1, column], sheet.Cells[row + nData + nForecasts, column]]; var rangeData = sheet.Range[sheet.Cells[row + 2, column - 1], sheet.Cells[row + nData + nForecasts, column - 1]]; var rangeForecast = sheet.Range[sheet.Cells[row + 2, column + 1], sheet.Cells[row + nData + nForecasts, column + 1]]; new TimeSeriesGraph().CreateNewGraph2(sheet, rowFigure, rangeData, rangeForecast, name); } // Calculate exponential smoothing (holt) forecast if box is checked if (rdbHoltsExponentialSmoothing) { CalculateHolt(array2, row, sheet, column); string name = "Forecast Exponential smoothing (Holt): " + variable.name; //var rangeLabels = sheet.Range[sheet.Cells[row + 1, column], sheet.Cells[row + nData + nForecasts, column]]; var rangeData = sheet.Range[sheet.Cells[row + 2, column - 1], sheet.Cells[row + nData + nForecasts, column - 1]]; var rangeForecast = sheet.Range[sheet.Cells[row + 2, column + 2], sheet.Cells[row + nData + nForecasts, column + 2]]; new TimeSeriesGraph().CreateNewGraph2(sheet, rowFigure, rangeData, rangeForecast, name); } // Calculate exponential smoothing (winters) forecast if box is checked if (rdbWintersExponentialSmoothing) { CalculateWinter(array2, row, sheet, column); string name = "Forecast Exponential smoothing (Winter): " + variable.name; //var rangeLabels = sheet.Range[sheet.Cells[row + 1, column], sheet.Cells[row + nData + nForecasts, column]]; var rangeData = sheet.Range[sheet.Cells[row + 2, column - 1], sheet.Cells[row + nData + nForecasts, column - 1]]; var rangeForecast = sheet.Range[sheet.Cells[row + 2, column + 3], sheet.Cells[row + nData + nForecasts, column + 3]]; new TimeSeriesGraph().CreateNewGraph2(sheet, rowFigure, rangeData, rangeForecast, name); } column = column + 6; } }
public void createChiKwadraatPlot(DataSet dataSet) { string range = model.range.Replace("$", null); //string range = model.range; //string[] splitted = range.Split(':'); string first = range.Split(':')[0]; string second = range.Split(':')[1]; /*if (splitted[0].Length > 4) * { * int i = splitted[0].Length; * first = splitted[0][1] + Convert.ToString(splitted[0][3]); * int j = 4; * while (j < i) * { * first = first + Convert.ToString(splitted[0][j]); * j++; * } * } * else * { * first = splitted[0][1] + Convert.ToString(splitted[0][3]); * } * if (splitted[1].Length > 4) * { * int i = splitted[1].Length; * second = splitted[1][1] + Convert.ToString(splitted[1][3]); * int j = 4; * while (j < i) * { * second = second + Convert.ToString(splitted[1][j]); * j++; * } * } * else * { * second = splitted[1][1] + Convert.ToString(splitted[1][3]); * }*/ _Worksheet sheet = WorksheetHelper.NewWorksheet("Chi Square"); Range from = dataSet.getWorksheet().Range[first, second]; int rangeWidth = from.Columns.Count; int rangeHeight = from.Rows.Count; //original Counts sheet.Cells[2, 1] = "Original Counts"; //sheet.get_Range("A2").Cells.HorizontalAlignment = XlHAlign.xlHAlignFill; sheet.get_Range("A2", AddressConverter.CellAddress(2, rangeWidth + 2, false, false)).Borders[XlBordersIndex.xlEdgeBottom].LineStyle = XlLineStyle.xlDouble; Range to = sheet.Range[sheet.Cells[3, 2], sheet.Cells[3, 2].offset(rangeHeight, rangeWidth)]; from.Copy(to); int col = 2; while (col < rangeWidth + 2) { int row = 3; double sum = 0; while (row < rangeHeight + 3) { sum += Convert.ToDouble((sheet.Cells[row, col] as Range).Value); row++; } sheet.Cells[3 + rangeHeight, col] = sum; col++; } sheet.Cells[2, col] = "Total"; int roww = 3; while (roww < rangeHeight + 4) { int coll = 2; double summ = 0; while (coll < rangeWidth + 2) { summ += Convert.ToDouble((sheet.Cells[roww, coll] as Range).Value); coll++; } sheet.Cells[roww, 2 + rangeWidth] = summ; roww++; } sheet.Cells[roww - 1, 1] = "Total"; // percentage of Rows sheet.Cells[rangeHeight + 6, 1] = "Percentage of Rows"; sheet.get_Range("A2", AddressConverter.CellAddress(rangeHeight + 6, rangeWidth + 1, false, false)).Borders[XlBordersIndex.xlEdgeBottom].LineStyle = XlLineStyle.xlDouble; int rowb = rangeHeight + 7; while (rowb < rangeHeight + rangeHeight + 7) { int cola = 2; while (cola < rangeWidth + 2) { double proda = 0; proda = Convert.ToDouble((sheet.Cells[rowb - rangeHeight - 4, cola] as Range).Value) / Convert.ToDouble((sheet.Cells[rowb - rangeHeight - 4, rangeWidth + 2] as Range).Value); sheet.Cells[rowb, cola] = Math.Round(proda * 100, 2); //sheet.Range[rowb, cola].NumberFormat = "0.00%"; cola++; } rowb++; } //Percentage of Colums sheet.Cells[rangeHeight + rangeHeight + 9, 1] = "Percentage of Colums"; sheet.get_Range("A2", AddressConverter.CellAddress(2 * rangeHeight + 9, rangeWidth + 1, false, false)).Borders[XlBordersIndex.xlEdgeBottom].LineStyle = XlLineStyle.xlDouble; int rowd = rangeHeight + rangeHeight + 10; while (rowd < rangeHeight + rangeHeight + rangeHeight + 10) { int colc = 2; while (colc < rangeWidth + 2) { double prodc = 0; prodc = Convert.ToDouble((sheet.Cells[rowd - rangeHeight - rangeHeight - 7, colc] as Range).Value) / Convert.ToDouble((sheet.Cells[3 + rangeHeight, colc] as Range).Value); sheet.Cells[rowd, colc] = Math.Round(prodc * 100, 2); //sheet.Range[rowd, colc].NumberFormat = "0.00%"; colc++; } rowd++; } // Expected Counts sheet.Cells[3 * (rangeHeight) + 12, 1] = "Expected Counts"; sheet.get_Range("A2", AddressConverter.CellAddress(3 * rangeHeight + 12, rangeWidth + 1, false, false)).Borders[XlBordersIndex.xlEdgeBottom].LineStyle = XlLineStyle.xlDouble; int rowe = 3 * rangeHeight + 13; while (rowe < 4 * (rangeHeight) + 13) { int cole = 2; while (cole < rangeWidth + 2) { double prode = 0; prode = Convert.ToDouble((sheet.Cells[rangeHeight + 3, cole] as Range).Value) * Convert.ToDouble((sheet.Cells[rowe - 3 * (rangeHeight) - 10, rangeWidth + 2] as Range).Value) / Convert.ToDouble((sheet.Cells[rangeHeight + 3, rangeWidth + 2] as Range).Value); sheet.Cells[rowe, cole] = prode; cole++; } rowe++; } //distance ftom expected sheet.Cells[4 * rangeHeight + 15, 1] = "Distance from Expected"; sheet.get_Range("A2", AddressConverter.CellAddress(4 * rangeHeight + 15, rangeWidth + 1, false, false)).Borders[XlBordersIndex.xlEdgeBottom].LineStyle = XlLineStyle.xlDouble; int rowf = 4 * rangeHeight + 16; while (rowf < 5 * rangeHeight + 16) { int colf = 2; while (colf < rangeWidth + 2) { double prodf = 0; double tussenprod = Convert.ToDouble((sheet.Cells[rowf - 4 * rangeHeight - 13, colf] as Range).Value) - Convert.ToDouble((sheet.Cells[rowf - rangeHeight - 3, colf] as Range).Value); prodf = Math.Pow(tussenprod, 2) / Convert.ToDouble((sheet.Cells[rowf - rangeHeight - 3, colf] as Range).Value); sheet.Cells[rowf, colf] = Math.Round(prodf, 4); colf++; } rowf++; } //chi square dings sheet.Cells[5 * rangeHeight + 18, 1] = "Chi-Square Statistic"; sheet.Cells[5 * rangeHeight + 19, 1] = "CHi-Square"; sheet.Cells[5 * rangeHeight + 20, 1] = "p-value"; //=1-CHIKW.VERD(B39;3*3;1) sheet.get_Range("A2", AddressConverter.CellAddress(5 * rangeHeight + 18, 2, false, false)).Borders[XlBordersIndex.xlEdgeBottom].LineStyle = XlLineStyle.xlDouble; int rowg = 4 * rangeHeight + 16; double som = 0; while (rowg < 5 * rangeHeight + 16) { int colg = 2; while (colg < rangeWidth + 2) { som += Convert.ToDouble((sheet.Cells[rowg, colg] as Range).Value); colg++; } rowg++; } sheet.Cells[5 * rangeHeight + 19, 2] = som; sheet.Cells[5 * rangeHeight + 20, 2] = sheet.Application.WorksheetFunction.ChiSq_Dist(som, (rangeHeight - 1) * (rangeWidth - 1), false); //autofit ((Range)sheet.Cells[1, 1]).EntireColumn.AutoFit(); //((Range)sheet.Cells[1, 2]).EntireColumn.AutoFit(); //((Range)sheet.Cells[1, 3]).EntireColumn.AutoFit(); //((Range)sheet.Cells[1, 4]).EntireColumn.AutoFit(); //((Range)sheet.Cells[1, 5]).EntireColumn.AutoFit(); //((Range)sheet.Cells[1, 6]).EntireColumn.AutoFit(); //((Range)sheet.Cells[1, 7]).EntireColumn.AutoFit(); //((Range)sheet.Cells[1, 8]).EntireColumn.AutoFit(); //((Range)sheet.Cells[1, 9]).EntireColumn.AutoFit(); //((Range)sheet.Cells[1, 10]).EntireColumn.AutoFit(); // Merge Headers sheet.Range[sheet.Cells[1, 2], sheet.Cells[1, rangeWidth + 2]].Merge(); sheet.Range[sheet.Cells[rangeHeight + 5, 2], sheet.Cells[rangeHeight + 5, rangeWidth + 1]].Merge(); sheet.Range[sheet.Cells[2 * rangeHeight + 8, 2], sheet.Cells[2 * rangeHeight + 8, rangeWidth + 1]].Merge(); sheet.Range[sheet.Cells[3 * rangeHeight + 11, 2], sheet.Cells[3 * rangeHeight + 11, rangeWidth + 1]].Merge(); sheet.Range[sheet.Cells[4 * rangeHeight + 14, 2], sheet.Cells[4 * rangeHeight + 14, rangeWidth + 1]].Merge(); // Build Headers var header = ""; if (model.hasRowTitle) { header += "Rows: {0}"; } if (model.hasRowTitle && model.hasColTitle) { header += " / "; } if (model.hasColTitle) { header += "Columns: {1}"; } header = String.Format(header, model.rowTitle, model.colTitle); // Fill in Headers sheet.Cells[1, 2] = header; sheet.Cells[rangeHeight + 5, 2] = header; sheet.Cells[2 * rangeHeight + 8, 2] = header; sheet.Cells[3 * rangeHeight + 11, 2] = header; sheet.Cells[4 * rangeHeight + 14, 2] = header; // Center Headers sheet.Cells[1, 2].HorizontalAlignment = XlHAlign.xlHAlignCenter; sheet.Cells[rangeHeight + 5, 2].HorizontalAlignment = XlHAlign.xlHAlignCenter; sheet.Cells[2 * rangeHeight + 8, 2].HorizontalAlignment = XlHAlign.xlHAlignCenter; sheet.Cells[3 * rangeHeight + 11, 2].HorizontalAlignment = XlHAlign.xlHAlignCenter; sheet.Cells[4 * rangeHeight + 14, 2].HorizontalAlignment = XlHAlign.xlHAlignCenter; // Row and column titles if (model.hasRowColHeaders) { String[] rowTitles = new String[rangeHeight]; String[] colTitles = new String[rangeWidth]; for (int i = 0; i < rangeHeight; i++) { rowTitles[i] = dataSet.getWorksheet().Range[first].Offset[i, -1].Value; } for (int i = 0; i < rangeWidth; i++) { colTitles[i] = dataSet.getWorksheet().Range[first].Offset[-1, i].Value; } for (int i = 0; i < rangeHeight; i++) { sheet.Cells[3 + i, 1] = rowTitles[i]; sheet.Cells[7 + rangeHeight + i, 1] = rowTitles[i]; sheet.Cells[10 + 2 * rangeHeight + i, 1] = rowTitles[i]; sheet.Cells[13 + 3 * rangeHeight + i, 1] = rowTitles[i]; sheet.Cells[16 + 4 * rangeHeight + i, 1] = rowTitles[i]; } for (int i = 0; i < rangeWidth; i++) { sheet.Cells[2, 2 + i] = colTitles[i]; sheet.Cells[6 + rangeHeight, 2 + i] = colTitles[i]; sheet.Cells[9 + 2 * rangeHeight, 2 + i] = colTitles[i]; sheet.Cells[12 + 3 * rangeHeight, 2 + i] = colTitles[i]; sheet.Cells[15 + 4 * rangeHeight, 2 + i] = colTitles[i]; } } }
public bool checkInput(List <Variable> variables, DataSet selectedDataSet, bool rdbMean, bool rdbMedian) { if (variables.Count == 0) { return(false); } _Worksheet worksheet = WorksheetHelper.NewWorksheet("Runs test"); int column = 1; int row = 2; worksheet.Cells[row, column] = "Runs test for randomness"; worksheet.Cells[row++, column] = "Observations"; if (rdbMean) { worksheet.Cells[row++, column] = "Mean"; } if (rdbMedian) { worksheet.Cells[row++, column] = "Median"; } worksheet.Cells[row++, column] = "Below cutoff"; worksheet.Cells[row++, column] = "Above cutoff"; worksheet.Cells[row++, column] = "Number of runs"; worksheet.Cells[row++, column] = "E(R)"; worksheet.Cells[row++, column] = "Stddev(R)"; worksheet.Cells[row++, column] = "Z-Value"; worksheet.Cells[row++, column] = "P-Value (two-tailed)"; ((Range)worksheet.Cells[row, column]).EntireColumn.AutoFit(); row = 1; column = 2; foreach (Variable variable in variables) { worksheet.Cells[row++, column] = variable.name; var range = variable.getRange().Address(true, true, true); worksheet.Cells[row++, column] = selectedDataSet.rangeSize(); var ntotal = selectedDataSet.rangeSize(); if (rdbMean) { worksheet.Cells[row++, column] = "=AVERAGE(" + range + ")"; } if (rdbMedian) { worksheet.Cells[row++, column] = "=MEDIAN(" + range + ")"; } var cutoffValue = (double)worksheet.Cells[row - 1, column].Value; int amountOfRuns = calculateRuns(worksheet, selectedDataSet, variable.getRange(), cutoffValue); worksheet.WriteFunction(row++, column, "COUNTIF(" + range + ",\"<\"&" + AddressConverter.CellAddress(row - 2, column) + ")"); worksheet.WriteFunction(row++, column, "COUNTIF(" + range + ",\">\"&" + AddressConverter.CellAddress(row - 3, column) + ")"); worksheet.Cells[row++, column] = amountOfRuns; worksheet.WriteFunction(row++, column, "1 + (2*" + AddressConverter.CellAddress(row - 4, column) + "*" + AddressConverter.CellAddress(row - 3, column) + ")/(" + AddressConverter.CellAddress(row - 4, column) + "+" + AddressConverter.CellAddress(row - 3, column) + ")"); worksheet.WriteFunction(row++, column, "SQRT(2*" + AddressConverter.CellAddress(row - 5, column) + "*" + AddressConverter.CellAddress(row - 4, column) + "*(2*" + AddressConverter.CellAddress(row - 5, column) + "*" + AddressConverter.CellAddress(row - 4, column) + "-" + AddressConverter.CellAddress(row - 5, column) + "-" + AddressConverter.CellAddress(row - 4, column) + ")/((" + AddressConverter.CellAddress(row - 5, column) + "+" + AddressConverter.CellAddress(row - 4, column) + ")*(" + AddressConverter.CellAddress(row - 5, column) + "+" + AddressConverter.CellAddress(row - 4, column) + ")*(" + AddressConverter.CellAddress(row - 5, column) + "+" + AddressConverter.CellAddress(row - 4, column) + "-1)))"); worksheet.WriteFunction(row++, column, "(" + AddressConverter.CellAddress(row - 4, column) + "-" + AddressConverter.CellAddress(row - 3, column) + ")/" + AddressConverter.CellAddress(row - 2, column)); worksheet.WriteFunction(row++, column, "2*(1-NORMSDIST(ABS(" + AddressConverter.CellAddress(row - 2, column) + ")))"); ((Range)worksheet.Cells[row, column]).EntireColumn.AutoFit(); row = 1; column++; } return(true); }
public void createHypothese(List <Variable> variables, DataSet dataSet) { _Worksheet worksheet = WorksheetHelper.NewWorksheet("Hypothesis test"); if (variables.Count == 1) { foreach (Variable variable in variables) { worksheet.Cells[1, 1] = "Hypothesis test"; worksheet.Cells[1, 2] = variable.name; worksheet.Cells[2, 1] = "Sample size"; worksheet.Cells[2, 2] = "=ROWS(" + dataSet.getWorksheet().Name + "!" + variable.Range + ")"; worksheet.Cells[3, 1] = "Sample mean"; worksheet.Cells[3, 2] = "=AVERAGE(" + dataSet.getWorksheet().Name + "!" + variable.Range + ")"; worksheet.Cells[4, 1] = "Sample Std. Dev"; worksheet.Cells[4, 2] = "=STDEV(" + dataSet.getWorksheet().Name + "!" + variable.Range + ")"; if (model.equal) { PrintCategories(worksheet, 5, "equal", dataSet); } else if (model.greater) { PrintCategories(worksheet, 5, "greater", dataSet); } } } else if (variables.Count == 2) { worksheet.Cells[1, 1] = "Hypothesis test"; worksheet.Cells[1, 2] = variables[0].name + " - " + variables[1].name; worksheet.Cells[2, 1] = "Sample size"; worksheet.Cells[2, 2] = "=ROWS(" + dataSet.getWorksheet().Name + "!" + variables[0].Range + ")"; worksheet.Cells[3, 1] = "Sample mean"; worksheet.Cells[3, 2] = "=AVERAGE(" + dataSet.getWorksheet().Name + "!" + variables[0].Range + ") - AVERAGE(" + dataSet.getWorksheet().Name + "!" + variables[1].Range + ")"; worksheet.Cells[4, 1] = "Sample Std. Dev"; int length = Convert.ToInt32((worksheet.Cells[2, 2] as Range).Value); double[] vals1 = new double[length]; string ran1 = variables[0].Range.ToString(); Array arr1 = dataSet.getWorksheet().Range[ran1].Value; double[] vals2 = new double[length]; string ran2 = variables[1].Range.ToString(); Array arr2 = dataSet.getWorksheet().Range[ran2].Value; int count = 0; foreach (var item in arr1) { double temp = Convert.ToDouble(item); vals1[count] = temp; count = count + 1; } count = 0; foreach (var item in arr2) { double temp = Convert.ToDouble(item); vals2[count] = temp; count = count + 1; } count = 0; while (count < vals2.Length) { vals2[count] = vals2[count] - vals1[count]; count++; } double std = worksheet.Application.WorksheetFunction.StDev(vals2); worksheet.Cells[4, 2] = std; //worksheet.Cells[4, 2] = "=STDEV(" + dataSet.getWorksheet().Name + "!" + variables[0].Range + ") + STDEV(" + dataSet.getWorksheet().Name + "!" + variables[1].Range + ")"; if (model.equal) { PrintCategories(worksheet, 5, "equal", dataSet); } else if (model.greater) { PrintCategories(worksheet, 5, "greater", dataSet); } } }
//public void createRegression(List<Variable> variables) public void createDiscriminant(List <Variable> variablesD, List <Variable> variablesI, DataSet dataSet) { _Worksheet sheet = WorksheetHelper.NewWorksheet("Discriminant"); sheet.Cells[100, 100] = "=ROWS(" + dataSet.getWorksheet().Name + "!" + variablesI[0].Range + ")"; int length = Convert.ToInt32((sheet.Cells[100, 100] as Range).Value); sheet.Cells[100, 100] = ""; int row = 1; int column = 1; sheet.Cells[2, 1] = "Sample Summary"; sheet.Cells[3, 1] = "No"; sheet.Cells[4, 1] = "Yes"; sheet.Cells[1, 2] = "Sample"; sheet.Cells[2, 2] = "Size"; int i = 0; while (i < variablesI.Count) { sheet.Cells[1, 3 + i] = "Mean"; sheet.Cells[2, 3 + i] = variablesI[i].name; sheet.Cells[8 + i, 1] = variablesI[i].name; i++; } row = 8 + i - 1 + 3; column = 3 + i - 1; string let = ColumnIndexToColumnLetter(3 + i - 1); List <double[]> final = fillTable1(variablesD, variablesI, dataSet, 3, sheet); double[] coef = calcCoef(final, variablesI.Count, sheet); sheet.get_Range("A2", "J200").Cells.HorizontalAlignment = XlHAlign.xlHAlignCenter; sheet.get_Range("A2", let + "2").Borders[XlBordersIndex.xlEdgeBottom].LineStyle = XlLineStyle.xlDouble; sheet.Cells[7, 1] = "Discriminant Function"; sheet.Cells[7, 2] = "Coefficient"; sheet.get_Range("A7", "B7").Borders[XlBordersIndex.xlEdgeBottom].LineStyle = XlLineStyle.xlDouble; sheet.Cells[row, 1] = "Classification Matrix"; sheet.Cells[row, 2] = "No"; sheet.Cells[row, 3] = "Yes"; sheet.Cells[row, 4] = "Correct"; double cut = cutoff(coef, final, variablesI.Count, sheet); string num = Convert.ToString(row); row++; sheet.Cells[row++, 1] = "No"; sheet.Cells[row, 1] = "Yes"; classification(cut, coef, final, variablesI.Count, sheet, row); sheet.get_Range("A" + num, "D" + num).Borders[XlBordersIndex.xlEdgeBottom].LineStyle = XlLineStyle.xlDouble; row = row + 3; num = Convert.ToString(row); sheet.Cells[row++, 1] = "Summary Classification"; sheet.Cells[row++, 1] = "Correct"; sheet.Cells[row++, 1] = "Base"; sheet.Cells[row, 1] = "Improvement"; sheet.get_Range("A" + num, "B" + num).Borders[XlBordersIndex.xlEdgeBottom].LineStyle = XlLineStyle.xlDouble; //sheet.get_Range("B3", "B5").NumberFormat = "0.0000"; //sheet.get_Range("B11", AddressConverter.CellAddress(14, variables.Count + 1, false, false)).NumberFormat = "0.000"; sheet.get_Range("B18", "B20").NumberFormat = "0.0000"; sheet.get_Range("D18", "E19").NumberFormat = "0.0000"; row = row + 3; num = Convert.ToString(row); sheet.Cells[row++, 1] = "Cutoff Score"; sheet.get_Range("A" + num, "A" + num).Borders[XlBordersIndex.xlEdgeBottom].LineStyle = XlLineStyle.xlDouble; sheet.Cells[row, 1] = cut; //sheet.get_Range("B24", AddressConverter.CellAddress(23 + c, 2, false, false)).NumberFormat = "0.0000"; //sheet.get_Range("C24", AddressConverter.CellAddress(23 + c, 8, false, false)).NumberFormat = "0.000000"; //sheet.get_Range("C24", AddressConverter.CellAddress(23 + c, 8, false, false)).Cells.HorizontalAlignment = XlHAlign.xlHAlignRight; Globals.ExcelAddIn.Application.ActiveWindow.DisplayGridlines = false; ((Range)sheet.Cells[1, 1]).EntireColumn.AutoFit(); ((Range)sheet.Cells[1, 2]).EntireColumn.AutoFit(); ((Range)sheet.Cells[1, 3]).EntireColumn.AutoFit(); ((Range)sheet.Cells[1, 4]).EntireColumn.AutoFit(); ((Range)sheet.Cells[1, 5]).EntireColumn.AutoFit(); ((Range)sheet.Cells[1, 6]).EntireColumn.AutoFit(); }
/// <summary> /// Print the Discriminant Analysis to a new <see cref="Microsoft.Office.Interop.Excel._Worksheet"/>. /// </summary> /// <param name="dataSet">The <see cref="DataSet"/> which needs (a) Scatterplot(s).</param> /// <param name="doIncludeX">A <see cref="List{T}"/> of <see cref="bool"/>s that corresponds to which <see cref="Models.Data"/> in the <see cref="DataSet.DataList"/> should be included for X.</param> /// <param name="doIncludeY">A <see cref="List{T}"/> of <see cref="bool"/>s that corresponds to which <see cref="Models.Data"/> in the <see cref="DataSet.DataList"/> should be included for Y.</param> /// <param name="probablility">The prior probability.</param> /// <param name="misclassification0">Misclassification cost 0 as 1.</param> /// <param name="misclassification1">Misclassification cost 1 as 0.</param> public bool Print(DataSet dataSet, List <bool> doIncludeX, List <bool> doIncludeY, string probablility, string misclassification0, string misclassification1) { var valuesArraysX = new List <Models.Data>(); var valuesArraysY = new List <Models.Data>(); var sheet = WorksheetHelper.NewWorksheet("Discriminant Analysis"); // Loop to add X for (var j = 0; j < dataSet.DataList.Count; j++) { // Check if the Set of Data is an X. if (!doIncludeX[j]) { continue; } var safe = true; foreach (var value in dataSet.DataList[j].GetValuesList()) { if (value != null) { continue; } MessageBox.Show(dataSet.DataList[j].Name + " has null data and will not be included.", "NoruST - Discriminant Analysis", MessageBoxButtons.OK, MessageBoxIcon.Warning); safe = false; } // If the Set of Data is an X, add it to the list. if (safe) { valuesArraysX.Add(dataSet.DataList[j]); } } for (var j = 0; j < dataSet.DataList.Count; j++) { // Check if the Set of Data is an Y. if (!doIncludeY[j]) { continue; } // If the Set of Data is Y add to list. valuesArraysY.Add(dataSet.DataList[j]); // only one Y (currently) so break loop if Y is found. break; } // Tables location const int setupName = 3; const int summaryName = setupName + 4; const int functionName = summaryName + 4; var matrixName = functionName + valuesArraysX.Count + 2; var classificationName = matrixName + 4; var covarsName = classificationName + 5; var cutOffName = covarsName + valuesArraysX.Count + 2; var dataName = cutOffName + 3; var discrimName = valuesArraysX.Count + 2; // write tables sheet.Cells[1, 1] = "Discriminant Analysis"; sheet.Cells[setupName, 1] = "Setup"; sheet.Cells[setupName, 2] = "Prior Probability of 0"; sheet.Cells[setupName, 3] = "Misclassication cost: 0 as 1"; sheet.Cells[setupName, 4] = "Misclassication cost: 1 as 0"; sheet.Cells[setupName + 1, 2] = probablility; sheet.Cells[setupName + 1, 3] = misclassification0; sheet.Cells[setupName + 1, 4] = misclassification1; sheet.Cells[summaryName, 1] = "Sample Summary"; sheet.Cells[summaryName + 1, 1] = "0"; sheet.Cells[summaryName + 2, 1] = "1"; sheet.Cells[summaryName, 2] = "Sample Size"; for (var i = 0; i < valuesArraysX.Count; i++) { sheet.Cells[summaryName, 3 + i] = "Mean " + valuesArraysX[i].Name; } sheet.Cells[functionName, 1] = "Discriminant Function"; sheet.Cells[functionName, 2] = "Coefficient"; for (var i = 0; i < valuesArraysX.Count; i++) { sheet.Cells[functionName + 1 + i, 1] = valuesArraysX[i].Name; } sheet.Cells[matrixName, 1] = "Classification Matrix"; sheet.Cells[matrixName + 1, 1] = "0"; sheet.Cells[matrixName + 2, 1] = "1"; sheet.Cells[matrixName, 2] = "0"; sheet.Cells[matrixName, 3] = "1"; sheet.Cells[matrixName, 4] = "Correct"; sheet.Cells[classificationName, 1] = "Classification Summary"; sheet.Cells[classificationName + 1, 1] = "Correct"; sheet.Cells[classificationName + 2, 1] = "Base"; sheet.Cells[classificationName + 3, 1] = "Improvement"; sheet.Cells[covarsName, 1] = "Covariance Matrix"; for (var i = 0; i < valuesArraysX.Count; i++) { sheet.Cells[covarsName + 1 + i, 1] = valuesArraysX[i].Name; sheet.Cells[covarsName, 2 + i] = valuesArraysX[i].Name; } sheet.Cells[cutOffName, 1] = "Cut off "; sheet.Cells[dataName, 1] = "Data"; for (var i = 0; i < valuesArraysX.Count; i++) { sheet.Cells[dataName, 2 + i] = valuesArraysX[i].Name; } sheet.Cells[dataName, discrimName] = "Discriminant"; sheet.Cells[dataName, discrimName + 1] = "Class"; sheet.Cells[dataName, discrimName + 2] = "Analysis"; // create X matrix var matrixX = Matrix <double> .Build.Dense(valuesArraysX[0].GetValuesList().Count, valuesArraysX.Count); for (var i = 0; i < valuesArraysX[0].GetValuesList().Count; i++) { for (var j = 0; j < valuesArraysX.Count; j++) { matrixX[i, j] = valuesArraysX[j].GetValuesArray()[i]; } } var mean = new double[valuesArraysX.Count]; var mean0 = Matrix <double> .Build.Dense(valuesArraysX.Count, 1); var mean1 = Matrix <double> .Build.Dense(valuesArraysX.Count, 1); // mean for all X for (var i = 0; i < valuesArraysX.Count; i++) { mean[i] = matrixX.Column(i).Average(); } var n = valuesArraysY[0].GetValuesList().Count; var n0 = 0; var n1 = 0; var total0 = new double[valuesArraysX.Count]; var total1 = new double[valuesArraysX.Count]; //mean for X_0 and X_1, total elements n_0 and n_1 for (var i = 0; i < valuesArraysY[0].GetValuesList().Count; i++) { if (valuesArraysY[0].GetValuesList()[i] == 0) { n0++; for (var j = 0; j < valuesArraysX.Count; j++) { total0[j] = total0[j] + valuesArraysX[j].GetValuesList()[i]; } } else { n1++; for (var j = 0; j < valuesArraysX.Count; j++) { total1[j] = total1[j] + valuesArraysX[j].GetValuesList()[i]; } } } sheet.Cells[summaryName + 1, 2] = n0; sheet.Cells[summaryName + 2, 2] = n1; for (var i = 0; i < valuesArraysX.Count; i++) { mean0[i, 0] = total0[i] / Convert.ToDouble(n0); mean1[i, 0] = total1[i] / Convert.ToDouble(n1); sheet.Cells[summaryName + 1, 3 + i] = mean0[i, 0]; sheet.Cells[summaryName + 2, 3 + i] = mean1[i, 0]; } // create X_average matrix var matrixXAver = Matrix <double> .Build.Dense(valuesArraysX[0].GetValuesList().Count, valuesArraysX.Count); for (var i = 0; i < valuesArraysY[0].GetValuesList().Count; i++) { if (valuesArraysY[0].GetValuesList()[i] == 0) { for (var j = 0; j < valuesArraysX.Count; j++) { matrixXAver[i, j] = mean0[j, 0]; } } else { for (var j = 0; j < valuesArraysX.Count; j++) { matrixXAver[i, j] = mean1[j, 0]; } } } var matrixXTransposed = matrixX.Transpose() * matrixX; var matrixXAverTransposed = matrixXAver.Transpose() * matrixXAver; var matrixS = Matrix <double> .Build.Dense(matrixXTransposed.RowCount, matrixXTransposed.ColumnCount); for (var i = 0; i < matrixS.RowCount; i++) { for (var j = 0; j < matrixS.ColumnCount; j++) { matrixS[i, j] = (matrixXTransposed[i, j] - matrixXAverTransposed[i, j]) / (n - valuesArraysX.Count); sheet.Cells[covarsName + 1 + i, 2 + j] = matrixS[i, j]; } } var matrixSInv = matrixS.Inverse(); var matrixResult = matrixSInv * (mean0 - mean1); for (var i = 0; i < matrixResult.RowCount; i++) { sheet.Cells[functionName + 1 + i, 2] = matrixResult[i, 0]; } var z0 = mean0.Transpose() * matrixResult; var z1 = mean1.Transpose() * matrixResult; var cutOff = (z0[0, 0] + z1[0, 0]) / 2 + Math.Log(Convert.ToDouble(misclassification1) * (1 - Convert.ToDouble(probablility)) / Convert.ToDouble(misclassification0) / Convert.ToDouble(probablility)); sheet.Cells[cutOffName + 1, 1] = cutOff; var correctClass0 = 0; var correctClass1 = 0; for (var i = 0; i < valuesArraysY[0].GetValuesList().Count; i++) { sheet.Cells[dataName + 1 + i, 1] = i + 1; double discrim = 0; for (var j = 0; j < valuesArraysX.Count; j++) { sheet.Cells[dataName + 1 + i, j + 2] = valuesArraysX[j].GetValuesList()[i]; discrim = discrim + valuesArraysX[j].GetValuesList()[i] * matrixResult[j, 0]; } sheet.Cells[dataName + 1 + i, discrimName] = discrim; sheet.Cells[dataName + 1 + i, discrimName + 1] = valuesArraysY[0].GetValuesList()[i]; if (discrim > cutOff) { sheet.Cells[dataName + 1 + i, discrimName + 2] = 0; if (valuesArraysY[0].GetValuesList()[i] == 0) { correctClass0++; } } else { sheet.Cells[dataName + 1 + i, discrimName + 2] = 1; if (valuesArraysY[0].GetValuesList()[i] == 1) { correctClass1++; } } } sheet.Cells[matrixName + 1, 2] = correctClass0; sheet.Cells[matrixName + 1, 3] = n0 - correctClass0; sheet.Cells[matrixName + 2, 3] = correctClass1; sheet.Cells[matrixName + 2, 2] = n1 - correctClass1; sheet.Cells[matrixName + 1, 4] = Convert.ToDouble(correctClass0) / Convert.ToDouble(n0); sheet.Cells[matrixName + 2, 4] = Convert.ToDouble(correctClass1) / Convert.ToDouble(n1); var correct = Convert.ToDouble(correctClass0 + correctClass1) / Convert.ToDouble(n); double basis; if (Convert.ToDouble(n0) * 2 > Convert.ToDouble(n)) { basis = Convert.ToDouble(n0) / Convert.ToDouble(n); } else { basis = Convert.ToDouble(n1) / Convert.ToDouble(n); } var improvement = (correct - basis) / (1 - basis); sheet.Cells[classificationName + 1, 2] = correct; sheet.Cells[classificationName + 2, 2] = basis; sheet.Cells[classificationName + 3, 2] = improvement; return(true); }
public void createCorrelationCovariance(List <Variable> variablesX, DataSet dataSet) { _Worksheet sheet = WorksheetHelper.NewWorksheet("Correlation Covariance"); int col = 1; int row = 1; int countX = variablesX.Count; int i = 0; sheet.Cells[row, col] = "Correlation"; sheet.Cells[row + countX + 2, col] = "Covariance"; while (i < variablesX.Count) { col = col + 1; sheet.Cells[row, col] = variablesX[i].name.ToString(); i++; } i = 0; col = 1; row = 1; while (i < variablesX.Count) { row = row + 1; sheet.Cells[row, col] = variablesX[i].name.ToString(); i++; } row = countX + 3; col = 1; i = 0; while (i < variablesX.Count) { col = col + 1; sheet.Cells[row, col] = variablesX[i].name.ToString(); i++; } i = 0; col = 1; row = countX + 3; while (i < variablesX.Count) { row = row + 1; sheet.Cells[row, col] = variablesX[i].name.ToString(); i++; } // CORRELATION i = 0; row = 2; col = 2; while (i < variablesX.Count) { String range1 = variablesX[i].Range; int j = 0; while (j < variablesX.Count) { String range2 = variablesX[j].Range; sheet.Cells[row + i, col + j] = "=CORREL(" + dataSet.getWorksheet().Name + "!" + range1 + "," + dataSet.getWorksheet().Name + "!" + range2 + ")"; j++; } i++; } // COVARIANCE i = 0; row = countX + 4; col = 2; while (i < variablesX.Count) { String range1 = variablesX[i].Range; int j = 0; while (j < variablesX.Count) { String range2 = variablesX[j].Range; sheet.Cells[row + i, col + j] = "=COVARIANCE.S(" + dataSet.getWorksheet().Name + "!" + range1 + "," + dataSet.getWorksheet().Name + "!" + range2 + ")"; j++; } i++; } }