text:
import docx
from docx.shared import Pt
from docx.enum.text import WD_ALIGN_PARAGRAPH, WD_BREAK
from docx.shared import Cm
import os
import math
import pandas as pd
import numpy as np
import re
from datetime import date
import streamlit as st
import json
import glob
from PIL import Image
import smtplib
import docx2pdf
import shutil
import zipfile
from datetime import datetime
import platform
import matplotlib.pyplot as plt

def User_validation():

    f=open("Validation/Validation.json","r")
    past=json.loads(f.read())
    f.close()

    now=datetime.now()
    dt_string = now.strftime("%d/%m/%Y %H:%M")
    time_past=datetime.strptime(past['Acceso']["Hora"], "%d/%m/%Y %H:%M")

    timesince = now - time_past
    Time_min= int(timesince.total_seconds() / 60)

    bool_negate = Time_min<120

    if not bool_negate:
        past['Acceso'].update({"Estado":"Negado"})
        str_json_0=json.dumps(past, indent=4)
        J_f=open("Validation/Validation.json","w")
        J_f.write(str_json_0)
        J_f.close()

    bool_aprove= past['Acceso']["Estado"]=="Aprovado"

    if not bool_aprove:

        colums= st.columns([1,2,1])

        with colums[1]:
            #st.image("Imagenes/Escudo_unal.png")
            st.subheader("Ingrese el usuario y contraseña")
            Usuario=st.text_input("Usuario")
            Clave=st.text_input("Contraseña",type="password")

        Users=["Gestor Comercial"]
        bool_user = Usuario in Users
        bool_clave = (Clave)==("1234")

        bool_user_email = past['Acceso']["User"] == Usuario

        bool_time2 = Time_min<1000

        bool_1 =  bool_time2 and bool_user_email

        bool_2 = bool_user and bool_clave

        if not bool_user_email and  bool_2:

            past['Acceso'].update({"User":Usuario,"Estado":"Aprovado","Hora":dt_string})
            str_json_0=json.dumps(past, indent=4)
            J_f=open("Validation/Validation.json","w")
            J_f.write(str_json_0)
            J_f.close()

        if not bool_2:
            if (Usuario != "") and (Clave!=""):
                with colums[1]:
                    st.warning("Usuario o contraseña incorrectos.\n\n Por favor intente nuevamente.")

        elif bool_2 and not bool_1:

            past['Acceso'].update({"User":Usuario,"Estado":"Aprovado","Hora":dt_string})
            str_json_0=json.dumps(past, indent=4)
            J_f=open("Validation/Validation.json","w")
            J_f.write(str_json_0)
            J_f.close()

            EMAIL_ADDRESS = 'ASPproyectos2021@gmail.com'
            EMAIL_PASSWORD = 'Samuelmamahuevo2021'

            try:

                with smtplib.SMTP('smtp.gmail.com', 587) as smtp:
                    smtp.ehlo()
                    smtp.starttls()
                    smtp.ehlo()

                    smtp.login(EMAIL_ADDRESS, EMAIL_PASSWORD)

                    subject = 'Acceso aplicacion Julia'
                    body = 'Acceso usuario ' + Usuario +' el '+dt_string

                    msg = f'Subject: {subject}\n\n{body}'
                    smtp.sendmail(EMAIL_ADDRESS, EMAIL_ADDRESS, msg)
            except:
                pass
            with colums[1]:
                st.button("Acceder a la aplicación")
        elif bool_2:

            past['Acceso'].update({"Estado":"Aprovado","Hora":dt_string,"User":Usuario})
            str_json_0=json.dumps(past, indent=4)
            J_f=open("Validation/Validation.json","w")
            J_f.write(str_json_0)
            J_f.close()

            with colums[1]:
                st.button("Acceder a la aplicación")

    return bool_aprove
def Num_dias(leng):
    if leng==1:
        return "1 día"
    else:
        return str(leng) + " días"
def day_week(dia):
    if dia ==0:
        Dia="Lunes"
    elif dia ==1:
        Dia="Martes"
    elif dia ==2:
        Dia="Miércoles"
    elif dia ==3:
        Dia="Jueves"
    elif dia ==4:
        Dia="Viernes"
    elif dia ==5:
        Dia="Sábado"
    elif dia ==6:
        Dia="Domingo-Festivo"

    return Dia
def remove_row(table, row):
    tbl = table._tbl
    tr = row._tr
    tbl.remove(tr)

def Range_fecha(dates):

    if len(dates)==1:
        return pd.to_datetime(dates[0]).strftime('%Y-%m-%d')
    else:
        return pd.to_datetime(dates[0]).strftime('%Y-%m-%d')+" hasta "+ pd.to_datetime(dates[-1]).strftime('%Y-%m-%d')

def any2str(obj):
    if isinstance(obj, str):
        return obj
    elif math.isnan(obj):
        return ""
    elif isinstance(obj, int):
        return str(obj)
    elif isinstance(obj, float):
        return str(obj)

def dt_fechas(data,data_user,Fechas,tipo_dia):
    dt_Final=pd.DataFrame(columns=["Dia","Fecha","Requerimiento","Respaldo"])
    for dia in Fechas:
        data_fecha=data_user[data_user["Fecha"]== dia]
        data_dia_todos=data[data["Fecha"]==dia]
        try:
            d_week=tipo_dia[Tipo_dia["FECHA"]==dia]["TIPO D"].to_numpy()[0]
        except:
            st.warning("Actualizar el calendario del excel extra")
            d_week=day_week(pd.Series(data=dia).dt.dayofweek.to_numpy()[0])

        df=pd.DataFrame([[d_week,dia,data_dia_todos["CANTIDAD"].sum(),data_fecha["CANTIDAD"].sum()]],columns=["Dia","Fecha","Requerimiento","Respaldo"])
        dt_Final=dt_Final.append(df, ignore_index=True)

    return dt_Final

def dt_fechas_2(data,data_user,Fechas,tipo_dia):
    dt_Final=pd.DataFrame(columns=["Dia","Fecha","Requerimiento","Respaldo"])
    for dia in Fechas:
        data_fecha=data_user[data_user["FECHA"]== dia]
        data_dia_todos=data[data["FECHA"]==dia]
        try:
            d_week=tipo_dia[Tipo_dia["FECHA"]==dia]["TIPO D"].to_numpy()[0]
        except:
            st.warning("Actualizar el calendario del excel extra")
            d_week=day_week(pd.Series(data=dia).dt.dayofweek.to_numpy()[0])

        df=pd.DataFrame([[d_week,dia,data_dia_todos["CANTIDAD"].sum(),data_fecha["CANTIDAD"].sum()]],columns=["Dia","Fecha","Requerimiento","Respaldo"])
        dt_Final=dt_Final.append(df, ignore_index=True)

    return dt_Final

def dt_fechas_3(data,data_user,Fechas,tipo_dia):
    dt_Final=pd.DataFrame(columns=["Dia","Fecha","Respaldo","P_neto","TRM","PRECIO PONDERADO"])
    for dia in Fechas:
        data_fecha=data_user[data_user["FECHA"]== dia]

        try:
            d_week=tipo_dia[Tipo_dia["FECHA"]==dia]["TIPO D"].to_numpy()[0]
        except:
            st.warning("Actualizar el calendario del excel extra")
            d_week=day_week(pd.Series(data=dia).dt.dayofweek.to_numpy()[0])

        df=pd.DataFrame([[d_week,dia,data_fecha["CANTIDAD"].sum(),data_fecha["P NETO"].sum(),round(data_fecha["TRM"].mean(),2),round(data_fecha["PRECIO PONDERADO"].mean(),2)]],
                        columns=["Dia","Fecha","Respaldo","P_neto","TRM","PRECIO PONDERADO"])
        dt_Final=dt_Final.append(df, ignore_index=True)

    return dt_Final

def Mes_espa(mes):

    if mes =="01":
        Mes="Enero"
    elif mes =="02":
        Mes="Febrero"
    elif mes =="03":
        Mes="Marzo"
    elif mes =="04":
        Mes="Abril"
    elif mes =="05":
        Mes="Mayo"
    elif mes =="06":
        Mes="Junio"
    elif mes =="07":
        Mes="Julio"
    elif mes =="08":
        Mes="Agosto"
    elif mes =="09":
        Mes="Septiembre"
    elif mes =="10":
        Mes="Octubre"
    elif mes =="11":
        Mes="Noviembre"
    elif mes =="12":
        Mes="Diciembre"

    return Mes

def F_Liq_pag(mes,ano):

    if mes%12 ==1:
        Fecha ="Enero"

    elif mes%12 ==2:
        Fecha ="Febrero"

    elif mes%12 ==3:
        Fecha ="Marzo"

    elif mes%12 ==4:
        Fecha ="Abril"

    elif mes%12 ==5:
        Fecha ="Mayo"

    elif mes%12 ==6:
        Fecha ="Junio"

    elif mes%12 ==7:
        Fecha ="Julio"

    elif mes%12 ==8:
        Fecha="Agosto"

    elif mes%12 ==9:
        Fecha="Septiembre"

    elif mes%12 ==10:
        Fecha="Octubre"

    elif mes%12 ==11:
        Fecha="Noviembre"

    elif mes%12 ==0:
        Fecha="Diciembre"

    if mes > 12:
        Fecha += " "+ str(ano+1)
    else:
        Fecha += " "+ str(ano)
    return Fecha

def num2money(num):
    if num < 1e3:
        return str(round(num,2))
    elif num < 1e6:
        return str(round(num*1e3/1e6,2))+ " miles."
    elif num < 1e9:
        return str(round(num*1e3/1e9,2))+ " mill."
    elif num < 1e12:
        return str(round(num*1e3/1e12,2))+ " mil mill."

def mes_espa(mes):

    if mes =="01":
        Mes="enero"
    elif mes =="02":
        Mes="febrero"
    elif mes =="03":
        Mes="marzo"
    elif mes =="04":
        Mes="abril"
    elif mes =="05":
        Mes="mayo"
    elif mes =="06":
        Mes="junio"
    elif mes =="07":
        Mes="julio"
    elif mes =="08":
        Mes="agosto"
    elif mes =="09":
        Mes="septiembre"
    elif mes =="10":
        Mes="octubre"
    elif mes =="11":
        Mes="noviembre"
    elif mes =="12":
        Mes="diciembre"

    return Mes

def mes_num(mes):
    Opciones2=("Enero","Febrero","Marzo","Abril","Mayo","Junio","Julio","Agosto","Septiembre","Octubre","Noviembre","Diciembre")
    if mes == Opciones2[0]:
        Mes="01"
    elif mes == Opciones2[1]:
        Mes="02"
    elif mes == Opciones2[2]:
        Mes="03"
    elif mes == Opciones2[3]:
        Mes="04"
    elif mes == Opciones2[4]:
        Mes="05"
    elif mes == Opciones2[5]:
        Mes="06"
    elif mes == Opciones2[6]:
        Mes="07"
    elif mes == Opciones2[7]:
        Mes="08"
    elif mes == Opciones2[8]:
        Mes="09"
    elif mes == Opciones2[9]:
        Mes="10"
    elif mes == Opciones2[10]:
        Mes="11"
    elif mes == Opciones2[11]:
        Mes="12"

    return Mes

def dia_esp(dia):

    if dia =="01":
        Dia="1"
    elif dia =="02":
        Dia="2"
    elif dia =="03":
        Dia="3"
    elif dia =="04":
        Dia="4"
    elif dia =="05":
        Dia="5"
    elif dia =="06":
        Dia="6"
    elif dia =="07":
        Dia="7"
    elif dia =="08":
        Dia="8"
    elif dia =="09":
        Dia="9"
    else :
        Dia = dia

    return Dia

def set_font(rows,fila,col,size):

    run=rows[fila].cells[col].paragraphs[0].runs
    font = run[0].font
    font.size= Pt(size)
    font.name = 'Tahoma'
