<jupyter_start><jupyter_text>**Get data**<jupyter_code>def get_data_from_monolingual_lang_table(lang, keys, isParaphrase, excludeInnerIdenticals=True):
    query = "SELECT * from monolingual" + lang.upper() + " WHERE isParaphrase=" + str(isParaphrase) + ";"
    cur = execute_sql(conn, query)
    rows = []
    counter = 0
    for row in cur:
        #if row['annotation_identifier'] != "Aaf/Fragment 009 01_7":
        #    continue
            
        new_row = []
        for key in keys:
            val = row[key]
            #print(type(val))
            if type(val)==unicode:
                #print(val)
                #val = val.decode('latin-1').encode("utf-8")
                val = val.encode("utf-8")
                #print(val)
                #print("")
                
            new_row.append(val)
        if excludeInnerIdenticals:
            plag = row['plagiat_sent'].lower()
            src = row['source_sent'].lower()
            if plag in src or src in plag:
                counter += 1
                continue    
        rows.append(new_row)
    
    print("# skipped (inner identicals): " + str(counter))
    return rows

def split_data(percent_test, data):
    split = int(percent_test * len(data))
    test_data = data[:split+1]
    train_data = data[split+1:]
    return train_data, test_data<jupyter_output><empty_output><jupyter_text>Include negative paraphrase pairs<jupyter_code>def addValToQuery(vals, query):
    for idx, key in enumerate(vals):
        if idx==len(vals)-1:
            query += str(key) + ") "
        else:
            query += str(key) + ", "
    return query
            
def addNegatives(conn, tablename, fakeSrcDict):
    err_count = 0
    nb_negatives = 0
    cur = execute_sql(conn, "SELECT * from " + tablename + " WHERE isParaphrase=1;")
    for row in cur:
        annotation_id = row['annotation_identifier']
        fake_src = fakeSrcDict[annotation_id]
        if not fake_src: continue
        row['annotation_identifier'] = annotation_id + "_f"
        row['source_sent'] = fake_src
        row['isParaphrase'] = 0
        
        query = "INSERT INTO " + tablename + " ("
        query = addValToQuery(row.keys(), query)
        query += " VALUES ("
        values = []
        for item in row.values():
            if type(item) == unicode:
                item = item.replace("'","\\'")
                item = item.encode('utf-8')
            try:
                values.append("'"+str(item)+"'")
            except Exception as e: 
                err_count += 1
                # print(item)
                
        query = addValToQuery(values, query)
        query += ";"
        try:
            cur2 = conn.cursor()
            cur2.execute(query)
            cur2.close()
            nb_negatives += 1
        except Exception as e:
            ## Fehler passieren durch Abostrophe im Text ... 
            print(row['url'])
            #if row['url'] == 'http://de.vroniplag.wikia.com/wiki/Ww/Fragment_016_01' or row['url'] == 'http://de.vroniplag.wikia.com/wiki/Yb/Fragment_186_14':
                #print(query)
    print("#encoding errors: {}".format(err_count))    
    print("#nb fake pairs: {}".format(nb_negatives))
    
    conn.commit()<jupyter_output><empty_output><jupyter_text>**Fake Sources**<jupyter_code># Get mapping from annotation_identifier to fake source sent
def getFakeSources(lang):
    query = "SELECT * from monolingual WHERE lang_plagiat='"+lang+"' and lang_source='"+lang+"';"
    cur_annotation = execute_sql(conn, query)
    ids_to_fake_sources = {}
    for row in cur_annotation:
        key = row['annotation_identifier']
        ids_to_fake_sources[key] = row['fake_source_sent']

    cur_annotation.close()
    return ids_to_fake_sources<jupyter_output><empty_output><jupyter_text>**Generate CSV-Files in UTF-8**<jupyter_code>keys = ['plagiat_sent', 'source_sent', 'isParaphrase', 'url']
get_data_from_monolingual_lang_table('es', keys, 1);
def write_csv_files(lang, suffix, keys):
    data_isPP = get_data_from_monolingual_lang_table(lang, keys, 1)
    print("nb paraphrases: " + str(len(data_isPP)))
    data_noPP = get_data_from_monolingual_lang_table(lang, keys, 0)
    print("nb fake-paraphrases: " + str(len(data_noPP)))

    train_data_isPP, test_data_isPP = split_data(0.2, data_isPP)
    train_data_noPP, test_data_noPP = split_data(0.2, data_noPP)

    train_data = pd.DataFrame(train_data_isPP + train_data_noPP,columns=keys)
    test_data = pd.DataFrame(test_data_isPP + test_data_noPP, columns=keys)

    print("train shape: " + str(train_data.shape))
    print("test shape: " + str(test_data.shape))
    train_data.to_csv(lang+ suffix +'_train.csv', encoding='utf-8')
    test_data.to_csv(lang+ suffix + '_test.csv', encoding='utf-8')<jupyter_output><empty_output><jupyter_text>Enrich the *annotation*-table with the columns **category**, **lang_source** and **lang_plagiat** from the *fragment*-table<jupyter_code>query = "CREATE TABLE annotationAugmented AS SELECT * from annotation"
