public void addLags(Variable variable, int numberOfLags, DataSet dataSet)
{
//for (int i = 1; i <= numberOfLags; i++)
//{
// for (int j = 1; j <= numberOfLags; j++)
// {
// worksheet.Cells[i + 1, range.Columns.Count + j] = 0;
// }
//}
for (int i = 1; i <= numberOfLags; i++)
{
Range source = rangeLayout == COLUMNS
? variable.getRange().extendRangeByRows(-i)
: variable.getRange().extendRangeByColumns(-i);
Range destination = rangeLayout == COLUMNS
? variable.getRange()
.extendRangeByRows(-i, false)
.shiftRangeByColumns(variables.Count - variables.IndexOf(variable) + i - 1)
: variable.getRange()
.extendRangeByColumns(-i, false)
.shiftRangeByRows(variables.Count - variables.IndexOf(variable) + i - 1);
source.Copy(destination);
if (!variableNamesInFirstRowOrColumn)
{
continue;
}
source = rangeLayout == COLUMNS
? variable.getRange().first().shiftRangeByRows(-1)
: variable.getRange().first().shiftRangeByColumns(-1);
destination = rangeLayout == COLUMNS
? variable.getRange()
.first()
.shiftRangeByRows(-1)
.shiftRangeByColumns(variables.Count - variables.IndexOf(variable) + i - 1)
: variable.getRange()
.first()
.shiftRangeByColumns(-1)
.shiftRangeByRows(variables.Count - variables.IndexOf(variable) + i - 1);
destination.Value = source.Value + " Lag " + i.ToString();
}
Globals.ExcelAddIn.Application.CutCopyMode = XlCutCopyMode.xlCopy;
DataSet nwRange = DataSetFactory.modify(dataSet, ColumnIndexToColumnLetter(range.Columns.Count + numberOfLags));
dataSet.setRange(nwRange.getRange());
dataSet.setVariables(nwRange.getVariables());
//range = rangeLayout == COLUMNS
// ? range.Resize[range.Rows.Count, range.Columns.Count + numberOfLags]
// : range.Resize[range.Rows.Count + numberOfLags, range.Columns.Count];
//recalculateVariables();
}
private void recalculateVariables()
{
List <Variable> newVariables = DataSetFactory.createVariables(worksheet, range, rangeLayout, variableNamesInFirstRowOrColumn);
bool variablesAdded = newVariables.Count != variables.Count;
bool variablesLengthened = rangeLayout == COLUMNS
? newVariables.First().getRange().Rows.Count != variables.First().getRange().Rows.Count
: newVariables.First().getRange().Columns.Count != variables.First().getRange().Columns.Count;
bool rangesHaveChanged = variablesAdded || variablesLengthened;
if (rangesHaveChanged || variables.Count == 0)
{
variables.Clear();
foreach (Variable newVariable in newVariables)
{
variables.Add(newVariable);
}
}
}
public void Interaction(Variable variable, Variable variable2, DataSet dataSet) //cat = gender var = salary
{
String colLetter = dataSet.getVariables()[dataSet.getVariables().Count - 1].Range[1].ToString();
int columnIndex = ColumnLetterToColumnIndex(colLetter) + 1;
string ran2 = variable.Range.ToString();
String colLetterCat = variable.Range[1].ToString();
int columnIndexCat = ColumnLetterToColumnIndex(colLetterCat) - 1;
Array distCat = dataSet.getWorksheet().Range[ran2].Value;
Array cat = dataSet.getWorksheet().Range[ran2].Value;
string ran = variable2.Range.ToString();
String colLetterVar = variable2.Range[1].ToString();
int columnIndexVar = ColumnLetterToColumnIndex(colLetterVar) - 1;
Array dist = dataSet.getWorksheet().Range[ran].Value;
int count = 0;
foreach (var item in distCat)
{
if (item == null)
{
count = 2;
}
else if (item.GetType().ToString() == "System.String")
{
count = 1;
break;
}
else
{
count = 2;
break;
}
}
foreach (var item in dist)
{
if (item == null)
{
count = 2;
}
else if (item.GetType().ToString() == "System.String")
{
count = 1;
break;
}
else
{
count = 2;
break;
}
}
if (count == 1)
{
MessageBox.Show("Interaction can only be calculated for numbers.");
return;
}
double[] var1 = distCat.OfType <double>().ToArray();
double[] var2 = dist.OfType <double>().ToArray();
System.Diagnostics.Debug.WriteLine(var1.Length);
System.Diagnostics.Debug.WriteLine(var2.Length);
int row = 1;
int column = columnIndex;
worksheet.Cells[row, column] = dataSet.getVariables()[columnIndexCat].name + " - " + dataSet.getVariables()[columnIndexVar].name;
row = 0;
int len = var1.Length;
if (var2.Length < var1.Length)
{
len = var2.Length;
}
while (row < len)
{
if (var1.Length < var2.Length)
{
column = columnIndex;
int temp = var2.Length - var1.Length;
worksheet.Cells[row + 2 + temp, column] = var1[row] * var2[row + temp];
row = row + 1;
}
else
{
column = columnIndex;
int temp = var1.Length - var2.Length;
worksheet.Cells[row + 2 + temp, column] = var1[row + temp] * var2[row];
row = row + 1;
}
}
DataSet nwRange = DataSetFactory.modify(dataSet, ColumnIndexToColumnLetter(column));
dataSet.setRange(nwRange.getRange());
dataSet.setVariables(nwRange.getVariables());
}
public void addDummy(Variable variable, DataSet dataSet)
{
string ran = variable.Range.ToString();
String colLetter2 = variable.Range[1].ToString();
int columnIndex2 = ColumnLetterToColumnIndex(colLetter2) - 1;
String colLetter = dataSet.getVariables()[dataSet.getVariables().Count - 1].Range[1].ToString();
int columnIndex = ColumnLetterToColumnIndex(colLetter) + 1;
Array dist = dataSet.getWorksheet().Range[ran].Value;
int count = 0;
foreach (var item in dist)
{
if (item.GetType().ToString() == "System.String")
{
count = 1;
}
else
{
count = 2;
}
break;
}
if (count == 1)
{
List <String> values = dist.OfType <String>().ToList();
dist = values.Distinct <String>().ToArray();
int row = 1;
int column = columnIndex;
foreach (var item in dist)
{
worksheet.Cells[row, column] = dataSet.getVariables()[columnIndex2].name + "=" + item.ToString();
column = column + 1;
}
row = 0;
while (row < values.Count)
{
String temp = values[row];
column = columnIndex;
foreach (var item in dist)
{
if (temp.Equals(item.ToString()))
{
worksheet.Cells[row + 2, column] = "1";
}
else
{
worksheet.Cells[row + 2, column] = "0";
}
column = column + 1;
}
row = row + 1;
}
DataSet nwRange = DataSetFactory.modify(dataSet, ColumnIndexToColumnLetter(column - 1));
dataSet.setRange(nwRange.getRange());
dataSet.setVariables(nwRange.getVariables());
}
else
{
List <Double> values = dist.OfType <Double>().ToList();
List <Double> value = dist.OfType <Double>().ToList();
values.Sort();
dist = values.Distinct <Double>().ToArray();
int row = 1;
int column = columnIndex;
foreach (var item in dist)
{
worksheet.Cells[row, column] = dataSet.getVariables()[columnIndex2].name + "=" + item.ToString();
column = column + 1;
}
row = 0;
while (row < values.Count)
{
double temp = value[row];
column = columnIndex;
foreach (var item in dist)
{
if (temp == Convert.ToInt16(item))
{
worksheet.Cells[row + 2, column] = "1";
}
else
{
worksheet.Cells[row + 2, column] = "0";
}
column = column + 1;
}
row = row + 1;
}
DataSet nwRange = DataSetFactory.modify(dataSet, ColumnIndexToColumnLetter(column - 1));
dataSet.setRange(nwRange.getRange());
dataSet.setVariables(nwRange.getVariables());
}
}