def replace_text_for_image(paragraph, key, value,wid,hei):

    if key in paragraph.text:
        inline = paragraph.runs
        for item in inline:
            if key in item.text:
                item.text = item.text.replace(key, "")

        for val in value:
            r = paragraph.add_run()
            r.add_picture(val,width=Cm(wid), height=Cm(hei))

def replace_text_in_paragraph(paragraph, key, value):
    if key in paragraph.text:
        inline = paragraph.runs

        for item in inline:
            if key in item.text:
                item.text = item.text.replace(key, value)

def delete_columns(table, columns):
    # sort columns descending
    columns.sort(reverse=True)

    grid = table._tbl.find("w:tblGrid", table._tbl.nsmap)
    for ci in columns:
        for cell in table.column_cells(ci):
            cell._tc.getparent().remove(cell._tc)

        # Delete column reference.
        col_elem = grid[ci]
        grid.remove(col_elem)

st.set_page_config(
	layout="centered",  # Can be "centered" or "wide". In the future also "dashboard", etc.
	initial_sidebar_state="auto",  # Can be "auto", "expanded", "collapsed"
	page_title="JULIA RD",  # String or None. Strings get appended with "• Streamlit".
	page_icon="📊",  # String, anything supported by st.image, or None.
)

if User_validation():
#if True:

    Opciones1=("Oferta Firme de Respaldo","Certificado de Reintegros","Informe Comercial")
    eleccion=st.sidebar.selectbox('Seleccione el proyecto',Opciones1)

    #if False:
    if eleccion==Opciones1[0]:

        st.header("Creación ofertas firmes de respaldo")
        st.subheader("Introducción de los documentos")

        colums= st.columns([1,1,1])
        with colums[0]:
            uploaded_file_1 = st.file_uploader("Suba el consolidado base")
        with colums[1]:
            uploaded_file_2 = st.file_uploader("Suba la plantilla del documento")
        with colums[2]:
            uploaded_file_3 = st.file_uploader("Suba el excel adicional")
        if (uploaded_file_1 is not None) and (uploaded_file_2 is not None) and (uploaded_file_3 is not None):
            try:
                data=pd.read_excel(uploaded_file_1)
                Extras=pd.read_excel(uploaded_file_3,sheet_name="Usuarios")
                Tipo_dia=pd.read_excel(uploaded_file_3,sheet_name="Calendario")
            except:
                st.warning("Recuerde que el formato del Excel tiene que ser xls")
            data["Fecha"]=data["FECHAINI"].dt.to_pydatetime()

            if data["USUARIO"].isnull().values.any():
                st.warning("Revisar archivo de consolidado base, usuario no encontrado.")
                data.dropna(subset = ["USUARIO"], inplace=True)
                Users=pd.unique(data["USUARIO"])
            else:
                Users=pd.unique(data["USUARIO"])

            Extras=pd.read_excel(uploaded_file_3,sheet_name="Usuarios")
            Tipo_dia=pd.read_excel(uploaded_file_3,sheet_name="Calendario")
            template_file_path = uploaded_file_2

            today =  date.today()
            fecha=dia_esp(today.strftime("%d")) +" de "+ mes_espa(today.strftime("%m")) +" de "+ today.strftime("%Y")

            colums= st.columns([1,4,1])
            with colums[1]:

                st.subheader("Introducción de las variables")

                P_bolsa=st.text_input("Introduzca el Precio de Escasez de Activación",value="10.00")
                P_contrato=st.text_input("Introduzca el precio del contrato [USD]",value="10.00")
                P_TMR=st.text_input("Introduzca el valor de la TRM",value="3,950.00")
                F_TRM = st.date_input("Seleccione la fecha del valor de la TRM:",value=today).strftime("%Y-%m-%d")
                Agente_extra = st.text_input("Introduzca el nombre particular del agente")

            columns_2 = st.columns([1,2,2,1])

            Opciones2=("Enero","Febrero","Marzo","Abril","Mayo","Junio","Julio","Agosto","Septiembre","Octubre","Noviembre","Diciembre")
            Opciones3=("I","II","III","IV","V")

            with columns_2[1]:
                eleccion2=st.selectbox('Seleccione el mes de la OFR',Opciones2)
            with columns_2[2]:
                eleccion3=st.selectbox('Selecciona la semana de la OFR',Opciones3)

            if Agente_extra:
                Agente_extra="_"+Agente_extra
            else:
                Agente_extra=""
            columns_3 = st.columns([2,1,2])

            with columns_3[1]:
                if platform.system()=='Windows':
                    b=st.checkbox("PDF")
                else:

                    b=False
                a=st.button("Crear los documentos")

            Ruta="Documentos/OFR/"+str(today.year)+"/"+mes_num(eleccion2)+"-"+eleccion2 +"/"+ eleccion3
            Ruta_x="Documentos_exportar/"

            if os.path.exists(Ruta_x):

                shutil.rmtree(Ruta_x)
                Ruta_x=Ruta_x+"/"
            Ruta_x=Ruta_x+"/"
            os.makedirs(Ruta_x, exist_ok=True)

            if a:

                try:
                    path1 = os.path.join(Ruta)
                    shutil.rmtree(path1)
                    os.makedirs(Ruta, exist_ok=True)
                except:
                    os.makedirs(Ruta, exist_ok=True)

                Ruta_word=Ruta+"/Word"
                Ruta_pdf=Ruta+"/PDF"

                Info ={"Ruta": Ruta,
                      "File_names": None
                    }

                File_names=[]

                os.makedirs(Ruta_word, exist_ok=True)
                if b:

                    os.makedirs(Ruta_pdf, exist_ok=True)

                zf = zipfile.ZipFile(
                    "Resultado.zip", "w", zipfile.ZIP_DEFLATED)
                my_bar=st.progress(0)
                steps=len(Users)
                steps_done=0

                for usuario in Users:

                    data_user=data.copy()
                    data_user=data_user[data_user["USUARIO"]==usuario]
                    Empresas = pd.unique(data_user["agente1"])

                    Respaldo = data[data["USUARIO"]== usuario]["CANTIDAD"].sum()
                    Fechas = pd.unique(data_user["Fecha"])

                    R_fechas = Range_fecha(Fechas)

                    Data_frame_fechas=dt_fechas(data.copy(),data_user,Fechas,Tipo_dia)

                    try:
                        Email = str(Extras[Extras["USUARIO"] == usuario]["CORREO"].values)
                        Porc_come = Extras[Extras["USUARIO"] == usuario]["MARGEN"].values[0]
                    except:

                        Email = ""
                        Porc_come = 0.1
                        st.warning("No hay coincidencia en el Excel de usuarios para: "+usuario)

                    Email = re.sub("\[|\]|\'|0","",Email)

                    tx_empresas=""
                    for idx ,val in enumerate(Empresas):

                        if len(Empresas)<4:
                            val_2=val[0:3]
                            tx_empresas += val_2
                            if idx==len(Empresas)-1:
                                pass
                            else:
                                tx_empresas +=", "

                        else:
                            tx_empresas += "Los Generadores"

                    P_kwh=float(re.sub(",","",P_TMR))*float(P_contrato)/1000
                    Ingreso=int(P_kwh*Respaldo)
                    C_comer=int(Ingreso*Porc_come)
                    C_GMS=int(Ingreso*4/1000)
                    I_NETO=Ingreso-C_comer-C_GMS

                    if len(Data_frame_fechas.index.values)>13:
                        Enter="\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n"
                    else:
                        Enter=""

                    variables = {
                        "${FECHA}": fecha,
                        "${MES}": eleccion2,
                        "${AGENTES}": tx_empresas,
                        "${USUARIO}": usuario,
                        "${PRECIO_BOLSA}": P_bolsa,
                        "${PRECIO_CONTRATO}": P_contrato,
                        "${FECHA_TRM}": F_TRM,
                        "${PRECIO_TRM}": P_TMR,
                        "${EMAIL_USUARIO}": Email,
                        "${PRECIO_PKWH}":str(round(P_kwh,2)),
                        "${PORC_COMER}":str(int(Porc_come*100))+"%",
                        "${RESPALDO_TOT}":f'{Respaldo:,}',
                        "${INGRESO}":f'{Ingreso:,}',
                        "${COST_COME}":f'{C_comer:,}',
                        "${COST_GMS}":f'{C_GMS:,}',
                        "${INGRESO_NETO}": f'{I_NETO:,}',
                        "${NUM_DIAS}":Num_dias(len(Fechas)),
                        "${RANGO_FECHAS_1}": R_fechas,
                        "${ENTER}": Enter,
                        "${MES_LIQUIDACION}": F_Liq_pag(Opciones2.index(eleccion2)+2,int(today.strftime("%Y"))),
                        "${MES_PAGO}":  F_Liq_pag(Opciones2.index(eleccion2)+3,int(today.strftime("%Y"))),
                        "${INDICADOR}": eleccion3
                    }

                    template_document = docx.Document(template_file_path)

                    for variable_key, variable_value in variables.items():

                        for section in template_document.sections:
                            for paragraph in section.header.paragraphs:
                                replace_text_in_paragraph(paragraph, variable_key, variable_value)

                        for paragraph in template_document.paragraphs:

                            replace_text_in_paragraph(paragraph, variable_key, variable_value)

                        for table in template_document.tables:
                            for col in table.columns:
                                for cell in col.cells:
                                    for paragraph in cell.paragraphs:
                                        replace_text_in_paragraph(paragraph, variable_key, variable_value)

                    rows = template_document.tables[1].rows
                    index_1=Data_frame_fechas.index.values
                    Acum_Req=0
                    Acum_Res=0
                    for idx in index_1:

                        rows[int(idx)+1].cells[0].text = Data_frame_fechas.iloc[idx]["Dia"]

                        rows[int(idx)+1].cells[1].text = Data_frame_fechas.iloc[idx]["Fecha"].strftime('%Y-%m-%d')

                        rows[int(idx)+1].cells[2].text = f'{Data_frame_fechas.iloc[idx]["Requerimiento"]:,}'
                        Acum_Req += Data_frame_fechas.iloc[idx]["Requerimiento"]
                        rows[int(idx)+1].cells[3].text = f'{Data_frame_fechas.iloc[idx]["Respaldo"]:,}'
                        Acum_Res += Data_frame_fechas.iloc[idx]["Respaldo"]

                        for idx_2 in range(0,4):
                            run=rows[int(idx)+1].cells[idx_2].paragraphs[0].runs
                            font = run[0].font
                            font.size= Pt(10)
                            font.name = 'Tahoma'

                    for idx in np.arange(len(index_1)+1,37):

                        remove_row(template_document.tables[1], rows[len(index_1)+1])

                    rows[-1].cells[1].text = Num_dias(len(Fechas))
                    rows[-1].cells[2].text = f'{Acum_Req:,}'
                    rows[-1].cells[3].text = f'{Acum_Res:,}'

                    version=1

                    template_document.save(Ruta_x+usuario+"_OFR"+Agente_extra+"_"+eleccion2+"_"+str(today.year)+".docx")
                    zf.write(Ruta_x+usuario+"_OFR"+Agente_extra+"_"+eleccion2+"_"+str(today.year)+".docx")
                    if b:

                        docx2pdf.convert(Ruta_x+usuario+"_OFR"+Agente_extra+"_"+eleccion2+"_"+str(today.year)+".docx",
                                         Ruta_pdf+"/"+usuario+"_OFR"+Agente_extra+"_"+eleccion2+"_"+str(today.year)+".pdf")
                        zf.write(Ruta_pdf+"/"+usuario+"_OFR"+Agente_extra+"_"+eleccion2+"_"+str(today.year)+".pdf")
                    File_names.extend([usuario+"_OFR"+Agente_extra+"_"+eleccion2+"_"+str(today.year)+".docx"])

                    steps_done += 1
                    my_bar.progress(int(steps_done*100/steps))

                Info.update({"File_names":File_names})
                json_info = json.dumps(Info, indent = 4)
                with open(Ruta_x+'/00_data.json', 'w') as f:
                    json.dump(json_info, f)
                zf.write(Ruta_x+'/00_data.json')
                zf.close()

                with open("Resultado.zip", "rb") as fp:
                    with columns_3[1]:
                        btn = st.download_button(
                            label="Descargar resultados",
                            data=fp,
                            file_name="Resultado.zip",
                            mime="application/zip"
                        )

        else:
            st.warning("Necesita subir los tres archivos")
    #elif False:
    elif eleccion==Opciones1[1]:
        st.header("Creación certificados de reintegros")
        st.subheader("Introducción de los documentos")
        if True:
            colums= st.columns([1,1,1])
            with colums[0]:
                uploaded_file_1 = st.file_uploader("Suba el documento de liquidación")
            with colums[1]:
                uploaded_file_2 = st.file_uploader("Suba la plantilla del documento")
            with colums[2]:
                uploaded_file_3 = st.file_uploader("Suba el excel adicional")

        else:
            uploaded_file_1="Liquidacion_base.xlsm"
            uploaded_file_2="Certificado_base.docx"
            uploaded_file_3="Excel_extra_certificados.xls"

        if (uploaded_file_1 is not None) and (uploaded_file_2 is not None) and (uploaded_file_3 is not None):
            try:

                data=pd.read_excel(uploaded_file_1)
                Extras=pd.read_excel(uploaded_file_3,sheet_name="Usuarios")
                Tipo_dia=pd.read_excel(uploaded_file_3,sheet_name="Calendario")
                Agentes=pd.read_excel(uploaded_file_3,sheet_name="Agentes")
            except:
                st.warning("Recuerde que el formato del Excel tiene que ser xls")

            data["FECHA"]=data["FECHA"].dt.to_pydatetime()

            if data["USUARIO"].isnull().values.any():
                st.warning("Revisar archivo de consolidado base, usuario no encontrado.")
                data.dropna(subset = ["USUARIO"], inplace=True)
                Users=pd.unique(data["USUARIO"])
            else:
                Users=pd.unique(data["USUARIO"])

            template_file_path = uploaded_file_2

            today =  date.today()
            fecha=dia_esp(today.strftime("%d")) +" de "+ mes_espa(today.strftime("%m")) +" de "+ today.strftime("%Y")

            colums= st.columns([1,4,1])
            with colums[1]:

                st.subheader("Introducción de las variables")

                F_TRM = st.date_input("Seleccione la fecha del valor de la TRM:",value=today).strftime("%Y-%m-%d")

            P_TMR=str(round(data["TRM"].mean(),2))
            columns_2 = st.columns([1,2,2,1])

            Opciones2=("Enero","Febrero","Marzo","Abril","Mayo","Junio","Julio","Agosto","Septiembre","Octubre","Noviembre","Diciembre")

            with columns_2[1]:
                eleccion3=st.number_input('Seleccione el año del cerficado',value=today.year)
            with columns_2[2]:
                eleccion2=st.selectbox('Seleccione el mes del cerficado',Opciones2)

            columns_3 = st.columns([2,1,2])

            with columns_3[1]:
                if platform.system()=='Windows':
                    b=st.checkbox("PDF")
                else:

                    b=False
                a=st.button("Crear los documentos")

            Ruta="Documentos/Certificados/"+str(eleccion3) +"/"+ mes_num(eleccion2)+"-"+eleccion2
            Ruta_x="Documentos_exportar"
            if os.path.exists(Ruta_x):
                shutil.rmtree(Ruta_x)

                Ruta_x=Ruta_x+"/"
            Ruta_x=Ruta_x+"/"
            os.makedirs(Ruta_x, exist_ok=True)

            if a:
                try:
                    path1 = os.path.join(Ruta)
                    shutil.rmtree(path1)
                    os.makedirs(Ruta, exist_ok=True)
                except:
                    os.makedirs(Ruta, exist_ok=True)

                Ruta_word=Ruta+"/Word"
                Ruta_pdf=Ruta+"/PDF"

                Info ={"Ruta": Ruta,
                      "File_names": None
                    }

                File_names=[]

                os.makedirs(Ruta_word, exist_ok=True)
                if b:

                    os.makedirs(Ruta_pdf, exist_ok=True)

                os.makedirs(Ruta_word, exist_ok=True)
                if b:

                    os.makedirs(Ruta_pdf, exist_ok=True)

                zf = zipfile.ZipFile(
                    "Resultado.zip", "w", zipfile.ZIP_DEFLATED)
                my_bar=st.progress(0)
                steps=len(Users)
                steps_done=0

                for usuario in Users:

                    data_user=data.copy()
                    data_user=data_user[data_user["USUARIO"]==usuario]
                    Empresas = pd.unique(data_user["COMPRADOR"])

                    Respaldo = data[data["USUARIO"]== usuario]["CANTIDAD"].sum()
                    Fechas = pd.unique(data_user["FECHA"])

                    R_fechas = Range_fecha(Fechas)

                    Data_frame_fechas=dt_fechas_2(data.copy(),data_user,Fechas,Tipo_dia)

                    try:
                        Email = str(Extras[Extras["USUARIO"] == usuario]["CORREO"].values)
                        Porc_come = Extras[Extras["USUARIO"] == usuario]["MARGEN"].values[0]
                    except:

                        Email = ""
                        Porc_come = 0.1
                        st.warning("No hay coincidencia en el Excel de usuarios para: "+usuario)

                    Email = re.sub("\[|\]|\'|0","",Email)

                    # tx_empresas=""
                    # for idx ,val in enumerate(Empresas):

                    #     if len(Empresas)<4:
                    #         val_2=val[0:3]
                    #         tx_empresas += val_2
                    #         if idx==len(Empresas)-1:
                    #             pass
                    #         else:
                    #             tx_empresas +=", "

                    #     else:
                    #         tx_empresas += "Los Generadores"

                    if len(Data_frame_fechas.index.values)>13:
                        Enter="\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n"
                    else:
                        Enter=""

                    REQ_MAXIMO=Data_frame_fechas["Respaldo"].max()

                    Valor_total=int(round(data_user["P NETO"].sum()))

                    variables = {
                        "${FECHA}": fecha,
                        "${MES}": eleccion2,
                        "${ANO}": str(eleccion3),
                        # "${AGENTES}": tx_empresas,
                        "${USUARIO}": usuario,
                        "${OFERTA_MAX}": f'{REQ_MAXIMO:,}',
                        "${FECHA_TRM}": F_TRM,
                        "${P_TRM}": P_TMR,
                        "${EMAIL_USUARIO}": Email,
                        "${PORC_COMER}":str(int(Porc_come*100))+"%",
                        "${RESPALDO_TOT}":"$ "+f'{Valor_total:,}',
                        "${NUM_DIAS}":Num_dias(len(Fechas)),
                        "${RANGO_FECHAS_1}": R_fechas,
                        "${ENTER}": Enter,
                        "${MES_LIQUIDACION}": F_Liq_pag(Opciones2.index(eleccion2)+2,int(today.strftime("%Y"))),
                        "${MES_PAGO}":  F_Liq_pag(Opciones2.index(eleccion2)+3,int(today.strftime("%Y"))),
                        "${INDICADOR}": eleccion3
                    }

                    template_document = docx.Document(template_file_path)

                    for variable_key, variable_value in variables.items():

                        for section in template_document.sections:
                            for paragraph in section.header.paragraphs:

                                replace_text_in_paragraph(paragraph, variable_key, variable_value)

                        for paragraph in template_document.paragraphs:

                            replace_text_in_paragraph(paragraph, variable_key, variable_value)

                        for table in template_document.tables:
                            for col in table.columns:
                                for cell in col.cells:
                                    for paragraph in cell.paragraphs:
                                        replace_text_in_paragraph(paragraph, variable_key, variable_value)

                    rows = template_document.tables[1].rows
                    index_1=Agentes.index.values
                    Total_ingreso=0
                    Total_respaldo=0

                    dt_participa=pd.DataFrame(columns=["OFR","INGRESO","FILA_1","FILA_2"])

                    for idx in index_1:
                        run=rows[int(idx)+3].cells[0].paragraphs[0]
                        rows[int(idx)+3].cells[0].text = "Precio "+Agentes.iloc[idx]["AGENTE"]
                        rows[int(idx)+3].cells[1].text = f'{Agentes.iloc[idx]["PRECIO"]:,}'
                        rows[int(idx)+3].cells[2].text = f'{round(float(re.sub(",","",P_TMR))*Agentes.iloc[idx]["PRECIO"]/1000,2):,}'

                        Respaldo=data_user[data_user["OFR"]==Agentes.iloc[idx]["OFR"]]["CANTIDAD"].sum()

                        rows[int(idx)+15].cells[0].text = "Respaldo "+Agentes.iloc[idx]["AGENTE"]
                        rows[int(idx)+15].cells[1].text = "kWh"
                        rows[int(idx)+15].cells[2].text = f'{Respaldo:,}'

                        for idx_2 in range(0,3):
                            run=rows[int(idx)+3].cells[idx_2].paragraphs[0].runs
                            font = run[0].font
                            font.size= Pt(10)
                            font.name = 'Tahoma'

                        for idx_2 in range(0,3):
                            run=rows[int(idx)+15].cells[idx_2].paragraphs[0].runs
                            font = run[0].font
                            font.size= Pt(10)
                            font.name = 'Tahoma'
                            if idx_2==0:

                                font.bold= True

                        Total_respaldo += Respaldo

                        df=pd.DataFrame([[Agentes.iloc[idx]["OFR"],Respaldo,int(idx)+3,int(idx)+15]],columns=["OFR","INGRESO","FILA_1","FILA_2"])
                        dt_participa=dt_participa.append(df, ignore_index=True)

                        Total_ingreso += Respaldo*float(re.sub(",","",P_TMR))*Agentes.iloc[idx]["PRECIO"]/1000

                    filas=[22,24,25,26,28]
                    valores=[Total_respaldo,
                             Total_ingreso,
                             Total_ingreso*Porc_come,
                             Total_ingreso*0.004,
                             Valor_total]

                    for idx, val in enumerate(filas):
                        rows[val].cells[2].text = f'{int(round(valores[idx])):,}'
                        run=rows[val].cells[2].paragraphs[0].runs
                        font = run[0].font
                        font.size= Pt(10)
                        font.name = 'Tahoma'

                    contador1=0
                    contador2=0
                    for idx in list(dt_participa.index):
                        if dt_participa.iloc[idx]["INGRESO"]==0:
                            remove_row(template_document.tables[1], rows[dt_participa.iloc[idx]["FILA_1"]+contador1])
                            contador1 -= 1
                            remove_row(template_document.tables[1], rows[dt_participa.iloc[idx]["FILA_2"]+contador1+contador2])
                            contador2 -= 1

                    rows = template_document.tables[2].rows
                    index_1=Data_frame_fechas.index.values
                    Acum_Req=0
                    Acum_Res=0
                    for idx in index_1:

                        rows[int(idx)+1].cells[0].text = Data_frame_fechas.iloc[idx]["Fecha"].strftime('%Y-%m-%d')

                        rows[int(idx)+1].cells[1].text = Data_frame_fechas.iloc[idx]["Dia"]

                        rows[int(idx)+1].cells[2].text = f'{Data_frame_fechas.iloc[idx]["Requerimiento"]:,}'
                        Acum_Req += Data_frame_fechas.iloc[idx]["Requerimiento"]
                        rows[int(idx)+1].cells[3].text = f'{Data_frame_fechas.iloc[idx]["Respaldo"]:,}'
                        Acum_Res += Data_frame_fechas.iloc[idx]["Respaldo"]

                        for idx_2 in range(0,4):
                            run=rows[int(idx)+1].cells[idx_2].paragraphs[0].runs
                            font = run[0].font
                            font.size= Pt(10)
                            font.name = 'Tahoma'

                    for idx in np.arange(len(index_1)+1,37):

                        remove_row(template_document.tables[2], rows[len(index_1)+1])

                    #rows[-1].cells[1].text = Num_dias(len(Fechas))
                    rows[-1].cells[2].text = f'{Acum_Req:,}'
                    rows[-1].cells[3].text = f'{Acum_Res:,}'

                    for idx_2 in range(1,4):
                        run=rows[-1].cells[idx_2].paragraphs[0].runs
                        font = run[0].font
                        font.size= Pt(10)
                        font.name = 'Tahoma'

                    # version=1
                    name_word=usuario+"_Certificado_Reintegros_"+eleccion2+"_"+str(eleccion3)+".docx"
                    name_pdf=usuario+"_Certificado_Reintegros_"+eleccion2+"_"+str(eleccion3)+".pdf"
                    template_document.save(Ruta_x+name_word)
                    zf.write(Ruta_x+name_word)
                    if b:

                        docx2pdf.convert(Ruta_x+name_word, Ruta_x+name_pdf)
                        zf.write(Ruta_x+name_pdf)
                    File_names.extend([name_word])

                    steps_done += 1
                    my_bar.progress(int(steps_done*100/steps))

                Info.update({"File_names":File_names})
                json_info = json.dumps(Info, indent = 4)
                with open(Ruta_x+'/00_data.json', 'w') as f:
                    json.dump(json_info, f)
                zf.write(Ruta_x+'/00_data.json')
                zf.close()

                with open("Resultado.zip", "rb") as fp:
                    with columns_3[1]:
                        btn = st.download_button(
                            label="Descargar resultados",
                            data=fp,
                            file_name="Resultado.zip",
                            mime="application/zip"
                        )

        else:
            st.warning("Necesita subir los tres archivos")
    #elif True:
    elif eleccion==Opciones1[2]:
        st.header("Creación consolidados mensuales")
        st.subheader("Introducción de los documentos")
        if True:
            colums= st.columns([1,1])
            with colums[0]:
                uploaded_file_1 = st.file_uploader("Suba el documento de base principal")
            with colums[1]:
                uploaded_file_2 = st.file_uploader("Suba la plantilla del documento")
            # with colums[2]:
            #     uploaded_file_3 = st.file_uploader("Suba el excel adicional")

        else:
            uploaded_file_1="Excel_base_informe.xls"
            uploaded_file_2="Plantilla_base.docx"
            # uploaded_file_3="Excel_extra_certificados.xls"

        if (uploaded_file_1 is not None) and (uploaded_file_2 is not None): #and (uploaded_file_3 is not None):
            try:

                excel_1=pd.ExcelFile(uploaded_file_1)
                Fronteras=pd.read_excel(excel_1,"FRONTERAS")
                Usuarios=pd.read_excel(excel_1,"USUARIOS")
                Year_list=[s for s in excel_1.sheet_names if "INGRESOS" in s]
                Ingresos=pd.read_excel(excel_1,Year_list)
                Tabla_3=pd.read_excel(excel_1,sheet_name="LIQUIDACION")
                Tipo_dia=pd.read_excel(excel_1,sheet_name="CALENDARIO")
                #Extras=pd.read_excel(uploaded_file_3,sheet_name="Usuarios")

                #Agentes=pd.read_excel(uploaded_file_3,sheet_name="Agentes")
            except:
                st.warning("Recuerde que el formato del Excel tiene que ser xls")

            if Usuarios["USUARIO"].isnull().values.any():
                st.warning("Revisar archivo de consolidado base, usuario no encontrado.")
                Usuarios.dropna(subset = ["USUARIO"], inplace=True)
                Users1=pd.unique(Usuarios["USUARIO"])
                Users1= Users1[Users1 != "JULIA-RD"]
            else:
                Users1=pd.unique(Usuarios["USUARIO"])
                Users1= Users1[Users1 != "JULIA-RD"]

            if Fronteras["USUARIO"].isnull().values.any():
                st.warning("Revisar archivo de consolidado base, usuario no encontrado.")
                Usuarios.dropna(subset = ["USUARIO"], inplace=True)
                Users2=pd.unique(Fronteras["USUARIO"])
                Users2= Users2[Users2 != "JULIA-RD"]
            else:
                Users2=pd.unique(Fronteras["USUARIO"])
                Users2= Users2[Users2 != "JULIA-RD"]

            if True:
                Users = Users1
            else:
                Users = np.intersect1d(Users1, Users2)

            template_file_path = uploaded_file_2

            today =  date.today()
            fecha=dia_esp(today.strftime("%d")) +" de "+ mes_espa(today.strftime("%m")) +" de "+ today.strftime("%Y")

            colums= st.columns([1,4,1])
            with colums[1]:

                st.subheader("Introducción de las variables")

            columns_2 = st.columns([1,2,2,1])

            Opciones2=("Enero","Febrero","Marzo","Abril","Mayo","Junio","Julio","Agosto","Septiembre","Octubre","Noviembre","Diciembre")

            with columns_2[1]:
                eleccion3=st.number_input('Seleccione el año del Informe Comercial',value=today.year)
            with columns_2[2]:
                eleccion2=st.selectbox('Seleccione el mes del Informe Comercial',Opciones2)

            columns_5 = st.columns([1,10,1])
            Users_options=Users.copy()
            Users_options=np.append(["TODAS LAS OPCIONES"],Users_options)
            with columns_5[1]:
                Users_eleccion=st.multiselect('Seleccione los usuarios para obtener el documento',options=Users_options,default=["TODAS LAS OPCIONES"])

            if not np.any(np.array(Users_eleccion)=="TODAS LAS OPCIONES"):
                Users=Users_eleccion

            columns_3 = st.columns([2,1,2])

            with columns_3[1]:
                if platform.system()=='Windows':
                    DPI_IMGA=150
                    b=st.checkbox("PDF")
                else:
                    DPI_IMGA=150
                    b=False
                a=st.button("Crear los documentos")

            Ruta="Documentos/Informe_Comercial/"+str(eleccion3) +"/"+ mes_num(eleccion2)+"-"+eleccion2
            Ruta_x="Documentos_exportar"
            if os.path.exists(Ruta_x):
                shutil.rmtree(Ruta_x)

                Ruta_x=Ruta_x+"/"
            else:
                Ruta_x=Ruta_x+"/"
            os.makedirs(Ruta_x, exist_ok=True)
            if a:
                try:
                    path1 = os.path.join(Ruta)
                    shutil.rmtree(path1)
                    os.makedirs(Ruta, exist_ok=True)
                except:
                    os.makedirs(Ruta, exist_ok=True)

                Ruta_word=Ruta+"/Word"
                Ruta_pdf=Ruta+"/PDF"

                Info ={"Ruta": Ruta,
                      "File_names": None
                    }

                File_names=[]

                os.makedirs(Ruta_word, exist_ok=True)
                if b:

                    os.makedirs(Ruta_pdf, exist_ok=True)

                os.makedirs(Ruta_word, exist_ok=True)
                if b:

                    os.makedirs(Ruta_pdf, exist_ok=True)

                zf = zipfile.ZipFile(
                    "Resultado.zip", "w", zipfile.ZIP_DEFLATED)
                my_bar=st.progress(0)
                text_rest = st.empty()
                steps=len(Users)
                steps_done=0

                for usuario in Users:

                    text_rest.text("Progreso: "+str(steps_done)+"/"+str(steps) + " -  Actualmente en el usuario: " +usuario )

                    data_user=Usuarios.copy()
                    data_user=data_user[data_user["USUARIO"]==usuario]

                    Num_fronteras = data_user["NUMERO DE FRONTERAS"].values[0]
                    Ene_agregada =  data_user["ENERGÍA AGREGADA"].values[0]
                    Contrato = data_user["CONTRATO"].values[0]
                    Ven_contrato = data_user["VENCIMIENTO CONTRATO"].values[0]
                    Ejecutivo=data_user["EJECUTIVO DE CUENTA"].values[0]
                    Extra_1=data_user["PUNTO FOCAL"].values[0]
                    try:
                        if math.isnan(Extra_1):
                            Extra_1 = ""
                        elif isinstance(Extra_1, int):
                            Extra_1 = str(Extra_1)
                    except:
                        pass
                    try:
                        if math.isnan(Contrato):
                            Contrato = ""
                    except:
                        pass
                    try:
                        if isinstance(Ven_contrato, datetime):
                            Ven_contrato=Ven_contrato.strftime("%d/%m/%Y")
                        elif isinstance(Ven_contrato, int):
                            Ven_contrato = str(Ven_contrato)
                        elif math.isnan(Ven_contrato):
                            Ven_contrato = ""
                    except:
                        pass
                    try:
                        Punto_focal = str(Usuarios[Usuarios["USUARIO"] == usuario]["PUNTO FOCAL"].values)
                    except:

                        Punto_focal  = ""
                        st.warning("No hay coincidencia en el Excel de usuarios para: "+usuario)

                    Punto_focal  = re.sub("\[|\]|\'|0","",Punto_focal )

                    template_document = docx.Document(template_file_path)

                    Anos_dias=[s for s in data_user.columns if "DÍAS CERTIFICADOS " in s]
                    Anos=[]

                    for value in Anos_dias:
                        if data_user[value].values[0] != 0:

                            Anos.append(value.replace('DÍAS CERTIFICADOS ', ''))

                    rows = template_document.tables[0].rows

                    contador=0
                    for idx,val in enumerate(Anos):
                        rows[3+contador].cells[0].text = f'{data_user["INGRESO "+val].values[0]:,}'
                        rows[4+contador].cells[0].text = "INGRESO "+val

                        rows[3+contador].cells[2].text = f'{data_user["DÍAS CERTIFICADOS "+val].values[0]:,}'
                        rows[4+contador].cells[2].text = "DÍAS CERTIFICADOS "+val

                        rows[3+contador].cells[3].text = f'{data_user["ENERGIA RESPALDADA "+val].values[0]:,}'
                        rows[4+contador].cells[3].text = "ENERGIA RESPALDADA "+val
                        for idx_2 in [0,2,3]:
                            set_font(rows,3+contador,idx_2,9)
                            run=rows[4+contador].cells[idx_2].paragraphs[0].runs
                            font = run[0].font
                            font.size= Pt(8.5)
                            font.name = 'Tahoma'
                            font.bold = True

                        contador += 3
                    cont_enter=0
                    for idx in np.arange(0,28-(6+contador)):

                        remove_row(template_document.tables[0], rows[-1])
                        cont_enter += 1

                    if False:
                        if len(Anos) ==0:
                            st.warning("El usuario "+ usuario+" no tiene registros anuales" )
                            Enter_final ="\n"*(cont_enter+3)
                            for paragraph in template_document.paragraphs:
                                replace_text_in_paragraph(paragraph, "${IMGENES_2}", '')
                        if len(Anos) > 2:
                            Enter_final="\n"*cont_enter

                            for paragraph in template_document.paragraphs:
                                replace_text_in_paragraph(paragraph, "${IMGENES_2}", '${IMA_INGRESOS}')
                                if "${IMA_INGRESOS}" in  paragraph.text:
                                    run = paragraph.add_run()
                                    run.add_break(WD_BREAK.PAGE)

                        elif len(Anos) == 1:
                            Enter_final="\n"*int(cont_enter/4)+'${IMA_INGRESOS}'+"\n"*int(cont_enter/4)
                            for paragraph in template_document.paragraphs:
                                replace_text_in_paragraph(paragraph, "${IMGENES_2}", '')
                        elif len(Anos) == 2:
                            Enter_final='${IMA_INGRESOS}'
                            for paragraph in template_document.paragraphs:
                                replace_text_in_paragraph(paragraph, "${IMGENES_2}", '')
                    else:
                        if len(Anos) ==0:
                            st.warning("El usuario "+ usuario+" no tiene registros anuales" )

                        # st.write(usuario)
                        # st.write(cont_enter)
                        # st.write(int(cont_enter/2))
                        if cont_enter%2==0:
                            Enter_final_1="\n"*int(cont_enter/2-1)
                            Enter_final_2="\n"*int(cont_enter/2)
                        else:
                            Enter_final_1="\n"*int(cont_enter/2)
                            Enter_final_2="\n"*int(cont_enter/2)
                        for paragraph in template_document.paragraphs:
                                replace_text_in_paragraph(paragraph, "${IMGENES_2}", '')

                    variables = {
                        "${USUARIO}": usuario,
                        "${MES}": eleccion2.upper(),
                        "${ANO}": str(eleccion3),
                        "${EJECUTIVO}":any2str(Ejecutivo),
                        "${NUM_FRONTE}": str(Num_fronteras),
                        "${ENE_AGREGADA}": f'{round(Ene_agregada):,}',
                        "${CONTRATO}": Contrato,
                        "${VEN_CONTRATO}": Ven_contrato,
                        "${PUNTO_FOC}":Extra_1,
                        "${ENTER_1}":Enter_final_1,
                        "${ENTER_2}":Enter_final_2
                    }

                    for variable_key, variable_value in variables.items():

                        for section in template_document.sections:
                            for paragraph in section.header.paragraphs:

                                replace_text_in_paragraph(paragraph, variable_key, variable_value)

                        for paragraph in template_document.paragraphs:

                            replace_text_in_paragraph(paragraph, variable_key, variable_value)

                        for table in template_document.tables:
                            for col in table.columns:
                                for cell in col.cells:
                                    for paragraph in cell.paragraphs:
                                        replace_text_in_paragraph(paragraph, variable_key, variable_value)
                    Ruta_img="Imagenes"
                    Meses=np.array(["Enero","Febrero","Marzo","Abril","Mayo","Junio","Julio","Agosto","Septiembre","Octubre","Noviembre","Diciembre"])

                    try:
                        pathx = os.path.join(Ruta_img)
                        shutil.rmtree(pathx)
                        os.makedirs(Ruta_img, exist_ok=True)
                    except:
                        os.makedirs(Ruta_img, exist_ok=True)

                    Meses_ano=np.array(["FECHA","Enero","Febrero","Marzo","Abril","Mayo","Junio","Julio","Agosto","Septiembre","Octubre","Noviembre","Diciembre"])

                    dt_Final=pd.DataFrame(columns=Meses_ano)

                    Imagenes_name=[]
                    color_b = [(0.94,0.49,0.15,1),(0.9,0.13,0.28,1),(0.61,0.61,0.61,1)]

                    for idx,val in enumerate(Anos):

                        name="INGRESOS "+val+".png"
                        data_graph=Ingresos["INGRESOS "+val][Ingresos["INGRESOS "+val]["USUARIO"]==usuario]
                        data_graph= data_graph.dropna(how='all', axis=1)
                        data_graph['MES'] = Meses[data_graph['FECHA'].dt.month-1]
                        if len(data_graph['MES'].unique()) != len(data_graph['MES']):
                            st.warning("Mes repetido revisar "+ "INGRESOS "+val+ " para el usuario "+ usuario)
                        Meses_gr = data_graph['MES']
                        if False:
                            fig = plt.figure(figsize=(9, 3.38))
                            ax = fig.add_axes([0,0,1,1])

                            Ingresos_gr = data_graph["INGRESOS "+val]
                            ax.bar(Meses_gr,Ingresos_gr,color=color_b[idx % 3])
                            plt.xlabel("")
                            plt.ylabel("Energía  [COP]")
                            plt.title("INGRESOS "+val)
                            max_val=Ingresos_gr.max()
                            if len(data_graph['MES'])>10:
                                plt.xticks(rotation=90)
                            plt.ylim((0,max_val*1.2))

                            for idx,value in enumerate(list(data_graph.index)):
                                    plt.text(x = idx , y = data_graph.loc[value]["INGRESOS "+val] + Ingresos_gr.max()*0.05, s = num2money(data_graph.loc[value]["INGRESOS "+val]), size = 9,ha='center',va='center')

                            plt.gca().axes.get_yaxis().set_visible(False)
                            plt.savefig(Ruta_img+"/"+name,dpi=DPI_IMGA, bbox_inches='tight',transparent=True)
                            Imagenes_name.extend([Ruta_img+"/"+name])

                        list_1=[val]

                        for idx4 in Meses:

                            if idx4 in list(Meses_gr):
                                list_1.append(data_graph["INGRESOS "+val][data_graph['MES']==idx4].iloc[0])
                            else:
                                list_1.append(0)

                        df=pd.DataFrame([list_1],columns=Meses_ano)
                        dt_Final=dt_Final.append(df, ignore_index=True)

                    try:
                        Promedio=int(dt_Final[Meses][dt_Final[Meses]>0].mean().mean())
                        bool_1=True

                    except:
                        bool_1=False

                    if bool_1:
                        if Promedio<1e3:
                            div=1e0
                            nomina=""
                        elif Promedio<1e6:
                            div=1e3
                            nomina="miles de"
                        elif Promedio<1e9:
                            div=1e6
                            nomina="millones de"
                        elif Promedio<1e12:
                            div=1e9
                            nomina="miles de millones de"
                        elif Promedio<1e15:
                            div=1e12
                            nomina="billones de"

                        rows = template_document.tables[1].rows
                        index_1=dt_Final.index.values
                        contad_enter_3=0
                        for idx in index_1:
                            rows[0].cells[int(idx)+1].text=dt_Final["FECHA"].iloc[idx]
                            for idx_2,val in enumerate(Meses):
                                num=round(dt_Final[val].iloc[idx]/div,2)
                                if num > 0:

                                    rows[idx_2+1].cells[int(idx)+1].text=f'{num:,}'
                                elif num == 0:
                                    rows[idx_2+1].cells[int(idx)+1].text="-"

                            run=rows[0].cells[idx+1].paragraphs[0].runs
                            font = run[0].font
                            font.size= Pt(10)
                            font.name = 'Tahoma'
                            font.bold = True

                            for idx_3 in range(1,len(Meses_ano)):
                                set_font(rows,idx_3,idx+1,8)

                        for idx_ex in range(0,7-len(Anos)):
                            delete_columns(template_document.tables[1], [7-idx_ex])

                        for paragraph in template_document.paragraphs:
                            replace_text_in_paragraph(paragraph, "${TITULO_TAB_1}", "HISTORICO REINTEGRO DDV")
                        for paragraph in template_document.paragraphs:
                            replace_text_in_paragraph(paragraph, "${DENOMINACION}", "Los valores de la tabla están en "+nomina+" COP")
                    else:
                        rows = template_document.tables[1].rows
                        for idx_ex in range(0,13):
                            remove_row(template_document.tables[1], rows[-1])

                        for paragraph in template_document.paragraphs:
                            replace_text_in_paragraph(paragraph, "${TITULO_TAB_1}", "")
                        for paragraph in template_document.paragraphs:
                            replace_text_in_paragraph(paragraph, "${DENOMINACION}", "")

                    Var_imagenes = {
                        "${IMA_INGRESOS}": Imagenes_name
                    }

                    for variable_key, variable_value in Var_imagenes.items():

                        for paragraph in template_document.paragraphs:

                            replace_text_for_image(paragraph, variable_key,variable_value,10.77,4.04)

                    Imagenes_name=[]
                    Suma_dia=np.array([])
                    color_b = [(0.94,0.49,0.15,1),(0.9,0.13,0.28,1),(0.61,0.61,0.61,1)]
                    dias=np.array(['1. LUNES', '2. MARTES','3. MIERCOLES', '4. JUEVES', '5. VIERNES', '6. SÁBADO', '7. DOMINGO','PROMEDIO'])

                    name="FRONTERAS.png"
                    data_graph=Fronteras[Fronteras["USUARIO"]==usuario]

                    if data_graph.size !=0:
                        data_graph= data_graph.dropna(how='all', axis=1)
                        for idx_2 in dias:
                            suma=data_graph[idx_2].sum()
                            Suma_dia=np.append([suma],Suma_dia)

                        Suma_dia=np.flip(Suma_dia)
                        if Suma_dia.sum()==0:
                            st.warning("El usuario "+ usuario+ " no tiene registros semanales")
                            for paragraph in template_document.paragraphs:
                                replace_text_in_paragraph(paragraph, "${IMGENES_3}", '')
                            extra=1
                        else:
                            extra=0
                        fig = plt.figure(figsize=(9, 2.8))
                        ax = fig.add_axes([0,0,1,1])

                        ax.bar(dias,Suma_dia,color=color_b[0])
                        plt.xlabel("")
                        plt.ylabel("Energía [kWh-día]")
                        plt.title("DDV HABILITADA")
                        max_val=Suma_dia.max()

                        plt.ylim((0,max_val*1.2))

                        for idx,value in enumerate(Suma_dia):
                                plt.text(x = idx , y = value + max_val*0.05, s = f'{int(value):,}', size = 9,ha='center',va='center')

                        #plt.gca().axes.get_yaxis().set_visible(False)
                        plt.savefig(Ruta_img+"/"+name,dpi=DPI_IMGA, bbox_inches='tight',transparent=True)
                        Imagenes_name.extend([Ruta_img+"/"+name])

                        Var_imagenes = {
                            "${IMGENES_3}": Imagenes_name
                        }

                        for variable_key, variable_value in Var_imagenes.items():

                            for paragraph in template_document.paragraphs:

                                replace_text_for_image(paragraph, variable_key,variable_value,23,7.15)

                        rows = template_document.tables[3].rows

                        contador=0
                        for idx,val in enumerate(data_graph.index):
                            try:
                                if (int(data_graph["ACTUALIZACION"].values[idx].strftime("%Y")) >= 2018):
                                    rows[contador+1].cells[0].text = any2str(data_graph["FRONTERA COMERCIAL"].values[idx])
                                    rows[contador+1].cells[1].text = any2str(data_graph["COD SIC"].values[idx])
                                    #rows[contador+1].cells[2].text = any2str(data_graph["PREDIO"].values[idx])
                                    rows[contador+1].cells[2].text = any2str(data_graph["FRT DDV"].values[idx])
                                    rows[contador+1].cells[3].text = any2str(data_graph["TIPO DDV"].values[idx])
                                    rows[contador+1].cells[4].text = any2str(f'{int(data_graph["PROMEDIO"].values[idx]):,}')
                                    rows[contador+1].cells[5].text = any2str(data_graph["ACTUALIZACION"].values[idx].strftime("%Y-%m-%d"))
                                    rows[contador+1].cells[6].text = any2str(data_graph["MARGEN"].values[idx])
                                    rows[contador+1].cells[7].text = any2str(data_graph["ÚLTIMA DESCONEXION"].values[idx].strftime("%Y-%m-%d"))
                                    rows[contador+1].cells[8].text = any2str(str(data_graph["DÍAS CERTIFICADOS"].values[idx]))
                                    try:
                                        rows[contador+1].cells[9].text = any2str(data_graph["PROXIMA PRUEBA DDV"].values[idx].strftime("%Y-%m-%d"))
                                    except:

                                        rows[contador+1].cells[9].text = str(data_graph["PROXIMA PRUEBA DDV"].values[idx])[:10]

                                    for idx_2 in range(0,10):
                                        set_font(rows,contador+1,idx_2,8)
                                    contador+=1
                                else:
                                    rows[contador+1].cells[0].text = any2str(data_graph["FRONTERA COMERCIAL"].values[idx])
                                    rows[contador+1].cells[1].text = any2str(data_graph["COD SIC"].values[idx])
                                    #rows[contador+1].cells[2].text = any2str(data_graph["PREDIO"].values[idx])#dsfdsfdsfds
                                    rows[contador+1].cells[2].text = any2str(data_graph["FRT DDV"].values[idx])
                                    rows[contador+1].cells[3].text = any2str(data_graph["TIPO DDV"].values[idx])
                                    rows[contador+1].cells[4].text = any2str(f'{int(data_graph["PROMEDIO"].values[idx]):,}')
                                    rows[contador+1].cells[5].text = "NO APLICA"
                                    rows[contador+1].cells[6].text = any2str(data_graph["MARGEN"].values[idx])
                                    rows[contador+1].cells[7].text = "NO APLICA"
                                    rows[contador+1].cells[8].text = any2str(str(data_graph["DÍAS CERTIFICADOS"].values[idx]))
                                    try:
                                        rows[contador+1].cells[9].text = any2str(data_graph["PROXIMA PRUEBA DDV"].values[idx].strftime("%Y-%m-%d"))
                                    except:

                                        rows[contador+1].cells[9].text = str(data_graph["PROXIMA PRUEBA DDV"].values[idx])[:10]

                                    for idx_2 in range(0,10):
                                        set_font(rows,contador+1,idx_2,8)
                                    contador+=1
                            except:
                                st.warning("Error en hoja fronteras para usuario: "+ usuario)
                                for idx_2 in range(0,10):
                                    rows[contador+1].cells[idx_2].text = "ERROR"
                                    set_font(rows,contador+1,idx_2,8)
                                contador+=1

                        for idx in np.arange(0,200-contador+extra):
                            remove_row(template_document.tables[3], rows[-1])

                    else:
                        st.warning("El usuario "+usuario+" no se encuentra en la hoja de Fronteras")
                        rows = template_document.tables[3].rows
                        for idx in np.arange(0,201):
                            remove_row(template_document.tables[3], rows[-1])

                        for paragraph in template_document.paragraphs:
                            replace_text_in_paragraph(paragraph, "${IMGENES_3}", '')

                    data_user_t3=Tabla_3.copy()
                    data_user_t3=data_user_t3[data_user_t3["USUARIO"]==usuario]

                    Fechas_t3 = pd.unique(data_user_t3["FECHA"])

                    Data_frame_fechas=dt_fechas_3(Tabla_3.copy(),data_user_t3,Fechas_t3 ,Tipo_dia)

                    rows = template_document.tables[2].rows
                    index_1=Data_frame_fechas.index.values
                    contad_enter_3=0
                    for idx in index_1:

                        rows[int(idx)+1].cells[0].text = Data_frame_fechas.iloc[idx]["Fecha"].strftime('%Y-%m-%d')

                        rows[int(idx)+1].cells[1].text = Data_frame_fechas.iloc[idx]["Dia"]

                        rows[int(idx)+1].cells[2].text = f'{Data_frame_fechas.iloc[idx]["Respaldo"]:,}'
                        rows[int(idx)+1].cells[3].text = f'{Data_frame_fechas.iloc[idx]["PRECIO PONDERADO"]:,}'
                        rows[int(idx)+1].cells[4].text = f'{Data_frame_fechas.iloc[idx]["TRM"]:,}'
                        rows[int(idx)+1].cells[5].text = f'{Data_frame_fechas.iloc[idx]["P_neto"]:,}'
                        # Acum_Res += Data_frame_fechas.iloc[idx]["Respaldo"]

                        for idx_2 in [0,1,2,4,5]:
                            run=rows[int(idx)+1].cells[idx_2].paragraphs[0].runs
                            font = run[0].font
                            font.size= Pt(10)
                            font.name = 'Tahoma'

                    for idx in np.arange(len(index_1)+1,32):

                        remove_row(template_document.tables[2], rows[len(index_1)+1])
                        contad_enter_3+=1

                    if len(index_1) > 0:
                        for paragraph in template_document.paragraphs:
                                replace_text_in_paragraph(paragraph, "${TITULO_TAB_2}", "REINTEGRO MECANISMO DDV "+eleccion2.upper()+" "+str(eleccion3))
                        for paragraph in template_document.paragraphs:
                                replace_text_in_paragraph(paragraph, "${ENTER_3}", '\n'*contad_enter_3)

                    elif len(index_1)==0:
                        st.warning("El usuario "+usuario+" no cuenta con registros en la hoja de liquidación")
                        for key in ["${ENTER_3}","${TITULO_TAB_2}"]:
                            for paragraph in template_document.paragraphs:
                                replace_text_in_paragraph(paragraph, key, "")

                        remove_row(template_document.tables[2], rows[-1])

                    #rows[-1].cells[1].text = Num_dias(len(Fechas))
                    # rows[-1].cells[2].text = f'{Acum_Req:,}'
                    # rows[-1].cells[3].text = f'{Acum_Res:,}'

                    # for idx_2 in range(1,4):
                    #     run=rows[-1].cells[idx_2].paragraphs[0].runs
                    #     font = run[0].font
                    #     font.size= Pt(10)
                    #     font.name = 'Tahoma'

                    name_word=usuario+"_Informe_Comercial_"+eleccion2+"_"+str(eleccion3)+".docx"
                    name_pdf=usuario+"_Informe_Comercial_"+eleccion2+"_"+str(eleccion3)+".pdf"
                    template_document.save(Ruta_x+name_word)
                    zf.write(Ruta_x+name_word)
                    if b:

                        docx2pdf.convert(Ruta_x+name_word, Ruta_x+name_pdf)
                        zf.write(Ruta_x+name_pdf)
                    File_names.extend([name_word])

                    steps_done += 1
                    my_bar.progress(int(steps_done*100/steps))

                    text_rest.text("Progreso: "+str(steps_done)+"/"+str(steps) + " -  Actualmente en el usuario: " +usuario )

                Info.update({"File_names":File_names})
                json_info = json.dumps(Info, indent = 4)
                with open(Ruta_x+'/00_data.json', 'w') as f:
                    json.dump(json_info, f)
                zf.write(Ruta_x+'/00_data.json')
                zf.close()

                with open("Resultado.zip", "rb") as fp:
                    with columns_3[1]:
                        btn = st.download_button(
                            label="Descargar resultados",
                            data=fp,
                            file_name="Resultado.zip",
                            mime="application/zip"
                        )

        else:
            st.warning("Necesita subir los dos archivos")

