Finaquant Community Forum

As I wrote before, I am working on a Python module which makes the table functions of the non-commercial .NET library Finaquant Protos available for Python developers. You may download the latest release of this module (tablenet.py) here:

Download tablenet.py - Version 1.02 (29. June 2015)

Prerequisites:

1. Python 2.7
2. .NET4 Framework
3. .NET integration module pythonnet must be installed ("pip install pythonnet" in command line)

Installation and testing:
Unzip the downloaded zip file. You will find two files in the folder: tablenet.py (Python module) and FinaquantProtos.dll (.NET assembly with the class MatrixTable that contain table-valued functions). You can now run tablenet.py in Python to test the module.

As of today (26. June 2015), the first version of the module tablenet.py includes only a small subset of the table functions available in Finaquant Protos. I will continue to work on the module to extend the features, and publish the latest release regularly here.

This development process may continue for several months, even years; I will append the release history here. Please let me know if you have any questions, or improvement suggestions.

Module information for the first release:
Table-Valued Functions in Python. Version: 1.01 (26. June 2015)
Python (2.7) module with the Table class, which is a wrapper for the MatrixTable
class of the .NET library 'Finaquant Protos' with table-valued functions.

This module (tablenet.py) requires .NET library 'Finaquant Protos' for execution.
The .NET integration module pythonnet must be installed
('pip install pythonnet' in command line) before running or importing this module.

Author: Tunc Ali Kutukcuoglu, tuncalik (at) finaquant.com
Website: http://www.finaquant.com
LinkedIn: http://ch.linkedin.com/pub/tunc-ali-kutukcuoglu/45/664/105
Stackoverflow: http://stackoverflow.com/users/1625567/tuncalik

Example test code from the module:

Converting a pandas DataFrame to a Table (MatrixTable)

Code: [Select all] [Expand/Collapse] [Download] (Untitled.txt)

1. mdat = MetaData.CreateEmptyMetaData()
2.  
3. # convert a [url=http://pandas.pydata.org/]pandas[/url] DataFrame to a Table object, and vice versa
4. import pandas as pd
5. df = pd.DataFrame({ 'Product' : ['car','bus','cycle','motor'],
6.     'Price' : np.array([120.3, 150, 23, 87.6]),
7.     'Serial' : np.array([3,5,6,7], int),
8.     'Category' : pd.Categorical(["test","train","test","train"]),
9.     'Type' : 'pricey' })
10. print "df w/0 Timestamp:\n", df
11. tbl = DataFrameToMatrixTable(df, mdat)
12. tbl.ViewTable("Converted DataFrame w/o a Timestamp field")

GeSHi ©

Converting a Table to a to a DataFrame

Code: [Select all] [Expand/Collapse] [Download] (Untitled.txt)

1. # create table manually with values
2. fields = ['product', 'id', 'date', 'price']
3. types = [Table.TextAttributeID(), Table.IntegerAttributeID(), Table.DateAttributeID(), Table.KeyFigureID()]
4. # types = [FieldType.TextAttribute, FieldType.DateAttribute, FieldType.KeyFigure]
5. print "types = " , types
6. values = [
7.     "car", 12, 5, 120.0,
8.     "bus", 9, 10, 145.0]
9. tbl3 = Table.CreateTableWithValues(mdat,fields,types,values)
10. tbl3.ViewTable("tbl 3")
11. # convet Table to DataFrame
12. df = tbl3.ToDataFrame

GeSHi ©

As you see above, a DataFrame can easily be converted to a Table (MatrixTable), and vice versa. This is practical, because DataFrame class of pandas doesn't have all the table functions included in Table, like table arithmetic, filtering with a condition table, subtable transformers, function routers, filtering cells and cell lists, and so on. On the other hand, Table class doesn't have all the table functions included in DataFrame, especially the more SQL-like ones (select, update etc.).

More examples with Table class:

Code: [Select all] [Expand/Collapse] [Download] (Untitled.txt)

