<jupyter_start><jupyter_text>This tutorial describes how to evaluate new rules applicable to the assets and derivatives data.<jupyter_code>from arelle import ModelManager, Cntlr, ModelFormulaObject, ModelXbrl, ViewFileFormulae, XbrlConst, ViewFileRenderedGrid
from arelle import RenderingEvaluator 
import pandas as pd
import numpy as np
from os import listdir
from os.path import join, isfile
import re
from src import Evaluator
import logging
import data_patterns
import pickle
DECIMALS = 0
RULES_PATH = join('..', 'ftk-rules')
INSTANCES_DATA_PATH = join('..','data','instances','...') #path of folder with converted xbrl-instance data
TEST_DATA_PATH = join('..', 'tests', 'data', 'demo')
RESULTS_PATH = join('..', 'results')
DATA_PATH = join('..', 'data')
logging.basicConfig(filename = join(RESULTS_PATH, 'rules.log'),level = logging.INFO, 
                    format='%(asctime)s - %(name)s - %(levelname)s - %(message)s')<jupyter_output><empty_output><jupyter_text>Import rules We start with importing the new rules applicable to the assets and derivatives data. There are several sets of rules applicable to different templates:* K208A (Information on positions held)* K208B (Information on assets)* K208A (Information on positions held) and K208B (Information on assets)* K210A (Information on positions held) and K210B (Information on derivatives)* K210B (Information on derivatives)  K208<jupyter_code>dfr_208 = pd.read_excel(join(RULES_PATH,'FTK_K208.xlsx'), engine='openpyxl')
