<jupyter_start><jupyter_text>Processing the results of assited labelingThe assited labeling notebook yields several csv files one for each query. As the process have been parallelized, we have as many copies as threads we have run. So there is the need for merging the results in one single excel file per policy instrument.TODO: this code is highly inefficient for large amount of data, probably due to the way the file is open and handled. Better alternatives should be worked out.<jupyter_code>import boto3
import codecs
import copy
import csv
import json
import numpy as np
from openpyxl import load_workbook
import pandas as pd<jupyter_output><empty_output><jupyter_text>Functions<jupyter_code>def aws_credentials(path, filename):
    file = path + filename
    with open(file, 'r') as dict:
        key_dict = json.load(dict)
    for key in key_dict:
        KEY = key
        SECRET = key_dict[key]
    return KEY, SECRET

def read_csv_from_s3(s3_object, columns):
    pre_labeled =[]
    try:
        for row in csv.DictReader(codecs.getreader("utf-8")(s3_object.get()['Body'])):
            list_row = []
            for column in columns:
                list_row.append(row[column])
            pre_labeled.append(list_row)
#             print(list_row[1])
    except:
        print(f"Problem with the file {obj.key}")
        pass
    return pre_labeled

def export_list_as_excel(file, list_to_save, name_of_sheet):
    df = pd.DataFrame(list_to_save)
    book = load_workbook(file)
    writer = pd.ExcelWriter(file, engine='openpyxl', mode = "a")
    writer.book = book
    writer.sheets = {ws.title: ws for ws in book.worksheets}
    print(name_of_sheet)
    if name_of_sheet not in writer.sheets:
        df.to_excel(writer, sheet_name = name_of_sheet, index=False, header=False)
    else:
        df.to_excel(writer, sheet_name = name_of_sheet, startrow=writer.sheets[name_of_sheet].max_row, index=False, header=False)
    writer.save()
    
def merge_dicts(old_dict, new_dict):
    for key, value in new_dict.items():
        if key in old_dict and value[0] < old_dict[key][0]:
#             print("\n\n *** ", value[0], " *** ", old_dict[key][0], " ***\n\n")
            old_dict[key] = value
        elif key not in old_dict:
            old_dict[key] = value
    return old_dict

def dict_to_sorted_list(dictionary):
    llista = []
    for value in dictionary.values():
        llista.append(value)
    llista_ordenada = sorted(llista, key=lambda x: x[0])
    return llista_ordenada<jupyter_output><empty_output><jupyter_text>Connecting to S3<jupyter_code>path = "C:/Users/jordi/Documents/claus/"
# path = "/home/propietari/Documents/claus/"
filename = "AWS_S3_keys_wri.json"
aws_id, aws_secret = aws_credentials(path, filename)
region = 'us-east-1'

bucket = 'wri-nlp-policy'

s3 = boto3.resource(
    service_name = 's3',
    region_name = region,
    aws_access_key_id = aws_id,
    aws_secret_access_key = aws_secret
)<jupyter_output><empty_output><jupyter_text>Loading the policy instruments<jupyter_code># Define queries
path = "../../input/"
filename = "English_queries.xlsx"
file = path + filename
df = pd.read_excel(file, engine='openpyxl', sheet_name = "Hoja1", usecols = "A:C")

policy_instrument = {}
for index, row in df.iterrows():
    if row['Policy instrument'] in policy_instrument:
        policy_instrument[row['Policy instrument']] = policy_instrument[row['Policy instrument']] + 1
    else:
        policy_instrument[row['Policy instrument']] = 1
policy_instrument<jupyter_output><empty_output><jupyter_text>Loading csv files<jupyter_code>path = "../../output/"
filename = "pre_labeled_English.xlsx"
file = path + filename

columns = ["sentence_id", "similarity_score", "text"]
for i, obj in enumerate(s3.Bucket(bucket).objects.all().filter(Prefix="english_documents/assisted_labeling/")):
    if not obj.key.endswith("/"):# and i < 3:
        for item in policy_intrument:
            if item in obj.key:
                export_list_as_excel(file, read_csv_from_s3(obj, columns), item)
path = "../../output/"
filename = "pre_labeled_English_ready_short.xlsx"
file = path + filename


Dictionaries = {"Direct payment" : {}, "Fine" : {}, "Loan" : {}, "Supplies" : {}, "Tax benefit" : {}, "Technical assistance" : {}}
Queries = {"Direct payment" : 0, "Fine" : 0, "Loan" : 0, "Supplies" : 0, "Tax benefit" : 0, "Technical assistance" : 0}


columns = ["sentence_id", "similarity_score", "text"]
for i, obj in enumerate(s3.Bucket(bucket).objects.all().filter(Prefix="english_documents/assisted_labeling/")):
    if not obj.key.endswith("/"):# and i < 25
        item = obj.key.split("_")[3]
        query_number = int(obj.key.split("_")[4])
        if query_number in Dictionaries[item]:
            new_list = read_csv_from_s3(obj, columns)
            Dictionaries[item][query_number] = Dictionaries[item][query_number] + new_list
        else:
            Dictionaries[item][query_number] = []
            new_list = read_csv_from_s3(obj, columns)
            Dictionaries[item][query_number] = Dictionaries[item][query_number] + new_list
for instrument in Dictionaries:
    old_dict = {}
    for query in Dictionaries[instrument]:
        temp_list = copy.deepcopy(Dictionaries[instrument][query])
        sorted_list = sorted(temp_list, key=lambda x: x[1], reverse=True)
        new_dict = {}
        for i, item in enumerate(sorted_list):
            item.insert(0, i)
            new_dict[item[1]] = item
        old_dict = merge_dicts(old_dict, new_dict)
    final_list = dict_to_sorted_list(old_dict)
            
    export_list_as_excel(file, final_list[0:501], instrument)
final_list<jupyter_output><empty_output>
{'fraction_non_alphanumeric': 0.1211372064276885, 'fraction_numerical': 0.007593148507858026, 'mean_word_length': 3.684863523573201, 'pattern_counts': {'":': 0, '<': 24, '<?xml version=': 0, '>': 21, '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': '5', 'dir': 'jupyter-structured-clean-dedup', 'id': '374903', 'license': 'RSA-MD', 'n_tokens_mistral': 1925, 'n_tokens_neox': 1869, 'n_words': 440, 'path': 'tasks/data_augmentation/notebooks/assisted_labeling/results_processing.ipynb', 'repo_name': 'thefirebanks/policy-data-analyzer', 'size': '5663'}