reason: import numpy


text:
# Elizabeth A. Barnes and Randal J. Barnes
# January 18, 2021
# v2.08

import numpy as np
import numpy.ma as ma
import random
import xarray as xr
import scipy.stats as stats
import random
import sys
import h5py
from sklearn import preprocessing
import matplotlib.pyplot as plt
import scipy.io as sio

#----------------------------------------------------------------
#----------------------------------------------------------------

def is_tranquil(N,inoise):
    tr = np.ones((N,))
    i = np.intersect1d(np.arange(0,N),inoise)
    tr[i] = 0
    return tr

#----------------------------------------------------------------
def make_categories(y,n,y_perc=np.nan):

    p_vec = np.linspace(0,1,n+1)
    ycat = np.empty(np.shape(y))*np.nan
    y_p = np.empty((len(p_vec)-1,2))*np.nan

    if(y_perc is not np.nan):
        assert len(p_vec)==len(y_perc)+1

    for i in np.arange(0,n):
        if(y_perc is np.nan):
            low = np.percentile(y,p_vec[i]*100)
            high = np.percentile(y,p_vec[i+1]*100)
        else:
            low,high = y_perc[i]

        y_p[i,:] = low, high
        k = np.where(np.logical_and(y>=low, y<=high))[0]
        ycat[k] = i

    return ycat, y_p

