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

import pandas as pd
import sqlalchemy 
import psycopg2
import os
import numpy as np
import requests
import re
import copy
from pandas.api.types import is_numeric_dtype
from sklearn.linear_model import LinearRegression
from sqlalchemy import create_engine
import datetime
from datetime import datetime as dt
from sqlalchemy import inspect
import sys
import sqlalchemy.ext.declarative as sqld
pd.set_option('display.float_format', lambda x: '%.3f' % x)

sys.path.append('..')

from modules.transforms import *

DBname=os.environ['DB_NAME']
postgres_psswd=os.environ['POSTGRES_PASSWORD']
postgres_user=os.environ['POSTGRES_USER']
postgres_port=str(os.environ['POSTGRES_PORT'])

# A long string that contains the necessary Postgres login information
postgres_str = ('postgresql://'+postgres_user+':'+postgres_psswd+'@'+DBname+':'+postgres_port+'/superset')
# Create the connection
cnx = create_engine(postgres_str)

#guardar paso en csv
path='/data/ETLcache/'
now = dt.now()
timestamp = now.strftime("_%d%m%Y_%H%M%S")

pop=pd.read_sql_table('producto1_Covid_19', schema='producto1',con=cnx) 
pop=pop.groupby('Region')['Poblacion'].sum()
popTOT=pop.sum()

popTOT

# +
#----------------------------DEIS--------------------------------------------------------------------
s="DEIS"
n="DEIS_COVID"
DEIS=pd.read_sql_table(n, con=cnx,schema=s)
DEIS=DEIS[DEIS['COVID'].notnull()]
#fallecidos nuevos regionales (DEIS)
fr=DEIS.groupby(['FECHA_DEF','Metropolitana'])['FECHA_STR'].count()
fr=fr.reset_index()
fr.columns=['Fecha','Region Metropolitana','Fallecidos Nuevos']
fr=fr.pivot(index='Fecha',columns=['Region Metropolitana'])
fr=fr.T.reset_index().T
fr.columns=fr.iloc[0].astype(str)+' '+fr.iloc[1].astype(str)+' (DEIS)'
fr=fr.iloc[3:].reset_index()
#fallecidos regional acumulado (deis)
fr=fr.fillna(0)
fra=fr[fr.columns.to_list()[1:]].cumsum()
fra.columns=[x.replace('Nuevos','Acumulados') for x in fra.columns]
#concatenar
fr=pd.concat([fr,fra],axis=1)

deis=DEIS.groupby('FECHA_DEF')['index'].count().reset_index()
deis.columns=['Fecha','Fallecidos DEIS Diarios']
deis=deis.merge(DEIS[DEIS['COVID']=='Covid PCR+'].groupby('FECHA_DEF')['index'].count().reset_index().rename(columns={
    'FECHA_DEF':'Fecha','index':'Fallecidos DEIS Diarios (PCR+)'}),on='Fecha')
deis=deis.merge(DEIS[DEIS['COVID']=='Covid Sospechoso'].groupby('FECHA_DEF')['index'].count().reset_index().rename(columns={
    'FECHA_DEF':'Fecha','index':'Fallecidos DEIS Diarios (Covid Sospechoso)'}),on='Fecha')
deis['Fallecidos DEIS (PCR+) Acumulado']=deis['Fallecidos DEIS Diarios (PCR+)'].cumsum()
deis['Fallecidos DEIS (Covid Sospechoso) Acumulado']=deis['Fallecidos DEIS Diarios (Covid Sospechoso)'].cumsum()
deis['Fallecidos DEIS Acumulado']=deis['Fallecidos DEIS Diarios'].cumsum()
deis['Fallecidos DEIS (PCR+) x Millón/Hab.']=deis['Fallecidos DEIS (PCR+) Acumulado']/(popTOT/1000000)
deis['Fallecidos DEIS (Covid Sospechoso) x Millón/Hab.']=deis['Fallecidos DEIS (Covid Sospechoso) Acumulado']/(popTOT/1000000)
deis['Fallecidos DEIS x Millón/Hab.']=deis['Fallecidos DEIS Acumulado']/(popTOT/1000000)


