def build_triangle_from_table(dh):
new_dh = DataHolder(dh.name)
pool = DebuggablePool(pp.N_CORES)
# First find all date cols and see if one of them has target structure.
for dh_ind, ds in enumerate(dh.data_struct_list):
id_col, hori_date_col, vert_date_col = TriangleFromTableBuilder.do_the_magic(
ds, pool)
# cut each id into one row
cut_list = TriangleFromTableBuilder.make_cut_list(
ds.df_data[id_col])
# use the cut_list to insert all elements
tr_cols = pd.Series(ds.df_profiles.iloc[0, :] ==
SheetTypeDefinitions.TRIANGLE_ELEMENT,
index=ds.df_profiles.columns)
pad_header_mapping = TriangleFromTableBuilder.make_pad_header_mapping(
ds, hori_date_col)
vert_col_tup = (vert_date_col, ds.df_data[vert_date_col])
hori_col_tup = (hori_date_col, ds.df_data[hori_date_col])
id_col_tup = (id_col, ds.df_data[id_col])
func = partial(TriangleFromTableBuilder.apply_cuts, cut_list,
vert_col_tup, hori_col_tup, id_col_tup,
pad_header_mapping)
tr_col_tup_list = [(col_name, ds.df_data[col_name])
for col_name in tr_cols.index[tr_cols]]
out = pool.map(func, tr_col_tup_list)
#for name, tr_col in ds.df_data[tr_cols.index[tr_cols]].iteritems():
for temp_df_data, temp_df_profiles, name in out:
new_dh.add_sheet(name, temp_df_data, temp_df_profiles)
#new_dh.add_sheet(name, temp_df_data, temp_df_profiles)
pool.close()
return new_dh
def name_and_scrub_triangle(dh, new_dh_dict, meta_dh=None):
new_dh = DataHolder(dh.name)
word_set_list = list()
for ds in dh:
word_set_list.append(
SubTriangler.identify_category_name(ds, meta_dh))
if meta_dh != None:
if meta_dh.n > 0:
SubTriangler.divide_meta_data(dh, meta_dh, word_set_list)
# Find the most unique name
for i in range(len(word_set_list)):
ds = dh.data_struct_list[i]
difference = word_set_list[i].copy()
for j in range(len(word_set_list)):
if j != i and ds.orig_sheet_name == dh.data_struct_list[
j].orig_sheet_name:
difference = difference.difference(word_set_list[j])
if len(difference) > 0:
stringified = sorted([str(el) for el in difference])
name = " ".join(stringified)
name = name.translate(SubTriangler.remove_digits)
else:
name = str(i)
if ds.name != ds.orig_sheet_name:
name = ds.name + " " + name
new_dh.add_sheet(name,
ds.df_data,
ds.df_profiles,
orig_sheet_name=ds.orig_sheet_name)
new_dh_dict[dh.name] = new_dh
def test_fill_hollow_str_cols(self):
dh = DataHolder("test")
df_data = pd.DataFrame(data={'col1': [1, "", "", "j", "", "", "6", "", "b",
"g", "", "", "j", "", "", "6", "", "b", "", ""],
'1992': ["", "", "", "j", "", "", "6", "", "b",
"g", "", "", "j", "", "", "6", "", "b", "g", "hrumpff"]})
df_profiles = pd.DataFrame(data={'col1': [2, 0, 0, 1, 0, 0, 1, 0, 1,
1, 0, 0, 1, 0, 0, 1, 0, 1, 0, 0],
'1992': [0, 0, 0, 1, 0, 0, 1, 0, 1,
1, 0, 0, 1, 0, 0, 1, 0, 1, 1, 1]})
dh.add_sheet("test", df_data, df_profiles)
StringFiller.fill_hollow_str_cols(dh)
dh_sol = DataHolder("test")
df_data_sol = pd.DataFrame(data={'col1': [1, 1, 1, "j", "j", "j", "6", "6", "b",
"g", "g", "g", "j", "j", "j", "6", "6", "b", "b", "b"],
'1992': ["", "", "", "j", "j", "j", "6", "6", "b",
"g", "g", "g", "j", "j", "j", "6", "6", "b", "g", "hrumpff"]})
df_profiles_sol = pd.DataFrame(data={'col1': [2, 2, 2, 1, 1, 1, 1, 1, 1,
1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1],
'1992': [0, 0, 0, 1, 1, 1, 1, 1, 1,
1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1]})
dh_sol.add_sheet("test", df_data_sol, df_profiles_sol)
self.assertTrue(dh.equals(dh_sol))
def numify_dates(dh):
meta_dh = DataHolder(dh.name + "_meta")
for ds in dh:
d_cols = DateColIdentifier.identify_marked_date_cols(ds)
date_data = ds.df_data[d_cols.index[d_cols]]
meta_data = pd.DataFrame()
meta_profiles = pd.DataFrame()
for name, col in date_data.iteritems():
ds.df_profiles.loc[:, name] = SheetTypeDefinitions.STRING_DATE
types = col.map(lambda x: isinstance(x, float))
if types.all():
#do nothing basically
ds.df_data[name] = col.astype(int)
else:
meta_data[name] = col.copy()
meta_profiles[name] = ds.df_profiles[name].copy()
temp_col = col.copy()
temp_col[types] = temp_col[types]
#SOME PROBLEM WITH KEEPING TYEPS INT IN SHEET W
temp_col[np.logical_not(types)] = temp_col[np.logical_not(types)].map(lambda x: longest_numeral(x))
temp_col = temp_col.astype(int)
ds.df_data[name] = temp_col
# meta_backed = False
# for index, val in col.iteritems():
# if not isinstance(val, int):
# if not meta_backed:
# meta_backed = True
# meta_data[name] = col.copy()
# meta_profiles[name] = ds.df_profiles[name].copy()
# num = longest_numeral(val)
# ds.df_data.loc[index, name] = num
meta_dh.add_sheet("date_backup", meta_data, meta_profiles, orig_sheet_name=ds.orig_sheet_name)
return dh, meta_dh
def make_sol_dict():
"""
Run present pipeline and save the merge results
:return:
"""
file_names = ["FORMAT3_Copy of KommuneMTPLforTriangle.xls",
"C Triangulations analysis R2017 GC20161109.xls",
"EVOLUTION 2017 _ M+F - Triangles cat nat brut net.xls",
"Bsp8 _ Dreiecke aus GCNA für CU1.4.1.xls",
"Analysis MTPL MOD.xls",
"Bsp6 _ Dreiecke aus GCNA für CU1.4.1.xls",
"FORMAT6_sinistres.xls",
"FORMAT1_LOSSES-MTPL-OVER-500-GROUP-2005_modified.xls"]
solutions_dict = dict()
raw_dict = dict()
for file_name in file_names:
sr_list, file_name = ExcelLoader.load_excel(pdir.RESOURCES_DIR + "/raw_test_files/" + file_name)
dh = DataHolder()
for sr in sr_list:
dh.add_sheet(sr.sheet_name, pd.DataFrame(columns=sr.headers, data=sr.row_vals),
pd.DataFrame(columns=sr.headers, data=sr.xls_types), orig_sheet_name=sr.sheet_name)
dh = SheetPreProcessor.separate_components(dh)
raw_dict[file_name] = dh.encode()
dh = HorizontalMerger.horizontal_merge(dh)
#temp_path = pdir.RESOURCES_DIR + "/temp/"
#dh.write_excel(temp_path + file_name)
solutions_dict[file_name] = dh
solutions_dict = MergePararametersOptimizer.make_ind_col_dict(solutions_dict)
with open(pdir.RESOURCES_DIR + "/test/merge_solutions.obj", "wb") as temp_file:
pickle.dump(solutions_dict, temp_file)
with open(pdir.RESOURCES_DIR + "/test/raw_test.obj", "wb") as temp_file:
pickle.dump(raw_dict, temp_file)
def chop_triangles_horizontally(dh):
"""
checks for repeating header and splits ds:s
:param dh: DataHolder
:return: DataHolder
"""
chop, chop_lists = TriangleChopper.make_occurrence_list(dh)
if not chop:
return dh
else:
new_dh = DataHolder(dh.name)
for ind, ds in enumerate(dh):
occurrence_list = chop_lists[ind]
if any(occurrence_list):
for i in range(1, np.max(occurrence_list) + 1):
bools = np.logical_or(occurrence_list == 0,
occurrence_list == i)
df_data = ds.df_data[ds.df_data.columns[bools]].copy()
df_profiles = ds.df_profiles[
ds.df_profiles.columns[bools]].copy()
new_dh.add_sheet(ds.name,
df_data,
df_profiles,
orig_sheet_name=ds.orig_sheet_name)
else:
new_dh.add_ds(ds)
return new_dh
def merge_with_merges_list(dh, merges):
new_dh = DataHolder(dh.name)
for merge in merges:
profiles = None
data = None
name_set = set()
for ind in merge:
ds = dh.id_dict[ind]
name_set.update([ds.name])
if profiles is None:
profiles = ds.df_profiles.copy()
data = ds.df_data.copy()
else:
temp_profiles = ds.df_profiles.copy()
temp_data = ds.df_data.copy()
# TODO: generalize to other positions then the first position
#if profiles.shape[1] > temp_profiles.shape[1]:
# for header in profiles.columns[temp_profiles.shape[1]:]:
# temp_profiles[header] = SheetTypeDefinitions.ZERO_FLOAT
# temp_data[header] = 0.0
profiles = pd.concat([profiles, temp_profiles], sort=True)
profiles.fillna(SheetTypeDefinitions.ZERO_FLOAT,
inplace=True)
data = pd.concat([data, temp_data], sort=True)
new_dh.add_sheet("_".join(list(name_set)),
data,
profiles,
orig_sheet_name=ds.orig_sheet_name)
return new_dh
def vertical_category_division(ds, new_dh_dict, meta_dh):
# find the category column
# Should be strings (for now) (used)
# Kind of periodic (thus repetitive entries) (used)
# some entries may change slightly (used)
# period may change slightly (not checked for now)(should be checked in new if statment)
# Should get tag matches in dict (not checked for now)
df_data = ds.df_data
df_profiles = ds.df_profiles
orig_name = ds.orig_sheet_name
for col_name, col in df_data.iteritems():
string_ratio = np.sum(
df_profiles[col_name].values == SheetTypeDefinitions.STRING
) / df_profiles[col_name].values.size
if string_ratio > pp.MIN_STRING_RATIO_CAT_COL:
# check periodic potential
string_col = col.astype(str)
unique, counts = np.unique(string_col, return_counts=True)
ratio = np.max(counts) / col.size
if ratio < pp.MAX_RATIO_LARGEST_CAT and ratio > pp.MIN_RATIO_LARGEST_CAT and len(
unique) < pp.MAX_N_CATS:
if col_name in new_dh_dict:
new_dh = new_dh_dict[col_name]
else:
new_dh = DataHolder(col_name)
new_dh_dict[col_name] = new_dh
#period_label_bool = counts * period > string_col.size - period
# now get the remaining
#sub_period_label = unique[period_label_bool == False]
match_dict = SubTriangler.component_finder(unique)
# now load the new_dh
for name in match_dict:
cond = np.array([
string_col.values == sub_name
for sub_name in match_dict[name]
]).any(axis=0)
sub_df_data = df_data[cond].drop(
columns=[string_col.name])
sub_df_profiles = df_profiles[cond].drop(
columns=[string_col.name])
if name == "" or np.sum(cond) < 4:
new_ds = DataStruct(sub_df_data,
sub_df_profiles,
name,
orig_sheet_name=orig_name)
for split in new_ds.col_split_ds():
if not np.all(split.df_profiles ==
SheetTypeDefinitions.EMPTY_STRING
) and not (np.all(
split.df_data == "")):
meta_dh.add_ds(split)
else:
new_dh.add_sheet(ds.name + " - " + name,
sub_df_data,
sub_df_profiles,
orig_sheet_name=orig_name)
def main(file, settings):
print(file)
sr_list, file_name = ExcelLoader.load_excel(file)
dh = DataHolder(file_name)
for sr in sr_list:
dh.add_sheet(sr.sheet_name,
pd.DataFrame(columns=sr.headers, data=sr.row_vals),
pd.DataFrame(columns=sr.headers, data=sr.xls_types),
orig_sheet_name=sr.sheet_name)
dummy, new_dh = TrianglePipeline.table_triangle_pipeline_dh(dh)
temp_path = pdir.RESOURCES_DIR + "/temp/"
new_dh.write_excel(temp_path + file_name)
def perform_horizontal_merge(dh, distances):
new_dh = DataHolder(dh.name)
# make a greedy merge
merged_set = set()
for part in distances:
if part[0] > pp.MAX_HORIZONTAL_MERGE_DISTANCE:
break
merge = [part[1]] + part[2]
if len(set(merge).intersection(merged_set)) == 0:
if len(part[2]) > 1:
# Merge vertically!
df_data_list = [
dh.id_dict[df_id].df_data for df_id in part[2]
]
df_profiles_list = [
dh.id_dict[df_id].df_profiles for df_id in part[2]
]
df_data = pd.concat(df_data_list, axis=0, sort=True)
df_profiles = pd.concat(df_profiles_list,
axis=0,
sort=True)
else:
df_data = dh.id_dict[part[2][0]].df_data
df_profiles = dh.id_dict[part[2][0]].df_profiles
df_data = pd.concat([dh.id_dict[part[1]].df_data, df_data],
axis=1,
sort=True)
df_profiles = pd.concat(
[dh.id_dict[part[1]].df_profiles, df_profiles],
axis=1,
sort=True)
df_data = df_data.reindex(sorted(df_data.columns), axis=1)
df_profiles = df_profiles.reindex(sorted(df_profiles.columns),
axis=1)
merged_set.update(merge)
new_dh.add_sheet(
dh.id_dict[merge[0]].name,
df_data,
df_profiles,
orig_sheet_name=dh.id_dict[merge[0]].orig_sheet_name)
# add the remaining
for id_key in dh.id_dict:
if id_key not in merged_set:
new_dh.add_sheet(
dh.id_dict[id_key].name,
dh.id_dict[id_key].df_data,
dh.id_dict[id_key].df_profiles,
orig_sheet_name=dh.id_dict[id_key].orig_sheet_name)
return new_dh
def run_test_per_file_name(file_name):
print(file_name)
logger = logging.getLogger("svm_writer")
sr_list, file_name = ExcelLoader.load_excel(file_name)
dh = DataHolder(file_name.split(".")[0])
for sr in sr_list:
dh.add_sheet(sr.sheet_name,
pd.DataFrame(columns=sr.headers, data=sr.row_vals),
pd.DataFrame(columns=sr.headers, data=sr.xls_types),
orig_sheet_name=sr.sheet_name)
dh = SheetPreProcessor.pre_strip(dh)
DateColIdentifier.identify_and_gen_date_cols(dh,
replace_col=False,
svm_logger=logger)
def testFindTriangleHeaders(self):
names = ["First", "Second"]
dh = DataHolder("test")
d1 = pd.DataFrame(data={'col1' + ps.HEADER_PLACE_HOLDER: ["1", "2", 1],
'col2' + ps.HEADER_PLACE_HOLDER: [3, "2", "3b"],
'col3' + ps.HEADER_PLACE_HOLDER: ["brum2", "4", 4],
'col4' + ps.HEADER_PLACE_HOLDER: [24, "4", "brum25"],
})
d2 = d1.copy()
d2.iloc[:, :]=1
dh.add_sheet(names[0], d1, d2)
dh = TriangleHeaderFinder.find_triangle_headers(dh, test_settings=True)
headers = list(dh.data_struct_list[0].df_data.columns)
self.assertEqual(headers, ["col11", "col23", "col3brum2", "col424"])
def testDistrMatching(self):
test_distr = np.array([1,1,1,1,1,2,2,2,2,3,3,3,4,4,5])
test_distr = test_distr/mad(test_distr)
test_distr = test_distr - np.mean(test_distr)
distr = {
ps.CAT_RESERVED_NAME: test_distr
}
dh = DataHolder("test")
dh.add_sheet(self.names[0], pd.DataFrame(data=[1, 1, 1, 1, 2, 2, 2, 3, 3, 4]), pd.DataFrame(data=[SheetTypeDefinitions.TRIANGLE_ELEMENT]*10))
dh.add_sheet(self.names[0], pd.DataFrame(data=[5, 9, 3, 7, 18]), pd.DataFrame(data=[SheetTypeDefinitions.TRIANGLE_ELEMENT]*5))
matches = list()
for id in dh.id_dict:
matches.append(InputMatcher.compare_with_distribution(id, ps.CAT_RESERVED_NAME, dh, distr))
# First entry is more similar to the reference, therefore, first match should be better
self.assertTrue(matches[0] > matches[1])
def post(self, request):
sr_list = jsonpickle.decode(request.data['sr_list'])
dhName = request.data['dhName']
selected_sheets = request.data['selected_sheets']
data_holder = DataHolder(dhName)
for sr in sr_list:
if sr.sheet_name in selected_sheets:
data_holder.add_sheet(sr.sheet_name,
pd.DataFrame(columns=sr.headers,
data=sr.row_vals),
pd.DataFrame(columns=sr.headers,
data=sr.xls_types),
orig_sheet_name=sr.sheet_name)
encoded = data_holder.encode()
return Response(encoded, status=200)
def test_col_identification(self):
df_data = pd.DataFrame(data={'col1': ["1", "", "1991", "1992", "2007", "rew", "1993", "1994", "1995x"],
'col2': [43, 1994, 2015, 1994, 7, 2015, 1994, 1999, 2015],
'1991': [1993, 1, 6, 1993, 1, 6, 1993, 1, 6],
'1992': ["g", "r", "h", "j", "t", "f", "6", "p", "6"],
'1993': ["1", "1993", "6", "1993", "", "rew", "1993", "1994", ""]})
df_profiles = pd.DataFrame(data={'col1': [1, 1, 1, 1, 1, 1, 1, 1, 1],
'col2': [2, 2, 2, 2, 2, 2, 2, 2, 2],
'1991': [2, 2, 2, 2, 2, 2, 2, 2, 2],
'1992': [1, 1, 1, 1, 1, 1, 1, 1, 1],
'1993': [1, 1, 1, 1, 1, 1, 1, 1, 1]})
dh = DataHolder('test')
dh.add_sheet('test', df_data, df_profiles)
dh = DateFiller.identify_and_gen_date_cols(dh, replace_col=False)
dh = ColTypeIdentifier.identify_col_types(dh)
profiles = dh.data_struct_list[0].df_profiles
self.assertTrue(profiles.iloc[1,1], SheetTypeDefinitions.STRING_DATE)
self.assertTrue(profiles.iloc[0, 2], SheetTypeDefinitions.TRIANGLE_ELEMENT)
self.assertTrue(profiles.iloc[0, 3], SheetTypeDefinitions.ID_ELEMENT)
def run_cleaning_per_file_name(file_name):
print(file_name)
logger = logging.getLogger("svm_writer")
sr_list, file_name = ExcelLoader.load_excel(file_name)
dh = DataHolder(file_name.split(".")[0])
for sr in sr_list:
dh.add_sheet(sr.sheet_name,
pd.DataFrame(columns=sr.headers, data=sr.row_vals),
pd.DataFrame(columns=sr.headers, data=sr.xls_types),
orig_sheet_name=sr.sheet_name)
dh, meta_dh = HeaderFinder.find_headers(dh)
# save original state
dh.create_memento()
# Find and remove deviating rows
dh = DevRowFinder.delete_deviating_rows(dh)
# Identify and add date col
DateColIdentifier.identify_and_gen_date_cols(dh, svm_logger=logger)
def testTurnTriangle(self):
dh = DataHolder("test")
data = pd.DataFrame(data={'col1': ["", "", 0], 'col2': [0, 0, 2], '1991': [0, 0, 0], '1992': [1, 0, 0]})
prof = pd.DataFrame(data={'col1': [1, 1, 6], 'col2': [6, 6, 6], '1991': [6, 6, 6], '1992': [2, 6, 6]})
dh.add_sheet("test", data, prof)
data = pd.DataFrame(data={'col1': ["", "", 0], 'col2': [1, 1, 0], '1991': [0, 0, 0], '1992': [1, 0, 0]})
prof = pd.DataFrame(data={'col1': [1, 1, 6], 'col2': [6, 6, 6], '1991': [6, 6, 6], '1992': [2, 6, 6]})
dh.add_sheet("test", data, prof)
data = pd.DataFrame(data={'col1': ["", "", 0], 'col2': [3, 1, 0], '1991': [0, 0, 0], '1992-': [1, 0, 0]})
prof = pd.DataFrame(data={'col1': [1, 1, 6], 'col2': [6, 6, 6], '1991': [6, 6, 6], '1992-': [2, 6, 6]})
dh.add_sheet("test", data, prof)
tr_cols_dict = {tuple(dh.data_struct_list[0].df_data.columns) + dh.data_struct_list[0].df_data.shape:
pd.Series([False, True, True, True], index=dh.data_struct_list[0].df_data.columns),
tuple(dh.data_struct_list[2].df_data.columns) + dh.data_struct_list[0].df_data.shape:
pd.Series([False, True, True, True], index=dh.data_struct_list[2].df_data.columns)
}
dh_copy = dh.copy_without_memory()
TriangleStripper.turn_triangle(dh, tr_cols_dict, alt_min_score=0.6)
for ds in dh_copy.data_struct_list:
tr_cols = tr_cols_dict[tuple(ds.df_data.columns) + ds.df_data.shape]
tri_part = ds.df_data[tr_cols.index[tr_cols]].values
ds.df_data[tr_cols.index[tr_cols]] = np.transpose(tri_part)
for ds, ds_copy in zip(dh.data_struct_list, dh_copy.data_struct_list):
self.assertTrue(ds.df_data.equals(ds_copy.df_data))
self.assertTrue(ds.df_profiles.equals(ds_copy.df_profiles))
def get_context_data(self, **kwargs):
context = super().get_context_data(**kwargs)
if self.request.session.get('data_holder'):
sr_list = jsonpickle.decode(
self.request.session.get('data_holder'))
data_holder = DataHolder()
for sr in sr_list:
data_holder.add_sheet(
sr.sheet_name,
pd.DataFrame(columns=sr.headers, data=sr.row_vals),
pd.DataFrame(columns=sr.headers, data=sr.xls_types))
context[
"diff_dicts"], data_holder = CleaningPipeline.clean_data_dh(
data_holder)
else:
sheets = DataSheet.objects.filter(
owner=self.request.user).order_by('sheet_name')
context["diff_dicts"], data_holder = CleaningPipeline.clean_data(
sheets)
#sheets = DataSheet.objects.filter(owner=self.request.user).order_by('sheet_name')
#context["diff_dicts"], mem_dict = CleaningPipeline.clean_data(sheets)
return context
def find_triangles(dh, **kwargs):
return_meta = False
if 'return_meta' in kwargs:
return_meta = kwargs['return_meta']
triangle_dh = DataHolder(dh.name)
rest_dh = DataHolder(dh.name + '_non-triangular')
#bool_array = np.zeros(triangle_dh.n, dtype=bool)
for dh_ind, ds in enumerate(dh.data_struct_list):
df_data, df_profiles = ds.df_data, ds.df_profiles
# now select triangles in som smart way
bool = TriangleFinder.is_triangle(ds, **kwargs)
if bool:
TriangleFinder.add_triangle_to_dh(ds, triangle_dh)
else:
rest_dh.add_sheet(ds.name,
df_data,
df_profiles,
orig_sheet_name=ds.orig_sheet_name)
# Now get the triangle similiar data structs
triangle_similar = TriangleFinder.find_triangles_by_similarity(
triangle_dh, rest_dh)
if len(triangle_similar) > 0:
rest_copy = rest_dh.copy_without_memory()
rest_dh = DataHolder(rest_copy.name)
for ds in rest_copy:
if ds.id in triangle_similar:
TriangleFinder.add_triangle_to_dh(ds, triangle_dh)
else:
rest_dh.add_sheet(ds.name,
ds.df_data,
ds.df_profiles,
orig_sheet_name=ds.orig_sheet_name)
if return_meta:
return triangle_dh, rest_dh
else:
return triangle_dh
def run_test_per_file_name(in_obj, in_tup, form):
"""
Performs a fixture test for one file. The encapsulators determine the scope of the test.
:param in_obj:
:param in_tup:
:param form:
:return:
"""
if not pp.LOG_SVM_FEATURES:
print(in_tup)
sr_list, file_name = ExcelLoader.load_excel(pdir.RESOURCES_DIR +
"/raw_test_files/" +
in_tup[0])
dh = DataHolder(file_name.split(".")[0])
for sr in sr_list:
dh.add_sheet(sr.sheet_name,
pd.DataFrame(columns=sr.headers, data=sr.row_vals),
pd.DataFrame(columns=sr.headers, data=sr.xls_types),
orig_sheet_name=sr.sheet_name)
#Choose encapsulator class to determine test scope
#dhce = DataHolderCallTestEncapsulator(pdir.RESOURCES_DIR + '/temp/pickles/', pdir.RESOURCES_DIR + '/test/pickles/')
#dhce = DataHolderCallSaveEncapsulator(pdir.RESOURCES_DIR + '/test/pickles/')
#dhce = DataHolderCallOutputEncapsulator(pdir.RESOURCES_DIR + '/left_triangles/' + in_tup[0], in_tup[1])
dhce = DataHolderCallEncapsulator()
#dhce = DataHolderCallTimeEncapsulator()
if form == "triangle_table":
dummy, dh = TrianglePipeline.table_triangle_pipeline_dh(dh, dhce)
elif form == "triangle":
dummy, dh = TrianglePipeline.triangle_pipeline_dh(
dh,
dhce,
tri_type=in_tup[1]["tri_type"],
n_outputs=in_tup[1]["n_outputs"])
elif form == "cleaning":
dummy, dh = CleaningPipeline.clean_data_dh(dh)
ToDiscComparer.compare_to_disc(in_obj, file_name, dh)
class SubTrianglerTest(TestCase):
def setUp(self):
self.names = ["first", "second"]
self.dh = DataHolder("test")
d1 = pd.DataFrame(data={'col1': ["1", "2"], 'col2': ["3", "4"]})
d2 = pd.DataFrame(data={'col1': [1, 2], 'col2': [3, 4]})
self.dh.add_sheet(self.names[0], d1, d2)
d1 = pd.DataFrame(data={'col1': ["1", "2"], 'col2': ["1", "1"]})
d2 = pd.DataFrame(data={'col1': [1, 2], 'col2': [1, 1]})
self.dh.add_sheet(self.names[0], d1, d2)
d1 = pd.DataFrame(data={'col1': ["1", "2"], 'col2': ["15", "16"]})
d2 = pd.DataFrame(data={'col1': [1, 2], 'col2': [15, 16]})
self.dh.add_sheet(self.names[1], d1, d2)
def test_component_finder(self):
# make a list of similar and dissimilar strings
strings = ["asdfgh", "sdfgh", "qwert", "asdgh", "qwerty", "asdfgh"]
match_dict = SubTriangler.component_finder(strings)
test_set1 = set(["asdfgh", "sdfgh", "asdgh", "asdfgh"])
test_set2 = set(["qwert", "qwerty"])
self.assertEqual(test_set1, set(match_dict["sdfgh"]))
self.assertEqual(test_set2, set(match_dict["qwert"]))
class DataHolderTest(TestCase):
def setUp(self):
self.names = ["first", "second"]
self.dh = DataHolder("test")
d1 = pd.DataFrame(data={'col1': ["1", "2"], 'col2': ["3", "4"]})
d2 = pd.DataFrame(data={'col1': [1, 2], 'col2': [3, 4]})
self.dh.add_sheet(self.names[0], d1, d2, orig_sheet_name="1")
d1 = pd.DataFrame(data={'col1': ["1", "2"], 'col2': ["1", "1"]})
d2 = pd.DataFrame(data={'col1': [1, 2], 'col2': [1, 1]})
self.dh.add_sheet(self.names[0], d1, d2, orig_sheet_name="2")
d1 = pd.DataFrame(data={'col1': ["1", "2"], 'col2': ["15", "16"]})
d2 = pd.DataFrame(data={'col1': [1, 2], 'col2': [15, 16]})
self.dh.add_sheet(self.names[1], d1, d2, orig_sheet_name="2")
def test_set_card_ids(self):
trngs = []
trngs.append({"group_id": "one"})
trngs.append({"group_id": "two"})
RowParser.set_card_ids(trngs, self.dh)
# print(self.dh)
true_ids = [0, 1, 1]
for ds, ind in zip(self.dh.data_struct_list, true_ids):
self.assertEqual(ds.card_id, ind)
def test_updating(self):
for dh_ind, df_data, df_profiles in self.dh.enumerate():
if dh_ind == 0:
df_data.loc[0, "col2"] = "5"
df_profiles.loc[0, "col2"] = 5
if dh_ind == 1:
df_data.loc[0, "col1"] = "5"
df_profiles.loc[0, "col1"] = 5
if dh_ind == 2:
df_data.loc[1, "col1"] = "19"
df_profiles.loc[1, "col1"] = 19
self.dh.update_with_ind(dh_ind, df_data, df_profiles)
assert_frame_equal(self.dh.data_dict[self.names[0]][0].df_data, self.dh.data_struct_list[0].df_data)
assert_frame_equal(self.dh.data_dict[self.names[0]][1].df_data, self.dh.data_struct_list[1].df_data)
assert_frame_equal(self.dh.data_dict[self.names[1]][0].df_data, self.dh.data_struct_list[2].df_data)
assert_frame_equal(self.dh.data_dict[self.names[0]][0].df_profiles, self.dh.data_struct_list[0].df_profiles)
assert_frame_equal(self.dh.data_dict[self.names[0]][1].df_profiles, self.dh.data_struct_list[1].df_profiles)
assert_frame_equal(self.dh.data_dict[self.names[1]][0].df_profiles, self.dh.data_struct_list[2].df_profiles)
for key in self.dh.data_dict:
for d_struct in self.dh.data_dict[key]:
self.assertEqual(key, d_struct.name)
for ds in self.dh.data_struct_list:
self.assertEqual(ds, self.dh.id_dict[ds.id])
def test_mementos(self):
self.dh.create_memento()
for dh_ind, df_data, df_profiles in self.dh.enumerate():
if dh_ind == 0:
df_data.loc[0, "col2"] = "5"
df_profiles.loc[0, "col2"] = 5
if dh_ind == 1:
df_data.loc[0, "col1"] = "5"
df_profiles.loc[0, "col1"] = 5
if dh_ind == 2:
df_data.loc[1, "col1"] = "19"
df_profiles.loc[1, "col1"] = 19
self.dh.update_with_ind(dh_ind, df_data, df_profiles)
self.dh.create_memento()
diff_dict_list = SheetStateComparer.compare_states(self.dh.mementos[0], self.dh.mementos[1])
#for diff_dict in diff_dict_list:
#pass#diff_
for i in range(2):
for j in range(2):
el = diff_dict_list[0]["diff_array"][i][j]
if i == 0 and j == 1:
self.assertEqual(el.change, "Corrected")
else:
self.assertEqual(el.change, "No change")
def test_serialization(self):
self.dh.data_struct_list[0].roles.append("Claims Paid")
self.dh.data_struct_list[0].df_data.sort_values("col1", ascending=False, inplace=True)
serialized = self.dh.encode()
data_framed = DataHolder.decode(serialized)
assert_frame_equal(self.dh.data_struct_list[0].df_data, data_framed.data_struct_list[0].df_data)
assert_frame_equal(self.dh.data_struct_list[1].df_data, data_framed.data_struct_list[1].df_data)
assert_frame_equal(self.dh.data_struct_list[2].df_data, data_framed.data_struct_list[2].df_data)
assert_frame_equal(self.dh.data_struct_list[0].df_profiles, data_framed.data_struct_list[0].df_profiles)
assert_frame_equal(self.dh.data_struct_list[1].df_profiles, data_framed.data_struct_list[1].df_profiles)
assert_frame_equal(self.dh.data_struct_list[2].df_profiles, data_framed.data_struct_list[2].df_profiles)
self.assertEqual(data_framed.data_struct_list[0].roles[0], "Claims Paid")
# Test conservation of ids
for ind in range(len(self.dh.data_struct_list)):
self.assertEqual(data_framed.data_struct_list[ind].id, self.dh.data_struct_list[ind].id)
def horizontal_category_division(ds, new_dh_dict, meta_dh):
# find potential category rows
# for now, look for strings
str_ratio = (ds.df_profiles == SheetTypeDefinitions.STRING).sum(
axis=1) / ds.df_profiles.shape[1]
cat_cols = str_ratio >= pp.MIN_STRING_RATIO_CAT_ROW
for ind in cat_cols.index[cat_cols]:
cat_row = ds.df_data.loc[ind, :]
unique, counts = np.unique(cat_row, return_counts=True)
ratio = np.max(counts) / cat_row.size
if ratio < 0.5 and len(unique) / cat_row.size < 0.5:
row_name = "Row " + str(ind)
if row_name in new_dh_dict:
new_dh = new_dh_dict[row_name]
else:
new_dh = DataHolder(row_name)
new_dh_dict[row_name] = new_dh
match_dict = SubTriangler.component_finder(unique)
rev_match_dict = dict()
for key, val in match_dict.items():
for item in val:
rev_match_dict[item] = key
count_dict = {}
for key, val in match_dict.items():
active = np.isin(unique, val)
count_dict[key] = np.sum(counts[active])
# get number of data_structs to make
headers_dict = {}
for key, val in count_dict.items():
if val > pp.MIN_YEARS_SPANNED:
headers_dict[key] = []
len_array = np.zeros(len(headers_dict), dtype=int)
for enum, key in enumerate(headers_dict):
for name, val in cat_row.iteritems():
if rev_match_dict[
val] not in headers_dict or rev_match_dict[
val] == key:
headers_dict[key].append(name)
len_array[enum] = len(headers_dict[key])
# Now fill the dh
# First, if same length, find optimal header naming
same_length = np.std(len_array) == 0
if same_length:
out_headers = deepcopy(headers_dict)
for i in range(len_array[0]):
i_headers = np.array(
[val[i] for val in headers_dict.values()])
missing = np.array([
"Missing header" in header for header in i_headers
])
if np.any(missing) and np.any(np.logical_not(missing)):
header = i_headers[np.logical_not(missing)][0]
for key in out_headers:
out_headers[key][i] = header
for key, val in headers_dict.items():
df_data = ds.df_data.loc[ds.df_data.index != ind, val]
df_profiles = ds.df_profiles.loc[ds.df_data.index != ind,
val]
if same_length:
df_data = pd.DataFrame(df_data.values,
index=df_data.index,
columns=out_headers[key])
df_profiles = pd.DataFrame(df_profiles.values,
index=df_profiles.index,
columns=out_headers[key])
new_dh.add_sheet(ds.name + " - " + key,
df_data,
df_profiles,
orig_sheet_name=ds.orig_sheet_name)
class InputMatcherTest(TestCase):
def setUp(self):
self.trngs = [{
'headers': ["Year", "unit"],
'categories': [{
'name': 'Claim - Incurred',
'type': 'sum',
'from': [ps.CAT_PAID_NAME, ps.CAT_RESERVED_NAME]
}, {
'name': ps.CAT_PAID_NAME,
'type': 'independent',
'from': []
}, {
'name': ps.CAT_RESERVED_NAME,
'type': 'independent',
'from': []
}],
"group_id":
0,
"type":
"single loss"
}, {
'headers': ["Year", "unit"],
'categories': [{
'name': ps.CAT_PREMIUM_NAME,
'type': 'independent',
'from': []
}],
"group_id":
0,
"type":
"single loss"
}]
self.names = [
"Premium_", "Premium", "Total Outstanding 2004", "Paid",
"Total Incurred"
]
self.dh = DataHolder()
self.dh.add_sheet(self.names[0], pd.DataFrame(data=[0]),
pd.DataFrame(data=[0]))
self.dh.add_sheet(self.names[1], pd.DataFrame(data=[0]),
pd.DataFrame(data=[0]))
self.dh.add_sheet(self.names[2], pd.DataFrame(data=[0]),
pd.DataFrame(data=[0]))
self.dh.add_sheet(self.names[3], pd.DataFrame(data=[0]),
pd.DataFrame(data=[0]))
self.dh.add_sheet(self.names[4], pd.DataFrame(data=[0]),
pd.DataFrame(data=[0]))
def testTextualMatching(self):
self.dh, dummy, dummy = RowParser.set_card_ids(self.trngs, self.dh)
InputMatcher.match_triangles_to_output(self.trngs, self.dh)
self.assertEqual(
self.dh.id_dict[self.trngs[0]["connection"][ps.CAT_PAID_NAME]
["data_struct_ids"][0]].name, self.names[3])
self.assertEqual(
self.dh.id_dict[self.trngs[0]["connection"][ps.CAT_RESERVED_NAME]
["data_struct_ids"][0]].name, self.names[2])
self.assertEqual(
self.dh.id_dict[self.trngs[1]["connection"][ps.CAT_PREMIUM_NAME]
["data_struct_ids"][0]].name, self.names[1])
def post(self, request):
#Recieve name of file
filename = request.data.get('fileName')
# Build data holder
sr_list = jsonpickle.decode(request.data['sr_list'])
selected_sheets = request.data['selected_sheets']
data_holder = DataHolder(filename)
for sr in sr_list:
if sr.sheet_name in selected_sheets:
data_holder.add_sheet(sr.sheet_name,
pd.DataFrame(columns=sr.headers,
data=sr.row_vals),
pd.DataFrame(columns=sr.headers,
data=sr.xls_types),
orig_sheet_name=sr.sheet_name)
response_data = {}
#dump this shiiiit
#data_holder.to_pickle_file(pdir.TEMP_DIR + "from_views.pickle")
if data_holder is None:
raise ValueError("No data holder found")
elif data_holder.n == 0:
raise ValueError("No sheets in data holder")
#Recieve triangle formats
triangles = request.data.get('triangles')
user_defined_triangles = triangles['templates']
outputFormats = triangles['output_formats']
n_outputs = triangles['number_of_outputs']
input_format = triangles['inputFormat']
tr_type = user_defined_triangles[0]['type']
# if tr_type == "single":
# n_outputs = len(user_defined_triangles)
# else:
# n_outputs = int(len(user_defined_triangles)/2)
try:
if input_format[0] == 'triangle':
#print(tr_type, n_outputs)
data_holder_dict, data_holder = TrianglePipeline.triangle_pipeline_dh(
data_holder, tri_type=tr_type, n_outputs=n_outputs)
else:
data_holder_dict, data_holder = TrianglePipeline.table_triangle_pipeline_dh(
data_holder)
#DataHolder manipulation
data_holder, group_ids, sheet_names = RowParser.set_card_ids(
user_defined_triangles, data_holder)
user_defined_triangles = InputMatcher.match_triangles_to_output(
user_defined_triangles, data_holder)
user_defined_triangles = RowParser.parse_output_from_triangle_forms(
user_defined_triangles, data_holder)
except DataHolderException as err:
data = {}
data['message'] = err.message
data['dh'] = err.dh
return Response({'response_error': data})
SheetWriter.trngs_to_existing_excel(
user_defined_triangles, pdir.TEMP_DIR + ps.OUTPUT_NAME + filename)
#Unsure if all neded
response_data["group_ids"] = group_ids
response_data['output_triangles'] = user_defined_triangles
#Building list for initial rendering
response_data[
"unit_triangles"] = ConnectDataAPIView.make_unit_triangle_list(
data_holder)
response_data["str_data_holder"] = data_holder.encode()
if len(data_holder_dict) > 1:
response_data["str_data_holder_dict"] = {
key: val.encode()
for key, val in data_holder_dict.items()
}
else:
response_data["str_data_holder_dict"] = {
data_holder.name: response_data["str_data_holder"]
}
return Response({'data': response_data})
class InputMatcherTest(TestCase):
def setUp(self):
# self.trngs = [{'headers': ["Year", "unit"],
# 'categories': [
# {'name': 'Claim - Incurred',
# 'type': 'sum',
# 'from': [ps.CAT_PAID_NAME, ps.CAT_RESERVED_NAME]},
# {'name': ps.CAT_PAID_NAME,
# 'type': 'independent',
# 'from': []},
# {'name': ps.CAT_RESERVED_NAME,
# 'type': 'independent',
# 'from': []}
# ],
# "group_id": 0,
# "type": "aggregate"
# },
# {'headers': ["Year", "unit"],
# 'categories': [
# {'name': ps.CAT_PREMIUM_NAME,
# 'type': 'independent',
# 'from': []}],
# "group_id": 0,
# "type": "aggregate"
# }]
#info_dict = {'tri_type': 'aggregate', 'n_outputs': 1}
#print(info_dict)
#user_defined_triangles = OutputTriangleParser.generate_output_triangles(info_dict)
self.names = ["Premium_", "Premium", "Total Outstanding 2004", "Paid", "Total Incurred"]
self.dh = DataHolder("test")
self.dh.add_sheet(self.names[0], pd.DataFrame(data=[0]), pd.DataFrame(data=[0]))
self.dh.add_sheet(self.names[1], pd.DataFrame(data=[0]), pd.DataFrame(data=[0]))
self.dh.add_sheet(self.names[2], pd.DataFrame(data=[0]), pd.DataFrame(data=[0]))
self.dh.add_sheet(self.names[3], pd.DataFrame(data=[0]), pd.DataFrame(data=[0]))
self.dh.add_sheet(self.names[4], pd.DataFrame(data=[0]), pd.DataFrame(data=[0]))
# def testFitForOutput(self):
# dh = DataHolder("test")
# data = pd.DataFrame(data={'001. col2': [0, 0], '002. 1991': [0, 0]})
# prof = pd.DataFrame(data={'001. col2': [9, 9], '002. 1991': [9, 9]})
# dh.add_sheet("test", data, prof)
# data = pd.DataFrame(data={'001. col2': ["", 0], '002. 1991': [0, 0]})
# prof = pd.DataFrame(data={'001. col2': [9, 9], '002. 1991': [9, 9]})
# dh.add_sheet("test", data, prof)
# data = pd.DataFrame(data={'001. col3': [0, 0], '002. 1991': [0, 0]})
# prof = pd.DataFrame(data={'001. col3': [9, 9], '002. 1991': [9, 9]})
# dh.add_sheet("test", data, prof)
# data = pd.DataFrame(data={'001. col2': [0, 0], '002. 1991': [0, 0]})
# prof = pd.DataFrame(data={'001. col2': [9, 9], '002. 1991': [9, 9]})
# dh.add_sheet("test", data, prof)
# card_ids = [0, 0, 0, 1]
# for ds, id in zip(dh, card_ids):
# ds.card_id = id
# InputMatcher.set_fit_for_output(dh)
# solution = [True, False, False, True]
# for ds, sol in zip(dh, solution):
# self.assertEqual(ds.fit_for_output, sol)
def testDistrMatching(self):
test_distr = np.array([1,1,1,1,1,2,2,2,2,3,3,3,4,4,5])
test_distr = test_distr/mad(test_distr)
test_distr = test_distr - np.mean(test_distr)
distr = {
ps.CAT_RESERVED_NAME: test_distr
}
dh = DataHolder("test")
dh.add_sheet(self.names[0], pd.DataFrame(data=[1, 1, 1, 1, 2, 2, 2, 3, 3, 4]), pd.DataFrame(data=[SheetTypeDefinitions.TRIANGLE_ELEMENT]*10))
dh.add_sheet(self.names[0], pd.DataFrame(data=[5, 9, 3, 7, 18]), pd.DataFrame(data=[SheetTypeDefinitions.TRIANGLE_ELEMENT]*5))
matches = list()
for id in dh.id_dict:
matches.append(InputMatcher.compare_with_distribution(id, ps.CAT_RESERVED_NAME, dh, distr))
# First entry is more similar to the reference, therefore, first match should be better
self.assertTrue(matches[0] > matches[1])
def test_headers_are_padded(self):
n_padded = 11
headers = [str(num + 1).zfill(pp.N_DIGITS_HEADER_PADDING) + ". " + ps.HEADER_PLACE_HOLDER for num in range(n_padded)]
headers.append("date001.2")
bools = InputMatcher.headers_are_padded(headers)
truth = [True for i in range(n_padded)]
truth.append(False)
self.assertTrue(np.all(np.array(truth) == np.array(bools)))