#----------------------------------------------------------------
def undersample(x,y,tr):

    cats = np.unique(y)
    mincount = len(y)
    for c in cats:
        num = np.count_nonzero(y==c)
        if(num < mincount):
            mincount = num
    # print results
    for c in cats:
        print('number originally in each class '
              + str(c)
              + ' = '
              + str(np.count_nonzero(y==c)))

    yout = y[:1]
    xout = x[:1]
    trout = tr[:1]
    for c in cats:
        i = np.where(y==c)[0]
        i = i[:mincount]
        xout = np.append(xout,x[i],axis=0)
        yout = np.append(yout,y[i],axis=0)
        trout = np.append(trout,tr[i],axis=0)

    yout = np.asarray(yout[1:])
    xout = np.asarray(xout[1:])
    trout = np.asarray(trout[1:])

    # print results
    for c in cats:
        print('number in undersampled class '
              + str(c)
              + ' = '
              + str(np.count_nonzero(yout==c)))

    return xout, yout, trout

#----------------------------------------------------------------
def split_data(X, y_cat, tranquil, corrupt):

    NSAMPLES = np.shape(X)[0]
    n_test = 5000
    n_val = 5000
    n_train = NSAMPLES - n_val - n_test

    # make training data
    X_train = X[:n_train,:,:]
    y_train_cat = y_cat[:n_train]
    tr_train = tranquil[:n_train]
    corrupt_train = corrupt[:n_train]
    print('----Training----')
    print(np.shape(X_train))
    print(np.shape(y_train_cat))
    print(np.shape(tr_train))

    # make validation data
    X_val = X[n_train:n_train+n_val,:,:]
    y_val_cat = y_cat[n_train:n_train+n_val]
    tr_val = tranquil[n_train:n_train+n_val]
    corrupt_val = corrupt[n_train:n_train+n_val]
    print('----Validation----')
    print(np.shape(X_val))
    print(np.shape(y_val_cat))
    print(np.shape(tr_val))

    # make testing data
    X_test = X[n_train+n_val:n_train+n_val*2,:,:]
    y_test_cat = y_cat[n_train+n_val:n_train+n_val*2]
    tr_test = tranquil[n_train+n_val:n_train+n_val*2]
    corrupt_test = corrupt[n_train+n_val:n_train+n_val*2]
    print('----Testing----')
    print(np.shape(X_test))
    print(np.shape(y_test_cat))
    print(np.shape(tr_test))

    return (X_train, y_train_cat, tr_train, corrupt_train), (X_val, y_val_cat, tr_val, corrupt_val), (X_test, y_test_cat, tr_test, corrupt_test)