1. def Execute(execstr):
2.     print execstr, ':\n', eval(execstr)
3.  
4. # write/read table to/from file (saving and reading tables)
5. filepath = r"tbl3.txt"
6. tbl3.WriteTableToFile(filepath)
7.  
8. tbl4 = Table(mdat)
9. tbl4.ReadFromFile(filepath)
10. print "tbl4:\n", tbl4
11.  
12. # create table with NumPy arrays
13. FieldNValuesDic = (
14.     {'product' : np.array(['car', 'bus', 'cycle', 'motor']),
15.     'category': np.array(['economy','luxury','economy','luxury']),
16.     'date': np.array([2,4,6,8], int),
17.     'price': np.array([120, 200, 300, 250], float)})
18.  
19. tbl5 = Table.CreateTableWithNumArrays(mdat, FieldNValuesDic)
20. print "tbl5:\n", tbl5
21.  
22. # create table with field value combination
23. FieldNValuesDic2 = (
24.     {'product' : np.array(['car', 'bus', 'cycle', 'motor']),
25.     'category': np.array(['economy','luxury']),
26.     'price': np.array([120, 200, 300], float)})
27.  
28. tbl = Table.CreateTableByFieldValueComb(mdat, FieldNValuesDic2)
29. tbl.ViewTable("Field Value Combination - 1")
30.  
31. # get and set table elements by indices
32. print "tbl6[(2,2)] = ", tbl6[(2,2)]
33. print "tbl6[('product',2)] = ", tbl6[('product',2)]
34.  
35. tbl6[(2,2)] = 7
36. tbl6[('product',2)] = 'cycle zzz'
37. print "tbl6[(2,2)] = ", tbl6[(2,2)]
38. print "tbl6[('product',2)] = ", tbl6[('product',2)]
39.  
40. # check if two tables are equal
41. print "tbl.IsEqual(tbl6): ", tbl.IsEqual(tbl6)
42.  
43. # sort table
44. Execute("tbl6.SortRows()")
45. Execute("tbl6.SortRows('product DESC')")
46.  
47. # create table containing all possible row combinations
48. fields = ['product', 'date']
49. types = [Table.TextAttributeID(), Table.DateAttributeID()]
50. values = [
51.     "car", 1200,
52.     "bus", 2300]
53. tbl1 = Table.CreateTableWithValues(mdat,fields,types,values)
54.  
55. fields = ['category', 'price']
56. types = [Table.TextAttributeID(), Table.KeyFigureID()]
57. values = [
58.     "luxury", 250.0,
59.     "economy", 500.0]
60. tbl2 = Table.CreateTableWithValues(mdat,fields,types,values)
61.  
62. tbl = Table.CreateTableByRowComb(mdat, tbl1, tbl2)
63. tbl.ViewTable("Table by Row Combination")
64.  
65. # add a scalar value to all key figures of table
66. tbl = tbl6.InsertNewColumn('length', Table.KeyFigureID())  # insert key figure
67. Execute("tbl.AddScalarToAllKeyFigures(15.55)")
68. Execute("tbl + 25.33")
69.  
70. # multiply all key figures of table with a scalar value
71. Execute("tbl.MultiplyAllKeyFiguresWithScalar(5.55)")
72. Execute("(tbl + 10) * 3.333")
73.  
74. # subtract a scalar value from all key figures of table
75. Execute("tbl.SubtractScalarFromAllKeyFigures(5.11)")
76. Execute("(tbl * 10) - 33.33")
77.  
78. # combine tables
79. FieldNValuesDic1 = (
80.     {'product' : np.array(['car', 'bus', 'cycle', 'motor']),
81.     'category': np.array(['economy','luxury']),
82.     'price': np.array([120, 200, 300], float)})
83. tbl1 = Table.CreateTableByFieldValueComb(mdat, FieldNValuesDic2)
84.  
85. fields = ['product', 'category', 'brand', 'costs']
86. types = [Table.TextAttributeID(), Table.TextAttributeID(), Table.TextAttributeID(), Table.KeyFigureID()]
87. values = [
88.     "car", "luxury", 'WW', 400.0,
89.     "ALL", "luxury", 'ZZ', 300.0,
90.     "ALL", "economy", 'XX', 200.0]
91. tbl2 = Table.CreateTableWithValues(mdat,fields,types,values)
92.  
93. Execute("tbl1.CombineTables(tbl2, JokerMatchesAllvalues = True)")
94. Execute("tbl1.CombineTablesFirstMatch(tbl2, JokerMatchesAllvalues = True)")
95.  