DEIS=deis.merge(fr,on='Fecha',how='left')
#----------------------------DEIS--------------------------------------------------------------------
# -

def melt(df,id_var=['Fecha'],Vars=['Fallecidos Nuevos Región Metropolitana (DEIS)',
       'Fallecidos Nuevos Resto de Chile (DEIS)',
       'Fallecidos Acumulados Región Metropolitana (DEIS)',
       'Fallecidos Acumulados Resto de Chile (DEIS)']):
    df=df[Vars+id_var]
    df=df.melt(id_vars=id_var)
    
    return df


# +
#----------------------------ICOVID--------------------------------------------------------------------

#casos nuevos según fecha de inicio de síntomas (icovid)
s="producto53"
n="producto53_confirmados_"
nuevos=pd.read_sql_table(n, con=cnx,schema=s)
nuevos=pd.DataFrame(nuevos.groupby('fecha')['Promedio'].sum())
nuevos.columns=['Casos Nuevos (ICOVID)']
nuevos['Incidencia Nuevos (ICOVID) x 100 K Hab.']=nuevos['Casos Nuevos (ICOVID)']/popTOT*100000
nuevos['Casos Acumulados (ICOVID)']=nuevos['Casos Nuevos (ICOVID)'].cumsum()

#casos activos según fecha de inicio de síntomas (icovid)
s="producto53"
n="producto53_confirmados_regionale_casos_activos"
activos=pd.read_sql_table(n, con=cnx,schema=s)
activos=pd.DataFrame(activos.groupby('fecha')['Promedio'].sum())
activos.columns=['Casos Activos (ICOVID)']
activos['Incidencia Activos (ICOVID) x 100 K Hab.']=activos['Casos Activos (ICOVID)']/popTOT*100000

#activos regional icovid
s="producto53"
n="producto53_confirmados_regionale_casos_activos"
ar=pd.read_sql_table(n, con=cnx,schema=s)
ar=pd.DataFrame(ar.groupby(['fecha','Region_Metropolitana'])[['Poblacion','Promedio']].sum()).reset_index()
ar=ar.pivot(index='fecha',columns=['Region_Metropolitana']).T.reset_index().T
ar.columns=ar.iloc[0].astype(str)+' '+ar.iloc[1].astype(str)+' Casos Activos (ICOVID)'
ar=ar.iloc[3:].reset_index()
ar=ar.rename(columns={'fecha':'Fecha'})

#nuevos regionales icovid
s="producto53"
n="producto53_confirmados_regionale"
nr=pd.read_sql_table(n, con=cnx,schema=s)
nr=pd.DataFrame(nr.groupby(['fecha','Region_Metropolitana'])[['Poblacion','Promedio']].sum()).reset_index()
nr=nr.pivot(index='fecha',columns=['Region_Metropolitana']).T.reset_index().T
nr.columns=nr.iloc[0].astype(str)+' '+nr.iloc[1].astype(str)+' Casos Nuevos (ICOVID)'
nr=nr.iloc[3:].reset_index()
nr=nr.rename(columns={'fecha':'Fecha'})

#icovid consolidado
icovid=pd.concat([nuevos,activos],axis=1)
icovid=icovid.reset_index()
icovid=icovid.rename(columns={'fecha':'Fecha'})

icovid=icovid.merge(ar,how='left',on='Fecha').merge(nr,how='left',on='Fecha')
#----------------------------ICOVID--------------------------------------------------------------------

# +
#----------------------------MINSAL--------------------------------------------------------------------

#casos MINSAL
s="producto5"
n="producto5_TotalesNacionales_T"
minsal=pd.read_sql_table(n, con=cnx,schema=s)
minsal=minsal.drop('index',axis=1)
minsal.columns=[x+' (MINSAL)' for x in minsal.columns]
minsal=minsal.rename(columns={'Fecha (MINSAL)': 'Fecha'})
minsal['Fecha']=pd.to_datetime(minsal['Fecha'],errors='coerce')