dfr_208B = pd.read_excel(join(RULES_PATH,'FTK_K208B.xlsx'), engine='openpyxl')
dfr_208A = pd.read_excel(join(RULES_PATH,'FTK_K208A.xlsx'), engine='openpyxl')<jupyter_output><empty_output><jupyter_text>K210<jupyter_code>dfr_210 = pd.read_excel(join(RULES_PATH,'FTK_K210.xlsx'), engine='openpyxl')
dfr_210B = pd.read_excel(join(RULES_PATH,'FTK_K210B.xlsx'), engine='openpyxl')<jupyter_output><empty_output><jupyter_text>Import templates Next we import the reporting data. In the tutorial 'Convert XBRL-instances to CSV, HTML and pickles' the XBRL-instances are converted to pickle files per template. The pickle files are written to the data/instances folder. We import these pickle files. We merge dataframes for the sets of rules that are applicable to two templates.  S06 Import data and make index unique if necessary<jupyter_code># df_208A = pd.read_pickle(join(INSTANCES_DATA_PATH,'FTK.K208A.pickle')).fillna(0).reset_index()
# df_208A['K208A,020A'] = df_208A['K208A,020']
# listt=list(df_208A['K208A,020A'])
# for i in listt:
#     lenn = len(df_208A[df_208A['K208A,020A']==i])
#     if lenn > 1:
#         list_ind = list(df_208A.loc[df_208A['K208A,020A']==i].index)
#         temp = 0
#         for j in list_ind[1:]:
#             temp=temp+1
#             df_208A['K208A,020A'].iloc[j] = df_208A['K208A,020A'].iloc[j] + '_' + str(temp)
# df_208A = df_208A.set_index(['entity', 'period', 'K208A,020A'])
# df_208B = pd.read_pickle(join(INSTANCES_DATA_PATH, 'FTK.K208B.pickle')).fillna(0).reset_index()
# df_208B = df_208B.set_index(['entity', 'period', 'K208B,130'])
# df_208B['K208B,130'] = df_208B.index.get_level_values(2)
# df_208 = pd.merge(pd.read_pickle(join(INSTANCES_DATA_PATH,'FTK.K208A.pickle')).reset_index(),pd.read_pickle(join(INSTANCES_DATA_PATH,'FTK.K208B.pickle')).reset_index(),how='inner', left_on=['entity','period','K208A,020'], right_on=['entity','period','K208B,130']).set_index(['entity', 'period', 'K208A,020'])
# df_208 = df_208.fillna(0).reset_index()
# df_208['K208A,020A'] = df_208['K208A,020']
# listt=list(df_208['K208A,020A'])
# for i in listt:
#     lenn = len(df_208[df_208['K208A,020A']==i])
#     if lenn > 1:
#         list_ind = list(df_208.loc[df_208['K208A,020A']==i].index)
#         temp = 0
#         for j in list_ind[1:]:
#             temp=temp+1
#             df_208['K208A,020A'].iloc[j] = df_208['K208A,020A'].iloc[j] + '_' + str(temp)
# df_208 = df_208.set_index(['entity', 'period', 'K208A,020A'])<jupyter_output><empty_output><jupyter_text>In this tutorial we work with dummy data in order to show results<jupyter_code>df_208A = pd.read_pickle(join(TEST_DATA_PATH,'FTK.K208A.pickle')).reset_index() #Import demo pickles
df_208B = pd.read_pickle(join(TEST_DATA_PATH,'FTK.K208B.pickle')).reset_index() #Import demo pickles
df_208 = pd.merge(df_208A,df_208B,how='inner', left_on=['entity', 'period', 'K208A,020'], right_on=['entity','period', 'K208B,130']).set_index(['entity', 'period', 'K208A,020'])
df_208B = df_208B.set_index(['entity','period', 'K208B,130'])
df_208A = df_208A.set_index(['entity', 'period', 'K208A,020'])
df_208B['K208B,130'] = df_208B.index.get_level_values(2)
df_208['K208A,020'] = df_208.index.get_level_values(2)<jupyter_output><empty_output><jupyter_text>S08<jupyter_code># df_210B = pd.read_pickle(join(INSTANCES_DATA_PATH, 'FTK.K210B.pickle')).fillna(0).reset_index()
# df_210B = df_210B.set_index(['entity', 'period', 'K210B,200'])
# df_210B['K210B,200'] = df_210B.index.get_level_values(2)
# df_210 = pd.merge(pd.read_pickle(join(INSTANCES_DATA_PATH,'FTK.K210A.pickle')).reset_index(),pd.read_pickle(join(INSTANCES_DATA_PATH,'FTK.K210B.pickle')).reset_index(),how='inner', left_on=['entity','period','K210A,020'], right_on=['entity','period','K210B,200']).set_index(['entity', 'period', 'K210A,020'])
# df_210 = df_210.fillna(0).reset_index()
# df_210['K210A,020A'] = df_210['K210A,020']
# listt=list(df_210['K210A,020A'])
# for i in listt:
#     lenn = len(df_210[df_210['K210A,020A']==i])
#     if lenn > 1:
#         list_ind = list(df_210.loc[df_210['K210A,020A']==i].index)
#         temp = 0
#         for j in list_ind[1:]:
#             temp=temp+1
#             df_210['K210A,020A'].iloc[j] = df_210['K210A,020A'].iloc[j] + '_' + str(temp)
# df_210 = df_210.set_index(['entity', 'period', 'K210A,020A'])<jupyter_output><empty_output><jupyter_text>In this tutorial we work with dummy data in order to show results<jupyter_code>df_210A = pd.read_pickle(join(TEST_DATA_PATH,'FTK.K210A.pickle')).reset_index() #Import demo pickles
df_210B = pd.read_pickle(join(TEST_DATA_PATH, 'FTK.K210B.pickle')).reset_index() #Import demo pickles
df_210 = pd.merge(df_210A,df_210B,how='inner', left_on=['entity', 'period', 'K210A,020'], right_on=['entity', 'period', 'K210B,200']).set_index(['entity', 'period', 'K210A,020'])
df_210A = df_210A.set_index(['entity', 'period', 'K210A,020'])
df_210B = df_210B.set_index(['entity', 'period', 'K210B,200'])
df_210B['K210B,200'] = df_210B.index.get_level_values(2)
df_210['K210A,020'] = df_210.index.get_level_values(2)<jupyter_output><empty_output><jupyter_text>Evaluate rules Now we are ready to evaluate the different sets of rules. First, we construct a PatternMiner-object with the data-patterns package using the rules dataframe. Second, we use the analyze-function to get the results of the rules. We do this for each set of rules separately.  S06<jupyter_code>miner = data_patterns.PatternMiner(df_patterns=dfr_208)
results_208 = miner.analyze(df_208)
results_208
miner = data_patterns.PatternMiner(df_patterns=dfr_208B)
results_208B = miner.analyze(df_208B)
results_208B
miner = data_patterns.PatternMiner(df_patterns=dfr_208A)
results_208A = miner.analyze(df_208A)
results_208A<jupyter_output>100%|████████████████████████████████████████████████████████████████████████████████████████████████████████████████████████| 2/2 [00:00<00:00, 499.80it/s]<jupyter_text>S08<jupyter_code>miner = data_patterns.PatternMiner(df_patterns=dfr_210)
results_210 = miner.analyze(df_210)
results_210
miner2 = data_patterns.PatternMiner(df_patterns=dfr_210B)
results2_210B = miner2.analyze(df_210B)
results2_210B<jupyter_output>100%|████████████████████████████████████████████████████████████████████████████████████████████████████████████████████| 568/568 [00:01<00:00, 548.11it/s]
{'fraction_non_alphanumeric': 0.18515665125834618, 'fraction_numerical': 0.09527478171545968, 'mean_word_length': 6.956077630234933, 'pattern_counts': {'":': 0, '<': 37, '<?xml version=': 0, '>': 38, 'https://': 0, 'lorem ipsum': 0, 'www.': 0, 'xml': 0}, 'pii_count': 0, 'substrings_counts': 0, 'word_list_counts': {'cursed_substrings.json': 0, 'profanity_word_list.json': 0, 'sexual_word_list.json': 0, 'zh_pornsignals.json': 0}}
{'chain_length': '9', 'dir': 'jupyter-structured-clean-dedup', 'id': '631490', 'license': 'MIT', 'n_tokens_mistral': 3687, 'n_tokens_neox': 3078, 'n_words': 506, 'path': 'notebooks/Tutorial evaluate new rules FTK.ipynb', 'repo_name': 'DeNederlandscheBank/data-quality-rules', 'size': '7788'}