# ---
# jupyter:
#   jupytext:
#     text_representation:
#       extension: .py
#       format_name: light
#       format_version: '1.5'
#       jupytext_version: 1.14.4
#   kernelspec:
#     display_name: Python 3
#     language: python
#     name: python3
# ---

import copy
from difflib import SequenceMatcher
import os
import pandas as pd
from tqdm import tqdm

# <h2>Cargamos libro Excel</h2>

# +
df_main = pd.read_excel('Compilado02.xlsx', engine='openpyxl', sheet_name=None)

for key in df_main.keys():
    print(key)
    
# -

# <h2>Hacemos las comparaciones de nombres</h2>

# +
df_students01 = df_main['alumnas2020'].copy(deep=True)
df_students02 = df_main['alumnas2021'].copy(deep=True)

# This function will help us to compare strings
def similar(a, b):
    return SequenceMatcher(None, a, b).ratio()

# This one makes the comparison industrial
def gen_similar(list01, list02):
    list_similars = []
    
    for word in list01:
        ratio = 0

        for spiegel in list02:
            if ratio < max(ratio, similar(word, spiegel)):
                ratio = similar(word, spiegel)
                solution = (word, ratio, spiegel)

        list_similars.append(solution)

    return(list_similars)


list01 = df_students01['Nombre'].to_list()
list02 = df_students02['Nombre'].to_list()
list_similars = gen_similar(list01, list02)
# -

# <h2>Creamos nuestra columna relacional</h2>

# +
for row in range(df_students02.shape[0]):
    name_2021 = df_students02.at[row, 'Nombre']
    
    for i, col in enumerate(['Nombre 2020', 'Name match %']):
        value = [entry[i] for entry in list_similars if entry[-1] == name_2021]

        if len(value) != 0:
            df_students02.at[row, col] = value[0]
            
dict_final = {'students2020': df_students01, 'students2021': df_students02, 'classes': df_main['clases']}

# Adicionalmente, se guarda el diccionario en formato de libro en Excel
writer = pd.ExcelWriter('test.xlsx', engine='xlsxwriter')

for df_name, df in dict_final.items():
    df.to_excel(writer, sheet_name=df_name)
    
writer.save()

# +
num = 289
den = 2

while den < 100:
    if num % den == 0:
        print(den, num/den)
        break
    
    den += 1
# -

# <h2>Agregamos los nombres del 2021 como llave</h2>

# +
names_2020 = dict_final['students2020']['Nombre'].to_list()
names_2021 = dict_final['students2021']['Nombre'].to_list()

df_2020 = dict_final['students2021'].copy(deep=True)
df_2021 = dict_final['students2021'].copy(deep=True)
df_classes = dict_final['classes'].copy(deep=True)

# We create our new columns with None values for every row
df_classes['name 2020'], df_classes['ratio 2020'], df_classes['email 2020'] = None, None, None
df_classes['name 2021'], df_classes['ratio 2021'], df_classes['email 2021'] = None, None, None

# A list called "cols" is created to order our columns in the future
cols = [col for col in df_classes]

# Through an iteration the best match-ratio gets chosen
for i, row in enumerate(tqdm(range(df_classes.shape[0]))):
    name = df_classes.at[row, 'Nombre']
    solution_2020, solution_2021 = None, None
    ratio_2020, ratio_2021 = 0, 0

    for name_2020 in names_2020:
        if ratio_2020 < max(ratio_2020, similar(name, name_2020)):
            ratio_2020 = similar(name, name_2020)
            solution_2020 = name_2020
    
    for name_2021 in names_2021:
        if ratio_2021 < max(ratio_2021, similar(name, name_2021)):
            ratio_2021 = similar(name, name_2021)
            solution_2021 = name_2021
    
    # In this line we finally fill our rows with the corresponding information
    df_classes.at[row, 'name 2020'], df_classes.at[row, 'ratio 2020'] = solution_2020, ratio_2020
    df_classes.at[row, 'name 2021'], df_classes.at[row, 'ratio 2021'] = solution_2021, ratio_2021

    dict_aux = {solution_2020: ('email 2020', df_2020), solution_2021: ('email 2021', df_2021)}
    
    # Agregamos los correos
    for key, value in dict_aux.items():
        try:
            df_classes.at[row, value[0]] = value[1][value[1]['Nombre'] == key]['Mail'].values[0].strip()
        except:
            pass
    