cur = execute_sql(conn, query)
conn.commit()
cur.close()
query = "ALTER TABLE annotationAugmented ADD COLUMN lang_source VARCHAR(80), ADD COLUMN lang_plagiat VARCHAR(80), "
query += "ADD COLUMN category VARCHAR(80);"
print(query)
cur = execute_sql(conn, query)
conn.commit()
cur.close()
# Insert data
cur_fragment = execute_sql(conn, "SELECT * from fragment")
count=0
for row in cur_fragment:
    url = row['url']
    lang_original = row['lang_source']
    lang_plagiat = row['lang_plagiat']
    category = row['category']
    query = "UPDATE annotationAugmented SET lang_source='" + lang_original + "', lang_plagiat='" + lang_plagiat;
    query += "', category='" + category;
    query += "' WHERE url='" + url + "';"
    execute_sql(conn, query)
    if(count % 1000 == 0):
        print("{0} processed".format(count))
    count+=1
    
conn.commit()
cur_fragment.close()<jupyter_output><empty_output><jupyter_text>Create Monolingual database from *annotationAugmented*-table<jupyter_code>query = get_query_monolingual_table()
cur = execute_sql(conn, query)
conn.commit()
cur.close()<jupyter_output>CREATE TABLE monolingual AS SELECT annotation_identifier, url, plagiat_sent, source_sent, fake_source_sent, lang_plagiat, lang_source, bow_diff, nb_words_ratio from annotationAugmented WHERE category='Verschleierung' and lang_plagiat=lang_source;<jupyter_text>**MonolingualEN**-table<jupyter_code>query = get_query_monolingual_lang_table("monolingualEN", "en", word_ratio_min=0.5, word_ratio_max=1.5,
                                         bow_diff_min=6)
cur = execute_sql(conn, query)
conn.commit()
cur.close()
query = "ALTER TABLE monolingualEN ADD COLUMN isParaphrase TINYINT(1) DEFAULT 1"
cur = execute_sql(conn, query)
conn.commit()
cur.close()
ids_to_fake_sources = getFakeSources("en")
addNegatives(conn, "monolingualEN", ids_to_fake_sources)
# write train and test csv files
write_csv_files("en",today, keys)<jupyter_output># skipped (inner identicals): 7
nb paraphrases: 1183
# skipped (inner identicals): 0
nb fake-paraphrases: 1101
train shape: (1826, 4)
test shape: (458, 4)<jupyter_text>**MonolingualDE**-table<jupyter_code>query = get_query_monolingual_lang_table("monolingualDE", "de", word_ratio_min=0.5, word_ratio_max=1.5,
                                         bow_diff_min=6)
cur = execute_sql(conn, query)
conn.commit()
cur.close()
query = "ALTER TABLE monolingualDE ADD COLUMN isParaphrase TINYINT(1) DEFAULT 1"
cur = execute_sql(conn, query)
conn.commit()
cur.close()
ids_to_fake_sources = getFakeSources("de")
addNegatives(conn, "monolingualDE", ids_to_fake_sources)
# write train and test csv files
write_csv_files("de", today, keys)<jupyter_output># skipped (inner identicals): 29
nb paraphrases: 9501
# skipped (inner identicals): 1
nb fake-paraphrases: 8555
train shape: (14443, 4)
test shape: (3613, 4)<jupyter_text>**MonolingualES**-table<jupyter_code>query = get_query_monolingual_lang_table("monolingualES", "es", word_ratio_min=0.5, word_ratio_max=1.5,
                                         bow_diff_min=6)
cur = execute_sql(conn, query)
conn.commit()
cur.close()
query = "ALTER TABLE monolingualES ADD COLUMN isParaphrase TINYINT(1) DEFAULT 1"
cur = execute_sql(conn, query)
conn.commit()
cur.close()
###Einbauen, dass jedes annotation_identifier nur einmal eingefügt werden darf!! D.h. jedes negative Beispiel darf
### nur einmal eingebaut werden!!
# MonolingualES
ids_to_fake_sources = getFakeSources("es")
addNegatives(conn, "monolingualES", ids_to_fake_sources)
# write train and test csv files
write_csv_files("es", today, keys)
conn.close()<jupyter_output><empty_output>
{'fraction_non_alphanumeric': 0.12601249710715112, 'fraction_numerical': 0.01388567461235825, 'mean_word_length': 3.5180345007841085, 'pattern_counts': {'":': 1, '<': 34, '<?xml version=': 0, '>': 34, 'https://': 0, 'lorem ipsum': 0, 'www.': 0, 'xml': 0}, 'pii_count': 0, 'substrings_counts': 0, 'word_list_counts': {'cursed_substrings.json': 6, 'profanity_word_list.json': 0, 'sexual_word_list.json': 0, 'zh_pornsignals.json': 0}}
{'chain_length': '9', 'dir': 'jupyter-structured-clean-dedup', 'id': '622341', 'license': 'Apache-2.0', 'n_tokens_mistral': 3008, 'n_tokens_neox': 2860, 'n_words': 598, 'path': 'src/main/python/ParaphraseCleaner.ipynb', 'repo_name': 'jnehring/vroniplag-corpus', 'size': '8642'}