private void MenuFileOpen_Click(object sender, EventArgs e)
{
if (openFileDialog.ShowDialog(this) == DialogResult.OK)
{
string filename = openFileDialog.FileName;
string extension = Path.GetExtension(filename);
if (extension == ".xls" || extension == ".xlsx")
{
ExcelReader db = new ExcelReader(filename, true, false);
TableSelectDialog t = new TableSelectDialog(db.GetWorksheetList());
if (t.ShowDialog(this) == DialogResult.OK)
{
this.sourceTable = db.GetWorksheet(t.Selection);
this.dgvAnalysisSource.DataSource = sourceTable;
this.cbTimeName.Items.Clear();
this.cbEventName.Items.Clear();
this.checkedListBox1.Items.Clear();
foreach (DataColumn col in sourceTable.Columns)
{
this.cbTimeName.Items.Add(col.ColumnName);
this.cbEventName.Items.Add(col.ColumnName);
this.checkedListBox1.Items.Add(col.ColumnName);
}
this.cbTimeName.SelectedIndex = 0;
}
}
}
}
private static void testData1(ExcelReader withHeader, ExcelReader sansHeader)
{
var sheets1 = withHeader.GetWorksheetList();
var sheets2 = sansHeader.GetWorksheetList();
Assert.AreEqual(1, sheets1.Length);
Assert.AreEqual(1, sheets2.Length);
Assert.AreEqual("testnormal", sheets1[0]);
Assert.AreEqual("testnormal", sheets2[0]);
var dataWithHeader = withHeader.GetWorksheet("testnormal");
var dataSansHeader = sansHeader.GetWorksheet("testnormal");
Assert.AreEqual(119, dataWithHeader.Rows.Count);
Assert.AreEqual(17, dataWithHeader.Columns.Count);
Assert.AreEqual(120, dataSansHeader.Rows.Count);
Assert.AreEqual(17, dataSansHeader.Columns.Count);
var firstRowWithHeader = dataWithHeader.Rows[0].ItemArray;
Assert.AreEqual("90", firstRowWithHeader[0]);
Assert.AreEqual("0", firstRowWithHeader[1]);
Assert.AreEqual("0", firstRowWithHeader[2]);
Assert.AreEqual("0", firstRowWithHeader[3]);
Assert.AreEqual("0", firstRowWithHeader[4]);
var firstRowSansHeader = dataSansHeader.Rows[0].ItemArray;
Assert.AreEqual("1", firstRowSansHeader[0]);
Assert.AreEqual("2", firstRowSansHeader[1]);
Assert.AreEqual("3", firstRowSansHeader[2]);
Assert.AreEqual("4", firstRowSansHeader[3]);
Assert.AreEqual("5", firstRowSansHeader[4]);
}
public void ExcelReaderConstructorTest()
{
string path = @"..\..\..\..\Unit Tests\Accord.Tests.Statistics\Resources\sample.xls";
// Create a new reader, opening a given path
ExcelReader reader = new ExcelReader(path);
// Afterwards, we can query the file for all
// worksheets within the specified workbook:
string[] sheets = reader.GetWorksheetList();
// Finally, we can request an specific sheet:
DataTable table = reader.GetWorksheet(sheets[1]);
// Now, we have loaded the Excel file into a DataTable. We
// can go further and transform it into a matrix to start
// running other algorithms on it:
double[,] matrix = table.ToMatrix();
// We can also do it retrieving the name for each column:
string[] columnNames; matrix = table.ToMatrix(out columnNames);
// Or we can extract specific columns into single arrays:
double[] column = table.Columns[0].ToArray();
// PS: you might need to import the Accord.Math namespace in
// order to be able to call the ToMatrix extension methods.
Assert.AreEqual(6, matrix.Length);
Assert.AreEqual(3, columnNames.Length);
Assert.AreEqual(2, column.Length);
}
private void MenuFileOpen_Click(object sender, EventArgs e)
{
if (openFileDialog.ShowDialog(this) == DialogResult.OK)
{
string filename = openFileDialog.FileName;
string extension = Path.GetExtension(filename);
if (extension == ".xls" || extension == ".xlsx")
{
ExcelReader db = new ExcelReader(filename, true, false);
TableSelectDialog t = new TableSelectDialog(db.GetWorksheetList());
if (t.ShowDialog(this) == DialogResult.OK)
{
DataTable tableSource = db.GetWorksheet(t.Selection);
double[,] sourceMatrix = tableSource.ToMatrix(out sourceColumns);
// Detect the kind of problem loaded.
if (sourceMatrix.GetLength(1) == 2)
{
MessageBox.Show("Missing class column.");
}
else
{
this.dgvLearningSource.DataSource = tableSource;
this.dgvTestingSource.DataSource = tableSource.Copy();
CreateScatterplot(graphInput, sourceMatrix);
// enable "Start" button
startButton.Enabled = true;
}
}
}
else if (extension == ".csv")
{
DataTable tableSource = CSVReader.ReadCSVFile(filename.Replace("\\", "//"), true);
double[,] sourceMatrix = tableSource.ToMatrix(out sourceColumns);
// Detect the kind of problem loaded.
if (sourceMatrix.GetLength(1) == 2)
{
MessageBox.Show("Missing class column.");
}
else
{
this.dgvLearningSource.DataSource = tableSource;
this.dgvTestingSource.DataSource = tableSource.Copy();
CreateScatterplot(graphInput, sourceMatrix);
// enable "Start" button
startButton.Enabled = true;
}
}
}
}
public void GaussianMixtureModelTest5()
{
Accord.Math.Tools.SetupGenerator(0);
MemoryStream stream = new MemoryStream(Resources.CircleWithWeights);
ExcelReader reader = new ExcelReader(stream, xlsx: false);
DataTable table = reader.GetWorksheet("Sheet1");
double[,] matrix = table.ToMatrix();
double[][] points = matrix.Submatrix(null, 0, 1).ToArray();
double[] weights = matrix.GetColumn(2);
GaussianMixtureModel gmm = new GaussianMixtureModel(2);
gmm.Compute(points, new GaussianMixtureModelOptions()
{
Weights = weights
});
int a = 0;
int b = 1;
if ((-0.010550720353814949).IsRelativelyEqual(gmm.Gaussians[1].Mean[0], 1e-4))
{
a = 1;
b = 0;
}
Assert.AreEqual(-0.010550720353814949, gmm.Gaussians[a].Mean[0], 1e-4);
Assert.AreEqual(0.40799698773355553, gmm.Gaussians[a].Mean[1], 1e-3);
Assert.AreEqual(0.011896812071918696, gmm.Gaussians[b].Mean[0], 1e-4);
Assert.AreEqual(-0.40400708592859663, gmm.Gaussians[b].Mean[1], 1e-4);
Assert.AreEqual(1, gmm.Gaussians[0].Proportion + gmm.Gaussians[1].Proportion, 1e-15);
Assert.IsFalse(gmm.Gaussians[0].Mean.HasNaN());
Assert.IsFalse(gmm.Gaussians[1].Mean.HasNaN());
}
public void ApplyTest4()
{
string path = @"Resources\intrusion.xls";
ExcelReader db = new ExcelReader(path, false, true);
DataTable table = db.GetWorksheet("test");
Codification codebook = new Codification(table);
DataTable result = codebook.Apply(table);
Assert.IsNotNull(result);
foreach (DataColumn col in result.Columns)
Assert.AreNotEqual(col.DataType, typeof(string));
Assert.IsTrue(result.Rows.Count > 0);
}
public void MixtureWeightsFitTest2()
{
MemoryStream stream = new MemoryStream(Resources.CircleWithWeights);
ExcelReader reader = new ExcelReader(stream, xlsx: false);
DataTable table = reader.GetWorksheet("Sheet1");
double[,] matrix = table.ToMatrix();
double[][] points = matrix.Submatrix(null, 0, 1).ToArray();
double[] weights = matrix.GetColumn(2);
// Randomly initialize some mixture components
MultivariateNormalDistribution[] components = new MultivariateNormalDistribution[2];
components[0] = new MultivariateNormalDistribution(new double[] { 0, 1 }, Matrix.Identity(2));
components[1] = new MultivariateNormalDistribution(new double[] { 1, 0 }, Matrix.Identity(2));
// Create an initial mixture
var mixture = new MultivariateMixture<MultivariateNormalDistribution>(components);
mixture.Fit(points, weights);
// Our model will be:
double mean00 = mixture.Components[0].Mean[0];
double mean01 = mixture.Components[0].Mean[1];
double mean10 = mixture.Components[1].Mean[0];
double mean11 = mixture.Components[1].Mean[1];
Assert.AreEqual(-0.11704994950834195, mean00, 1e-10);
Assert.AreEqual(0.11603470123007256, mean01, 1e-10);
Assert.AreEqual(0.11814483652855159, mean10, 1e-10);
Assert.AreEqual(-0.12029275652994373, mean11, 1e-10);
}
private void MenuFileOpen_Click(object sender, EventArgs e)
{
if (openFileDialog.ShowDialog(this) == DialogResult.OK)
{
string filename = openFileDialog.FileName;
string extension = Path.GetExtension(filename);
if (extension == ".xls" || extension == ".xlsx")
{
ExcelReader db = new ExcelReader(filename, true, false);
TableSelectDialog t = new TableSelectDialog(db.GetWorksheetList());
if (t.ShowDialog(this) == DialogResult.OK)
{
DataTable tableSource = db.GetWorksheet(t.Selection);
double[,] sourceMatrix = tableSource.ToMatrix(out columnNames);
// Detect the kind of problem loaded.
if (sourceMatrix.GetLength(1) == 2)
{
MessageBox.Show("Missing class column.");
}
else
{
this.dgvLearningSource.DataSource = tableSource;
this.dgvTestingSource.DataSource = tableSource.Copy();
CreateScatterplot(graphInput, sourceMatrix);
}
}
}
}
lbStatus.Text = "Switch to the Machine Creation tab to create a learning machine!";
}
private void MenuFileOpen_Click(object sender, EventArgs e)
{
if (openFileDialog.ShowDialog(this) == DialogResult.OK)
{
string filename = openFileDialog.FileName;
string extension = Path.GetExtension(filename);
if (extension == ".xls" || extension == ".xlsx")
{
ExcelReader db = new ExcelReader(filename, true, false);
TableSelectDialog t = new TableSelectDialog(db.GetWorksheetList());
if (t.ShowDialog(this) == DialogResult.OK)
{
this.sourceTable = db.GetWorksheet(t.Selection);
this.dgvSource.DataSource = sourceTable;
}
}
else if (extension == ".xml")
{
DataTable dataTableAnalysisSource = new DataTable();
dataTableAnalysisSource.ReadXml(openFileDialog.FileName);
this.sourceTable = dataTableAnalysisSource;
this.dgvSource.DataSource = sourceTable;
}
}
}
public static DataTable Load(Stream stream, TableFormat format)
{
switch (format)
{
case TableFormat.SerializedXml:
{
XmlSerializer serializer = new XmlSerializer(typeof(DataTable));
return (DataTable)serializer.Deserialize(stream);
}
case TableFormat.SerializedBin:
{
BinaryFormatter serializer = new BinaryFormatter();
return (DataTable)serializer.Deserialize(stream);
}
case TableFormat.OctaveMatFile:
{
MatReader reader = new MatReader(stream);
return reader.Fields.First().Value.GetValue<double[,]>().ToTable();
}
case TableFormat.OpenDocument:
{
ExcelReader reader = new ExcelReader(stream, true);
string ws = reader.GetWorksheetList().First();
return reader.GetWorksheet(ws);
}
case TableFormat.OlderExcel:
{
ExcelReader reader = new ExcelReader(stream, false);
string ws = reader.GetWorksheetList().First();
return reader.GetWorksheet(ws);
}
case TableFormat.Csv:
{
CsvReader reader = new CsvReader(stream, true);
return reader.ToTable();
}
case TableFormat.Tsv:
{
CsvReader reader = new CsvReader(stream, true, '\t');
return reader.ToTable();
}
case TableFormat.LibSVM:
{
SparseReader reader = new SparseReader(stream);
return reader.ToTable();
}
case TableFormat.Idx:
{
IdxReader reader = new IdxReader(stream);
return reader.ReadToEndAsVectors().ToTable();
}
case TableFormat.CSharp:
throw new NotSupportedException();
}
}
public void SpreadsheetNames_Success()
{
string path = Path.Combine("Resources", "excel", "spreadsheet_names.xls");
// Create a new reader, opening a given path
ExcelReader reader = new ExcelReader(path);
string[] sheets = reader.GetWorksheetList();
Assert.AreEqual(4, sheets.Length);
Assert.AreEqual("Example 1", sheets[0]);
Assert.AreEqual("Example 2", sheets[1]);
Assert.AreEqual("Example 3", sheets[2]);
Assert.AreEqual("References", sheets[3]);
// Finally, we can request an specific sheet:
DataTable table = reader.GetWorksheet(sheets[1]);
Assert.AreEqual(2, table.Columns.Count);
Assert.AreEqual(42, table.Rows.Count);
}
private static void testTables(ExcelReader target)
{
DataSet set = target.GetWorksheet();
Assert.AreEqual(4, set.Tables.Count);
Assert.AreEqual("Plan1", set.Tables[0].TableName);
Assert.AreEqual("Plan2", set.Tables[1].TableName);
Assert.AreEqual("Plan3", set.Tables[2].TableName);
Assert.AreEqual("Sheet1", set.Tables[3].TableName);
}
public void ExcelReaderConstructorTest()
{
string path = Path.Combine("Resources", "sample.xls");
// Create a new reader, opening a given path
ExcelReader reader = new ExcelReader(path);
// Afterwards, we can query the file for all
// worksheets within the specified workbook:
string[] sheets = reader.GetWorksheetList();
// Finally, we can request an specific sheet:
DataTable table = reader.GetWorksheet(sheets[1]);
// Now, we have loaded the Excel file into a DataTable. We
// can go further and transform it into a matrix to start
// running other algorithms on it:
Assert.AreEqual(4, sheets.Length);
Assert.AreEqual("Plan1", sheets[0]);
Assert.AreEqual("Plan2", sheets[1]);
Assert.AreEqual("Plan3", sheets[2]);
Assert.AreEqual("Sheet1", sheets[3]);
Assert.AreEqual(3, table.Columns.Count);
Assert.AreEqual(2, table.Rows.Count);
}
public void RevertTest2_new_method()
{
string path = @"Resources\examples.xls";
// Create a new reader, opening a given path
ExcelReader reader = new ExcelReader(path);
// Afterwards, we can query the file for all
// worksheets within the specified workbook:
string[] sheets = reader.GetWorksheetList();
// Finally, we can request an specific sheet:
DataTable table = reader.GetWorksheet("Wikipedia");
// Now, we have loaded the Excel file into a DataTable. We
// can go further and transform it into a matrix to start
// running other algorithms on it:
double[][] matrix = table.ToArray();
IKernel kernel = new Gaussian(5);
// Create analysis
var target = new KernelPrincipalComponentAnalysis(kernel)
{
Method = PrincipalComponentMethod.Center,
Center = true // Center in feature space
};
var regression = target.Learn(matrix);
double[][] forward = regression.Transform(matrix);
double[][] reversion = target.Revert(forward);
Assert.IsTrue(!reversion.HasNaN());
}
/// <summary>
/// Reads the specified file or stream into a table.
/// </summary>
///
public DataTable Read(Stream stream)
{
ExcelReader reader = new ExcelReader(stream, true, true);
return reader.GetWorksheet(0);
}
public static DataTable Load(Stream stream, TableFormat format)
{
switch (format)
{
case TableFormat.SerializedXml:
{
XmlSerializer serializer = new XmlSerializer(typeof(DataTable));
return((DataTable)serializer.Deserialize(stream));
}
case TableFormat.SerializedBin:
{
BinaryFormatter serializer = new BinaryFormatter();
return((DataTable)serializer.Deserialize(stream));
}
case TableFormat.OctaveMatFile:
{
MatReader reader = new MatReader(stream);
return(reader.Fields.First().Value.GetValue <double[, ]>().ToTable());
}
case TableFormat.OpenDocument:
{
ExcelReader reader = new ExcelReader(stream, true);
string ws = reader.GetWorksheetList().First();
return(reader.GetWorksheet(ws));
}
case TableFormat.OlderExcel:
{
ExcelReader reader = new ExcelReader(stream, false);
string ws = reader.GetWorksheetList().First();
return(reader.GetWorksheet(ws));
}
case TableFormat.Csv:
{
CsvReader reader = new CsvReader(stream, true);
return(reader.ToTable());
}
case TableFormat.Tsv:
{
CsvReader reader = new CsvReader(stream, true, '\t');
return(reader.ToTable());
}
case TableFormat.LibSVM:
{
SparseReader reader = new SparseReader(stream);
return(reader.ToTable());
}
case TableFormat.Idx:
{
IdxReader reader = new IdxReader(stream);
return(reader.ReadToEndAsVectors().ToTable());
}
case TableFormat.CSharp:
throw new NotSupportedException();
}
}
public void GaussianMixtureModelTest5()
{
Accord.Math.Tools.SetupGenerator(0);
MemoryStream stream = new MemoryStream(Resources.CircleWithWeights);
ExcelReader reader = new ExcelReader(stream, xlsx: false);
DataTable table = reader.GetWorksheet("Sheet1");
double[,] matrix = table.ToMatrix();
double[][] points = matrix.Submatrix(null, 0, 1).ToJagged();
double[] weights = matrix.GetColumn(2);
GaussianMixtureModel gmm = new GaussianMixtureModel(2);
gmm.Initializations = 1;
gmm.Compute(points, new GaussianMixtureModelOptions()
{
Weights = weights
});
int a = 0;
int b = 1;
double tol = 1e-3;
if ((-0.407859903454185).IsRelativelyEqual(gmm.Gaussians[1].Mean[0], tol))
{
a = 1;
b = 0;
}
Assert.AreEqual(-0.407859903454185, gmm.Gaussians[a].Mean[0], tol);
Assert.AreEqual(-0.053911705279706859, gmm.Gaussians[a].Mean[1], tol);
Assert.AreEqual(0.39380877640250328, gmm.Gaussians[b].Mean[0], tol);
Assert.AreEqual(0.047186154880776772, gmm.Gaussians[b].Mean[1], tol);
Assert.AreEqual(1, gmm.Gaussians[0].Proportion + gmm.Gaussians[1].Proportion, 1e-15);
Assert.IsFalse(gmm.Gaussians[0].Mean.HasNaN());
Assert.IsFalse(gmm.Gaussians[1].Mean.HasNaN());
}
public void RevertTest3()
{
string path = @"..\..\..\..\Unit Tests\Accord.Tests.Statistics\Resources\examples.xls";
// Create a new reader, opening a given path
ExcelReader reader = new ExcelReader(path);
// Afterwards, we can query the file for all
// worksheets within the specified workbook:
string[] sheets = reader.GetWorksheetList();
// Finally, we can request an specific sheet:
DataTable table = reader.GetWorksheet("Wikipedia");
// Now, we have loaded the Excel file into a DataTable. We
// can go further and transform it into a matrix to start
// running other algorithms on it:
double[,] matrix = table.ToMatrix();
IKernel kernel = new Polynomial(2);
// Create analysis
KernelPrincipalComponentAnalysis target = new KernelPrincipalComponentAnalysis(matrix,
kernel, AnalysisMethod.Center, centerInFeatureSpace: true);
target.Compute();
double[,] forward = target.Result;
double[,] reversion = target.Revert(forward);
Assert.IsTrue(!reversion.HasNaN());
}
