Elementary table element, row and column operations

Create a test table by combination

Create a test table by combinationFollowing example1 generates a test table by combination with text attributes country and car, numeric attributes modelyear and date, and key figure sales_eur.

// First, create a test table by combinations
MatrixTable table1;
MetaData md;
TableFields tfields;
// call helper function to create test table
Create_test_table_by_combination(out table1, out md, out tfields);
// display table in immediate window
System.Diagnostics.Debug.WriteLine("table1 = \n" + table1);

Elementary table element operations

Get table element values by field name and row indexGet element value:
There is a specific get-value function for each field type. Note that row position indices of a table begin from 0.

// Getting an element (entry) value by field name and row
// get text attribute value, row index 0 for first row of table
String Country = table1.GetTextAttribValue("country", 0);
System.Diagnostics.Debug.WriteLine("Country(row: 0) = " + Country);
Country = table1.GetTextAttribValue("country", 12); 
// 13. row of table
System.Diagnostics.Debug.WriteLine("Country(row: 12) = " + Country);
// get numeric attribute value
int ModelYear = table1.GetNumAttribValue("modelyear", 4); 
// 5. row of table
System.Diagnostics.Debug.WriteLine("ModelYear(row: 4) = " + ModelYear);
// get key figure value
double SalesInEUR = table1.GetKeyFigValue("sales_eur", 4); 
// 5. row of table
System.Diagnostics.Debug.WriteLine("SalesInEUR(row: 4) = " + SalesInEUR);

Setting table element values by field name and row indexSet element value:
Similarly, there is a set-value function for each field type:

// Setting element values by name and row
// set text attribute value
MatrixTable.SetTextAttribValue(table1, "country", 0, "Peru");
// set numeric attribute value
MatrixTable.SetNumAttribValue(table1, "modelyear", 0, 1999);
// set key figure value
MatrixTable.SetKeyFigValue(table1, "sales_eur", 0, 999.99);
// display table in immediate window with new values in 1. row
System.Diagnostics.Debug.WriteLine("table1 = \n" + table1);

Elementary table row operations

Get table row and display its propertiesFollowing examples illustrate how to get a row and access its properties, how to add a new row to a table, how to table partitioning by row indices, and how to append a table to another vertically.

Get table row:

// get row of table into a TableRow object
// get 1. row of table1
TableRow trow = table1.GetTableRow(0);
// display row properties in immediate window
System.Diagnostics.Debug.WriteLine("Ordered field names: " + trow.ColumnNames);

Get field names and values:

// get attribute names into a vector
TextVector TextAttribFields = trow.TextAttributes;
// Text attributes
System.Diagnostics.Debug.WriteLine("Text attribute fields: " + TextAttribFields);
System.Diagnostics.Debug.WriteLine("Text attribute values: " + trow.TextAttribValues);
System.Diagnostics.Debug.WriteLine("Value of 1. text attribute: " + trow.TextAttribValues[0]);
// Numeric attributes
System.Diagnostics.Debug.WriteLine("Numeric attribute fields: " + trow.NumericAttributes);
System.Diagnostics.Debug.WriteLine("Numeric attribute values: " + trow.NumAttribValues);
// Key figures
System.Diagnostics.Debug.WriteLine("Key figure fields: " + trow.KeyFigures);
System.Diagnostics.Debug.WriteLine("Key figure values: " + trow.KeyFigValues);

Adding a row to tableAdd row to table:
The command AddRowToTable() appends a row to the end (bottom) of table. Note that the row to be added must have the same structure (i.e. fields) of the table.

// adding a new row to table
// create a new table row
// fields: country car modelyear date sales_eur
trow = TableRow.CreateTableRowWithElements(table1.tableFields, "Paraguay", "Honda", 1980,
DateFunctions.DayToNumber(18, 10, 2012), 333.33);
// add row to table
MatrixTable table2 = MatrixTable.AddRowToTable(table1, trow);
// display table with new row in immediate window
System.Diagnostics.Debug.WriteLine("table2 = \n" + table2);

Row partitioning of a tableRow partitioning of table:
A subtable can be generated by selecting certain rows of an input table.

// row partitioning of a table
// get a subtable of table1 with 1. 3. and 5. rows
MatrixTable table3 = MatrixTable.PartitionRow(table1,
	NumVector.CreateVectorWithElements(0, 2, 4));
