# ---
# 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
from sklearn.linear_model import LinearRegression
import seaborn as sns; sns.set()
import matplotlib.pyplot as plt
from sqlalchemy import create_engine
from datetime import datetime as dt
import sys
import boto3
import os
import plotly.graph_objects as go
from plotly.subplots import make_subplots
from plotly.graph_objs.layout import YAxis,XAxis,Margin
import chart_studio
import chart_studio.plotly as py

sys.path.append('..')

from modules.stats import *

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

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)

s="nacional"
n="consolidado_nacional"
df=pd.read_sql_table(n, con=cnx,schema=s)


def loglog(df,x,y,z,dpi=150,filename=''):
    df=df.loc[~df[x].isnull(),(x,y,z)]
    df[[x,y]]=df[[x,y]].rolling(7).mean()
    
    xlog_range=10**np.arange(np.log10(df[x].min()),np.log10(df[x].max()),0.3)
    
    fechas=[]
    for xtick in xlog_range:
        #print(xtick)
        idx=(df[x].fillna(0)-xtick).abs().argsort()[:2].iloc[1]
        #print(idx)
        #[1]
        #fechas.append(nacional.loc[idx,'Fecha'])
        fechas.append(idx)
        
    dates=df[z].dt.date.astype(str)
    fechas=dates.iloc[fechas].values
    
    layout = go.Layout(showlegend=False,
        xaxis=XAxis(
            title=x,
            type='log'
        ),
        xaxis2 = XAxis(
            title="Fecha",
            overlaying= 'x', 
            type='log',
            side= 'top',
            tickmode = 'array',
            tickangle=315,
        tickvals = xlog_range,
        ticktext = fechas
        ),
        yaxis=dict(
            title=y,
            type='log'
        ),
    )

    # Create figure with secondary x-axis
    fig = go.Figure(layout=layout)

    # Add traces
    fig.add_trace(
        go.Scatter(x=df[x], y=df[y]),
    )

    fig.add_trace(
        go.Scatter(x=df[x], y=df[y], xaxis='x2'),
    )

    #fig.write_html(filename)
    py.iplot(fig, filename = filename)


PLOTLY_API_KEY = os.environ['PLOTLY_API_KEY']
PLOTLY_USER = os.environ['PLOTLY_USER ']

chart_studio.tools.set_credentials_file(username=PLOTLY_USER, api_key=PLOTLY_API_KEY)

chart_studio.tools.set_config_file(world_readable=True,
                             sharing='public')

loglog(df,'Fallecidos DEIS Acumulado','Fallecidos DEIS Diarios','Fecha',dpi=100,filename='Muertes_DEIS.html')

loglog(df,'Casos Acumulados ICOVID','Casos Nuevos ICOVID','Fecha',dpi=100,filename='Contagios_ICOVID.html')

# +
#ACCESS_KEY = os.environ['S3_ACCESS_KEY']
#SECRET_KEY = os.environ['S3_SECRET_KEY']
#bucket=os.environ['S3_BUCKET']

# +
#s3 = boto3.client('s3', aws_access_key_id=ACCESS_KEY,
#                      aws_secret_access_key=SECRET_KEY)

# +
#filelist= [file for file in os.listdir(path) if file.endswith('.html')]

# +
#filelist

# +
#for f in filelist:
#    s3.upload_file(path+'/'+f, bucket, f, ExtraArgs={'ACL':'public-read','ContentType':'text/html'})
# -

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

StarcoderdataJupyterScriptsDedupFiltered