Table transformation functions

• Transforming key figures of a table
• Transforming rows of a table
• Transforming a complete table

Table transformation functions of finaquant libraries can be used to manipulate table entries. As the examples illustrate below, some of these functions are quite easy to use, some need basic programming skills with C#/.NET

Some typical transformation operations are rounding all key figures (numbers), arithmetic operations on key figures like profit = sales - costs, or row-by-row processing of a table. The ability to pass user defined functions to table transformations make these functions quite flexible and powerful.

Three types of table transformations are defined in finaquant libraries:

1. Key figure transformation: Mathematical operations on all or selected key figures like round(), without any dependence on attribute fields. The output (or transformed) table has exactly the same structure (exception: a new key figure can be inserted) and row-count of the input table. Examples:
   price = round(price), or
   price_rounded = round(price)
2. Row transformation: Row-by-row processing of tables. All fields (attributes and key figures) can be manipulated as a function of all other fields from the same row. The output (or transformed) table has exactly the same structure (i.e. fields) and row-count of the input table. Examples:
   price_adjusted = price + adjustment, or
   price_corrected = FUNCTION(price, category, year)
3. Table transformation: In some more advanced cases, row-by-row processing is not appropriate because the output values may depend on attributes and key figures from multiple rows of a table, like aggregations. A table transformation can produce an output table which has a totally different structure and row-count than the input table. See related articles subtable transformation and function router for more information with examples.

Subtable transformation: Applying a table (or matrix) function on all subtables of an input table.

Function router: Applying selected table (or matrix) functions on selected subtables of an input table.

Create an initial test table

Following code creates the MetaData and the initial test table to be used in the transformation functions explained below.

// define fields (MetaData)
MetaData md1 = MetaData.CreateEmptyMasterData();
MetaData.AddNewField(md1, "brand", FieldType.TextAttribute);
MetaData.AddNewField(md1, "model", FieldType.TextAttribute);
MetaData.AddNewField(md1, "year", FieldType.IntegerAttribute);
MetaData.AddNewField(md1, "price", FieldType.KeyFigure);
MetaData.AddNewField(md1, "price_adjustment", FieldType.KeyFigure);
MetaData.AddNewField(md1, "price_corrected", FieldType.KeyFigure);
MetaData.AddNewField(md1, "price_adjusted", FieldType.KeyFigure);
// define fields of table
TableFields tf1 = TableFields.CreateEmptyTableFields(md1);
TableFields.AddNewField(tf1, "brand");
TableFields.AddNewField(tf1, "model");
TableFields.AddNewField(tf1, "year");
TableFields.AddNewField(tf1, "price");
TableFields.AddNewField(tf1, "price_adjustment");
TableFields.AddNewField(tf1, "price_adjusted");
// create test table
MatrixTable tbl1 = MatrixTable.CreateTableWithElements_A(tf1,
"Mercedes", "Me1", 1990, 3500.5055, 120.5555, 0.0,
"Mercedes", "Me2", 2005, 3800.3333, 150.4444, 0.0,
"Audi", "Au1", 1988, 2500.2525, 355.6666, 0.0,
"Audi", "Au2", 2008, 2750.7575, 444.7777, 0.0);
// display table in immediate window
System.Diagnostics.Debug.WriteLine("Input table tbl1 = \n" + tbl1);
// view table in DataGrid
MatrixTable.View_MatrixTable(tbl1, "Initial test table");

Transforming key figures of a table

Transformation functions can be applied either on all, or on selected key figures of a table. Users can use existing mathematical functions of the .NET library like Math.Round, or they can write their own functions (user defined functions) to manipulate key figures. These mathematical functions are passed as delegates (function handles or parameters) to transformation functions.

Following example rounds all key figures of table to zero digits after decimal point. If both input parameters InputKeyFig and OutputKeyFig are assigned to null, the delegate function is applied to all key figures of table in all rows.

// transform all key figures with delegate function Math.Round
MatrixTable tblr =
MatrixTable.TransformKeyFigures(tbl1, Math.Round,
InputKeyFig: null, OutputKeyFig: null);
System.Diagnostics.Debug.WriteLine("Round(all key figures) tblr = \n" + tblr);

In the second example below, the delegate function Math.Floor is applied on a selected key figure price: price = Floor(price) Note that price is both the input and output key figure in this example.

// transform selected key figures: Floor(price) --> price</span>
tblr = MatrixTable.TransformKeyFigures(tbl1, Math.Floor,
InputKeyFig: "price", OutputKeyFig: "price");
System.Diagnostics.Debug.WriteLine("Floor(price/price) tblr = \n" + tblr);

In the following example, a user defined function in the form of an anonymous function. Search internet for information about the syntax of anonymous functions in C#/.NET. Note that the resultant output table contains a new key figure price_corrected which doesn’t exist in input table.

// transform selected key figure: Round2(price) -->; price_corrected
// insert new field to table: price_corrected
// round(x,2) with anonymous method syntax for C#
tblr = MatrixTable.TransformKeyFigures(tbl1,
x => Math.Round(x, 2), InputKeyFig: "price", OutputKeyFig: "price_corrected");
// view table in DataGrid
MatrixTable.View_MatrixTable(tblr, "price_corrected = Round2(price)");

Following example shows how a user can write his own delegate function, and pass it to a transformation function as a parameter. User defined functions can also have multiple parameters of any type including tables. This is a relatively advanced construct for users with some programming experience in C#. In this example, MyRound is a user-defined function which take two parameters, x and i for round(x, i), where x is the number to be rounded, and i is the number of digits after decimal point.

// transform selected key figure: Round(x, i) with additional parameter i = 2
tblr = MatrixTable.TransformKeyFigures(tbl1, UserFunctions.MyRound,
	"price", "price", 2);