#----------------------------------------------------------------
def preprocess_data(X_train, y_train, X_val, y_val, ABSTAIN):

    # standardize data
    xmean = np.nanmean(X_train[:]) #standardize here
    xstd = np.nanstd(X_train[:])
    X_train_std = (X_train-xmean)/xstd
    X_val_std = (X_val-xmean)/xstd

    # reshape the data
    X_train_std = np.reshape(X_train_std, (np.shape(X_train_std)[0],np.shape(X_train_std)[1]*np.shape(X_train_std)[2]))
    X_val_std = np.reshape(X_val_std, (np.shape(X_val_std)[0],np.shape(X_val_std)[1]*np.shape(X_val_std)[2]))

    # turn nan values (which correspond to land) to zero so they do not influence the training
    X_train_std = np.nan_to_num(X_train_std, copy=False, nan=0.0)
    X_val_std = np.nan_to_num(X_val_std, copy=False, nan=0.0)

    # one-hot encoding
    enc = preprocessing.OneHotEncoder()
    enc.fit(np.append(y_train, ABSTAIN)[:,np.newaxis])
    onehotlabels = enc.transform(np.array(y_train).reshape(-1, 1)).toarray()
    onehotlabels_val = enc.transform(np.array(y_val).reshape(-1, 1)).toarray()

    return X_train_std, onehotlabels, X_val_std, onehotlabels_val, xmean, xstd

