-
Notifications
You must be signed in to change notification settings - Fork 0
/
Copy pathXLKitLearn.py
4734 lines (3834 loc) · 253 KB
/
XLKitLearn.py
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
214
215
216
217
218
219
220
221
222
223
224
225
226
227
228
229
230
231
232
233
234
235
236
237
238
239
240
241
242
243
244
245
246
247
248
249
250
251
252
253
254
255
256
257
258
259
260
261
262
263
264
265
266
267
268
269
270
271
272
273
274
275
276
277
278
279
280
281
282
283
284
285
286
287
288
289
290
291
292
293
294
295
296
297
298
299
300
301
302
303
304
305
306
307
308
309
310
311
312
313
314
315
316
317
318
319
320
321
322
323
324
325
326
327
328
329
330
331
332
333
334
335
336
337
338
339
340
341
342
343
344
345
346
347
348
349
350
351
352
353
354
355
356
357
358
359
360
361
362
363
364
365
366
367
368
369
370
371
372
373
374
375
376
377
378
379
380
381
382
383
384
385
386
387
388
389
390
391
392
393
394
395
396
397
398
399
400
401
402
403
404
405
406
407
408
409
410
411
412
413
414
415
416
417
418
419
420
421
422
423
424
425
426
427
428
429
430
431
432
433
434
435
436
437
438
439
440
441
442
443
444
445
446
447
448
449
450
451
452
453
454
455
456
457
458
459
460
461
462
463
464
465
466
467
468
469
470
471
472
473
474
475
476
477
478
479
480
481
482
483
484
485
486
487
488
489
490
491
492
493
494
495
496
497
498
499
500
501
502
503
504
505
506
507
508
509
510
511
512
513
514
515
516
517
518
519
520
521
522
523
524
525
526
527
528
529
530
531
532
533
534
535
536
537
538
539
540
541
542
543
544
545
546
547
548
549
550
551
552
553
554
555
556
557
558
559
560
561
562
563
564
565
566
567
568
569
570
571
572
573
574
575
576
577
578
579
580
581
582
583
584
585
586
587
588
589
590
591
592
593
594
595
596
597
598
599
600
601
602
603
604
605
606
607
608
609
610
611
612
613
614
615
616
617
618
619
620
621
622
623
624
625
626
627
628
629
630
631
632
633
634
635
636
637
638
639
640
641
642
643
644
645
646
647
648
649
650
651
652
653
654
655
656
657
658
659
660
661
662
663
664
665
666
667
668
669
670
671
672
673
674
675
676
677
678
679
680
681
682
683
684
685
686
687
688
689
690
691
692
693
694
695
696
697
698
699
700
701
702
703
704
705
706
707
708
709
710
711
712
713
714
715
716
717
718
719
720
721
722
723
724
725
726
727
728
729
730
731
732
733
734
735
736
737
738
739
740
741
742
743
744
745
746
747
748
749
750
751
752
753
754
755
756
757
758
759
760
761
762
763
764
765
766
767
768
769
770
771
772
773
774
775
776
777
778
779
780
781
782
783
784
785
786
787
788
789
790
791
792
793
794
795
796
797
798
799
800
801
802
803
804
805
806
807
808
809
810
811
812
813
814
815
816
817
818
819
820
821
822
823
824
825
826
827
828
829
830
831
832
833
834
835
836
837
838
839
840
841
842
843
844
845
846
847
848
849
850
851
852
853
854
855
856
857
858
859
860
861
862
863
864
865
866
867
868
869
870
871
872
873
874
875
876
877
878
879
880
881
882
883
884
885
886
887
888
889
890
891
892
893
894
895
896
897
898
899
900
901
902
903
904
905
906
907
908
909
910
911
912
913
914
915
916
917
918
919
920
921
922
923
924
925
926
927
928
929
930
931
932
933
934
935
936
937
938
939
940
941
942
943
944
945
946
947
948
949
950
951
952
953
954
955
956
957
958
959
960
961
962
963
964
965
966
967
968
969
970
971
972
973
974
975
976
977
978
979
980
981
982
983
984
985
986
987
988
989
990
991
992
993
994
995
996
997
998
999
1000
##################################
# XLKitLearn #
# (C) Daniel Guetta, 2024 #
##################################
ADDIN_VERSION = '13.04'
EARLIEST_ALLOWABLE_VERSION = '13.04'
# Note that the seventh line in this file should contain the version number
# in the format ADDIN_VERSION = 'XX'. The eigth line should contain the earliest
# allowable version that won't result in a warning
# =====================
# = Load Packages =
# =====================
# Interaction with Excel
import xlwings as xw
# Basic packages
import pandas as pd
import numpy as np
import scipy as sp
import itertools
import time
import warnings
import keyword
import os
import signal
from collections import OrderedDict
import traceback
import sys
# Data preparation utilities
import patsy as pt
import sklearn.feature_extraction.text as f_e
import nltk
# Sparse matrices
from scipy import sparse
# Estimators, learners, etc...
warnings.filterwarnings("ignore", category = DeprecationWarning)
from sklearn.linear_model import LinearRegression, LogisticRegression, Lasso
from sklearn.neighbors import KNeighborsRegressor, KNeighborsClassifier
from sklearn.tree import DecisionTreeClassifier, DecisionTreeRegressor
from sklearn.decomposition import LatentDirichletAllocation
from sklearn.ensemble import RandomForestClassifier, RandomForestRegressor
from sklearn.ensemble import GradientBoostingClassifier, GradientBoostingRegressor
# Statsmodels learners for p-values
import statsmodels.api as sm
# Validation utilities and metrics
from sklearn import model_selection as sk_ms
from sklearn.metrics import r2_score, roc_auc_score, roc_curve, make_scorer
import sklearn.inspection as sk_i
# Plotting (ensure we don't use qt)
import matplotlib as mpl
mpl.use('Agg')
import matplotlib.pyplot as plt
import seaborn as sns
import json
# Verification
from datetime import datetime, timedelta
import hashlib
import requests
# =================
# = Utilities =
# =================
class AddinError(Exception):
pass
def trim_edges(x):
'''
This function returns the input string without its first and last character
'''
return x[1:-1]
def remove_dupes(x):
'''
This function removes duplicates from the list passed to it while preserving
the order of elements
'''
seen = set()
return [i for i in x if not (i in seen or seen.add(i))]
def listify(*args):
'''
For each argument, if it is a list, it will be returned as-is. If not, a single-
item list containing that item will be returned.
'''
out = tuple(i if type(i) is list else [i] for i in args)
if len(out) == 1:
return out[0]
else:
return out
def round_to_string(x,n):
'''
This function will take a number x. If it is an integer, it will convert it
as a string. If not, it will round it to n decimal places and convert it to
a string.
'''
if x == int(x):
return str(int(x))
else:
return str(round(x,n))
def wrap_line(text, max_width):
'''
This function will take a long line of text and wrap it to be no
wider than max_width
'''
text = [i.split(' ') for i in text.split('\n')]
out = []
for cur_row in text:
buffer = ''
for cur_word in cur_row:
if len(buffer + ' ' + cur_word) > max_width:
out.append(buffer)
buffer = ''
buffer = buffer + ' ' + cur_word
out.append(buffer)
return '\n'.join(out)
def pad_string(string, size, sep):
'''
This function will take a string, and pad it with the sep character to make
it size-long
'''
return string + ' ' + (sep*(size-len(string))) + ' '
class D(OrderedDict):
'''
This class extends the standard Python dictionary class in the following ways
1. It allows elements to be accessed and set as attributes:
- d.element will be equivalent to d.get('element', default=None)
- d.element=value will be equivalent to d['element']=value if the
dictionary has an alement called 'element'. If not, an
AttributeError is thrown
2. It allows each entry to also have an element called english_key, which
contains a more verbose version of the key (in English, for example).
This can be used in two ways
- d.key(x) returns the key for the element with english_name x. If
x does not exist, or multiple elements have english_name x, an
AttributeError is thrown
- d.english_keys() returns a list of all English keys for the
dictionary - it is the equivalent of d.key()
3. It provides an iterator, d.zip_entries() that allows us to iterate through
(key, value) tuples
'''
def __getattr__(self, item):
if item in self:
return self[item]
raise AttributeError(item)
def __setattr__(self, key, value):
if key in self:
self[key] = value
return
raise AttributeError(key)
def zip_entries(self):
'''
Iterates through (key, value) tuples in the dictionary
'''
return zip(self.keys(), self.values())
def key(self, english_key):
'''
Given an english_key, this function will return the corresponding key
'''
matching_keys = [i for i in self if self[i].english_key == english_key]
if len(matching_keys) == 1:
return matching_keys[0]
raise AttributeError(key)
def english_keys(self):
'''
Will return a list of English keys in this dictionary
'''
return [self[i].english_key for i in self if 'english_key' in self[i]]
def levenshtein_ratio_and_distance(s, t, ratio_calc = False):
""" levenshtein_ratio_and_distance:
Calculates levenshtein distance between two strings.
If ratio_calc = True, the function computes the
levenshtein distance ratio of similarity between two strings
For all i and j, distance[i,j] will contain the Levenshtein
distance between the first i characters of s and the
first j characters of t
"""
# From https://www.datacamp.com/community/tutorials/fuzzy-string-python
if len(s) == 0: return len(t)
if len(t) == 0: return len(s)
# Initialize matrix of zeros
rows = len(s)+1
cols = len(t)+1
distance = np.zeros((rows,cols),dtype = int)
# Populate matrix of zeros with the indeces of each character of both strings
for i in range(1, rows):
for k in range(1,cols):
distance[i][0] = i
distance[0][k] = k
# Iterate over the matrix to compute the cost of deletions,insertions and/or substitutions
for col in range(1, cols):
for row in range(1, rows):
if s[row-1] == t[col-1]:
cost = 0 # If the characters are the same in the two strings in a given position [i,j] then the cost is 0
else:
# In order to align the results with those of the Python Levenshtein package, if we choose to calculate the ratio
# the cost of a substitution is 2. If we calculate just distance, then the cost of a substitution is 1.
if ratio_calc == True:
cost = 2
else:
cost = 1
distance[row][col] = min(distance[row-1][col] + 1, # Cost of deletions
distance[row][col-1] + 1, # Cost of insertions
distance[row-1][col-1] + cost) # Cost of substitutions
if ratio_calc == True:
# Computation of the Levenshtein Distance Ratio
Ratio = ((len(s)+len(t)) - distance[row][col]) / (len(s)+len(t))
return Ratio
else:
# print(distance) # Uncomment if you want to see the matrix showing how the algorithm computes the cost of deletions,
# insertions and/or substitutions
# This is the minimum number of edits needed to convert string a to string b
return distance[row][col]
# ============================
# = Set global constants =
# ============================
EXCEL_INTERFACE = D( interface_sheet = 'Add-in',
settings_dict_comma = '`',
settings_dict_colon = '|',
list_splitter = '&',
email_cell = 'F17',
graph_line_per_inch = 3,
output_width = 116,
output_code_width = 82,
max_table_rows = 8000,
variable_importance_limit = 100)
PREDICTIVE_CONFIG = D( settings_cell = 'D9',
status_cell = 'F7',
english_key = 'predictive_addin',
expected_settings = D( model = D(english_key='Model name'),
formula = D(english_key='Formula'),
param1 = D(default=''),
param2 = D(default=''),
param3 = D(default=''),
training_data = D(english_key='Training data', kind='r'),
K = D(english_key='K', kind='i', default=None),
ts_data = D(english_key='Is data time series data?', kind='b'),
evaluation_perc = D(english_key='Size of evaluation set', kind='i', default=None),
evaluation_data = D(english_key='Evaluation data', kind='r', default=None),
prediction_data = D(english_key='Prediction data', kind='r', default=None),
seed = D(english_key='Seed', kind='i', default=123),
output_model = D(english_key='Output the model?', kind='b'),
output_evaluation_details = D(english_key='Output evaluation details?', kind='b'),
output_code = D(english_key='Output code?', kind='b') ) )
TEXT_CONFIG = D( settings_cell = 'D14',
status_cell = 'F12',
english_key = 'text_addin',
expected_settings = D( source_data = D(english_key='Source data'),
max_df = D(english_key='Upper limiting frequency', default=1.0, kind='f'),
min_df = D(english_key='Lower limiting frequency', default=1, kind='f'),
max_features = D(english_key='Maximum words', kind='i'),
stop_words = D(english_key='Remove stop words?', kind='b'),
tf_idf = D(english_key='TF-IDF?', kind='b'),
lda_topics = D(english_key='Number of LDA topics', default=None, kind='i'),
seed = D(english_key='Seed', kind='i'),
eval_perc = D(english_key='Evaluation percentage', default=0, kind='i'),
bigrams = D(english_key='Include bigrams?', kind='b'),
stem = D(english_key='Stem words?', kind='b'),
wordtovec = D(english_key='Use Word2Vec?', kind='b'),
output_code = D(english_key='Output code?', kind='b'),
sparse_output = D(english_key='Sparse output?', kind='b'),
max_lda_iter = D(english_key='LDA iterations', default=10, kind='i') ) )
MAX_LR_ITERS = 500
LINEAR_REGRESSION = 'lr'
NEAREST_NEIGHBORS = 'knn'
DECISION_TREE = 'dt'
BOOSTED_DT = 'bdt'
RANDOM_FOREST = 'rf'
MODELS = D( {LINEAR_REGRESSION : D( english_key = 'Linear/logistic regression',
params = D(param1 = D(english_key='Lasso penalty',
sklearn_name='alpha',
kind='f',
list_default=0))),
NEAREST_NEIGHBORS : D( english_key = 'K-Nearest Neighbors',
params = D(param1 = D(english_key='Neighbors',
sklearn_name='n_neighbors',
kind='i',
list_default=0),
param2 = D(english_key='Weighting',
sklearn_name='weights',
kind='s',
list_default="uniform"))),
DECISION_TREE : D( english_key = 'Decision tree',
params = D(param1 = D(english_key='Tree depth',
sklearn_name='max_depth',
kind='i')),
import_logic = D(subpackage='tree', class_name = 'DecisionTree')),
BOOSTED_DT : D( english_key = 'Boosted decision tree',
params = D(param1 = D(english_key='Tree depth',
sklearn_name='max_depth',
kind='i'),
param2 = D(english_key='Max trees',
sklearn_name='n_estimators',
kind='i'),
param3 = D(english_key='Learning rate',
sklearn_name='learning_rate',
kind='f',
list_default=0.1)),
import_logic = D(subpackage='ensemble', class_name = 'GradientBoosting') ),
RANDOM_FOREST : D( english_key = 'Random forest',
params = D(param1 = D(english_key='Tree depth',
sklearn_name='max_depth',
kind='i'),
param2 = D(english_key='Number of trees',
sklearn_name='n_estimators',
kind='i')),
import_logic = D(subpackage='ensemble', class_name = 'RandomForest'))})
# ========================
# = Excel Connectors =
# ========================
class ExcelConnector:
'''
This class mediates the connection to Excel via xlwings. It exposes a single
attribute, the xlwings workbook object, called wb.
It should be initiated with a single parameter (workbook). If None or omitted,
xlwings.Book.caller() will be used. If not, it will connect to workbook
'''
def __init__(self, workbook=None):
if workbook is None:
self._wb = xw.Book.caller()
else:
self._wb = xw.Book(workbook)
# Expose the wb_var macro
self.wb_var = self._wb.macro("wb_var")
@property
def wb(self):
return self._wb
class ExcelOutput:
'''
This class handles the output of data to the Excel spreadsheet.
'''
def __init__(self, sheet, excel_connector):
# Prepare a variable to contain our output
self._out = []
# Prepare a variable to contain formatting
# output
self._formatting_fields = ["font_medium", "font_large", "bottom_thick",
"top_thin", "italics", "bold", "align_center",
"align_right", "expand", "courier", "align_left", "number_format"]
self._formatting_data = {i:[] for i in self._formatting_fields}
# Prepare graph formatting data and start with a graph number of 0
self._graph_formatting = []
self._graph_number = 0
self._graphs = {}
# Prepare a variable to hold the current indentation level in the output
self._cur_indent = 0
# Store the sheet name, Excel interface, and formatting macro for
# post-processing
self._excel_connector = excel_connector
self._wb = excel_connector.wb
self._sheet = self._wb.sheets(sheet)
self._format_sheet = self._wb.macro("format_sheet")
@staticmethod
def _col_letter(col_num):
'''
This function takes a numeric column number, and returns the
letter corresponding to that excel column (eg 1 = A, 2 = B, etc...)
'''
if col_num <= 26:
return chr(64 + col_num)
elif col_num <= 27*26:
first_letter = chr( 64 + int(np.floor( (col_num - 1)/26)) )
return first_letter + ExcelOutput._col_letter( ( (col_num-1) % 26) + 1 )
else:
first_letter = chr( 64 + int(np.floor( (col_num-1) /(26*26))) )
return first_letter + ExcelOutput._col_letter( (col_num-1) % (26*26) + 1 )
def _determine_indent(self, indent_level):
'''
Various functions in this class require an indent as an argument, which can
be provided in one of three forms
- If a blank argument is provided, the current indentation level is used
- If an integer is provided, that integer is used as the indentation level
- If a string is provided, that string is converted to an integer and added
to the indentation level
This function takes this argument and returns the indentation level
'''
if indent_level == "":
return self._cur_indent
elif isinstance(indent_level, int):
return indent_level
else:
return self._cur_indent + int(indent_level)
def add_header(self, text, level, content = ""):
'''
This function will create a new header in the Excel spreadsheet, and increment
the indent level
It takes the following argument
- text : the text of the header
- level : the format of the header; 0 for title, 1 for subheading, and 2
for a sub-sub-heading with content
- content : when level = 2, the content of the subheading
'''
if level == 0:
self.add_row( [text], [ ["font_large", "bold"] ], indent_level = 0 )
self.add_blank_row()
self._cur_indent = 1
elif level == 1:
self.add_row( [text], [ ["font_medium", "bold"] ], indent_level = 1 )
self.add_blank_row()
self._cur_indent = 2
elif level == 2:
self.add_row( [ f"{text}{':' if text != '' else ''}", str(content) ],
[ ["bold", "align_right"], "align_left" ],
indent_level = self._cur_indent )
else:
raise
def add_blank_row(self):
'''
Add a blank row to the output
'''
self.add_row( [ '' ] )
def add_row(self, content, format = [], indent_level = "", split_newlines = False):
'''
This function will add a single row to our Excel output. It takes the
following arguments
- content : EITHER a single string, to be output to a single cell
OR a list, containing a row to be output
- format : The format of the output.
EITHER a single string, containing formatting that will be
applied to every cell output
OR a list of formatting instructions. The first element will
be applied to the first column, the second to the second,
etc... Each of these instructions can either be lists, for
multiple formatting instructions, or strings
Any instruction can be None or ''
- indent_level: the indentation level. If it is an integer, that integer
is used as an indentation level. If it is a string, the
number in that string is added to the current indentation
level (self.cur_indent)
- split_newlines: If True, the function will check whether any element
in content contains a newline. If it does, it will
split those and print them in multiple Excel cells.
If this happens, each of the resulting rows will have the
same format
Note that elements in the format list are always assumed to refer to different
columns. So for example, if
content = 'hello'
and
format = ['bold', 'align_right', 'bold']
The function will interpret these three elements are referring to three columns,
and only 'bold' will be applied to the output.
'''
# If the content are a single string, stick them in a list
content = listify(content)
# If the format is a single string, apply it to every column
if type(format) is not list:
format = [format]*len(content)
# Find the indentation level
indent_level = self._determine_indent(indent_level)
if split_newlines:
# Split the columns
content = [i.split('\n') for i in content]
# Transpose the results
n_rows = max([len(i) for i in content])
content = [[col[row] if len(col) > row else ''
for col in content] for row in range(n_rows)]
self.add_rows(content, format, indent_level)
else:
# Add the data, with the appropriate indent level
self._out.append([""]*indent_level + content)
# Handle formatting; find the cell reference for each cell, and appending it
# to the formatting dictionary
for col, col_format in enumerate(format):
cell_reference = self._col_letter(indent_level + col + 1) + str(len(self._out))
for cur_format in filter(None, listify(col_format)):
self._formatting_data[cur_format].append(cell_reference)
def add_rows(self, content, format = [], indent_level = ""):
'''
This function will add a multiple rows to our Excel output. Each row must
have the same format.
It takes the following arguments
- content : a two-dimensional list; each element is a list corresponding
to one row, with each element corresponding to one column
- format: a list containing as many entries as columns in content. Each
element in the list can either be
- A string, for a single formatting instruction for that column
- A list, for multiple formatting instructions for that column
- None or '', for no formatting instructions
- indent_level : the indentation level. If it is an integer, that integer
is used as an indentation level. If it is a string, the
number in that string is added to the current indentation
level (self.cur_indent)
The benefit of using this function instead of multiple add_row calls is that
the formatting instructions format each of the columns as one - this can
result in more efficient macro post-processing in Excel
'''
# Find the indentation level
indent_level = self._determine_indent(indent_level)
# Add the content
self._out.extend([[""]*indent_level + i for i in content])
# Push the format
first_row = len(self._out) - len(content) + 1
last_row = len(self._out)
for col, col_format in enumerate(format):
col_letter = self._col_letter(indent_level + col + 1)
col_range = f'{col_letter}{first_row}:{col_letter}{last_row}'
for cur_format in filter(None, listify(col_format)):
self._formatting_data[cur_format].append(col_range)
def add_table(self, df, three_dp = True, indent_level = "", alt_message=''):
'''
This function will output a Pandas table to Excel, formatting it in a
standard format
It takes the following arguments
- df : the dataframe in question
- three_dp : whether the table should be formatted to three decimal places.
If not, the numbers will be output as-is. Options are
- True : whole table formatting to 3dp
- False : none of the table
- -1 : only the last column to 3 dp
- indent_level : the indentation level. If it is an integer, that integer
is used as an indentation level. If it is a string, the
number in that string is added to the current indentation
level (self.cur_indent)
'''
if len(df) > EXCEL_INTERFACE.max_table_rows:
file_path = os.path.abspath(os.path.join(self._wb.fullname,os.path.pardir))
delim = '/' if '/' in file_path else '\\'
file_path = file_path + delim
file_name = 'file_' + str(int(np.random.uniform(0, 99999999))) + '.csv'
self.add_row(f'This table has more than {EXCEL_INTERFACE.max_table_rows} rows. It will '
f'not be printed. I\'ve saved it to a file called {file_name} instead.')
df.to_csv(file_path + file_name, index=False)
self.add_blank_row()
return False
# Find the indentation level
indent_level = self._determine_indent(indent_level)
# Add the data to our output
table_data = ( [ [""]*indent_level + df.columns.tolist() ] +
[ [""]*indent_level + i.tolist() for i in df.values ] )
self._out = self._out + table_data
# Find the top, bottom, left, and right coordinates of our table
bottom_row = len(self._out)
top_row = bottom_row - len(df)
left_col, right_col = self._col_letter(indent_level+1), self._col_letter(indent_level+len(df.columns))
# Expand and center every cell
for f in ['align_center', 'expand']:
self._formatting_data[f].append(f'{left_col}{top_row}:{right_col}{bottom_row}')
# Make the headers bold and add lines above and below
for f in ['bottom_thick', 'top_thin', 'bold']:
self._formatting_data[f].append(f'{left_col}{top_row}:{right_col}{top_row}')
# Add a line to the bottom of the table
self._formatting_data["bottom_thick"].append(f'{left_col}{bottom_row}:{right_col}{bottom_row}')
# Italicize the first column (minus the header)
for f in ["italics", "align_center"]:
self._formatting_data[f].append(f'{left_col}{top_row+1}:{left_col}{bottom_row}')
# Format the body of the table as 3 d.p. numbers if needed. Rather than
# doing each column individually, try and format contiguous ranges all
# at once
start_number_format = None
for col_n, col in enumerate(list(df.columns) + [None]):
try:
if (col is not None) and (df[col].apply(lambda x : len(str(float(x)).split('.')[1]) >= 4).sum() > 0):
if (start_number_format is None): start_number_format = col_n
else:
raise
except:
if start_number_format is not None:
start_col = self._col_letter(indent_level+1+start_number_format)
end_col = self._col_letter(indent_level+1+col_n - 1)
self._formatting_data['number_format'].append(f'{start_col}{top_row+1}:{end_col}{bottom_row}')
start_number_format = None
return True
def add_graph(self, fig, indent_level = "", manual_shift=None):
'''
This function adds a graph to our Excel spreadsheet.
It takes the following arguments
- graph : a matplotlib figure
- indent_level : the indentation level. If it is an integer, that integer
is used as an indentation level. If it is a string, the
number in that string is added to the current indentation
level (self.cur_indent)
'''
# Find the indentation level
indent_level = self._determine_indent(indent_level)
# Get the graph name
self._graph_number += 1
graph_name = f"g_{self._graph_number}"
# Add the graph to the spreadsheet
self._graphs[graph_name] = fig
# Get the figure dimensions
size = fig.get_size_inches()
top_row = len(self._out)+1
if manual_shift is None:
# Skip 5 rows per inch, plus one extra
self._out.extend([[]]*int(np.ceil(size[1]*EXCEL_INTERFACE['graph_line_per_inch'])+1))
# Add the graph's formating data
self._graph_formatting.append(f'{graph_name},{self._col_letter(indent_level+1)}{top_row},{size[0]},{size[1]}')
else:
self._graph_formatting.append(f'{graph_name},{self._col_letter(indent_level+1+manual_shift[0])}{top_row+manual_shift[1]},{size[0]},{size[1]}')
def _get_output_array(self):
'''
This function combines the formatting and data to produce a final output
string to Excel
'''
# Begin by printing out formatting cells in sequence
out = [[",".join(self._formatting_data[i])] for i in self._formatting_fields]
# Add the number of rows and columns
out.extend([[max([len(i) for i in self._out])], [len(self._out)]])
# Add the graph formatting data
out.append(["|".join(self._graph_formatting)])
# Add the remaining output
out.extend(self._out)
# Pad each row with spaces, to ensure each row has the same length
max_cols = max([len(i) for i in out])
out = [i + ['']*(max_cols - len(i)) for i in out]
return out
def _output_to_spreadsheet(self):
'''
This function will output all data in this object to Excel, and format the
data as required.
It returns the number of the last row output to the Excel spreadsheet. This
can be used by the calling class to print out additional information after the
write if needed
'''
# Get the output array
out_array = self._get_output_array()
# If any cells say true or false, add a quote before them
for row_n, row in enumerate(out_array):
for cell_n, cell in enumerate(row):
try:
if 'true' in cell.lower() or 'false' in cell.lower():
out_array[row_n][cell_n] = "'" + out_array[row_n][cell_n]
except:
pass
# Output in chunks of 500 rows; xlwings can die if too much data is
# output at once
cur_row = 1
row_chunk = 500
while cur_row <= len(out_array):
self._sheet.range("A" + str(cur_row)).value = out_array[(cur_row - 1):(cur_row - 1 + row_chunk)]
cur_row = cur_row + row_chunk
# Display the graphs
for graph in self._graphs:
self._sheet.pictures.add(self._graphs[graph], name=graph, update=True)
# Close all graphics we might have opened
plt.close('all')
# Return the number of rows output to the spreadsheet
return len(out_array)
class AddinOutput(ExcelOutput):
'''
This class inherits from ExcelOutput and handles the output of model
results to Excel
In addition to standard output, it will also output various runtime
statistics
'''
def __init__(self, sheet, excel_connector):
# Initialize the parent object
ExcelOutput.__init__(self, sheet, excel_connector)
# Keep track of the start time
self._start_time = time.time()
# Keep track of any events whose time needs to be benchmarked
self._events = []
self._event_times = []
def log_event(self, name):
'''
This function will log an event, and keep track of when it happened
It takes one argument - the name of the event
'''
self._events.append(name)
self._event_times.append(time.time())
def finalize(self, settings_string):
'''
This function will finalize the output of the model to Excel, and append
time profiling statistics
'''
self.add_header("Technical Details", 1)
# Print the settings string
self.add_header("Settings", 2, settings_string)
# Print the version number
self.add_header("Add-in version #", 2, ADDIN_VERSION)
# Add a blank row and print the time profiles
self.add_blank_row()
times = [i-j for i,j in zip(self._event_times, [self._start_time] + self._event_times[:-1])]
for event, t in zip(self._events, times):
self.add_header( event, 2, str(round(t, 2)) )
# Create a row for the write time (we'll modify this below once we've
# written to Excel)
self.add_header( "Write time", 2, 1 )
# Create a row for the overhead time with the sum of all times so far. This
# will be modified by the Excel macro
self.add_header( "Overhead time", 2, self._event_times[-1] - self._start_time )
# Output to the spreadsheet, and save the bottom row
bottom_row = self._output_to_spreadsheet()
# Modify the write time
self._sheet.range(f'D{bottom_row-1}').value = str(round(time.time() - self._event_times[-1], 2))
# Format the sheet
self._format_sheet()
class AddinErrorOutput(ExcelOutput):
'''
This class inherits from ExcelOutput and handles the output of model
errors to Excel
It exposes the following additional function
- add_error: this function add an error to the error report. If the
optional critical argument is True, an error is raised
to return to Excel. Otherwise, it just continues
- add_error_category: adds an header for the category of error
'''
def __init__(self, sheet, excel_connector, source):
# Initialize the parent object
ExcelOutput.__init__(self, sheet, excel_connector)
# Print the title
self.add_header( "Add-in Error Report", 0 )
# Create a flag to log whether we've recorded any error
self._has_error = False
# Create a flag to log whether we've recorded any error since the
# last header. This is so that if we add a new header with no errors
# logged under the previous one, we can print "No errors recorded in
# this section"
self._error_since_last_header = True
# Store the source
self._source = source
def add_error(self, text, critical=False):
'''
Add an error to the report. If critical is True, an error is raised
to return to Excel
'''
bullet = chr(8594)
text = wrap_line(text, EXCEL_INTERFACE.output_width)
self.add_row([bullet, text],
[['align_right'], ['courier']],
indent_level='-1',
split_newlines=True)
self._has_error = True
self._error_since_last_header = True
if critical:
self.finalize()
def add_error_category(self, text):
'''
Add a new error category. If no error was printed since the last category
was pushed, output "No errors recorded in this section"
'''
if not self._error_since_last_header:
self.add_row( ["No error recorded in this section"], ["italics"] )
self.add_blank_row()
self.add_header(text, level = 1)
self._error_since_last_header = False
def finalize(self):
'''
If self.has_error is true, this function outputs the error report to Excel,
and raises a AddinError. If not, it does nothing. This allows us to check - at
various points in our run - whether there is an error, end if there is, and
continue if not.
'''
if self._has_error:
self.add_error_category('')
# Send the error to the server
try:
requests.post(url = 'https://telemetry.xlkitlearn.com/log.php',
json = {'request_type':'error', 'run_id':self._run_id , 'source':self._source ,
'error_type':'caught_exception', 'error_text': str(self._get_output_array()), 'platform':os.name},
timeout = 5 )
except:
pass
# Output to the spreadsheet
self._output_to_spreadsheet()
# Run the formatting macro
self._format_sheet()
raise AddinError
# =======================
# = Addin instances =
# =======================
class AddinInstance:
def __init__(self, excel_connector, out_err, config, udf_server):
self._out_err = out_err
self._excel_connector = excel_connector
self._wb = excel_connector.wb
self._model_sheet = excel_connector.wb.sheets(EXCEL_INTERFACE.interface_sheet)
self._status_cell = config.status_cell
self._settings_cell = config.settings_cell
self._expected_settings = config.expected_settings
self._start_time = time.time()
self._udf_server = udf_server
def log_run(self):
self._v_message = ''
error_tracker = 'A'
fatal_error_message = None
try:
# Get the registered email
try:
reg_email = self._model_sheet.range(EXCEL_INTERFACE.email_cell).value
except:
reg_email = 'unknown'
error_tracker = 'B'
# Get version number
v_number = ADDIN_VERSION
error_tracker = 'C'
# Get run ID
try:
run_id = self._excel_connector.wb_var('run_id')
except:
run_id = 'unknown'
self._out_err._run_id = run_id
error_tracker = 'D'
if reg_email[-1] == '.':
plt.xkcd()
error_tracker = 'E'
# Get the data path for the add-in and the file version
data_path = None
file_version = None
try:
exec_path = os.path.dirname(sys.executable)
# PC
with open(os.path.join(exec_path, 'data', 'version'), 'r') as f:
file_version = f.read().strip()
data_path = os.path.join(exec_path, 'data')
except:
try:
exec_path = os.path.dirname(sys.executable)
# Mac
with open(os.path.join(exec_path, '..', 'data', 'version'), 'r') as f:
file_version = f.read().strip()
data_path = os.path.join(exec_path, '..', 'data')