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