public void SetName()
{
MutableDataTable dt = new MutableDataTable();
dt.Name = "test";
Assert.Equal("test", dt.Name);
}
public void ImportItems(MutableDataTable data, IDictionary <string, string> mapping = null)
{
foreach (var row in data.Rows)
{
var itemCreateInfo = new ListItemCreationInformation();
var item = _list.AddItem(itemCreateInfo);
foreach (var colname in data.ColumnNames)
{
var fieldName = colname;
if (mapping != null && mapping.ContainsKey(colname))
{
fieldName = mapping[colname];
}
var val = row.GetValueOrEmpty(colname);
if (!string.IsNullOrWhiteSpace(val))
{
item[fieldName] = val;
}
}
item.Update();
_ctx.ExecuteQuery();
}
}
public void RoundtripTable()
{
// Test that if we download and then reupload, it's ok.
var account = GetStorage();
var source = from x in Enumerable.Range(1, 200) select new { N = x, N2 = x * x };
DataTable dtSource = DataTable.New.FromEnumerable(source);
string tableName = "csvtesttable";
dtSource.SaveToAzureTable(account, tableName); // original upload
DataTable dtDownload1 = DataTable.New.ReadAzureTableLazy(account, tableName);
MutableDataTable dtA = DataTable.New.GetMutableCopy(dtDownload1);
// this writes back to the source that dtDownload1 was streaming from.
// But we already copied to dtA, so safe to overwrite.
dtA.SaveToAzureTable(account, tableName); // 2nd upload
DataTable dtDownload2 = DataTable.New.ReadAzureTableLazy(account, tableName);
MutableDataTable dtB = DataTable.New.GetMutableCopy(dtDownload2);
// Everything except timestamps should match.
dtA.DeleteColumns("TimeStamp");
dtB.DeleteColumns("TimeStamp");
Utility.AssertEquals(dtA, dtB);
}
public void RenameBadNewName()
{
MutableDataTable dt = GetTable();
// Fail when new name already exists.
Assert.Throws <InvalidOperationException>(() => dt.RenameColumn("first", "last"));
}
private IDictionary <string, string> mapColumnsToFields(MutableDataTable data, IDictionary <string, string> mapping, IDictionary <string, string> defaults, FieldCollection fields)
{
var columnNames = data.ColumnNames.Where(x => !string.IsNullOrEmpty(x)).ToList();
if (defaults != null)
{
columnNames.AddRange(defaults.Keys.ToList());
}
var fieldMap = columnNames.ToDictionary(x => x, x => {
var fieldTitle = x;
if (mapping != null && mapping.ContainsKey(fieldTitle))
{
fieldTitle = mapping[fieldTitle];
}
var field = fields.Where(f => f.Title == fieldTitle || f.InternalName == fieldTitle).FirstOrDefault();
if (field == null)
{
return("");
}
return(field.InternalName);
});
return(fieldMap);
}
public void ImportItems(MutableDataTable data, IDictionary <string, string> mapping = null, IDictionary <string, string> defaults = null)
{
var fields = _list.Fields;
_ctx.Load(fields);
_ctx.ExecuteQuery();
var fieldMap = mapColumnsToFields(data, mapping, defaults, fields);
var idx = 0;
foreach (var row in data.Rows)
{
idx++;
Console.WriteLine("Importing {0} of {1} records", idx, data.NumRows);
var itemCreateInfo = new ListItemCreationInformation();
var item = _list.AddItem(itemCreateInfo);
populateItem(item, row, fieldMap, defaults, fields);
item.Update();
_ctx.ExecuteQuery();
}
}
public void ArrayToTable()
{
Point[] ps = new Point[] {
new Point {
x = 11, y = 12
},
new Point {
x = 21, y = 22
},
new Point {
x = 31, y = 32
}
};
// Tests converting an array of structs into a table
MutableDataTable dt = DataTable.New.FromEnumerable(ps);
StringWriter sw = new StringWriter();
dt.SaveToStream(sw);
AnalyzeTests.AssertEquals(
@"x,y
11,12
21,22
31,32
", sw.ToString());
}
public void Join()
{
MutableDataTable d1 = DataTable.New.Read(new StringReader(
@"first, last
Bob, Jones
Alfred, Smith
Ed, Edson
"));
MutableDataTable d2 = DataTable.New.Read(new StringReader(
@"last, country
smith, English
Piere, French
Jones, American
"));
MutableDataTable merge = Analyze.Join(d1, d2, "last");
AssertEquals(
@"first,last,country
Bob,JONES,American
Alfred,SMITH,English
Ed,EDSON,
,PIERE,French
", merge);
}
public void RenameBadOldName()
{
MutableDataTable dt = GetTable();
// Fail when old name does not exist
Assert.Throws <InvalidOperationException>(() => dt.RenameColumn("illegal", "FName"));
}
public void ReadThrowsAssertExceptionIfAllowMismatchFalseAndLinesContainMismatch()
{
// Make reader tolerant
string content =
@"aaa,bbb,ccc,ddd
111,111,111,111
222,222,222
333,333,333,333";
var textReader = new StringReader(content);
MutableDataTable dt = Reader.Read(textReader);
var s = dt.SaveToString();
// Round-trip
// - Extra comma at row with missing value.
// - newline after all rows, even the last one
Assert.Equal(
@"aaa,bbb,ccc,ddd
111,111,111,111
222,222,222,
333,333,333,333
", s);
}
public void GetMissingColumnIsNull()
{
MutableDataTable dt = GetTable();
Column c = dt.GetColumn("missing");
Assert.Null(c);
}
public void SetName()
{
MutableDataTable dt = new MutableDataTable();
dt.Name = "test";
Assert.Equal("test", dt.Name);
}
public void Columns()
{
MutableDataTable dt = GetTable();
Assert.Equal(new string[] { "name", "age", "favorite fruit" },
dt.ColumnNames);
Assert.Null(dt.GetColumn("missing")); // missing columns return null
}
public void RemoveColumnIllegalIndex()
{
MutableDataTable dt = GetTable();
Assert.Equal(2, dt.Columns.Length); // initial value
Assert.Throws <ArgumentOutOfRangeException>(() => { dt.DeleteColumn(-1); });
Assert.Throws <ArgumentOutOfRangeException>(() => { dt.DeleteColumn(5); });
Assert.Equal(2, dt.Columns.Length); // no change
}
public void GetColumn()
{
MutableDataTable dt = GetTable();
Column c = dt.GetColumn("first");
Assert.Equal("first", c.Name);
Assert.Equal(new string[] { "Bob", "Fred" }, c.Values);
}
public void GetColumns()
{
MutableDataTable dt = GetTable();
Column[] cs = dt.GetColumns("last", "first");
Assert.Equal(2, cs.Length);
Assert.Equal("last", cs[0].Name);
Assert.Equal("first", cs[1].Name);
}
public void ReorderOutOfRange()
{
MutableDataTable dt = GetTable();
Assert.Throws <ArgumentOutOfRangeException>(
() => dt.ReorderColumn("first", 2));
Assert.Throws <ArgumentOutOfRangeException>(
() => dt.ReorderColumn("first", -1));
}
public void SampleTest()
{
DataTable dtOriginal = GetTable();
MutableDataTable result = Analyze.SampleTopN(dtOriginal, 2);
AssertEquals(
@"first,last,age
Bob,Smith,12
Bob,Jones,34
", result);
}
void GetColumnValueCounts(DataTable dtOriginal)
{
MutableDataTable result = Analyze.GetColumnValueCounts(dtOriginal, 1);
AssertEquals(
@"column name,count,Top Value 0,Top Occurrence 0
first,3,Bob,2
last,2,Smith,3
age,2,12,2
", result);
}
public void MutateRow()
{
MutableDataTable dt = GetTable();
Row row = dt.GetRow(0);
row["first"] = "Ed"; // modify by row
Assert.Equal("Ed", row["first"]);
Assert.Equal("Ed", row.Values[0]);
Assert.Equal(dt.GetColumn("first").Values[0], "Ed"); // Verify lookup by column
}
public void KeepRows()
{
MutableDataTable dt = GetTable();
dt.KeepRows(row => row["last"] == "Jones");
AnalyzeTests.AssertEquals(
@"first,last
Fred,Jones
", dt);
}
public void RenameSame()
{
MutableDataTable dt = GetTable();
dt.RenameColumn("first", "first");
AnalyzeTests.AssertEquals(
@"first,last
Bob,Smith
Fred,Jones
", dt);
}
public void CreateColumn()
{
MutableDataTable dt = GetTable();
dt.CreateColumn("fullname", row => row["first"] + "_" + row["last"]);
AnalyzeTests.AssertEquals(
@"first,last,fullname
Bob,Smith,Bob_Smith
Fred,Jones,Fred_Jones
", dt);
}
public void KeepColumnsDoNotThrow()
{
MutableDataTable dt = GetTable();
dt.KeepColumns(false, "last", "first", "made up column name");
AnalyzeTests.AssertEquals(
@"last,first
Smith,Bob
Jones,Fred
", dt);
}
public void Reorder()
{
MutableDataTable dt = GetTable();
dt.ReorderColumn("first", 1);
AnalyzeTests.AssertEquals(
@"last,first
Smith,Bob
Jones,Fred
", dt);
}
MutableDataTable GetTable()
{
string content =
@"first,last
Bob, Smith
Fred, Jones";
TextReader tr = new StringReader(content);
MutableDataTable dt = DataTable.New.Read(tr);
return(dt);
}
public void RemoveColumns()
{
MutableDataTable dt = GetTable();
dt.DeleteColumn(1);
AnalyzeTests.AssertEquals(
@"first
Bob
Fred
", dt);
}
public void KeepColumnsReorder()
{
MutableDataTable dt = GetTable();
dt.KeepColumns("last", "first");
AnalyzeTests.AssertEquals(
@"last,first
Smith,Bob
Jones,Fred
", dt);
}
public void KeepColumnsRemove()
{
MutableDataTable dt = GetTable();
dt.KeepColumns("last");
AnalyzeTests.AssertEquals(
@"last
Smith
Jones
", dt);
}
public void DeleteColumns()
{
MutableDataTable dt = GetTable();
dt.DeleteColumns("first");
AnalyzeTests.AssertEquals(
@"last
Smith
Jones
", dt);
}
public void ApplyToColumn()
{
MutableDataTable dt = GetTable();
dt.ApplyToColumn("first", value => value.ToUpper());
AnalyzeTests.AssertEquals(
@"first,last
BOB,Smith
FRED,Jones
", dt);
}
private static MutableDataTable ReadArray(IList<string> lines, char separator, bool fAllowMismatch = false)
{
if (separator == '\0')
{
separator = GuessSeparateFromHeaderRow(lines[0]);
}
int numRows = lines.Count - 1;
// First row is a header
string[] names = split(lines[0], separator);
int numColumns = names.Length;
var columns = new Column[numColumns];
for (int i = 0; i < numColumns; i++) {
columns[i] = new Column(names[i], numRows);
}
// Parse each row into data set
for (int i = 1; i < lines.Count; i++) {
string line = lines[i];
int row = i - 1;
string[] parts = split(line, separator);
if (parts.Length < numColumns) {
// Deal with possible extra commas at the end.
// Excel handles this.
for (int c = 0; c < parts.Length; c++) {
columns[c].Values[row] = parts[c];
}
for (int c = parts.Length; c < numColumns; c++) {
columns[c].Values[row] = String.Empty;
}
continue;
}
if (!fAllowMismatch) {
// If mismatch allowed, then treat this row as garbage rather
// than throw an exception
Utility.Assert(parts.Length == names.Length);
}
for (int c = 0; c < numColumns; c++) {
columns[c].Values[row] = parts[c];
}
}
MutableDataTable data = new MutableDataTable();
data.Columns = columns;
return data;
}
private static MutableDataTable ReadArray(IList<string> lines, char separator, bool fAllowMismatch = false, string[] defaultColumns = null)
{
if (separator == '\0')
{
separator = GuessSeparateFromHeaderRow(lines[0]);
}
int numRows = lines.Count - (defaultColumns != null ? 0 : 1);
// First row is a header only if we dont pass defaultColumns
// if defaultColumns is not null then we use them as columns
string[] names = defaultColumns ?? split(lines[0], separator);
int numColumns = names.Length;
var columns = new Column[numColumns];
for (int i = 0; i < numColumns; i++) {
columns[i] = new Column(names[i], numRows);
}
// Parse each row into data set
using (var lineEnumerator = lines.GetEnumerator())
{
if (defaultColumns == null)
{
lineEnumerator.MoveNext(); // in this case we have columns at first index
}
var row = -1;
while(lineEnumerator.MoveNext())
{
string line = lineEnumerator.Current;
row++;
string[] parts = split(line, separator);
if (parts.Length < numColumns) {
// Deal with possible extra commas at the end.
// Excel handles this.
for (int c = 0; c < parts.Length; c++) {
columns[c].Values[row] = parts[c];
}
if (fAllowMismatch)
{
for (int c = parts.Length; c < numColumns; c++)
{
columns[c].Values[row] = String.Empty;
}
continue;
}
}
if (!fAllowMismatch) {
// If mismatch allowed, then treat this row as garbage rather
// than throw an exception
Utility.Assert(
parts.Length == names.Length,
String.Format(
"Allow Mismatch is False. Line has incorrect number of parts. Line Number:{0}; Expected:{1}; Actual:{2}",
row + 1,
names.Length,
parts.Length));
}
for (int c = 0; c < numColumns; c++) {
columns[c].Values[row] = parts[c];
}
}
}
MutableDataTable data = new MutableDataTable();
data.Columns = columns;
return data;
}
// $$$ Clarify - multiple joins (inner, outer, etc)
/// <summary>
/// Performs a full outer join on two in-memory tables and returns a new table.
/// The number of rows in the resulting table is the sum of rows from each source table.
/// The number of columns in teh new table is the sum of columns in the the source tables minus 1
/// (since the join column is redundant)
/// </summary>
/// <param name="d1"></param>
/// <param name="d2"></param>
/// <param name="columnName">column name to join on. Both tables must have this column name.</param>
/// <returns>a new table</returns>
public static MutableDataTable Join(MutableDataTable d1, MutableDataTable d2, string columnName)
{
Column c1 = d1.GetColumn(columnName);
if (c1 == null)
{
throw new InvalidOperationException("Missing column");
}
Column c2 = d2.GetColumn(columnName);
if (c2 == null)
{
throw new InvalidOperationException("Missing column");
}
// Place d1 in first set of columns, and d2 in second set.
int kColumn = d1.Columns.Length;
int kTotalColumns = kColumn + d2.Columns.Length;
// Indices into new table where join columns are.
int joinColumn1 = Utility.GetColumnIndexFromName(d1.ColumnNames, columnName);
int joinColumn2 = Utility.GetColumnIndexFromName(d2.ColumnNames, columnName) + kColumn;
// $$$ could really optimize. Sort both on column and then zip.
Dictionary<string, int> m1 = GetRowIndex(c1);
Dictionary<string, int> m2 = GetRowIndex(c2);
// $$$ column names may not be unique.
//string[] headers = d1.ColumnNames.Union(d2.ColumnNames).ToArray();
string[] headers = new string[kTotalColumns];
Array.Copy(d1.ColumnNames.ToArray(), 0, headers, 0, kColumn);
Array.Copy(d2.ColumnNames.ToArray(), 0, headers, kColumn, kTotalColumns - kColumn);
string[] values = new string[headers.Length];
string path = GetTempFileName();
using (CsvWriter tw = new CsvWriter(path, headers))
{
foreach (var kv in m1)
{
Clear(values);
string key = kv.Key; // join column
int r1 = kv.Value;
int r2;
if (m2.TryGetValue(key, out r2))
{
// In both. write out
CopyRowIntoArray(values, kColumn, d2, r2);
m2.Remove(key);
}
else
{
// Only in M1.
}
CopyRowIntoArray(values, 0, d1, r1);
values[joinColumn1] = values[joinColumn2] = key;
tw.WriteRow(values);
}
// We remove all of M1's items from m2, so M2 is just unique items now. (possibly 0).
// Tag those onto the end.
foreach (var kv in m2)
{
int r2 = kv.Value;
Clear(values);
CopyRowIntoArray(values, kColumn, d2, r2);
values[joinColumn1] = values[joinColumn2] = kv.Key;
tw.WriteRow(values);
}
} // close tw
MutableDataTable t = Reader.ReadCSV(path);
DeleteLocalFile(path);
// Remove duplicate columns.
t.DeleteColumn(joinColumn2);
return t;
}
static void CopyRowIntoArray(string[] values, int index, MutableDataTable d, int row)
{
for (int c = 0; c < d.Columns.Length; c++)
{
values[index] = d.Columns[c].Values[row];
index++;
}
}
/// <summary>
/// Produces a table where each row is the number of unique values in a source column, followed by the top N occurences in that column.
/// </summary>
/// <param name="table">source table</param>
/// <param name="N">number of top N occurences to include in the summary table </param>
/// <returns>a summary table</returns>
public static MutableDataTable GetColumnValueCounts(DataTable table, int N)
{
if (N < 0)
{
throw new ArgumentOutOfRangeException("N");
}
string[] names = table.ColumnNames.ToArray();
int count = names.Length;
MutableDataTable dSummary = new MutableDataTable();
Column c1 = new Column("column name", count);
Column c2 = new Column("count", count);
int kFixed = 2;
Column[] cAll = new Column[kFixed + N * 2];
cAll[0] = c1;
cAll[1] = c2;
for (int i = 0; i < N; i++)
{
cAll[i * 2 + kFixed] = new Column("Top Value " + i, count);
cAll[i * 2 + 1 + kFixed] = new Column("Top Occurrence " + i, count);
}
dSummary.Columns = cAll;
int columnId = 0;
foreach (string name in names)
{
Tuple<string, int>[] hist = AsHistogram(table, columnId);
c1.Values[columnId] = name;
c2.Values[columnId] = hist.Length.ToString();
for (int i = 0; i < N; i++)
{
if (i >= hist.Length)
{
break;
}
cAll[i * 2 + kFixed].Values[columnId] = hist[i].Item1;
cAll[i * 2 + 1 + kFixed].Values[columnId] = hist[i].Item2.ToString();
}
columnId++;
}
return dSummary;
}