MatrixTable.View_MatrixTable(tblr, "price = Round(price, 2)");

User defined function MyRound() is formulated in a separate text file like UserFunc.cs as shown in the figure at the left. Though not difficult, writing such functions requires some basic programming experience in C#.

You can find some examples of user defined functions including MyRound in the initial starter Visual Studio project for finaquant® protos or calcs.

Transforming rows of a table

This is row-by-row processing of a table. All attributes and key figures can be manipulated by this process. Users need only to tell how a single row is transformed. The same transformation is then applied on all rows of a table. See also related article for more information with examples.

Following example calculates adjusted price:
price_adjusted = price + price_adjustment
This is an example where only key figures are involved. The formula above is implemented with a user-defined function CalcAdjustedPrice.

// price_adjusted = price + price_adjustment
tblr = MatrixTable.TransformRows(tbl1, UserFunctions.CalcAdjustedPrice);
MatrixTable.View_MatrixTable(tblr, "Adjusted price");

User-defined function CalcAdjustedPrice:

// Transform row: Calculate adjusted price: price_adjusted = price + price_adjustment 
public static TableRow CalcAdjustedPrice(TableRow trow) 
{ 
	if (trow.IsEmpty) throw new Exception("Empty row without fields!"); 
 
	// check if row has all key figures: price_adjusted, price, price_adjustment 
	if (!TextVector.IfV2containsV1( TextVector.CreateVectorWithElements("price", 		  "price_adjusted", "price_adjustment"), trow.KeyFigures)) 		
		throw new Exception("TableRow must contain key figures price, price_adjustment, price_adjusted!"); 
 
	// clone rows 
	var RowOut = TableRow.CloneTableRow(trow); 
 
	// get key figure values 
	double price = TableRow.GetKeyFigureValue(RowOut, "price"); 
	double price_adjustment = TableRow.GetKeyFigureValue(RowOut, "price_adjustment"); 
 
	// set key figure value 
	TableRow.SetKeyFigureValue(RowOut, "price_adjusted", (price + price_adjustment)); 
	return RowOut; 
}

Following example illustrates price correction with multipliers depending on attributes brand and model. A new key figure price_corrected is inserted to table before starting row operations.
price_corrected = price x multiplier(brand, model)
price_corrected = price x 1.20 for (Mercedes, Me1)
price_corrected = price x 1.50 for (Audi, Au1)
otherwise: price_corrected = price

// transform rows: price correction depending on attributes brand and model
// see user defined function: ApplyPriceAdjustments()
tblr = MatrixTable.InsertNewColumn(tbl1, "price_corrected", 0.0);
tblr = MatrixTable.TransformRows(tblr, UserFunctions.ApplyPriceAdjustments);
MatrixTable.View_MatrixTable(tblr, "Attribute dependent price correction");

You may see the user-defined function ApplyPriceAdjustments in the Visual Project FinaquantProtosStarter that can be downloaded at the product page of finaquant protos.

Another possibility for transforming tables is using the dictionary construct (associative arrays) for manipulating rows. Writing user-defined functions with dictionaries is quite easy. Compare following examples with their counterparts above.

// price_adjusted = round(price + price_adjustment, 2)
tblr = MatrixTable.TransformRowsDic(tbl1,
UserFunctions.CalcAdjustedPriceDic);
MatrixTable.View_MatrixTable(tblr, "Adjusted price (dictionary construct)");

User-defined function CalcAdjustedPriceDic as a parameter of the transformation function above with the dictionary construct:

// Calculate adjusted price: 
// price_adjusted = ROUND(price + price_adjustment, 2) 
public static void CalcAdjustedPriceDic(ref Dictionary TextAttribDic, 
	ref Dictionary NumAttribDic, ref Dictionary KeyFigDic) 
{ 
// Calculate adjusted price: price_adjusted = price + price_adjustment
KeyFigDic["price_adjusted"] = 
	Math.Round(KeyFigDic["price"] + KeyFigDic["price_adjustment"], 2); 
}

Note that the whole function is just an envelope for the user-defined formula.

Attribute dependent price correction: Correction multiplier as a function of attributes brand and model:

// price correction depending on attributes brand and model
// price_corrected = ROUND(price x multiplier(brand, model), 2)
tblr = MatrixTable.InsertNewColumn(tbl1, "price_corrected", 0.0);
tblr = MatrixTable.TransformRowsDic(tblr, 
	UserFunctions.ApplyPriceAdjustmentsDic);
MatrixTable.View_MatrixTable(tblr, 
	"Attribute dependent price correction (dictionary)");

User-defined function ApplyPriceAdjustmentsDic referenced in the transformation function above:

// price_corrected = ROUND(price x multiplier(brand, model) , 2) 
public static void ApplyPriceAdjustmentsDic( ref Dictionary TextAttribDic, 
	ref Dictionary NumAttribDic, ref Dictionary KeyFigDic) 
{ 
	// apply corrections dependent on attributes brand and model 
	if (TextAttribDic["brand"].ToLower() == "mercedes" && 
		TextAttribDic["model"].ToLower() == "me1") 
	{ 
		KeyFigDic["price_corrected"] = KeyFigDic["price"] * 1.25; 
	} 
	else if (TextAttribDic["brand"].ToLower() == "audi" 
		&& TextAttribDic["model"].ToLower() == "au1") 
	{ 
		KeyFigDic["price_corrected"] = KeyFigDic["price"] * 1.50; 
	} else { 
		KeyFigDic["price_corrected"] = KeyFigDic["price"]; 
	} 
	// round to 2 digits after decimal point 
	KeyFigDic["price_corrected"] = Math.Round(KeyFigDic["price_corrected"], 2); 
}

Copyright secured by Digiprove © 2012 Tunc Ali Kütükcüoglu