Relation: Table Valued Functions in Python

Table-valued functions in finaquant .net libraries

Relation: Table Valued Functions in Python

Postby tunc » 18 Oct 2015, 18:51

You may find below first release of the Python module named Relation that I started to develop several months ago. This module includes a subset of the table functions included in the .NET library Finaquant Calcs.

A Relation object is a simple table with 1 key figure and N attributes. This rather simple module is surprisingly flexible and powerful as my upcoming articles with use cases will demonstrate.

Relation class includes most versatile table functions like table arithmetic (table addition, multiplication, division, subtraction, and user-defined binary operations) and table aggregation (min, max, avg, sum, and user-defined aggregation operations).

Tunc

Code: [Select all] [Expand/Collapse] [Download] (Untitled.txt)
  1. #!/usr/bin/env python
  2. # -*- coding: utf-8 -*-
  3. # Version: 19.09.2015 (Tunc Ali Kütükcüoglu)
  4. """
  5. Python module with Relation class
  6.  
  7. Operations on Relation objects with a single key figure (number, measure)
  8. and N attributes: KeyFigure(Attribute1, Atribute2, ..., AttributeN)
  9.  
  10. A Relation object is a simple table with 1 key figure and N attributes.
  11. Module contains some simpler table functions as a subset of the broader features
  12. available in the .NET library Finaquant Calcs developed by Tunc Ali Kütükcüoglu.
  13.  
  14. In this class (Relation) a key figure need not be a number, and
  15. attributes need not be strings. For the highest flexibility, it is deliberately
  16. left to the user of this module to take care of data types for key figures
  17. or attributes.
  18.  
  19. Note that a key figure can be any object like a list, or a matrix.
  20. Nevertheless, in most cases, it makes sense to use key figures
  21. for scalar numbers only.
  22.  
  23. Copyrights: Tunc Ali Kütükcüoglu - Finaquant Analytics GmbH
  24. internet: www.finaquant.com
  25. email: tuncalik@finaquant.com
  26.  
  27. Terminology:
  28. - Fields: Key figures or attributes
  29. - Values: Key figure or attribute values
  30.  
  31. To Do's:
  32. * Check field names and values: length, special (unpermitted) characters etc.
  33. * sort
  34. """
  35. # see:
  36. # http://www.tutorialspoint.com/python/python_classes_objects.htm
  37. # https://dbader.org/blog/setting-up-sublime-text-for-python-development
  38. #       install All AutoComplete and SublimeCodeIntel
  39. # http://blog.teamtreehouse.com/operator-overloading-python
  40. # https://docs.python.org/3/reference/datamodel.html#special-method-names
  41. import sys
  42. import numpy as np
  43.  
  44. class Relation:
  45.     """
  46.     Class defining Relation objects: value(attribute1, atribute2, ..., attributeN)
  47.     """
  48.     # list of attribute names of type string
  49.     __Attributes = None
  50.  
  51.     # dictionary of key figure values: KeyFigure(attr1, attr2, ..., attrN)
  52.     __KeyFigureDic = None
  53.  
  54.     def __init__(self, AttributeFields):
  55.         """
  56.         Initiate a Relation with given attributes
  57.         """
  58.         self.__Attributes = []  # create empty list
  59.  
  60.         if AttributeFields == None:
  61.             raise Exception('AttributeFields cannot be null!')
  62.  
  63.         if len(AttributeFields) == 0:
  64.             raise Exception('AttributeFields cannot be an empty list!')
  65.  
  66.         AttrDic = {}
  67.  
  68.         for attribute in AttributeFields:
  69.             if attribute == None:
  70.                 raise Exception('An attribute name cannot be null')
  71.             if type(attribute) != type('str'):
  72.                 raise Exception('All attribute names must be of type string!')
  73.             if AttrDic.has_key(attribute):
  74.                 raise Exception('Every attribute name must be unique!')
  75.             else:
  76.                 AttrDic[attribute] = 1
  77.             # all checks passed
  78.             self.__Attributes.append(attribute)
  79.  
  80.     @property
  81.     def KeyFigureDic(self):
  82.         """
  83.         Get key figure value dictionary with attribute-combinations as keys:
  84.         {(attr1, attr2, att3, ..., attrN) : keyfigure }
  85.         """
  86.         return self.__KeyFigureDic
  87.  
  88.     @property
  89.     def AttributeFields(self):
  90.         """
  91.         Get all ordered attributes of the Relation in a list.
  92.         """
  93.         return self.__Attributes
  94.  
  95.     @classmethod
  96.     def IsNumber(cls, value):
  97.         """
  98.         Check if value is a number
  99.         """
  100.         return isinstance(value, (int, long, float, complex))
  101.  
  102.     def InsertRow(self, NumberValue, AttributeFields, AttributeValues):
  103.         """
  104.         Add a new row (record) to Relation. The given attribute-value
  105.         combination must not already exist in the Relation.
  106.         """
  107.         if not self.CheckIfIdenticalAttributes(AttributeFields):
  108.             raise Exception('Given set of attribute names must match the attributes of Relation!')
  109.        
  110.         if self.CheckIfAttributeValueCombExists(AttributeFields, AttributeValues):
  111.             raise Exception('Attribute-value set already exists in the Relation!')
  112.  
  113.         # reorder attribute values
  114.         OrderedAttributeValues = []
  115.         for attr in self.__Attributes:
  116.             OrderedAttributeValues.append(AttributeValues[AttributeFields.index(attr)])
  117.        
  118.         # add value to dictionary
  119.         if (self.__KeyFigureDic == None):
  120.             self.__KeyFigureDic = {}
  121.  
  122.         self.__KeyFigureDic[tuple(OrderedAttributeValues)] = NumberValue
  123.  
  124.     def InsertOrderedRow(self, KeyFigureValue, OrderedAttributeValues):
  125.         """
  126.         Insert a new row (record) to Relation with ordered attribute values.
  127.         """
  128.         if self.CheckIfAttributeValueCombExists(self.AttributeFields, OrderedAttributeValues):
  129.             raise Exception('Given attribute-value combination already exists in the Relation!')
  130.         # add value to dictionary
  131.         if (self.__KeyFigureDic == None):
  132.             self.__KeyFigureDic = {}
  133.         self.__KeyFigureDic[tuple(OrderedAttributeValues)] = KeyFigureValue
  134.  
  135.     def SetKeyFigure(self, KeyFigureValue, AttributeFields, AttributeValues):
  136.         """
  137.         Set key figure corresponding to given attribute-value combination.
  138.         The given attribute-value combination must exist in the Relation.
  139.         """
  140.         if not self.CheckIfIdenticalAttributes(AttributeFields):
  141.             raise Exception('Given set of attribute names must match the attributes of Relation!')
  142.        
  143.         if not self.CheckIfAttributeValueCombExists(AttributeFields, AttributeValues):
  144.             raise Exception('Attribute-value combination must exists in the Relation!')
  145.  
  146.         # reorder attribute values
  147.         OrderedAttributeValues = []
  148.         for attr in self.__Attributes:
  149.             OrderedAttributeValues.append(AttributeValues[AttributeFields.index(attr)])
  150.        
  151.         # update value in dictionary
  152.         self.__KeyFigureDic[tuple(OrderedAttributeValues)] = KeyFigureValue
  153.  
  154.     def SetKeyFigureOrd(self, KeyFigureValue, OrderedAttributeValues):
  155.         """
  156.         Set key figure corresponding to given ordered attribute-value combination.
  157.         Order: Relation's attribute field order
  158.         """
  159.         self.SetKeyFigure(KeyFigureValue, self.AttributeFields, OrderedAttributeValues)
  160.  
  161.     def __setitem__(self, OrderedAttributeValues, KeyFigureValue):
  162.         self.SetKeyFigureOrd(KeyFigureValue, OrderedAttributeValues)
  163.  
  164.     def CheckIfIdenticalAttributes(self, AttributeFields):
  165.         """
  166.         Check if array AttributeFields contain exactly the same set of attribute names
  167.         as defined in the Relation.
  168.         """
  169.         if AttributeFields == None:
  170.             return False;
  171.         if set(AttributeFields) == set(self.__Attributes):
  172.             return True
  173.         else:
  174.             return False
  175.  
  176.     def CheckIfAttributeValueCombExists(self, AttributeFields, AttributeValues):
  177.         """
  178.         Check if the given full attribute-value combination already exists in the Relation.
  179.         """
  180.         if AttributeFields == None or AttributeValues == None:
  181.             raise Exception('Null-valued input objects!')
  182.  
  183.         if len(AttributeFields) == 0 or len(AttributeValues) == 0:
  184.             raise Exception('Empty input objects!')
  185.  
  186.         if len(AttributeFields) != len(AttributeValues):
  187.             raise Exception('AttributeFields and AttributeValues must have equal lengths!')
  188.  
  189.         if set(AttributeFields) != set(self.__Attributes):
  190.             raise Exception('AttributeFields must contain the same set of attribute names as defined in the Relation!')
  191.  
  192.         if self.__KeyFigureDic == None: return False
  193.         if len(self.__KeyFigureDic) == 0: return False
  194.  
  195.         # reorder attribute values
  196.         OrderedAttributeValues = []
  197.         for attr in self.__Attributes:
  198.             OrderedAttributeValues.append(AttributeValues[AttributeFields.index(attr)])
  199.        
  200.         if self.__KeyFigureDic.has_key(tuple(OrderedAttributeValues)):
  201.             return True
  202.         else:
  203.             return False
  204.  
  205.     def Clone(self):
  206.         """
  207.         Return a clone (copy) of Relation
  208.         """
  209.         R = Relation(self.AttributeFields[:])
  210.         R.__KeyFigureDic = self.__KeyFigureDic.copy()
  211.         return R
  212.  
  213.     def GetKeyFigure(self, AttributeFields, AttributeValues):
  214.         """
  215.         Get key figure corresponding to the given attribute-value combination.
  216.         The given attribute-value combination must exist in the Relation..
  217.         """
  218.         if not self.CheckIfIdenticalAttributes(AttributeFields):
  219.             raise Exception('Given set of attribute names must match the attributes of Relation!')
  220.        
  221.         if not self.CheckIfAttributeValueCombExists(AttributeFields, AttributeValues):
  222.             raise Exception('Attribute-value combination must exists in the Relation!')
  223.  
  224.         # reorder attribute values
  225.         OrderedAttributeValues = []
  226.         for attr in self.__Attributes:
  227.             OrderedAttributeValues.append(AttributeValues[AttributeFields.index(attr)])
  228.        
  229.         # update value in dictionary
  230.         return self.__KeyFigureDic[tuple(OrderedAttributeValues)]
  231.  
  232.     def GetKeyFigureOrd(self, OrderedAttributeValues):
  233.         """
  234.         Get key figure with ordered attribute values.
  235.         Order: Relation's attribute field order
  236.         """
  237.         return self.GetKeyFigure(self.AttributeFields, OrderedAttributeValues)
  238.  
  239.     def __getitem__(self, OrderedAttributeValues):
  240.         return self.GetKeyFigureOrd(OrderedAttributeValues)
  241.  
  242.     @classmethod
  243.     def BinaryOperation_Addition(cls, x, y):
  244.         return x + y + 0.0
  245.  
  246.     @classmethod
  247.     def BinaryOperation_Subtraction(cls, x, y):
  248.         return x - y + 0.0
  249.  
  250.     @classmethod
  251.     def BinaryOperation_Multiplication(cls, x, y):
  252.         return x * y + 0.0
  253.  
  254.     @classmethod
  255.     def BinaryOperation_Division(cls, x, y):
  256.         return (x + 0.0) / y
  257.  
  258.     @classmethod
  259.     def BinaryOperation_Assignment(cls, x, y):
  260.         return y
  261.  
  262.     @classmethod
  263.     def RelationArithmetic(cls, R1, R2, BinaryOperation):
  264.         """
  265.         Arithmetic Relation operation: add/subtract/multiply/divide/assign
  266.         """
  267.         a1 = R1.AttributeFields
  268.         a2 = R2.AttributeFields
  269.         s1 = set(a1)
  270.         s2 = set(a2)
  271.  
  272.         CommonAttributes = s1.intersection(s2)
  273.         R1onlyAttributes = s1.difference(CommonAttributes)
  274.         R2onlyAttributes = s2.difference(CommonAttributes)
  275.  
  276.         UnionAttributes = list(R1onlyAttributes) + list(CommonAttributes) + list(R2onlyAttributes)
  277.         Rout = Relation(UnionAttributes)
  278.  
  279.         # loop over all possible attribute value combinations
  280.         for attr1 in R1.KeyFigureDic.keys():
  281.             for attr2 in R2.KeyFigureDic.keys():
  282.  
  283.                 IfMatch = True
  284.                 CommonAttribVals = []
  285.  
  286.                 for field in CommonAttributes:
  287.                     if attr2[a2.index(field)] != attr1[a1.index(field)]:
  288.                         IfMatch = False
  289.                         break;
  290.                     else:
  291.                         CommonAttribVals.append(attr1[a1.index(field)])
  292.  
  293.                 if IfMatch:
  294.                     val1 = R1.KeyFigureDic[attr1]
  295.                     val2 = R2.KeyFigureDic[attr2]
  296.  
  297.                     R1onlyAttribVals = []
  298.                     for field in R1onlyAttributes:
  299.                         R1onlyAttribVals.append(attr1[a1.index(field)])
  300.  
  301.                     R2onlyAttribVals = []
  302.                     for field in R2onlyAttributes:
  303.                         R2onlyAttribVals.append(attr2[a2.index(field)])
  304.  
  305.                     UnionAttribValComb = R1onlyAttribVals + CommonAttribVals + R2onlyAttribVals
  306.  
  307.                     val = BinaryOperation(val1, val2)
  308.                     Rout.InsertRow(val, UnionAttributes, UnionAttribValComb)
  309.         # return resultant relation
  310.         return Rout
  311.  
  312.     def AddRelation(self, R2):
  313.         """
  314.         Add Relation R2 to R1 (self)
  315.         """
  316.         return Relation.RelationArithmetic(self, R2, Relation.BinaryOperation_Addition)
  317.  
  318.     def __add__(self, R2): return self.AddRelation(R2)
  319.  
  320.     def SubtractRelation(self, R2):
  321.         """
  322.         Subtract Relation R2 from R1 (self)
  323.         """
  324.         return Relation.RelationArithmetic(self, R2, Relation.BinaryOperation_Subtraction)
  325.  
  326.     def __sub__(self, R2): return self.SubtractRelation(R2)
  327.  
  328.     def MultiplyRelation(self, R2):
  329.         """
  330.         Multiply Relation R1 (self) by R2
  331.         """
  332.         return Relation.RelationArithmetic(self, R2, Relation.BinaryOperation_Multiplication)
  333.  
  334.     def __mul__(self, R2): return self.MultiplyRelation(R2)
  335.  
  336.     def DivideRelation(self, R2):
  337.         """
  338.         Divide Relation R1 (self) by R2
  339.         """
  340.         return Relation.RelationArithmetic(self, R2, Relation.BinaryOperation_Division)
  341.  
  342.     def __div__(self, R2): return self.DivideRelation(R2)
  343.  
  344.     def AssignRelation(self, R2):
  345.         """
  346.         Assign Relation R2 (self) to R1
  347.         """
  348.         return Relation.RelationArithmetic(self, R2, Relation.BinaryOperation_Assignment)
  349.  
  350.     @classmethod
  351.     def ScalarArithmetic(cls, R, x, BinaryOperation):
  352.         """
  353.         Arithmetic scalar operation: add/subtract/multiply/divide/assign
  354.         """
  355.         Rout = R.Clone()
  356.  
  357.         for attr in R.KeyFigureDic.keys():
  358.             Rout.KeyFigureDic[attr] = BinaryOperation(Rout.KeyFigureDic[attr], x)
  359.  
  360.         return Rout
  361.  
  362.     def AddScalar(self, x):
  363.         """
  364.         Add a scalar value x to Relation
  365.         """
  366.         return self.ScalarArithmetic(self, x, Relation.BinaryOperation_Addition)
  367.  
  368.     def __radd__(self, x): return self.AddScalar(x)
  369.  
  370.     def SubtractScalar(self, x):
  371.         """
  372.         Subtract a scalar value x from Relation
  373.         """
  374.         return self.ScalarArithmetic(self, x, Relation.BinaryOperation_Subtraction)
  375.  
  376.     def MultiplyByScalar(self, x):
  377.         """
  378.         Multiply Relation by a scalar value x
  379.         """
  380.         return self.ScalarArithmetic(self, x, Relation.BinaryOperation_Multiplication)
  381.  
  382.     def __rmul__(self, x): return self.MultiplyByScalar(x)
  383.  
  384.     def DivideByScalar(self, x):
  385.         """
  386.         Divide Relation by a scalar value x
  387.         """
  388.         return self.ScalarArithmetic(self, x, Relation.BinaryOperation_Division)
  389.  
  390.     def AssignScalar(self, x):
  391.         """
  392.         Assign a single scalar value to all rows of Relation
  393.         """
  394.         return self.ScalarArithmetic(self, x, Relation.BinaryOperation_Assignment)
  395.  
  396.     @classmethod
  397.     def AggregationOperation_Sum(cls, KeyFigureList):
  398.         values = np.array(KeyFigureList)
  399.         return values.sum();
  400.  
  401.     @classmethod
  402.     def AggregationOperation_Average(cls, KeyFigureList):
  403.         values = np.array(KeyFigureList)
  404.         return values.mean();
  405.  
  406.     @classmethod
  407.     def AggregationOperation_Min(cls, KeyFigureList):
  408.         values = np.array(KeyFigureList)
  409.         return values.min();
  410.  
  411.     @classmethod
  412.     def AggregationOperation_Max(cls, KeyFigureList):
  413.         values = np.array(KeyFigureList)
  414.         return values.max();
  415.  
  416.     @classmethod
  417.     def AggregationOperation_Average(cls, KeyFigureList):
  418.         values = np.array(KeyFigureList)
  419.         return values.mean();
  420.  
  421.     def Aggregate(self, GroupingAttributes, AggregationOperation):
  422.         """
  423.         Aggregate Relation w.r.t. given summary (grouping) attributes; sum/avg/min/max/...
  424.         """
  425.         SummaryAttribs = set(GroupingAttributes)
  426.  
  427.         if not set(self.AttributeFields).issuperset(SummaryAttribs):
  428.             raise Exception("Given Attributes must be a subset of the attributes of Relation!")
  429.        
  430.         SummaryAttribList = list(SummaryAttribs)
  431.         Rout = Relation(SummaryAttribList)
  432.         GroupedValues = {}
  433.  
  434.         # find indices of summary attributes
  435.         ind= []
  436.         for field in SummaryAttribList:
  437.             ind.append(self.AttributeFields.index(field))
  438.  
  439.         # group (summarize) values
  440.         for attr in self.KeyFigureDic.keys():
  441.             val = self.KeyFigureDic[attr]
  442.  
  443.             SummaryAttribVals = []
  444.             for i in ind:
  445.                 SummaryAttribVals.append(attr[i])
  446.             sav = tuple(SummaryAttribVals)
  447.  
  448.             if GroupedValues.has_key(sav):
  449.                 GroupedValues[sav].append(val)
  450.             else:
  451.                 GroupedValues[sav] = [val]
  452.  
  453.         # apply aggregation operation on grouped lists of values
  454.         for attr in GroupedValues.keys():
  455.             values = GroupedValues[attr]
  456.             val = AggregationOperation(values)
  457.             Rout.InsertOrderedRow(val, attr)
  458.  
  459.         # return resultant Relation
  460.         return Rout
  461.  
  462.     def Aggregate_Sum(self, GroupingAttributes):
  463.         """
  464.         Aggregate Relation using sum() operation
  465.         """
  466.         return self.Aggregate(GroupingAttributes, Relation.AggregationOperation_Sum)
  467.  
  468.     def Aggregate_Average(self, GroupingAttributes):
  469.         """
  470.         Aggregate Relation using mean() operation
  471.         """
  472.         return self.Aggregate(GroupingAttributes, Relation.AggregationOperation_Average)
  473.  
  474.     def Aggregate_Min(self, GroupingAttributes):
  475.         """
  476.         Aggregate Relation using min() operation
  477.         """
  478.         return self.Aggregate(GroupingAttributes, Relation.AggregationOperation_Min)
  479.  
  480.     def Aggregate_Max(self, GroupingAttributes):
  481.         """
  482.         Aggregate Relation using max() operation
  483.         """
  484.         return self.Aggregate(GroupingAttributes, Relation.AggregationOperation_Max)
  485.  
  486.     def GetProportions(self, GroupingAttributes):
  487.         """
  488.         Get proportions (i.e. ratios) grouped by given summary attributes
  489.         """
  490.         return self.DivideRelation(self.Aggregate_Sum(GroupingAttributes))
  491.  
  492.     def GetSubRelation(self, AttributeFields, AttributeValues):
  493.         """
  494.         Return a sub-Relation with selected rows; i.e. rows with
  495.         selected attribute values.
  496.         """
  497.         SelectedFields = set(AttributeFields)
  498.         RelationFields = self.AttributeFields
  499.  
  500.         if not set(RelationFields).issuperset(SelectedFields):
  501.             raise Exception('Given attribute fields must be a subset of relation fields!')
  502.         if len(SelectedFields) != len(AttributeFields):
  503.             raise Exception('All attribute fields in array AttributeFields must be unique!')
  504.         if len(AttributeFields) != len(AttributeValues):
  505.             raise Exception('Lengths of arrays AttributeFields and AttributeValues must be identical!')
  506.  
  507.         Rout = Relation(RelationFields)
  508.        
  509.         for AttrValComb in self.KeyFigureDic.keys():
  510.             IfMatch = True
  511.  
  512.             for field in AttributeFields:
  513.                 if AttributeValues[AttributeFields.index(field)] != AttrValComb[RelationFields.index(field)]:
  514.                     IfMatch = False
  515.                     break;
  516.  
  517.             if IfMatch:
  518.                 val = self.__KeyFigureDic[AttrValComb]
  519.                 Rout.InsertRow(val, RelationFields, AttrValComb)
  520.         # return resultant Relation
  521.         return Rout
  522.  
  523.     @classmethod
  524.     def ListOperation_Reverse(cls, ValueList):
  525.         # see: http://stackoverflow.com/questions/2612802/how-to-clone-or-copy-a-list-in-python
  526.         L = ValueList[:]        # clone
  527.         L.reverse()
  528.         return L;
  529.  
  530.     def ApplyListOperationToSubRelation(self, GroupingAttributes, ListOperation):
  531.         """
  532.         Apply given list-to-list operation to sub-Relations grouped by given attributes.
  533.         ListOperation must return a list whose length is identical to the length of input list.
  534.         """
  535.         SummaryAttribs = set(GroupingAttributes)
  536.  
  537.         if not set(self.AttributeFields).issuperset(SummaryAttribs):
  538.             raise Exception("Given Attributes must be a subset of the attributes of Relation!")
  539.        
  540.         SummaryAttribList = list(SummaryAttribs)
  541.         Rout = self.Clone()
  542.         GroupedValues = {}
  543.         CorrAttribValues = {}  # attribute value combinations corresponding to values in the list
  544.  
  545.         # find indices of grouping attributes
  546.         ind= []
  547.         for field in SummaryAttribList:
  548.             ind.append(self.AttributeFields.index(field))
  549.  
  550.         # group values in separate lists
  551.         for attr in self.KeyFigureDic.keys():
  552.             val = self.KeyFigureDic[attr]
  553.  
  554.             SummaryAttribVals = []
  555.             for i in ind:
  556.                 SummaryAttribVals.append(attr[i])
  557.             sav = tuple(SummaryAttribVals)
  558.  
  559.             if GroupedValues.has_key(sav):
  560.                 GroupedValues[sav].append(val)
  561.                 CorrAttribValues[sav].append(attr)
  562.             else:
  563.                 GroupedValues[sav] = [val]
  564.                 CorrAttribValues[sav] = [attr]
  565.  
  566.         # apply aggregation operation on grouped lists of values
  567.         for sav in GroupedValues.keys():
  568.             ValueListIn = GroupedValues[sav]
  569.             ValueListOut = ListOperation(ValueListIn)
  570.             AttribValues = CorrAttribValues[sav]
  571.  
  572.             for i in range(0, len(ValueListOut)):
  573.                 Rout.__KeyFigureDic[AttribValues[i]] = ValueListOut[i] 
  574.  
  575.         # return resultant Relation
  576.         return Rout
  577.  
  578.     def __str__(self):
  579.         """
  580.         Convert Relation to printable string.
  581.         A key figure can be a scalar number, or a list of scalar numbers.
  582.         """
  583.         dstr = ''
  584.         if type(self.__KeyFigureDic[self.__KeyFigureDic.keys()[0]]) == type([1,2]): # type == list
  585.             dstr = '\nValueList ' + str(tuple(self.AttributeFields)) + '\n'
  586.             for AttribVals in self.__KeyFigureDic.keys():
  587.                 ValueList = self.__KeyFigureDic[AttribVals]
  588.                 dstr += str([round(x,2) for x in ValueList]) + ' ' + str(AttribVals) + '\n'
  589.  
  590.         elif self.IsNumber(self.__KeyFigureDic[self.__KeyFigureDic.keys()[0]]):     # type == scalar number
  591.             dstr = '\n{:>10}'.format('Value') + '  '
  592.  
  593.             for Attrib in self.AttributeFields:
  594.                 dstr += '{:^11}'.format(Attrib)
  595.             dstr += '\n'
  596.  
  597.             DisplayWith = (12 * (1 + len(self.AttributeFields)))
  598.             dstr += ('{:-^' + str(DisplayWith) + '}').format('') + '\n'
  599.  
  600.             for AttribVals in self.__KeyFigureDic.keys():
  601.                 val = self.__KeyFigureDic[AttribVals]
  602.                 dstr += '{:>10.2f}'.format(val) + ' ('
  603.                 for i in range(0,len(AttribVals)):
  604.                     if i < len(AttribVals) -1:
  605.                         dstr += '{:^10}'.format(AttribVals[i]) + ','
  606.                     else:
  607.                         dstr +=  '{:^10}'.format(AttribVals[i]) + ')'
  608.                 dstr += '\n'
  609.         else:
  610.             raise Exception('Unexpected value type!')
  611.         # return resultant string
  612.         return dstr
  613.  
  614.     def ExportToExcel(self, SaveFileAs = 'dummy.xlsx', KeyFigureFields = None):
  615.         """
  616.         Export Relation to a new MS Excel file.
  617.         A key figure can be a scalar number, or a list of scalar numbers.
  618.         """
  619.         # see: https://automatetheboringstuff.com/chapter12/
  620.         from openpyxl import Workbook
  621.         from openpyxl.cell import get_column_letter
  622.         wb = Workbook()
  623.         wb.visible = True
  624.         ws = wb.active
  625.  
  626.         # key figure type == list
  627.         if type(self.__KeyFigureDic[self.__KeyFigureDic.keys()[0]]) == type([1,2]):
  628.             ListLength = len(self.__KeyFigureDic[self.__KeyFigureDic.keys()[0]])
  629.  
  630.             # field names
  631.             if KeyFigureFields == None:
  632.                 for i in range(1, ListLength+1):
  633.                     CellAddr = get_column_letter(i) + str(1)
  634.                     ws[CellAddr] = 'Value-' + str(i)
  635.             elif len(KeyFigureFields) == ListLength:
  636.                 for i in range(1, ListLength+1):
  637.                     CellAddr = get_column_letter(i) + str(1)
  638.                     ws[CellAddr] = KeyFigureFields[i-1]
  639.             else:
  640.                 raise Exception('Number of fields in KeyFigureFields must be equal to the length of value list!')
  641.  
  642.             for i in range(1, len(self.__Attributes) + 1):
  643.                 CellAddr = get_column_letter(i+ListLength) + str(1)
  644.                 ws[CellAddr] = self.__Attributes[i-1]
  645.  
  646.             # field values
  647.             RowCount = 2
  648.             for AttribVals in self.__KeyFigureDic.keys():
  649.                 ValueList = self.__KeyFigureDic[AttribVals]
  650.                 for i in range(1, ListLength+1):
  651.                     CellAddr = get_column_letter(i) + str(RowCount)
  652.                     ws[CellAddr] = ValueList[i-1]
  653.                 for i in range(1, len(AttribVals)+1):
  654.                     CellAddr = get_column_letter(i+ListLength) + str(RowCount)
  655.                     ws[CellAddr] = AttribVals[i-1]
  656.                 RowCount +=1
  657.  
  658.         # key figure type == scalar value
  659.         elif self.IsNumber(self.__KeyFigureDic[self.__KeyFigureDic.keys()[0]]):
  660.             ListLength = 1
  661.  
  662.             # field names
  663.             if KeyFigureFields == None:
  664.                 ws['A1'] = 'Value'
  665.             elif len(KeyFigureFields) == ListLength:
  666.                 ws['A1'] = KeyFigureFields[0]
  667.             else:
  668.                 raise Exception('Number of fields in KeyFigureFields must be equal to the length of value list!')
  669.  
  670.             for i in range(1, len(self.__Attributes) + 1):
  671.                 CellAddr = get_column_letter(i+ListLength) + str(1)
  672.                 ws[CellAddr] = self.__Attributes[i-1]
  673.  
  674.             # field values
  675.             RowCount = 2
  676.             for AttribVals in self.__KeyFigureDic.keys():
  677.                 Value = self.__KeyFigureDic[AttribVals]
  678.                 CellAddr = get_column_letter(1) + str(RowCount)
  679.                 ws[CellAddr] = Value
  680.                 for i in range(1, len(AttribVals)+1):
  681.                     CellAddr = get_column_letter(i+ListLength) + str(RowCount)
  682.                     ws[CellAddr] = AttribVals[i-1]
  683.                 RowCount +=1
  684.         else:
  685.             raise Exception('Unexpected key figure value type!')
  686.         # save excel workbook
  687.         wb.save(filename = SaveFileAs)
  688.  
  689.     def KeyFiguresToAttribute(self, AttributeName, AttributeValues = None):
  690.         """
  691.         Convert a Relation with multiple key figures (list values) to a Relation
  692.         with a single scalar value, by adding a new attribute.
  693.         """
  694.         FirstValue = self.__KeyFigureDic[self.__KeyFigureDic.keys()[0]]
  695.  
  696.         if type(FirstValue) != type([1,2]):
  697.             raise Exception("Relation doesn't have a list of values!")
  698.  
  699.         if AttributeValues != None and len(FirstValue) != len(AttributeValues):
  700.             raise Exception("Length of Relation's ValueList must be equal to length of AttributeValues!")
  701.  
  702.         AttribFields = self.__Attributes[:]
  703.         AttribFields.append(AttributeName)
  704.         Rout = Relation(AttribFields)
  705.  
  706.         for AttribComb in self.__KeyFigureDic.keys():
  707.             ValueList = self.__KeyFigureDic[AttribComb]
  708.  
  709.             for i in range(0, len(ValueList)):
  710.                 if AttributeValues == None:
  711.                     AttribCombExt = AttribComb + tuple([i+1])
  712.                 else:
  713.                     AttribCombExt = AttribComb + tuple(AttributeValues[i])
  714.                 Rout.InsertOrderedRow(ValueList[i], AttribCombExt)
  715.         # return resultant Relation
  716.         return Rout
  717.  
  718. # test module
  719.  
  720. if __name__ == '__main__':
  721.  
  722.     # initiate a Relation
  723.     r1 = Relation(['year','country','product'])
  724.  
  725.     # add rows to Relation
  726.     r1.InsertRow(35,['year','country','product'],(1999,'Germany','Car'))
  727.     r1.InsertRow(65,['country','year','product'],('Turkey', 2009,'Olive'))
  728.     r1.InsertRow(900,['year','country','product'],(1999,'Germany','Train'))
  729.     r1.InsertRow(75,['country','year','product'],('Turkey', 2015,'Olive'))
  730.     r1.InsertRow(105,['country','year','product'],('Turkey', 2005,'Pistache'))
  731.  
  732.     # not matching attribute names
  733.     try:
  734.         r1.InsertRow(65,['region','year','product'],('North Africa', 2009, 'Car'))
  735.     except Exception, e:
  736.         print str(e)
  737.  
  738.     # already existing attribute-value set
  739.     try:
  740.         r1.InsertRow(65,['region','year','product'],('Turkey', 2009, 'Olive'))
  741.     except Exception, e:
  742.         print str(e)
  743.  
  744.     print "r1 after inserting rows: " + str(r1)
  745.  
  746.     # set key figure values in Relation
  747.     r1.SetKeyFigure(44,['year','country','product'],(1999,'Germany','Car'))
  748.     r1.SetKeyFigure(88,['country','year','product'],('Turkey', 2009,'Olive'))
  749.  
  750.     print "r1 after setting key figures:"
  751.     print r1
  752.  
  753.     # attribute-value combination does not exist in the Relation (2008)
  754.     try:
  755.         r1.SetKeyFigure(99,['country','year','product'],('Turkey', 2008,'Olive'))
  756.     except Exception, e:
  757.         print str(e)
  758.  
  759.     print "r1 after setting key figure values: " + str(r1)
  760.  
  761.     # clone relation
  762.     r = r1.Clone()
  763.     print "r = r1.Clone(): " + str(r.KeyFigureDic)
  764.  
  765.     # get value
  766.     print "r.GetKeyFigure(['year','country','product'],(1999,'Germany','Car')): " + str(r.GetKeyFigure(['year','country','product'],(1999,'Germany','Car')))
  767.     print "r.GetKeyFigure(['country', 'year','product'],('Germany', 1999,'Car')): " + str(r.GetKeyFigure(['country','year','product'],('Germany',1999,'Car')))
  768.  
  769.     # ordered attribute calues
  770.     print "\nGet key figure value with ordered attribute values:"
  771.     print "r.GetKeyFigureOrd((1999,'Germany','Car')): " + str(r.GetKeyFigureOrd((1999,'Germany','Car')))
  772.    
  773.     # get key figure value with index (with ordered attribute values)
  774.     print "r[(1999,'Germany','Car')]: " + str(r[(1999,'Germany','Car')])
  775.  
  776.     # set key figure value with index (with ordered attribute values)
  777.     print "r[(1999,'Germany','Car')] = 9999 : "
  778.     r[(1999,'Germany','Car')] = 9999
  779.     print r
  780.  
  781.     # arithmetical relation operations
  782.     r2 = Relation(['year','country'])
  783.     r2.InsertRow(2.5,['year','country'], [2009,'Turkey'])
  784.     r2.InsertRow(0.5,['year','country'], [1999,'Germany'])
  785.  
  786.     print "r = RelationArithmetic(r1, r2,'add'):"
  787.     r = Relation.RelationArithmetic(r1, r2, Relation.BinaryOperation_Addition)
  788.     print r.KeyFigureDic
  789.  
  790.     # relation addition
  791.     print "r = r1 + r2:"
  792.     r = r1 + r2
  793.     print r
  794.  
  795.     # relation multiplication
  796.     print "r = r1 * r2:"
  797.     r = r1 * r2
  798.     print r
  799.  
  800.     # relation subtraction
  801.     print "r = r1 - r2:"
  802.     r = r1 - r2
  803.     print r
  804.  
  805.     # relation division
  806.     print "r = r1 / r2:"
  807.     r = r1 / r2
  808.     print r
  809.  
  810.     # arithmetic scalar operations
  811.     print "r2 = r1.AddScalar(99.999)"
  812.     print "r2 before addition:"
  813.     print r2
  814.     r2 = r1.AddScalar(99.999)
  815.     print "r2 after addition:"
  816.     print r2
  817.  
  818.     print "100 + r2:"
  819.     r2 = 100 + r2
  820.     print r2
  821.  
  822.     print "0.1 + r2:"
  823.     r2 = 0.1 * r2
  824.     print r2
  825.  
  826.     print "Complex Relation multiplication:"
  827.     r3 = Relation(['year','country','quarter'])
  828.     r3.InsertOrderedRow(0.2, (1999,'Germany','Q1'))
  829.     r3.InsertOrderedRow(0.5, (1999,'Germany','Q2'))
  830.     r3.InsertOrderedRow(0.4, (2005,'Germany','Q1'))
  831.     r3.InsertOrderedRow(0.4, (2005,'Germany','Q3'))
  832.     r3.InsertOrderedRow(0.1, (1999,'France','Q2'))
  833.     r3.InsertOrderedRow(0.2, (1999,'France','Q3'))
  834.     r3.InsertOrderedRow(0.3, (1999,'France','Q4'))
  835.     print "r3:\n" + str(r3)
  836.  
  837.     print "r = r1 x r3"
  838.     r = r1 * r3
  839.     print r
  840.  
  841.     r4 = Relation(['months','quarter'])
  842.     r4.InsertOrderedRow(0.1, ('1-3','Q1'))
  843.     r4.InsertOrderedRow(0.2, ('4-6','Q2'))
  844.     r4.InsertOrderedRow(0.3, ('7-9','Q3'))
  845.     r4.InsertOrderedRow(0.4, ('10-12','Q4'))
  846.     r5 = r1 * r4
  847.     print "r5 = r1 * r4:\n" + str(r5)
  848.  
  849.     # aggregate relation
  850.     print "r = r5.Aggregate_Sum(['country'])"
  851.     r = r5.Aggregate_Sum(['country'])
  852.     print r
  853.  
  854.     print "r = r5.Aggregate_Max(['country'])"
  855.     r = r5.Aggregate_Max(['country'])
  856.     print r
  857.  
  858.     print "r = r5.Aggregate_Min(['country'])"
  859.     r = r5.Aggregate_Min(['country'])
  860.     print r
  861.  
  862.     print "r6 = r5.Aggregate_Average(['country','product'])"
  863.     r6 = r5.Aggregate_Average(['country','product'])
  864.     print r6
  865.  
  866.     # get proportions
  867.     print "r = r6.GetProportions(['country','product'])"
  868.     r = r6.GetProportions(['country','product'])
  869.     print r
  870.  
  871.     print "r = r5.GetProportions(['country','product'])"
  872.     r = r5.GetProportions(['country','product'])
  873.     print r
  874.  
  875.     # get sub-Relation
  876.     print "r5.GetSubRelation(['product', 'country'], ['Car','Germany']) :"
  877.     print r5.GetSubRelation(['product', 'country'], ['Car','Germany'])
  878.  
  879.     # apply list operation on sub-relations
  880.     def ExchangeMinMaxValues(ValueList):
  881.         L = ValueList[:]    # clone list
  882.         maxVal = max(L)
  883.         minVal = min(L)
  884.         IndMax = L.index(maxVal)
  885.         IndMin = L.index(minVal)
  886.         L[IndMin] = maxVal
  887.         L[IndMax] = minVal
  888.         return L
  889.  
  890.     l = [1,4,2,8,5]
  891.     print "l = " + str(l)
  892.     print "ExchangeMinMaxValues(l) = " + str(ExchangeMinMaxValues(l))
  893.  
  894.     print "r5:"
  895.     print r5
  896.  
  897.     print "r5.ApplyListOperationToSubRelation(['country','product'], ExchangeMinMaxValues):"
  898.     print r5.ApplyListOperationToSubRelation(['country','product'], ExchangeMinMaxValues)
  899.  
  900.     print "r5:"
  901.     print r5
  902.  
  903.     print "Export Relation to Excel"
  904.     print "r5.ExportToExcel()"
  905.     r5.ExportToExcel(SaveFileAs = 'dummy2.xlsx', KeyFigureFields=['Measure'])
GeSHi ©
tunc
 
Posts: 25
Joined: 22 Jul 2014, 19:29
Location: Switzerland

Return to Table Functions

Who is online

Users browsing this forum: No registered users and 0 guests

cron