# This line orders our dataframe columns as wanted
df_classes = df_classes[[cols[0]] + cols[-6:] + cols[1:-6]]

# Finally, we update our dictionary, generate a new Excel file, and display our edited dataframe
dict_final = {'students2020': df_students01, 'students2021': df_students02, 'classes': df_classes}

writer = pd.ExcelWriter('esperemos_que_funcione.xlsx', engine='xlsxwriter')

for df_name, df in dict_final.items():
    df.to_excel(writer, sheet_name=df_name)
    
writer.save()

display(df_classes)
# -

for key in dict_final.keys():
    print(key)

display(dict_final['students2021'])

# <h2>Preparación de la hoja de clases</h2>

# +
df_test = dict_final['classes'].copy(deep=True)
df_2021 = dict_final['students2021'].copy(deep=True)

cols = [col for col in df_test]
df_test['Correos'] = None

for row in range(df_test.shape[0]):
    current_row = df_test.loc[row]
    name = current_row['name 2021']
    
    # En las siguientes líneas corregimos el error en la fecha del workshop
    if (current_row['Grupo'] == 'Workshop') and (current_row['Días'] == 'Sa'):
        df_test.at[row, 'Inicio'] = current_row['Inicio'].replace(year=2021)
        df_test.at[row, 'Termino'] = current_row['Termino'].replace(year=2021)
        
    # Agregamos los correos
    try:
        df_test.at[row, 'Correos'] = df_2021[df_2021['Nombre'] == name]['Mail'].values[0].strip()
    except:
        pass
        
# Utilizando las columnas dejamos la llave al comienzo
df_test = df_test[[cols[-1]] + ['Correos'] + cols[1:-1] + [cols[0]]]

# Eliminamos los grupos que no iniciaron en 2021, ordenamos por fecha y se borran duplicados
df_test = df_test[(df_test['Inicio'].dt.year == 2021) & (df_test['Grupo'] != 'Workshop')]
df_test = df_test.sort_values(by='Inicio', ascending=False)
#df_test = df_test.drop_duplicates(subset='name 2021', keep='last')

df_test.to_excel('mailing_with_duplicates.xlsx')
display(df_test)
# -

# <h1 align='center'>Hasta aquí código funcional</h1>

# <h2>Comparisons as dictionaries</h2>

# +
df_students01 = df_main['alumnas2020'].copy(deep=True)
df_students02 = df_main['alumnas2021'].copy(deep=True)


def similar(a, b):
    return SequenceMatcher(None, a, b).ratio()


def gen_similar(title01, list01, title02, list02, title_ratio):
    dict_similars = {title01: [], title_ratio: [], title02: []}
    
    for word in list01:
        ratio = 0

        for spiegel in list02:
            if ratio < max(ratio, similar(word, spiegel)):
                ratio = similar(word, spiegel)
                solution = {title01: word, title_ratio: ratio, title02: spiegel}
                
        for key in dict_similars.keys():
                dict_similars[key].append(solution[key])

    return(dict_similars)


list01 = df_students01['Nombre'].to_list()
list02 = df_students02['Nombre'].to_list()
dict_similars = gen_similar('2020 names', list01, '2021 names', list02, '2020 to 2021 names')

# +
df_classes = df_main['clases'].copy(deep=True)


def gen_similar(list01, list02):
    list_similars = []
    
    for word in list01:
        ratio = 0

        for spiegel in list02:
            if ratio < max(ratio, similar(word, spiegel)):
                ratio = similar(word, spiegel)
                solution = (word, ratio, spiegel)

        list_similars.append(solution)

    return(list_similars)


list01 = df_students02['Nombre'].to_list()
list02 = df_classes['Nombre'].to_list()
list_similars = gen_similar(list01, list02)

print(df_classes['Nombre'][0], list_similars)

for row in range(df_classes.shape[0]):
    name_2021 = df_classes.at[row, 'Nombre']
    
    for i, col in enumerate(['Nombre 2021', 'Name match %']):
        value = [entry[i] for entry in list_similars if entry[-1] == name_2021]

        if len(value) != 0:
            df_classes.at[row, col] = value[0]
            
display(df_classes)

StarcoderdataJupyterScriptsDedupFiltered