#casos MINSAL regionales
s="producto3"
n="producto3_TotalesPorRegion_std"
minsalr=pd.read_sql_table(n, con=cnx,schema=s)
#minsal=minsal.drop('index',axis=1)
#minsal.columns=[x+' (MINSAL)' for x in minsal.columns]
#minsal=minsal.rename(columns={'Fecha (MINSAL)': 'Fecha'})
#minsal['Fecha']=pd.to_datetime(minsal['Fecha'],errors='coerce')

#casos nuevos regional minsal
s="producto13"
n="producto13_CasosNuevosCumulativo_std"
nrm=pd.read_sql_table(n, con=cnx,schema=s)
nrm=nrm[nrm['Region']!='Total']
nrm=pd.DataFrame(nrm.groupby(['Fecha','Region_Metropolitana'])['Total'].sum()).reset_index()
nrm=nrm.pivot(index='Fecha',columns=['Region_Metropolitana']).T.reset_index().T
nrm.columns='Casos Nuevos (MINSAL) '+nrm.iloc[1].astype(str)
nrm=nrm.iloc[3:].reset_index()

#casos activos por fecha inicio de sintomas regional (minsal)
s="producto19"
n="producto19_CasosActivosPorComuna_std"
arms=pd.read_sql_table(n, con=cnx,schema=s)
arms=arms.fillna(0)
arms=pd.DataFrame(arms.groupby(['Fecha','Region_Metropolitana'])['Casos activos'].sum()).reset_index()
arms=arms.pivot(index='Fecha',columns=['Region_Metropolitana']).T.reset_index().T
arms.columns='Casos Activos (MINSAL) '+arms.iloc[1].astype(str)
arms=arms.iloc[3:].reset_index()

#activos regional minsal
s="producto25"
n="producto25_CasosActualesPorComuna_std"
arm=pd.read_sql_table(n, con=cnx,schema=s)
arm=arm[arm['Region']!='Total']
arm=pd.DataFrame(arm.groupby(['Fecha','Region_Metropolitana'])[['Casos actuales']].sum()).reset_index()
arm=arm.pivot(index='Fecha',columns=['Region_Metropolitana']).T.reset_index().T
arm.columns=arm.iloc[0].astype(str)+' '+arm.iloc[1].astype(str)+' (MINSAL)'
arm=arm.iloc[3:].reset_index()
#arm=arm.rename(columns={'fecha':'Fecha'})

minsal=minsal.merge(arm,how='left',on='Fecha').merge(nrm,how='left',on='Fecha').merge(arms,how='left',on='Fecha')
#----------------------------MINSAL--------------------------------------------------------------------
# -

#movilidad
s="movilidad"
n="isci_entel"
arms=pd.read_sql_table(n, con=cnx,schema=s)
mov=pd.read_sql_table(n, schema=s,con=cnx)
mov_chile=pd.DataFrame(mov.groupby('date')['PERSONAS_MOVILIZADAS'].sum()).reset_index().rename(columns={'date':'Fecha'})
mov_rm=pd.DataFrame(mov[mov['REGION']=='13'].groupby('date')['PERSONAS_MOVILIZADAS'].sum()).reset_index().rename(columns={'date':'Fecha',
                                                                                                                          'PERSONAS_MOVILIZADAS':'PERSONAS_MOVILIZADAS_RM'})
mov_resto_chile=pd.DataFrame(mov[mov['REGION']!='13'].groupby('date')['PERSONAS_MOVILIZADAS'].sum()).reset_index().rename(columns={'date':'Fecha',
                                                                                                                                  'PERSONAS_MOVILIZADAS':'PERSONAS_MOVILIZADAS_RESTO_CHILE'})
mov=mov_chile.merge(mov_rm,on='Fecha',how='outer').merge(mov_resto_chile,on='Fecha',how='outer')

