Finaquant Community Forum

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 ©