/// <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++;
}
}