#----------------------------------------------------------------
def preprocess_reg_data(X_train, y_train, X_val, y_val, ABSTAIN):

    # standardize data
    xmean = np.nanmean(X_train[:]) #standardize here
    xstd = np.nanstd(X_train[:])
    X_train_std = (X_train-xmean)/xstd
    X_val_std = (X_val-xmean)/xstd

    # reshape the data
    X_train_std = np.reshape(X_train_std, (np.shape(X_train_std)[0],np.shape(X_train_std)[1]*np.shape(X_train_std)[2]))
    X_val_std = np.reshape(X_val_std, (np.shape(X_val_std)[0],np.shape(X_val_std)[1]*np.shape(X_val_std)[2]))

    # turn nan values (which correspond to land) to zero so they do not influence the training
    X_train_std = np.nan_to_num(X_train_std, copy=False, nan=0.0)
    X_val_std = np.nan_to_num(X_val_std, copy=False, nan=0.0)

    # one-hot encoding for regression
    onehotlabels = np.append(y_train,np.zeros(shape=np.shape(y_train)),axis=1)
    onehotlabels_val = np.append(y_val,np.zeros(shape=np.shape(y_val)),axis=1)

    return X_train_std, onehotlabels, X_val_std, onehotlabels_val, xmean, xstd

