Page 1 of 1

Download Python Module for Table Valued Functions

PostPosted: 26 Jun 2015, 09:33
by tunc
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 ©

Release History of Python Module tablenet.py

PostPosted: 29 Jun 2015, 11:22
by tunc
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

Testing new table functions added in 2. release

PostPosted: 01 Jul 2015, 12:01
by admin
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 ©