GeSHi ©

1. Release on 26. June 2015: Download tablenet.py - Version 1.01
Most basic table functions: Creating a data table manually with values, viewing tables, writing/reading tables to/from text files (incl. metadata), getting and setting table values, checking if two tables are equal, selecting columns and/or rows of a table, appending (concatenating) tables horizontally or vertically, converting a Table into a pandas DataFrame and vice-versa, inserting a new column (field) into table, reshuffling (mixing) rows of a table, creating a new table by getting all possible combinations of given field values (field combination), creating a new table by combinating rows of given tables (row combination), adding/subtracting/multiplying/dividing scalar values (arithmetic table-scalar operations), combining (joining) tables by common attributes

2. Release on 29. June 2015: Download tablenet.py - Version 1.02
New table functions in this release: Applying a user-defined function (with embedded code in C#/.NET) on every row of table, applying a user-defined function (with reference to a .NET method) on every row of table, applying a user-defined boolean filter (with embedded code in C#/.NET) on every row of table, applying a user-defined boolean filter (with reference to a .NET method) on every row of table, import/export table from/to a CSV file

Python code:

Code: [Select all] [Expand/Collapse] [Download] (Untitled.txt)

1. # apply user-defined function on rows of table, with UserCode
2. FieldNValuesDic = (
3.     {'product' : np.array(['car', 'bus', 'cycle']),
4.     'category': np.array(['economy','luxury']),
5.     'costs': np.array([200, 300], float),
6.     'margin': np.array([0.25, 0.40], float),
7.     'price': np.array([500], float)})
8. tbl = Table.CreateTableByFieldValueComb(mdat, FieldNValuesDic)
9.  
10. UserCode = 'if (TA["product"] != "bus")  KF["price"] = KF["costs"] * (1 + KF["margin"]);'
11. Execute("tbl.ApplyUserDefinedFunctionOnRows(UserCode)")
12.  
13. # apply user-defined function on rows of table, with .NET method reference
14. NETfuncName = 'CalculatePrice'      # see Visual Studio project TablePyHelper
15. Execute("tbl.ApplyUserDefinedNETFuncOnRows(NETfuncName)")
16.  
17. # apply user-defined (boolean) filter function on rows of table, with UserCode
18. UserCode = 'return (TA["product"] == "car" && KF["margin"] > 0.30);'
19. Execute("tbl.ApplyUserDefinedFilterOnRows(UserCode)")
20.  
21. # apply user-defined (boolean) filter function on rows of table, .NET method reference
22. NETfuncName = 'ProductMarginFilter'     # see Visual Studio project TablePyHelper
23. Execute("tbl.ApplyUserDefinedNETFilterOnRows(NETfuncName)")
24.  
25. # Import/Export Table from/to CSV file
26. tbl6.ExportTableToCSVfile("tbl6.csv") # check file in current working directory
27. Execute("Table.ImportTableFromCSVfile(mdat, 'tbl6.csv')")
28.  
29. # aggregate all key figures of table
30. FieldNValuesDic = (
31.     {'product' : np.array(['car', 'bus', 'cycle', 'motor']),
32.     'category': np.array(['economy','luxury']),
33.     'costs': np.array([400, 600], float),
34.     'sales': np.array([900, 1600], float),
35.     'price': np.array([120, 200, 300], float)})
36. tbl = Table.CreateTableByFieldValueComb(mdat, FieldNValuesDic)
37. tbl = tbl.AppendRows(tbl)
38. tbl = tbl.AppendRows(tbl)
39. tbl = tbl.ShuffleRows(50)
40. # print "Table before aggregation:\n", tbl
41.  
42. AggregateOptPerKeyFig = {'price': 'min', 'costs': 'avg'}
43. Execute("tbl.AggregateAllKeyFigures(AggregateOptPerKeyFig, 'sum')")

GeSHi ©