#----------------------------------------------------------------
def load_data():

    data_filename = '../data/synthClimate/synth_exm_Libby.mat'

    with h5py.File(data_filename, 'r') as file:
        print(list(file.keys()))

    with h5py.File(data_filename, 'r') as file:
        X = np.array(file['SSTrand'])
        lat = np.array(file['lat'])
        lon = np.array(file['lon'])
    #     Cnt = list(file['Cnt'])
        y = np.array(file['y'])

    X = np.swapaxes(X,1,2)

    return X, y, lat, lon

#----------------------------------------------------------------
def load_simpledata(size=None):
    if(size is None):
        data_filename = '../data/synthClimate/simple_climatedata.mat'
    else:
        data_filename = '../data/synthClimate/simple_climatedata_' + size + '.mat'
    mat_contents = sio.loadmat(data_filename)

    X = np.array(mat_contents['X'])
    lat = np.array(mat_contents['lat'])
    lon = np.array(mat_contents['lon'])
#     C = np.array(mat_contents['Cnt'])
    y = np.array(mat_contents['y'])[0,:][:,np.newaxis]

    return X, y, lat, lon
#----------------------------------------------------------------
def add_noise(data_name, X, y, lat, lon, pr_noise, nlabel, y_perc = np.nan, cutoff = 1., region_name='ENSO'):

    lat = lat.flatten()
    lon = lon.flatten()

    y_cat_orig, y_perc_out = make_categories(y,nlabel,y_perc)
    NSAMPLES = np.shape(X)[0]

    #-----------------------------------------------
    # define the tranquil and noisy regions
    if data_name.find('badClasses')==0:
        inoise = np.where(np.isin(y_cat_orig, [cutoff]))[0]
        tranquil = is_tranquil(NSAMPLES,inoise)

    elif data_name.find('mixedLabels')==0:
        inoise = np.arange(0,len(y_cat_orig))
        tranquil = is_tranquil(NSAMPLES,inoise)

    elif data_name.find('tranquilFOO') == 0:
        reg_lats, reg_lons = get_region(region_name = region_name)
        ilat = np.where(np.logical_and(lat>=reg_lats[0],lat<=reg_lats[1]))[0]
        ilon = np.where(np.logical_and(lon>=reg_lons[0],lon<=reg_lons[1]))[0]
        reg_avg = np.nansum(np.nansum(X[:,:,ilon],axis=-1)[:,ilat],axis=-1)/(len(ilat)*len(ilon))
        print('region shape = ' + str(len(ilat)) + ' x ' + str(len(ilon)))

        inoise = np.where(reg_avg<=cutoff)[0]
        tranquil = is_tranquil(NSAMPLES,inoise)

    elif data_name.find('noNoise') == 0:
        inoise = []
        tranquil = np.ones((NSAMPLES,))

    else:
        raise ValueError('no such data noise type.')

    #-----------------------------------------------
    #change the labels on the noisy data
    y_cat = np.copy(y_cat_orig)
    corrupt = np.zeros(np.shape(y_cat_orig))

    for i in inoise:
        if np.random.random() < pr_noise:
            label = y_cat_orig[i]
            label = (label + np.random.randint(low=1, high=nlabel)) % nlabel
            corrupt_value = 1
        else:
            label = y_cat_orig[i]
            corrupt_value = 0
        y_cat[i] = label
        corrupt[i] = corrupt_value

    itranquil = np.where(tranquil==1)[0]
    i = np.where(y_cat_orig!=y_cat)[0]
    k = np.where(y_cat_orig[itranquil]!=y_cat[itranquil])[0]
    print('\n----Mislabeled----')
    print('# tranquil = ' + str(len(itranquil)) + ' out of ' + str(NSAMPLES) + ' samples')
    print('percent tranquil = ' + str(np.round(100.*len(itranquil)/len(tranquil))) + '%')
    try:
        print('tranquil mislabeled = ' + str(np.round(100.*len(k)/len(itranquil))) + '%')
        print('non-tranquil mislabeled = ' + str(np.round(100.*len(i)/len(inoise))) + '%')
    except:
        pass
    print('total mislabeled = ' + str(np.round(100.*len(i)/len(y_cat))) + '%')
    #-----------------------------------------------

    return X, y_cat, tranquil, corrupt, y_perc_out

    #----------------------------------------------------------------
def add_reg_noise(data_name, X, y, lat, lon, pr_noise, nlabel, y_perc = np.nan, cutoff = 1., region_name='ENSO'):

    lat = lat.flatten()
    lon = lon.flatten()

    NSAMPLES = np.shape(X)[0]

    #-----------------------------------------------
    # define the tranquil and noisy regions
    if data_name.find('mixedLabels')==0:
        inoise = np.arange(0,len(y))
        tranquil = is_tranquil(NSAMPLES,inoise)

    elif data_name.find('tranquilFOO') == 0:
        reg_lats, reg_lons = get_region(region_name = region_name)
        ilat = np.where(np.logical_and(lat>=reg_lats[0],lat<=reg_lats[1]))[0]
        ilon = np.where(np.logical_and(lon>=reg_lons[0],lon<=reg_lons[1]))[0]
        reg_avg = np.nansum(np.nansum(X[:,:,ilon],axis=-1)[:,ilat],axis=-1)/(len(ilat)*len(ilon))
        print('region shape = ' + str(len(ilat)) + ' x ' + str(len(ilon)))

        inoise = np.where(reg_avg<=cutoff)[0]
        tranquil = is_tranquil(NSAMPLES,inoise)

    elif data_name.find('noNoise') == 0:
        inoise = []
        tranquil = np.ones((NSAMPLES,))

    else:
        raise ValueError('no such data noise type.')

    #-----------------------------------------------
    #change the labels on the noisy data
    y_new = np.copy(y)
    corrupt = np.zeros(np.shape(tranquil))

    for i in inoise:
        if np.random.random() < pr_noise:
            i_rand_sample = np.random.randint(0,NSAMPLES)
            value = y[i_rand_sample]
            corrupt_value = 1
        else:
            value = y[i]
            corrupt_value = 0
        y_new[i] = value
        corrupt[i] = corrupt_value

    itranquil = np.where(tranquil==1)[0]
    i = np.where(corrupt==1)[0]
    k = np.where(np.logical_and(tranquil==1, corrupt==1))[0]
    print('\n----Mislabeled----')
    print('# tranquil = ' + str(len(itranquil)) + ' out of ' + str(NSAMPLES) + ' samples')
    print('percent tranquil = ' + str(np.round(100.*len(itranquil)/len(tranquil))) + '%')
    try:
        print('tranquil mislabeled = ' + str(np.round(100.*len(k)/len(itranquil))) + '%')
        print('non-tranquil mislabeled = ' + str(np.round(100.*len(i)/len(inoise))) + '%')
    except:
        pass
    print('total mislabeled = ' + str(np.round(100.*len(i)/len(y_new))) + '%')
    #-----------------------------------------------

    return X, y_new, tranquil, corrupt

def get_region(region_name):

    if(region_name=='ENSO'):
        reg_lats = (-10.,10.)
        reg_lons = (360-170,360-82.)
    elif(region_name=='equatorialENSO'):
        reg_lats = (-5.,5.)
        reg_lons = (360-170,360-82.)
    elif(region_name=='shENSO'):
        reg_lats = (-10.,5.)
        reg_lons = (360-170,360-82.)
    elif(region_name=='nhENSO'):
        reg_lats = (5.,10.)
        reg_lons = (360-170,360-82.)
    elif(region_name=='tropicsENSO'):
        reg_lats = (-15.,15.)
        reg_lons = (360-170,360-82.)
    elif(region_name=='smallENSO'):
        reg_lats = (-5.,5.)
        reg_lons = (360-120,360-82.)
    elif(region_name=='NAO'):
        reg_lats = (40.,60.)
        reg_lons = (360-60.,360-20.)
    else:
        raise ValueError('no such region')

#     print(region_name)
    return reg_lats, reg_lons

ext: py

reason: import numpy,import scipy


text:
import pygame
import time
import speech_recognition as sr
import os
import cv2
import sounddevice as sd
from scipy.io.wavfile import write
from scipy.io import wavfile
import numpy as np
import speaker as sp
display_width = 1366
display_height = 768

black = (0,0,0)
alpha = (0,88,255)
white = (255,255,255)
red = (200,0,0)
green = (0,200,0)
bright_red = (255,0,0)
bright_green = (0,255,0)

pygame.init()
screen = pygame.display.set_mode((0,0),pygame.FULLSCREEN)
mimg = pygame.image.load('img.jpg')

def message_display(text):
    largeText = pygame.font.Font('freesansbold.ttf',30)
    TextSurf, TextRect = text_objects(text, largeText)
    TextRect.center = ((display_width/2),(display_height/2))
    screen.blit(TextSurf, TextRect)
    pygame.display.update()

def text_objects(text, font):
    textSurface = font.render(text, True, alpha)
    return textSurface, textSurface.get_rect()

def close():
    pygame.quit()
    quit()

def s2t():
    screen.blit(mimg,(0,0))

    pygame.mixer.music.load('recording-started.mp3')
    pygame.mixer.music.play(0)
    message_display("recording voice")

    time.sleep(2)
    fs = 16000
    seconds = 20
    data = sd.rec(int(seconds * fs), samplerate=fs, channels=2)
    sd.wait()

    y = (np.iinfo(np.int32).max * (data/np.abs(data).max())).astype(np.int32)
    write('test.wav', fs, y)

    screen.blit(mimg,(0,0))
    pygame.mixer.music.load('recorded-successfully.mp3')
    pygame.mixer.music.play(0)
    message_display("voice recorded successfully ")
    screen.blit(mimg,(0,0))

    time.sleep(3)
    str=sp.task_predict("test.wav", "model1.out")
    pygame.mixer.music.load('speaker-identified.mp3')
    pygame.mixer.music.play(0)

    img = pygame.image.load('pic\\'+str[1]+'.jpg')
    screen.blit(pygame.transform.scale(img, (200, 200)), (600, 150))
    message_display(str[0])

    pygame.mixer.music.load('welc\\i'+str[1]+'.mp3')
    pygame.mixer.music.play(0)

    time.sleep(10)

def button(msg,x,y,w,h,ic,ac,action=None):
    mouse = pygame.mouse.get_pos()
    click = pygame.mouse.get_pressed()
    if x+w > mouse[0] > x and y+h > mouse[1] > y:
        pygame.draw.rect(screen, ac,(x,y,w,h))

        if click[0] == 1 and action != None:
            action()
    else:
        pygame.draw.rect(screen, ic,(x,y,w,h))

    smallText = pygame.font.SysFont("comicsansms",20)
    textSurf, textRect = text_objects(msg, smallText)
    textRect.center = ( (x+(w/2)), (y+(h/2)) )
    screen.blit(textSurf, textRect)

def main():
    stream = 'introv.mov'
    cap = cv2.VideoCapture(stream)
    ret, img = cap.read()
    img = cv2.transpose(img)

    surface = pygame.surface.Surface((img.shape[0], img.shape[1]))

    pygame.mixer.music.load('introa.mp3')
    pygame.mixer.music.play(0)

    done = False
    while not done:
        pygame.display.flip()

        for event in pygame.event.get():
            if event.type == pygame.QUIT:
                done = True

        ret, img = cap.read()
        if not ret:
            done = True
            break
        else:
            img = cv2.transpose(img)
            pygame.surfarray.blit_array(surface, img)
            screen.blit(surface, (0,0))

    pygame.mixer.music.load('a1.mp3')
    pygame.mixer.music.play(0)

    while True:
        for event in pygame.event.get():
            if event.type == pygame.QUIT:
                pygame.quit()
                quit()

        screen.blit(mimg,(0,0))
        button("Speak!",450,650,100,50,green,bright_green,s2t)
        button("Quit",750,650,100,50,red,bright_red,close)
        pygame.display.update()

    pygame.quit()

if __name__ == '__main__':
    main()

reason: import numpy,from scipy


text:
import os
import copy
import cv2
import numpy as np
from collections import OrderedDict

import torch

def load_checkpoint(model, checkpoint_path):
    if not os.path.exists(checkpoint_path):
        print("----No checkpoints at given path----")
        return
    model.load_state_dict(torch.load(checkpoint_path, map_location=torch.device("cpu")))
    print("----checkpoints loaded from path: {}----".format(checkpoint_path))
    return model