// display subtable with 1. 3. and 5. rows
System.Diagnostics.Debug.WriteLine("subtable table3 = \n" + table3);

Reshuffling rows of a tableReshuffling rows of table:
In some test cases, random reshuffling (mixing) the rows of a table might be desirable to generate tables with unordered rows.

// reshuffle (mix) all rows of table1
// vector with row indices from 0 to (RowCount -1)
NumVector RowIndices = NumVector.CreateSequenceVector
(StartValue: 0, Interval: 1, nLength: table1.RowCount);
// swap randomly chosen element pairs 300 times
NumVector.MixVectorElements(RowIndices, 300);
// re-order table rows with reshuffled indices
MatrixTable table4 = MatrixTable.PartitionRow(table1, RowIndices);
// display table with reshuffled rows
System.Diagnostics.Debug.WriteLine("reshuffled table4 = \n" + table4);

Appending tables verticallyAppend tables vertically:
Tables can be appended vertically (i.e. appending tables row-wise) if they have exactly the same fields. The field order need not be the same.

// appending tables vertically:
// appending a table to another with the same structure
MatrixTable table5 = MatrixTable.AppendRowsToTable(
// subtable with 1. and 2. rows
MatrixTable.PartitionRow(table1, NumVector.CreateVectorWithElements(0, 1)),
// subtable with 6. and 7. rows
MatrixTable.PartitionRow(table1, NumVector.CreateVectorWithElements(5, 6)));
// display table in immediate window
System.Diagnostics.Debug.WriteLine("appended table5 = \n" + table5);

Elementary table column operations

Column partitioning of a tableColumn partitioning of table:
A subtable can be obtained by selecting columns (fields) of a table. In the example below, a subtable is generated by selecting three columns of input table.

// Get subtable with a subset of columns</span>
// fields of table1: country, car, modelyear, date, sales_eur
MatrixTable table6 = MatrixTable.PartitionColumn(table1,
	TextVector.CreateVectorWithElements("car", "modelyear", "sales_eur"));
// display table in immediate window
System.Diagnostics.Debug.WriteLine("subtable table6 = \n" + table6);

Insert a new column to a tableInserting a new column into table:
A new field can be inserted into a table with a default value provided that a) this field does not already exist in table, and b) this field is defined in MetaData.

// insert a new column (field) to table6
// field must be predefined in MasterData
MatrixTable table7 = MatrixTable.InsertNewColumn(table6, "country", FillAllValue: "Indonesia");
// display table in immediate window
System.Diagnostics.Debug.WriteLine("(insert country) table7 = \n" + table7);

Inserting a hierarchically related field into table:
A hierarchically related field can also be easily inserted into a table with the function InsertRelatedAttribute(). The values of the related field are then derived from the hierarchy matrix. For an example see related help page.

Appending tables horizontally (column-wise)Appending tables horizontally:
Two tables can be appended horizontally (column-wise) if a) they have no common fields, and b) they have identical number of rows.

// Appending two tables horizontally:
// - tables must have the same number of rows
// - tables must have distinct fields (i.e. no common columns)
MatrixTable table8 = MatrixTable.AppendColumnsToTable(
// first subtable with 3 columns
	TextVector.CreateVectorWithElements("car", "modelyear", "sales_eur")),
// second subtable with 2 columns
	TextVector.CreateVectorWithElements("country", "date")));
// display resultant table in immediate window
System.Diagnostics.Debug.WriteLine("(append tables horizontally) table8 = \n" + table8);

Renaming a column of table:
The new field name must have been already defined in MetaData, and it must have the same field type of old field (text/date/integer attribute, or key figure).

// add new field to MetaData
MetaData.AddNewField(md, "yearmodel", FieldType.IntegerAttribute);
// rename column
MatrixTable.RenameColumn(table8, OldFieldName: "modelyear", NewFieldName: "yearmodel");
// view table
MatrixTable.View_MatrixTable(table8, "Field name \"modelyear\" renamed to \"yearmodel\"");
Copyright secured by Digiprove © 2012 Tunc Ali Kütükcüoglu
  1. see related demo function in Visual Studio project FinaquantProtosStarter []

Leave a Reply