a table function for conditional rounding?

Table-valued functions in finaquant .net libraries

a table function for conditional rounding?

Postby lotus85 » 11 Feb 2014, 11:18

Hello. We have a big table with many many fields. Some number fields (not all of them) need to be rounded depending on some conditions like:

round price if asset = A OR f(price) > 2000, where asset and price are fields of table, f is any custom user-defined function in c#.

Is there a table function for such an operation? Thanks
lotus85
 
Posts: 6
Joined: 24 Sep 2013, 19:59

row transformer (user defined formula)

Postby finaquant » 17 Feb 2014, 20:07

Yes, Row Transformer MatrixTable.TransformRowsDic() is the proper table function for the conditional rounding you described.

With the Row Transformer (row-by-row processing) you can apply a user defined formula, or any logic, on every row of an input table, as the example C# code below will demonstrate.

First, create a test table with about 7000 rows using the table function MatrixTable.CombinateFieldValues_B():
Code: [Select all] [Expand/Collapse] [Download] (Untitled.txt)
  1. //***********************************************************************
  2. // Create a test table containing all possible combinations of attribute values
  3. //***********************************************************************
  4.  
  5. // define all fields
  6. MetaData md = MetaData.CreateEmptyMetaData();
  7. MetaData.AddNewField(md, "country", FieldType.TextAttribute);
  8. MetaData.AddNewField(md, "asset", FieldType.TextAttribute);
  9. MetaData.AddNewField(md, "date", FieldType.DateAttribute);
  10. MetaData.AddNewField(md, "cost", FieldType.KeyFigure);
  11. MetaData.AddNewField(md, "price", FieldType.KeyFigure);
  12.  
  13. // define table structure
  14. var PriceTableFields = TableFields.CreateEmptyTableFields(md);
  15. TableFields.AddNewField(PriceTableFields, "country");
  16. TableFields.AddNewField(PriceTableFields, "asset");
  17. TableFields.AddNewField(PriceTableFields, "date");
  18. TableFields.AddNewField(PriceTableFields, "cost");
  19. TableFields.AddNewField(PriceTableFields, "price");
  20.  
  21. // text attributes
  22. TextVector CountryVal = TextVector.CreateVectorWithElements("Peru", "Paraguay", "Argentina", "Brasil", "Ecuador");
  23. TextVector AssetVal = TextVector.CreateVectorWithElements("A", "B", "C", "D", "E", "F", "G");
  24.  
  25. // numeric attribûtes
  26. NumVector DateVal = NumVector.CreateSequenceVector(
  27.     StartValue: DateFunctions.DayToNumber(1, 1, 2010),
  28.     Interval: 10, nLength: 200);
  29.  
  30. // initiate field value dictionaries
  31. var TextAttribValues = new Dictionary<string, TextVector>();
  32. var NumAttribValues = new Dictionary<string, NumVector>();
  33.  
  34. // assign a value vector to each field
  35. TextAttribValues["country"] = CountryVal;
  36. TextAttribValues["asset"] = AssetVal;
  37. NumAttribValues["date"] = DateVal;
  38.  
  39. // default range for all key figures
  40. KeyValueRange DefaultRangeForAllKeyFigures = KeyValueRange.CreateRange(5000, 10000);
  41.  
  42. // range for selected key figures
  43. var RangeForSelectedKeywords = new Dictionary<string, KeyValueRange>();
  44. RangeForSelectedKeywords["price"] = KeyValueRange.CreateRange(8000, 20000);
  45.  
  46. // create test table
  47. MatrixTable PriceTable = MatrixTable.CombinateFieldValues_B(PriceTableFields,
  48.     TextAttribValues, NumAttribValues, DefaultRangeForAllKeyFigures, RangeForSelectedKeywords);
  49.  
  50. // view table
  51. MatrixTable.View_MatrixTable(PriceTable, "Price table");
  52.  
  53. // display row size
  54. Console.WriteLine("Row count: " + PriceTable.RowCount);
GeSHi ©


Image

Now, apply user-defined logic conditional rounding on every row of the test table created above, using row-transformer MatrixTable.TransformRowsDic():
Code: [Select all] [Expand/Collapse] [Download] (Untitled.txt)
  1. //***********************************************************************
  2. // Conditional Rounding
  3. // Row transformer: Applying a formula/logic on all rows of a table
  4. //***********************************************************************
  5.  
  6. DateTime t1 = DateTime.Now;
  7.  
  8. // call row transformer (row-by-row processing)
  9. var PriceTable2 = MatrixTable.TransformRowsDic(PriceTable, RoundPriceConditionally);
  10.  
  11. // process time
  12. DateTime t2 = DateTime.Now;
  13. TimeSpan ProcessTime = t2.Subtract(t1);
  14.  
  15. // view rounded table
  16. MatrixTable.View_MatrixTable(PriceTable2, "Rounded Price table");
  17.  
  18. // display process time
  19. Console.WriteLine("Process time for cond. rounding in mili-seconds: " + ProcessTime.Milliseconds);
  20.  
  21. Console.ReadKey();
  22.  
  23. // Method called by row transformer: Round price conditionally
  24. private static void RoundPriceConditionally(
  25.     ref Dictionary<string, string> TextAttribDic,
  26.     ref Dictionary<string, int> NumAttribDic,
  27.     ref Dictionary<string, double> KeyFigDic)
  28. {
  29.     if (TextAttribDic["asset"] == "A" && Func(KeyFigDic["price"]) > 100)
  30.         KeyFigDic["price"] = Math.Round(KeyFigDic["price"],2);
  31. }
  32.  
  33. // any function (square root)
  34. private static double Func(double x)
  35. {
  36.     return Math.Sqrt(x);
  37. }
GeSHi ©


Image

Image

Note that only prices of the asset "A" where SquareRoot(price) > 100 (i.e. price > 10000) are rounded to 2 digits after comma.
User avatar
finaquant
 
Posts: 61
Joined: 16 Sep 2013, 18:37


Return to Table Functions

Who is online

Users browsing this forum: No registered users and 1 guest