def load_checkpoint_mgpu(model, checkpoint_path):
    if not os.path.exists(checkpoint_path):
        print("----No checkpoints at given path----")
        return
    model_state_dict = torch.load(checkpoint_path, map_location=torch.device("cpu"))
    new_state_dict = OrderedDict()
    for k, v in model_state_dict.items():
        name = k[7:]  # remove `module.`
        new_state_dict[name] = v

    model.load_state_dict(new_state_dict)
    print("----checkpoints loaded from path: {}----".format(checkpoint_path))
    return model

def save_checkpoint(model, save_path):
    print(save_path)
    if not os.path.exists(os.path.dirname(save_path)):
        os.makedirs(os.path.dirname(save_path))
    torch.save(model.state_dict(), save_path)

def save_checkpoints(opt, itr, net):
    save_checkpoint(
        net,
        os.path.join(opt.save_dir, "checkpoints", "itr_{:08d}_u2net.pth".format(itr)),
    )


text:
import numpy as np
import tensorflow as tf

def xavier_init(fan_in, fan_out, constant = 1):
    low = -constant * np.sqrt(6.0 / (fan_in + fan_out))
    high = constant * np.sqrt(6.0 / (fan_in + fan_out))
    return tf.random_uniform((fan_in, fan_out), minval = low, maxval = high, dtype = tf.float32)

class AdditiveGaussianNoiseAutoEncoder(object):

    def __init__(self, num_input, num_hidden, transfer_function=tf.nn.sigmoid, optimizer=tf.train.AdamOptimizer(),
                 scale=0.1):
        self.num_input = num_input
        self.num_hidden = num_hidden
        self.transfer = transfer_function
        self.scale = tf.placeholder(tf.float32)
        self.training_scale = scale
        n_weights = self._initialize_weights()
        self.weights = n_weights

        # model
        self.x = tf.placeholder(tf.float32, [None, self.num_input])

        self.hidden_layer = self.transfer(tf.add(tf.matmul(self.x + scale * tf.random_normal((num_input,)),
                                                           self.weights['w1']),
                                                 self.weights['b1']))
        self.reconstruction = tf.add(tf.matmul(self.hidden_layer, self.weights['w2']), self.weights['b2'])

        # cost
        self.cost = 0.5 * tf.reduce_sum(tf.pow(tf.subtract(self.reconstruction, self.x), 2.0))

        self.optimizer = optimizer.minimize(self.cost)

        init = tf.global_variables_initializer()
        self.session = tf.Session()
        self.session.run(init)

    def _initialize_weights(self):
        weights = dict()
        weights['w1'] = tf.Variable(xavier_init(self.num_input, self.num_hidden))
        weights['b1'] = tf.Variable(tf.zeros([self.num_hidden], dtype=tf.float32))
        weights['w2'] = tf.Variable(tf.zeros([self.num_hidden, self.num_input], dtype=tf.float32))
        weights['b2'] = tf.Variable(tf.zeros([self.num_input], dtype=tf.float32))
        return weights

    def partial_fit(self, X):
        cost, opt = self.session.run((self.cost, self.optimizer), feed_dict={self.x: X,
                                                                             self.scale: self.training_scale
                                                                             })
        return cost

    def kl_divergence(self, p, p_hat):
        return tf.reduce_mean(p * tf.log(p) - p * tf.log(p_hat) + (1 - p) * tf.log(1 - p) - (1 - p) * tf.log(1 - p_hat))

    def calculate_total_cost(self, X):
        return self.session.run(self.cost, feed_dict={self.x: X,
                                                      self.scale: self.training_scale
                                                      })

    def transform(self, X):
        return self.session.run(self.hidden_layer, feed_dict={self.x: X,
                                                              self.scale: self.training_scale
                                                              })

    def generate_value(self, _hidden=None):
        if _hidden is None:
            _hidden = np.random.normal(size=self.weights["b1"])
        return self.session.run(self.reconstruction, feed_dict={self.hidden_layer: _hidden})

    def reconstruct(self, X):
        return self.session.run(self.reconstruction, feed_dict={self.x: X,
                                                                self.scale: self.training_scale
                                                                })

    def get_weights(self):
        return self.session.run(self.weights['w1'])

    def get_biases(self):
        return self.session.run(self.weights['b1'])


text:
import os
import numpy as np
from pathlib import Path
from nnga.utils.data_io import (
    load_image,
    save_encoder_parameters,
    save_decoder_parameters,
)
from nnga.datasets.base_dataset import BaseDataset
from nnga.utils.data_collector import read_dataset
from nnga.utils.data_manipulation import adjust_image_shape, normalize_image

class SegmentationDataset(BaseDataset):
    """SegmentationDataset loader
          class implements {BaseDataset} and provide a loader
    Arguments
        cfg : {yacs.config.CfgNode}
            Experiment config data.
            All the information to configure the dataset loader is stored
            in experiment config.
        is_validation : {bool}
            Flag to sinalize when dataset loader is a validation dataset
            it's important select information on experiment
            config to configure dataset loader corretly for validation
      """

    def __init__(self, cfg, logger, is_validation=False):
        self._is_validation = is_validation
        if self._is_validation:
            self._dataset_img_path = os.path.expandvars(
                cfg.DATASET.VAL_IMG_PATH
            )
        else:
            self._dataset_img_path = os.path.expandvars(
                cfg.DATASET.TRAIN_IMG_PATH
            )

        self.image_shape = cfg.MODEL.INPUT_SHAPE
        self.preserve_ratio = cfg.DATASET.PRESERVE_IMG_RATIO

        if not isinstance(self.preserve_ratio, bool):
            raise ValueError("DATASET.PRESERVE_IMG_RATIO must be a bool")

        if (
            not isinstance(self.image_shape, tuple)
            or len(self.image_shape) != 3
        ):
            raise ValueError(
                "MODEL.INPUT_SHAPE must be a tuple" "(width, height, channels)"
            )

        super().__init__(cfg, logger, is_validation)

    @property
    def input_shape(self):
        """
            Property is the input shape

        Returns:
            {int or tuple} -- input shape
        """
        return self.image_shape

    def _load_metadata(self):
        """Create metadata for classification.
            Metadata is type of index from all files that
            represents dataset. Any huge data is load here
            just create a kind of index.
        """
        data = read_dataset(
            os.path.join(self._dataset_img_path, 'image'),
            format="png, jpg, jpeg, tif",
        )

        self._metadata = {
            os.path.splitext(Path(img_path).name)[0]: {
                "image_path": img_path,
            }
            for img_path in data["data"]
        }

        mask_data = read_dataset(
            os.path.join(self._dataset_img_path, 'mask'),
            format="png, jpg, jpeg, tif",
        )
        mask_metadata = {
            os.path.splitext(Path(mask_path).name)[0]: {
                "mask_path": mask_path,
            }
            for mask_path in mask_data["data"]
        }

        if set(self._metadata.keys()) != set(mask_metadata.keys()):
            msg = "The files name from image path and mask path does not " \
                  "match!\nCheck the dataset!"
            self._logger.error(msg)
            raise RuntimeError(msg)

        for key in self._metadata.keys():
            self._metadata[key].update(mask_metadata[key])

    def _make_index(self):
        """Using the metadata structure populate attributes
        _indexes, _indexes
        """
        for key, _ in self._metadata.items():
            self._indexes.append(key)

    def _compute_class_weigths(self):
        """Using the metadata structure populate class_weigths
        """
        self._class_weigths = None

    def on_epoch_end(self):
        """
        Update dataset on epoch end
        Notes:
            This method must be called by keras according to
            the keras documentation, but it's not the case in
            tensorflow 2.1.0 implementation, so was needed to
            call this manually
        """
        if self._shuffle:
            np.random.shuffle(self._indexes)

    def _make_labels_info(self):
        self._class_to_id = {"0": 0, "255": 1}
        self._id_to_class = {v: k for k, v in self._class_to_id.items()}

    @property
    def n_classes(self):
        """
            Property number of classes on the dataset

        Returns:
            {int} -- number of classes on the dataset
        """
        return 2

    def save_parameters(self):
        """
        Save parameters from dataset
        """
        save_encoder_parameters(self._output_dir, self._class_to_id)
        save_decoder_parameters(self._output_dir, self._id_to_class)

    def _data_generation(self, indexes):
        """
        Method use indexes to generate a batch data

        Use metadata structure to:
                - load images and ground thruth
                - apply data augmentation
                - form batchs

        Parameters
        ----------
            indexes {list} -- list of indexes from metadata to be
                loaded in a bacth with input and ground thruth
        """
        # TODO: Data augmentation
        images = [
            normalize_image(
                adjust_image_shape(
                    load_image(self._metadata[idx]["image_path"],),
                    self.image_shape,
                )
            )
            for idx in indexes
        ]

        masks = [
            normalize_image(
                adjust_image_shape(
                    load_image(self._metadata[idx]["mask_path"], ),
                    (self.image_shape[0], self.image_shape[1], 1),
                )
            ).reshape(self.image_shape[0],
                      self.image_shape[1])
            for idx in indexes
        ]

        return np.array(images), np.array(masks)

max_stars_repo_name: rafaelsdellama/nnga

max_issues_repo_name: rafaelsdellama/nnga

max_forks_repo_name: rafaelsdellama/nnga


text:
import re
import numpy as np
ndarray_re = re.compile(
    r"ndarray\.(?P<dtype>[a-z0-9]+)(?P<shape>\([0-9\,\ ]+\))"
)

def parse_ndarray_type(type_name):
    m_ndarray = ndarray_re.match(type_name)
    if m_ndarray:
        dtype = getattr(np, m_ndarray.group('dtype'))
        shape = tuple(map(int, m_ndarray.group('shape')[1:-1].split(',')))
        return dtype, shape
    return None

# TODO: this needs to be set up in a way to make it extensible (without
# editing core code)
def backend_registration_type(type_name):
    backend_type = type_name
    ndarray_info = parse_ndarray_type(type_name)
    if ndarray_info:
        backend_type = 'ndarray'
    return backend_type

uuid_types = ['uuid', 'lazy']
uuid_list_types = ['list_uuid']
builtin_types = ['str', 'int', 'float']
ndarray_types = ['ndarray']

all_types = uuid_types + uuid_list_types + builtin_types + ndarray_types
all_uuid_types = ['uuid', 'lazy', 'list_uuid']
json_obj_types = ['json', 'json_obj']

max_stars_repo_name: bdice/openpathsampling

max_issues_repo_name: bdice/openpathsampling

max_forks_repo_name: dwhswenson/openpathsampling


text:
from sklearn.metrics import log_loss, accuracy_score
import numpy as np

from .kfold import PurgedKFold

def cv_score(clf, X, y, sample_weight=None, scoring='neg_log_loss',
             t1=None, n_splits=3, cv_gen=None, pct_embargo=0., purging=False):
    if scoring not in ['neg_log_loss', 'accuracy']:
        raise Exception('Wrong scoring method')
    if cv_gen is None:
        cv_gen = PurgedKFold(n_splits=n_splits, t1=t1,
                             pct_embargo=pct_embargo,
                             purging=purging)
    scores = []
    for train, test in cv_gen.split(X=X):
        train_params = dict()
        test_params = dict()
        # Sample weight is an optional parameter
        if sample_weight is not None:
            train_params['sample_weight'] = sample_weight.iloc[train].values
            test_params['sample_weight'] = sample_weight.iloc[test].values
        clf_ = clf.fit(X=X.iloc[train, :], y=y.iloc[train], **train_params)
        # Scoring
        if scoring == 'neg_log_loss':
            prob = clf_.predict_proba(X.iloc[test, :])
            score_ = -log_loss(y.iloc[test], prob, labels=clf.classes_, **test_params)
        else:
            pred = clf_.predict(X.iloc[test, :])
            score_ = accuracy_score(y.iloc[test], pred, **test_params)
        scores.append(score_)
    return np.array(scores)