#cuarentenas
s="cuarentenas"
n="cuarentenas"
cuarentenas=pd.read_sql_table(n, con=cnx,schema=s)

cuarentenas

pob_cuarentena=cuarentenas.groupby(['Fecha','COD_PASO'])['T_POB'].sum().reset_index()
pob_cuarentena=pob_cuarentena.pivot(columns='COD_PASO',values='T_POB',index='Fecha').reset_index()
pob_cuarentena.columns=[str(x)+'_CUA' if x is not 'Fecha' else x for x in pob_cuarentena.columns]

pob_cuarentena_rm=cuarentenas[cuarentenas['REG']=='Metropolitana de Santiago'].groupby(['Fecha','COD_PASO'])['T_POB'].sum().reset_index()
pob_cuarentena_rm=pob_cuarentena_rm.pivot(columns='COD_PASO',values='T_POB',index='Fecha').reset_index()
pob_cuarentena_rm.columns=[str(x)+'_CUA_RM' if x is not 'Fecha' else x for x in pob_cuarentena_rm.columns]

pob_cuarentena_rCH=cuarentenas[cuarentenas['REG']!='Metropolitana de Santiago'].groupby(['Fecha','COD_PASO'])['T_POB'].sum().reset_index()
pob_cuarentena_rCH=pob_cuarentena_rCH.pivot(columns='COD_PASO',values='T_POB',index='Fecha').reset_index()
pob_cuarentena_rCH.columns=[str(x)+'_CUA_RCH' if x is not 'Fecha' else x for x in pob_cuarentena_rCH.columns]

pob_cuarentena=pob_cuarentena.merge(pob_cuarentena_rm,on='Fecha',how='outer').merge(pob_cuarentena_rCH,on='Fecha',how='outer')

pob_cuarentena

# +
#s='@perez'
#n='<EMAIL>'
#nacional=pd.read_sql_table(n, con=cnx,schema=s)

# +
#nacional.sort_values(by='Fecha').dropna(subset=['Fecha'])
# -

master=DEIS.merge(icovid,on='Fecha',how='outer').merge(minsal,on='Fecha',
                                                       how='outer').merge(mov,on='Fecha',
                                                                          how='outer').merge(pob_cuarentena,on='Fecha',how='outer')

master=master.sort_values(by='Fecha')

master.columns=[x.replace('x','').replace('.','').replace('(','').replace(')','') for x in master.columns]

#Versión pivoteada
master_melted=melt(master,id_var=['Fecha'],Vars=[x for x in master.columns if x!='Fecha'])
master_melted['Nivel']='Nacional'
master_melted.loc[master_melted['variable'].str.contains('RM'),'Nivel']='RM'
master_melted.loc[master_melted['variable'].str.contains('Metropolitana'),'Nivel']='RM'
master_melted.loc[master_melted['variable'].str.contains('Resto'),'Nivel']='Resto de Chile'
master_melted.loc[master_melted['variable'].str.contains('RCH'),'Nivel']='Resto de Chile'
master_melted.loc[master_melted['variable'].str.contains('RESTO_CHILE'),'Nivel']='Resto de Chile'

name='consolidado_nacional'
schema='nacional'
if not cnx.dialect.has_schema(cnx, schema):
    print('schema '+schema+' does not exist, creating it')
    cnx.execute(sqlalchemy.schema.CreateSchema(schema))
else:
    print('schema '+schema+' exists, will not be created')
master.to_sql(name, schema=schema,con=cnx,if_exists='replace')
name='consolidado_nacional_melted'
master_melted.to_sql(name, schema=schema,con=cnx,if_exists='replace')

os.system('jupyter nbconvert --output /home/jovyan/work/ETLdocs/' + 'ETL_consolidado_nacional.html' + ' --to html ' + 
          '/home/jovyan/work/ETL/consolidado_nacional.ipynb')

StarcoderdataJupyterScriptsDedupFiltered