Relation: Table Valued Functions in Python

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
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)
- #!/usr/bin/env python
- # -*- coding: utf-8 -*-
- # Version: 19.09.2015 (Tunc Ali Kütükcüoglu)
- """
- Python module with Relation class
- Operations on Relation objects with a single key figure (number, measure)
- and N attributes: KeyFigure(Attribute1, Atribute2, ..., AttributeN)
- A Relation object is a simple table with 1 key figure and N attributes.
- Module contains some simpler table functions as a subset of the broader features
- available in the .NET library Finaquant Calcs developed by Tunc Ali Kütükcüoglu.
- In this class (Relation) a key figure need not be a number, and
- attributes need not be strings. For the highest flexibility, it is deliberately
- left to the user of this module to take care of data types for key figures
- or attributes.
- Note that a key figure can be any object like a list, or a matrix.
- Nevertheless, in most cases, it makes sense to use key figures
- for scalar numbers only.
- Copyrights: Tunc Ali Kütükcüoglu - Finaquant Analytics GmbH
- internet: www.finaquant.com
- email: tuncalik@finaquant.com
- Terminology:
- - Fields: Key figures or attributes
- - Values: Key figure or attribute values
- To Do's:
- * Check field names and values: length, special (unpermitted) characters etc.
- * sort
- """
- # see:
- # http://www.tutorialspoint.com/python/python_classes_objects.htm
- # https://dbader.org/blog/setting-up-sublime-text-for-python-development
- # install All AutoComplete and SublimeCodeIntel
- # http://blog.teamtreehouse.com/operator-overloading-python
- # https://docs.python.org/3/reference/datamodel.html#special-method-names
- import sys
- import numpy as np
- class Relation:
- """
- Class defining Relation objects: value(attribute1, atribute2, ..., attributeN)
- """
- # list of attribute names of type string
- __Attributes = None
- # dictionary of key figure values: KeyFigure(attr1, attr2, ..., attrN)
- __KeyFigureDic = None
- def __init__(self, AttributeFields):
- """
- Initiate a Relation with given attributes
- """
- self.__Attributes = [] # create empty list
- if AttributeFields == None:
- raise Exception('AttributeFields cannot be null!')
- if len(AttributeFields) == 0:
- raise Exception('AttributeFields cannot be an empty list!')
- AttrDic = {}
- for attribute in AttributeFields:
- if attribute == None:
- raise Exception('An attribute name cannot be null')
- if type(attribute) != type('str'):
- raise Exception('All attribute names must be of type string!')
- if AttrDic.has_key(attribute):
- raise Exception('Every attribute name must be unique!')
- else:
- AttrDic[attribute] = 1
- # all checks passed
- self.__Attributes.append(attribute)
- @property
- def KeyFigureDic(self):
- """
- Get key figure value dictionary with attribute-combinations as keys:
- {(attr1, attr2, att3, ..., attrN) : keyfigure }
- """
- return self.__KeyFigureDic
- @property
- def AttributeFields(self):
- """
- Get all ordered attributes of the Relation in a list.
- """
- return self.__Attributes
- @classmethod
- def IsNumber(cls, value):
- """
- Check if value is a number
- """
- return isinstance(value, (int, long, float, complex))
- def InsertRow(self, NumberValue, AttributeFields, AttributeValues):
- """
- Add a new row (record) to Relation. The given attribute-value
- combination must not already exist in the Relation.
- """
- if not self.CheckIfIdenticalAttributes(AttributeFields):
- raise Exception('Given set of attribute names must match the attributes of Relation!')
- if self.CheckIfAttributeValueCombExists(AttributeFields, AttributeValues):
- raise Exception('Attribute-value set already exists in the Relation!')
- # reorder attribute values
- OrderedAttributeValues = []
- for attr in self.__Attributes:
- OrderedAttributeValues.append(AttributeValues[AttributeFields.index(attr)])
- # add value to dictionary
- if (self.__KeyFigureDic == None):
- self.__KeyFigureDic = {}
- self.__KeyFigureDic[tuple(OrderedAttributeValues)] = NumberValue
- def InsertOrderedRow(self, KeyFigureValue, OrderedAttributeValues):
- """
- Insert a new row (record) to Relation with ordered attribute values.
- """
- if self.CheckIfAttributeValueCombExists(self.AttributeFields, OrderedAttributeValues):
- raise Exception('Given attribute-value combination already exists in the Relation!')
- # add value to dictionary
- if (self.__KeyFigureDic == None):
- self.__KeyFigureDic = {}
- self.__KeyFigureDic[tuple(OrderedAttributeValues)] = KeyFigureValue
- def SetKeyFigure(self, KeyFigureValue, AttributeFields, AttributeValues):
- """
- Set key figure corresponding to given attribute-value combination.
- The given attribute-value combination must exist in the Relation.
- """
- if not self.CheckIfIdenticalAttributes(AttributeFields):
- raise Exception('Given set of attribute names must match the attributes of Relation!')
- if not self.CheckIfAttributeValueCombExists(AttributeFields, AttributeValues):
- raise Exception('Attribute-value combination must exists in the Relation!')
- # reorder attribute values
- OrderedAttributeValues = []
- for attr in self.__Attributes:
- OrderedAttributeValues.append(AttributeValues[AttributeFields.index(attr)])
- # update value in dictionary
- self.__KeyFigureDic[tuple(OrderedAttributeValues)] = KeyFigureValue
- def SetKeyFigureOrd(self, KeyFigureValue, OrderedAttributeValues):
- """
- Set key figure corresponding to given ordered attribute-value combination.
- Order: Relation's attribute field order
- """
- self.SetKeyFigure(KeyFigureValue, self.AttributeFields, OrderedAttributeValues)
- def __setitem__(self, OrderedAttributeValues, KeyFigureValue):
- self.SetKeyFigureOrd(KeyFigureValue, OrderedAttributeValues)
- def CheckIfIdenticalAttributes(self, AttributeFields):
- """
- Check if array AttributeFields contain exactly the same set of attribute names
- as defined in the Relation.
- """
- if AttributeFields == None:
- return False;
- if set(AttributeFields) == set(self.__Attributes):
- return True
- else:
- return False
- def CheckIfAttributeValueCombExists(self, AttributeFields, AttributeValues):
- """
- Check if the given full attribute-value combination already exists in the Relation.
- """
- if AttributeFields == None or AttributeValues == None:
- raise Exception('Null-valued input objects!')
- if len(AttributeFields) == 0 or len(AttributeValues) == 0:
- raise Exception('Empty input objects!')
- if len(AttributeFields) != len(AttributeValues):
- raise Exception('AttributeFields and AttributeValues must have equal lengths!')
- if set(AttributeFields) != set(self.__Attributes):
- raise Exception('AttributeFields must contain the same set of attribute names as defined in the Relation!')
- if self.__KeyFigureDic == None: return False
- if len(self.__KeyFigureDic) == 0: return False
- # reorder attribute values
- OrderedAttributeValues = []
- for attr in self.__Attributes:
- OrderedAttributeValues.append(AttributeValues[AttributeFields.index(attr)])
- if self.__KeyFigureDic.has_key(tuple(OrderedAttributeValues)):
- return True
- else:
- return False
- def Clone(self):
- """
- Return a clone (copy) of Relation
- """
- R = Relation(self.AttributeFields[:])
- R.__KeyFigureDic = self.__KeyFigureDic.copy()
- return R
- def GetKeyFigure(self, AttributeFields, AttributeValues):
- """
- Get key figure corresponding to the given attribute-value combination.
- The given attribute-value combination must exist in the Relation..
- """
- if not self.CheckIfIdenticalAttributes(AttributeFields):
- raise Exception('Given set of attribute names must match the attributes of Relation!')
- if not self.CheckIfAttributeValueCombExists(AttributeFields, AttributeValues):
- raise Exception('Attribute-value combination must exists in the Relation!')
- # reorder attribute values
- OrderedAttributeValues = []
- for attr in self.__Attributes:
- OrderedAttributeValues.append(AttributeValues[AttributeFields.index(attr)])
- # update value in dictionary
- return self.__KeyFigureDic[tuple(OrderedAttributeValues)]
- def GetKeyFigureOrd(self, OrderedAttributeValues):
- """
- Get key figure with ordered attribute values.
- Order: Relation's attribute field order
- """
- return self.GetKeyFigure(self.AttributeFields, OrderedAttributeValues)
- def __getitem__(self, OrderedAttributeValues):
- return self.GetKeyFigureOrd(OrderedAttributeValues)
- @classmethod
- def BinaryOperation_Addition(cls, x, y):
- return x + y + 0.0
- @classmethod
- def BinaryOperation_Subtraction(cls, x, y):
- return x - y + 0.0
- @classmethod
- def BinaryOperation_Multiplication(cls, x, y):
- return x * y + 0.0
- @classmethod
- def BinaryOperation_Division(cls, x, y):
- return (x + 0.0) / y
- @classmethod
- def BinaryOperation_Assignment(cls, x, y):
- return y
- @classmethod
- def RelationArithmetic(cls, R1, R2, BinaryOperation):
- """
- Arithmetic Relation operation: add/subtract/multiply/divide/assign
- """
- a1 = R1.AttributeFields
- a2 = R2.AttributeFields
- s1 = set(a1)
- s2 = set(a2)
- CommonAttributes = s1.intersection(s2)
- R1onlyAttributes = s1.difference(CommonAttributes)
- R2onlyAttributes = s2.difference(CommonAttributes)
- UnionAttributes = list(R1onlyAttributes) + list(CommonAttributes) + list(R2onlyAttributes)
- Rout = Relation(UnionAttributes)
- # loop over all possible attribute value combinations
- for attr1 in R1.KeyFigureDic.keys():
- for attr2 in R2.KeyFigureDic.keys():
- IfMatch = True
- CommonAttribVals = []
- for field in CommonAttributes:
- if attr2[a2.index(field)] != attr1[a1.index(field)]:
- IfMatch = False
- break;
- else:
- CommonAttribVals.append(attr1[a1.index(field)])
- if IfMatch:
- val1 = R1.KeyFigureDic[attr1]
- val2 = R2.KeyFigureDic[attr2]
- R1onlyAttribVals = []
- for field in R1onlyAttributes:
- R1onlyAttribVals.append(attr1[a1.index(field)])
- R2onlyAttribVals = []
- for field in R2onlyAttributes:
- R2onlyAttribVals.append(attr2[a2.index(field)])
- UnionAttribValComb = R1onlyAttribVals + CommonAttribVals + R2onlyAttribVals
- val = BinaryOperation(val1, val2)
- Rout.InsertRow(val, UnionAttributes, UnionAttribValComb)
- # return resultant relation
- return Rout
- def AddRelation(self, R2):
- """
- Add Relation R2 to R1 (self)
- """
- return Relation.RelationArithmetic(self, R2, Relation.BinaryOperation_Addition)
- def __add__(self, R2): return self.AddRelation(R2)
- def SubtractRelation(self, R2):
- """
- Subtract Relation R2 from R1 (self)
- """
- return Relation.RelationArithmetic(self, R2, Relation.BinaryOperation_Subtraction)
- def __sub__(self, R2): return self.SubtractRelation(R2)
- def MultiplyRelation(self, R2):
- """
- Multiply Relation R1 (self) by R2
- """
- return Relation.RelationArithmetic(self, R2, Relation.BinaryOperation_Multiplication)
- def __mul__(self, R2): return self.MultiplyRelation(R2)
- def DivideRelation(self, R2):
- """
- Divide Relation R1 (self) by R2
- """
- return Relation.RelationArithmetic(self, R2, Relation.BinaryOperation_Division)
- def __div__(self, R2): return self.DivideRelation(R2)
- def AssignRelation(self, R2):
- """
- Assign Relation R2 (self) to R1
- """
- return Relation.RelationArithmetic(self, R2, Relation.BinaryOperation_Assignment)
- @classmethod
- def ScalarArithmetic(cls, R, x, BinaryOperation):
- """
- Arithmetic scalar operation: add/subtract/multiply/divide/assign
- """
- Rout = R.Clone()
- for attr in R.KeyFigureDic.keys():
- Rout.KeyFigureDic[attr] = BinaryOperation(Rout.KeyFigureDic[attr], x)
- return Rout
- def AddScalar(self, x):
- """
- Add a scalar value x to Relation
- """
- return self.ScalarArithmetic(self, x, Relation.BinaryOperation_Addition)
- def __radd__(self, x): return self.AddScalar(x)
- def SubtractScalar(self, x):
- """
- Subtract a scalar value x from Relation
- """
- return self.ScalarArithmetic(self, x, Relation.BinaryOperation_Subtraction)
- def MultiplyByScalar(self, x):
- """
- Multiply Relation by a scalar value x
- """
- return self.ScalarArithmetic(self, x, Relation.BinaryOperation_Multiplication)
- def __rmul__(self, x): return self.MultiplyByScalar(x)
- def DivideByScalar(self, x):
- """
- Divide Relation by a scalar value x
- """
- return self.ScalarArithmetic(self, x, Relation.BinaryOperation_Division)
- def AssignScalar(self, x):
- """
- Assign a single scalar value to all rows of Relation
- """
- return self.ScalarArithmetic(self, x, Relation.BinaryOperation_Assignment)
- @classmethod
- def AggregationOperation_Sum(cls, KeyFigureList):
- values = np.array(KeyFigureList)
- return values.sum();
- @classmethod
- def AggregationOperation_Average(cls, KeyFigureList):
- values = np.array(KeyFigureList)
- return values.mean();
- @classmethod
- def AggregationOperation_Min(cls, KeyFigureList):
- values = np.array(KeyFigureList)
- return values.min();
- @classmethod
- def AggregationOperation_Max(cls, KeyFigureList):
- values = np.array(KeyFigureList)
- return values.max();
- @classmethod
- def AggregationOperation_Average(cls, KeyFigureList):
- values = np.array(KeyFigureList)
- return values.mean();
- def Aggregate(self, GroupingAttributes, AggregationOperation):
- """
- Aggregate Relation w.r.t. given summary (grouping) attributes; sum/avg/min/max/...
- """
- SummaryAttribs = set(GroupingAttributes)
- if not set(self.AttributeFields).issuperset(SummaryAttribs):
- raise Exception("Given Attributes must be a subset of the attributes of Relation!")
- SummaryAttribList = list(SummaryAttribs)
- Rout = Relation(SummaryAttribList)
- GroupedValues = {}
- # find indices of summary attributes
- ind= []
- for field in SummaryAttribList:
- ind.append(self.AttributeFields.index(field))
- # group (summarize) values
- for attr in self.KeyFigureDic.keys():
- val = self.KeyFigureDic[attr]
- SummaryAttribVals = []
- for i in ind:
- SummaryAttribVals.append(attr[i])
- sav = tuple(SummaryAttribVals)
- if GroupedValues.has_key(sav):
- GroupedValues[sav].append(val)
- else:
- GroupedValues[sav] = [val]
- # apply aggregation operation on grouped lists of values
- for attr in GroupedValues.keys():
- values = GroupedValues[attr]
- val = AggregationOperation(values)
- Rout.InsertOrderedRow(val, attr)
- # return resultant Relation
- return Rout
- def Aggregate_Sum(self, GroupingAttributes):
- """
- Aggregate Relation using sum() operation
- """
- return self.Aggregate(GroupingAttributes, Relation.AggregationOperation_Sum)
- def Aggregate_Average(self, GroupingAttributes):
- """
- Aggregate Relation using mean() operation
- """
- return self.Aggregate(GroupingAttributes, Relation.AggregationOperation_Average)
- def Aggregate_Min(self, GroupingAttributes):
- """
- Aggregate Relation using min() operation
- """
- return self.Aggregate(GroupingAttributes, Relation.AggregationOperation_Min)
- def Aggregate_Max(self, GroupingAttributes):
- """
- Aggregate Relation using max() operation
- """
- return self.Aggregate(GroupingAttributes, Relation.AggregationOperation_Max)
- def GetProportions(self, GroupingAttributes):
- """
- Get proportions (i.e. ratios) grouped by given summary attributes
- """
- return self.DivideRelation(self.Aggregate_Sum(GroupingAttributes))
- def GetSubRelation(self, AttributeFields, AttributeValues):
- """
- Return a sub-Relation with selected rows; i.e. rows with
- selected attribute values.
- """
- SelectedFields = set(AttributeFields)
- RelationFields = self.AttributeFields
- if not set(RelationFields).issuperset(SelectedFields):
- raise Exception('Given attribute fields must be a subset of relation fields!')
- if len(SelectedFields) != len(AttributeFields):
- raise Exception('All attribute fields in array AttributeFields must be unique!')
- if len(AttributeFields) != len(AttributeValues):
- raise Exception('Lengths of arrays AttributeFields and AttributeValues must be identical!')
- Rout = Relation(RelationFields)
- for AttrValComb in self.KeyFigureDic.keys():
- IfMatch = True
- for field in AttributeFields:
- if AttributeValues[AttributeFields.index(field)] != AttrValComb[RelationFields.index(field)]:
- IfMatch = False
- break;
- if IfMatch:
- val = self.__KeyFigureDic[AttrValComb]
- Rout.InsertRow(val, RelationFields, AttrValComb)
- # return resultant Relation
- return Rout
- @classmethod
- def ListOperation_Reverse(cls, ValueList):
- # see: http://stackoverflow.com/questions/2612802/how-to-clone-or-copy-a-list-in-python
- L = ValueList[:] # clone
- L.reverse()
- return L;
- def ApplyListOperationToSubRelation(self, GroupingAttributes, ListOperation):
- """
- Apply given list-to-list operation to sub-Relations grouped by given attributes.
- ListOperation must return a list whose length is identical to the length of input list.
- """
- SummaryAttribs = set(GroupingAttributes)
- if not set(self.AttributeFields).issuperset(SummaryAttribs):
- raise Exception("Given Attributes must be a subset of the attributes of Relation!")
- SummaryAttribList = list(SummaryAttribs)
- Rout = self.Clone()
- GroupedValues = {}
- CorrAttribValues = {} # attribute value combinations corresponding to values in the list
- # find indices of grouping attributes
- ind= []
- for field in SummaryAttribList:
- ind.append(self.AttributeFields.index(field))
- # group values in separate lists
- for attr in self.KeyFigureDic.keys():
- val = self.KeyFigureDic[attr]
- SummaryAttribVals = []
- for i in ind:
- SummaryAttribVals.append(attr[i])
- sav = tuple(SummaryAttribVals)
- if GroupedValues.has_key(sav):
- GroupedValues[sav].append(val)
- CorrAttribValues[sav].append(attr)
- else:
- GroupedValues[sav] = [val]
- CorrAttribValues[sav] = [attr]
- # apply aggregation operation on grouped lists of values
- for sav in GroupedValues.keys():
- ValueListIn = GroupedValues[sav]
- ValueListOut = ListOperation(ValueListIn)
- AttribValues = CorrAttribValues[sav]
- for i in range(0, len(ValueListOut)):
- Rout.__KeyFigureDic[AttribValues[i]] = ValueListOut[i]
- # return resultant Relation
- return Rout
- def __str__(self):
- """
- Convert Relation to printable string.
- A key figure can be a scalar number, or a list of scalar numbers.
- """
- dstr = ''
- if type(self.__KeyFigureDic[self.__KeyFigureDic.keys()[0]]) == type([1,2]): # type == list
- dstr = '\nValueList ' + str(tuple(self.AttributeFields)) + '\n'
- for AttribVals in self.__KeyFigureDic.keys():
- ValueList = self.__KeyFigureDic[AttribVals]
- dstr += str([round(x,2) for x in ValueList]) + ' ' + str(AttribVals) + '\n'
- elif self.IsNumber(self.__KeyFigureDic[self.__KeyFigureDic.keys()[0]]): # type == scalar number
- dstr = '\n{:>10}'.format('Value') + ' '
- for Attrib in self.AttributeFields:
- dstr += '{:^11}'.format(Attrib)
- dstr += '\n'
- DisplayWith = (12 * (1 + len(self.AttributeFields)))
- dstr += ('{:-^' + str(DisplayWith) + '}').format('') + '\n'
- for AttribVals in self.__KeyFigureDic.keys():
- val = self.__KeyFigureDic[AttribVals]
- dstr += '{:>10.2f}'.format(val) + ' ('
- for i in range(0,len(AttribVals)):
- if i < len(AttribVals) -1:
- dstr += '{:^10}'.format(AttribVals[i]) + ','
- else:
- dstr += '{:^10}'.format(AttribVals[i]) + ')'
- dstr += '\n'
- else:
- raise Exception('Unexpected value type!')
- # return resultant string
- return dstr
- def ExportToExcel(self, SaveFileAs = 'dummy.xlsx', KeyFigureFields = None):
- """
- Export Relation to a new MS Excel file.
- A key figure can be a scalar number, or a list of scalar numbers.
- """
- # see: https://automatetheboringstuff.com/chapter12/
- from openpyxl import Workbook
- from openpyxl.cell import get_column_letter
- wb = Workbook()
- wb.visible = True
- ws = wb.active
- # key figure type == list
- if type(self.__KeyFigureDic[self.__KeyFigureDic.keys()[0]]) == type([1,2]):
- ListLength = len(self.__KeyFigureDic[self.__KeyFigureDic.keys()[0]])
- # field names
- if KeyFigureFields == None:
- for i in range(1, ListLength+1):
- CellAddr = get_column_letter(i) + str(1)
- ws[CellAddr] = 'Value-' + str(i)
- elif len(KeyFigureFields) == ListLength:
- for i in range(1, ListLength+1):
- CellAddr = get_column_letter(i) + str(1)
- ws[CellAddr] = KeyFigureFields[i-1]
- else:
- raise Exception('Number of fields in KeyFigureFields must be equal to the length of value list!')
- for i in range(1, len(self.__Attributes) + 1):
- CellAddr = get_column_letter(i+ListLength) + str(1)
- ws[CellAddr] = self.__Attributes[i-1]
- # field values
- RowCount = 2
- for AttribVals in self.__KeyFigureDic.keys():
- ValueList = self.__KeyFigureDic[AttribVals]
- for i in range(1, ListLength+1):
- CellAddr = get_column_letter(i) + str(RowCount)
- ws[CellAddr] = ValueList[i-1]
- for i in range(1, len(AttribVals)+1):
- CellAddr = get_column_letter(i+ListLength) + str(RowCount)
- ws[CellAddr] = AttribVals[i-1]
- RowCount +=1
- # key figure type == scalar value
- elif self.IsNumber(self.__KeyFigureDic[self.__KeyFigureDic.keys()[0]]):
- ListLength = 1
- # field names
- if KeyFigureFields == None:
- ws['A1'] = 'Value'
- elif len(KeyFigureFields) == ListLength:
- ws['A1'] = KeyFigureFields[0]
- else:
- raise Exception('Number of fields in KeyFigureFields must be equal to the length of value list!')
- for i in range(1, len(self.__Attributes) + 1):
- CellAddr = get_column_letter(i+ListLength) + str(1)
- ws[CellAddr] = self.__Attributes[i-1]
- # field values
- RowCount = 2
- for AttribVals in self.__KeyFigureDic.keys():
- Value = self.__KeyFigureDic[AttribVals]
- CellAddr = get_column_letter(1) + str(RowCount)
- ws[CellAddr] = Value
- for i in range(1, len(AttribVals)+1):
- CellAddr = get_column_letter(i+ListLength) + str(RowCount)
- ws[CellAddr] = AttribVals[i-1]
- RowCount +=1
- else:
- raise Exception('Unexpected key figure value type!')
- # save excel workbook
- wb.save(filename = SaveFileAs)
- def KeyFiguresToAttribute(self, AttributeName, AttributeValues = None):
- """
- Convert a Relation with multiple key figures (list values) to a Relation
- with a single scalar value, by adding a new attribute.
- """
- FirstValue = self.__KeyFigureDic[self.__KeyFigureDic.keys()[0]]
- if type(FirstValue) != type([1,2]):
- raise Exception("Relation doesn't have a list of values!")
- if AttributeValues != None and len(FirstValue) != len(AttributeValues):
- raise Exception("Length of Relation's ValueList must be equal to length of AttributeValues!")
- AttribFields = self.__Attributes[:]
- AttribFields.append(AttributeName)
- Rout = Relation(AttribFields)
- for AttribComb in self.__KeyFigureDic.keys():
- ValueList = self.__KeyFigureDic[AttribComb]
- for i in range(0, len(ValueList)):
- if AttributeValues == None:
- AttribCombExt = AttribComb + tuple([i+1])
- else:
- AttribCombExt = AttribComb + tuple(AttributeValues[i])
- Rout.InsertOrderedRow(ValueList[i], AttribCombExt)
- # return resultant Relation
- return Rout
- # test module
- if __name__ == '__main__':
- # initiate a Relation
- r1 = Relation(['year','country','product'])
- # add rows to Relation
- r1.InsertRow(35,['year','country','product'],(1999,'Germany','Car'))
- r1.InsertRow(65,['country','year','product'],('Turkey', 2009,'Olive'))
- r1.InsertRow(900,['year','country','product'],(1999,'Germany','Train'))
- r1.InsertRow(75,['country','year','product'],('Turkey', 2015,'Olive'))
- r1.InsertRow(105,['country','year','product'],('Turkey', 2005,'Pistache'))
- # not matching attribute names
- try:
- r1.InsertRow(65,['region','year','product'],('North Africa', 2009, 'Car'))
- except Exception, e:
- print str(e)
- # already existing attribute-value set
- try:
- r1.InsertRow(65,['region','year','product'],('Turkey', 2009, 'Olive'))
- except Exception, e:
- print str(e)
- print "r1 after inserting rows: " + str(r1)
- # set key figure values in Relation
- r1.SetKeyFigure(44,['year','country','product'],(1999,'Germany','Car'))
- r1.SetKeyFigure(88,['country','year','product'],('Turkey', 2009,'Olive'))
- print "r1 after setting key figures:"
- print r1
- # attribute-value combination does not exist in the Relation (2008)
- try:
- r1.SetKeyFigure(99,['country','year','product'],('Turkey', 2008,'Olive'))
- except Exception, e:
- print str(e)
- print "r1 after setting key figure values: " + str(r1)
- # clone relation
- r = r1.Clone()
- print "r = r1.Clone(): " + str(r.KeyFigureDic)
- # get value
- print "r.GetKeyFigure(['year','country','product'],(1999,'Germany','Car')): " + str(r.GetKeyFigure(['year','country','product'],(1999,'Germany','Car')))
- print "r.GetKeyFigure(['country', 'year','product'],('Germany', 1999,'Car')): " + str(r.GetKeyFigure(['country','year','product'],('Germany',1999,'Car')))
- # ordered attribute calues
- print "\nGet key figure value with ordered attribute values:"
- print "r.GetKeyFigureOrd((1999,'Germany','Car')): " + str(r.GetKeyFigureOrd((1999,'Germany','Car')))
- # get key figure value with index (with ordered attribute values)
- print "r[(1999,'Germany','Car')]: " + str(r[(1999,'Germany','Car')])
- # set key figure value with index (with ordered attribute values)
- print "r[(1999,'Germany','Car')] = 9999 : "
- r[(1999,'Germany','Car')] = 9999
- print r
- # arithmetical relation operations
- r2 = Relation(['year','country'])
- r2.InsertRow(2.5,['year','country'], [2009,'Turkey'])
- r2.InsertRow(0.5,['year','country'], [1999,'Germany'])
- print "r = RelationArithmetic(r1, r2,'add'):"
- r = Relation.RelationArithmetic(r1, r2, Relation.BinaryOperation_Addition)
- print r.KeyFigureDic
- # relation addition
- print "r = r1 + r2:"
- r = r1 + r2
- print r
- # relation multiplication
- print "r = r1 * r2:"
- r = r1 * r2
- print r
- # relation subtraction
- print "r = r1 - r2:"
- r = r1 - r2
- print r
- # relation division
- print "r = r1 / r2:"
- r = r1 / r2
- print r
- # arithmetic scalar operations
- print "r2 = r1.AddScalar(99.999)"
- print "r2 before addition:"
- print r2
- r2 = r1.AddScalar(99.999)
- print "r2 after addition:"
- print r2
- print "100 + r2:"
- r2 = 100 + r2
- print r2
- print "0.1 + r2:"
- r2 = 0.1 * r2
- print r2
- print "Complex Relation multiplication:"
- r3 = Relation(['year','country','quarter'])
- r3.InsertOrderedRow(0.2, (1999,'Germany','Q1'))
- r3.InsertOrderedRow(0.5, (1999,'Germany','Q2'))
- r3.InsertOrderedRow(0.4, (2005,'Germany','Q1'))
- r3.InsertOrderedRow(0.4, (2005,'Germany','Q3'))
- r3.InsertOrderedRow(0.1, (1999,'France','Q2'))
- r3.InsertOrderedRow(0.2, (1999,'France','Q3'))
- r3.InsertOrderedRow(0.3, (1999,'France','Q4'))
- print "r3:\n" + str(r3)
- print "r = r1 x r3"
- r = r1 * r3
- print r
- r4 = Relation(['months','quarter'])
- r4.InsertOrderedRow(0.1, ('1-3','Q1'))
- r4.InsertOrderedRow(0.2, ('4-6','Q2'))
- r4.InsertOrderedRow(0.3, ('7-9','Q3'))
- r4.InsertOrderedRow(0.4, ('10-12','Q4'))
- r5 = r1 * r4
- print "r5 = r1 * r4:\n" + str(r5)
- # aggregate relation
- print "r = r5.Aggregate_Sum(['country'])"
- r = r5.Aggregate_Sum(['country'])
- print r
- print "r = r5.Aggregate_Max(['country'])"
- r = r5.Aggregate_Max(['country'])
- print r
- print "r = r5.Aggregate_Min(['country'])"
- r = r5.Aggregate_Min(['country'])
- print r
- print "r6 = r5.Aggregate_Average(['country','product'])"
- r6 = r5.Aggregate_Average(['country','product'])
- print r6
- # get proportions
- print "r = r6.GetProportions(['country','product'])"
- r = r6.GetProportions(['country','product'])
- print r
- print "r = r5.GetProportions(['country','product'])"
- r = r5.GetProportions(['country','product'])
- print r
- # get sub-Relation
- print "r5.GetSubRelation(['product', 'country'], ['Car','Germany']) :"
- print r5.GetSubRelation(['product', 'country'], ['Car','Germany'])
- # apply list operation on sub-relations
- def ExchangeMinMaxValues(ValueList):
- L = ValueList[:] # clone list
- maxVal = max(L)
- minVal = min(L)
- IndMax = L.index(maxVal)
- IndMin = L.index(minVal)
- L[IndMin] = maxVal
- L[IndMax] = minVal
- return L
- l = [1,4,2,8,5]
- print "l = " + str(l)
- print "ExchangeMinMaxValues(l) = " + str(ExchangeMinMaxValues(l))
- print "r5:"
- print r5
- print "r5.ApplyListOperationToSubRelation(['country','product'], ExchangeMinMaxValues):"
- print r5.ApplyListOperationToSubRelation(['country','product'], ExchangeMinMaxValues)
- print "r5:"
- print r5
- print "Export Relation to Excel"
- print "r5.ExportToExcel()"
- r5.ExportToExcel(SaveFileAs = 'dummy2.xlsx', KeyFigureFields=['Measure'])
- GeSHi ©