#!/usr/bin/env python3 # turismo_data.py """Tratamiento de datos de turismo para django""" import logging import sys import pathlib import time from datetime import datetime from dateutil.relativedelta import relativedelta #from urllib import request import pandas as pd import numpy as np import matplotlib.pyplot as plt #import matplotlib.gridspec as gridspec # Regressions from scipy import signal from statsmodels.tsa.stattools import bds, acf, pacf from statsmodels.graphics.tsaplots import plot_acf, plot_pacf from statsmodels.tsa.stattools import adfuller # Dickey Fuller aumenado import statsmodels.api as sm logging.basicConfig( format="%(asctime)s %(levelname)s:%(name)s: %(message)s", level=logging.DEBUG, datefmt="%H:%M:%S", stream=sys.stderr, ) logger = logging.getLogger(__name__) def transform_file_df (local_log, f): # Fun: Fichero a dataframe ''' Transform file to dataframe Parameters: ---------- local_log: False activa el logging f: fichero de entrada Return: ---------- df: dataframe datos del fichero ''' logger = logging.getLogger('&transform_file_df') logger.disabled = local_log try: logger.info('File in: ' + f) df = pd.read_excel(f, header=0) logger.info('Return: df-> len: {}, type: {}'.format(len(df), type(df))) logger.info(df.describe()) return df except: # catch *all* exceptions logging.exception('Error: [{}]'.format(sys.exc_info())) def transform_filter_cat (local_log, df, cod, n_last): # Fun: Filtra por categoria ''' Filtra el dataframe por categoria Parameters: ---------- local_log: False activa el logging df: dataframe con los datos de todas las categorías cod: codigo de la categoria a filtrar n_last: filtramos los n ultimos meses return: ---------- df0: dataframe datos de la categoria ''' logger = logging.getLogger('&transform_filter_cat') logger.disabled = local_log try: logger.info('df in: ') df1 = df.loc[df['COD'] == cod] df2 = df1[['FECHA', 'VALOR']] df2.set_index('FECHA', inplace=True) df2 = df2.sort_values('FECHA') # Coluna añadida con valores retrasados 12 meses df2['VALOR(t-12)'] = df2['VALOR'].shift(12) df2['%-12'] = round((df2['VALOR']-df2['VALOR(t-12)'])*100/df2['VALOR(t-12)'],2) df2 = df2.tail(n_last) # Ultimos n meses print(df2) x = df2.index.tolist() # Valores de X y = df2['VALOR'].tolist() # Valores de y df0 = df2[['VALOR']] df0_lag12 = df2[['VALOR(t-12)']] df0_var12 = df2[['%-12']] xy_dict = dict(df0) logger.info('Return: df0 -> len: {}, type: {}'.format(len(df0), type(df0))) logger.info(df0.describe()) return xy_dict, x, y except: # catch *all* exceptions logging.exception('Error: [{}]'.format(sys.exc_info())) def data_download (local_log): # Descarga ficheros de la web ''' Descarga los datos a excel de aeronaves (salidas y llegadas), pasajeros y mercancías (salidas y llegadas) de la web: https://www.fomento.gob.es/BE/?nivel=2&orden=03000000 ''' logger = logging.getLogger('&data_download') logger.disabled = local_log urls = ['https://www.fomento.gob.es/BE/sedal/03010100.XLS', 'https://www.fomento.gob.es/BE/sedal/03010200.XLS', 'https://www.fomento.gob.es/BE/sedal/03010300.XLS' ] ficheros =['Aeronaves', 'Pasajeros', 'Mercancias'] for url in urls: df = pd.read_excel(url, header=0) #column_names = ['PERIODO', 'TOTAL', 'NACIONALTOTAL', 'NACIONALREGULAR', 'NACIONALNOREGULAR', 'INTERNACIONALTOTAL', 'INTERNACIONALREGULAR', 'INTERNACIONALNOREGULAR'] #df = df.set_axis(column_names, axis = 1) # Cambia el nombre de las columnas fichero = 'd_' + ficheros[urls.index(url)] + '.xlsx' df.to_excel(fichero, index = False) logger.info(fichero + ': '+ url) return None def data_transform (local_log, f): # Transforma fichero a serie temporal ''' Transforma datos del fichero excel original ''' logger = logging.getLogger('&data_transform') logger.disabled = local_log df = pd.read_excel(f, header=10) column_names = ['PERIODO', 'TOTAL', 'NACIONALTOTAL', 'NACIONALREGULAR', 'NACIONALNOREGULAR', 'INTERNACIONALTOTAL', 'INTERNACIONALREGULAR', 'INTERNACIONALNOREGULAR'] df = df.set_axis(column_names, axis = 1) # Cambia el nombre de las columnas logger.info('File: ' + f) meses = ['Ene', 'Feb', 'Mar', 'Abr', 'May', 'Jun', 'Jul', 'Ago', 'Sep', 'Oct', 'Nov', 'Dic'] anos = [] for i in range(1990, 2050,1): anos.append(str(i)) df['ANO'] = df['PERIODO'].astype(str).str[:4] df['MES'] = df['PERIODO'].astype(str).str[-3:] for row in range(0,round(len(df)/1),1): if not(df.loc[row,'MES'] in meses): df.at[row, 'MES'] = '' else: df.at[row, 'MES'] = '{:02}'.format(meses.index(df.loc[row,'MES'])+1) if not(df.loc[row,'ANO'] in anos): df.at[row, 'ANO'] = '' # Borra filas vacias y de comentarios if df.loc[row,'MES']=='' and df.loc[row,'ANO']=='': df = df.drop(row) df=df.reset_index(drop=True) # Reset index for row in range(0,round(len(df)/1),1): if df.loc[row,'ANO']=='' and row > 0: df.at[row, 'ANO'] = df.loc[row-1,'ANO'] df['FECHA'] = df['ANO'] + df['MES'] + '01' # Datos anuales df_anual= df[df['MES'] == ''] df_anual.drop(['ANO', 'MES','FECHA'], inplace=True, axis=1)# Elimino la columna df_anual = df_anual.reset_index(drop=True) # Reset index # Datos mensuales df = df[df['MES'] != ''] df.drop('PERIODO', inplace=True, axis=1)# Elimino la columna df = df.reset_index(drop=True) # Reset index df['FECHA'] = pd.to_datetime(df['FECHA']).dt.normalize() df.rename(columns={'FECHA':'PERIODO'}, inplace=True) #Exporta ficheros procesados fichero = f[2:] # Menual df.to_excel('dtm_'+ fichero, index = False) # Anual df_anual.to_excel('dta_'+ fichero, index = False) return df, df_anual def grafica(fanual, fmensual, titulo): # Grafica las series temporales, anuales y mensuales df0 = pd.read_excel(fmensual) df1 = pd.read_excel(fanual) x0 = df0['PERIODO'].tolist() y0 = df0['TOTAL'].tolist() x1 = df1['PERIODO'].tolist() y1 = df1['TOTAL'].tolist() # plot with various axes scales fig, axs = plt.subplots(ncols=1, nrows=2, figsize=(13, 7), constrained_layout=True) font = {'family': 'serif', 'color': 'darkblue', 'weight': 'bold', 'size': 16, } fig.suptitle(titulo,fontdict=font) plt.rcParams['axes.titley'] = 1.0 # y is in axes-relative coordinates. plt.rcParams['axes.titlepad'] = -14 # pad is in points... ax = axs[0] # Datos mensuales ax.plot(x0, y0, label = 'Mensual') #ax.set_title('Mensual') ax.grid(True) ax.legend(loc='upper left') ax = axs[1] # Datos anuales ax.plot(x1, y1, 'r-', label = 'Anual') #ax.set_title('Anual') ax.grid(True) ax.legend(loc='upper left') titulofil = titulo[0:titulo.find(' ')] # Nombre hasta el primer blanco fig.savefig('g_'+ titulofil + '.png') fig.show() def pinta(df, titulo): # Grafica un dataframe de serie temporal ''' Parameters ---------- df : dataframe de entrada serie temporal Returns ------- None Graficas: Señal, Analisis espectrales, autocorrelación y autocorrelacion parcial ''' font = {'family': 'serif', 'color': 'darkblue', 'weight': 'bold', 'size': 16, } fig, axes = plt.subplots(3, figsize=(13, 7)) fig.suptitle(titulo,fontdict=font) axes[0].set(title='Signal T = ' + str(df.shape[0])) axes[0].plot(df['TOTAL'], label='Signal') axes[0].legend(loc='upper left') axes[0].grid() dt = 2*np.pi/2 fs = len(df.index) / dt # periodos frec, Pxx_den = signal.welch(df['TOTAL'], fs, nperseg=len(df.index)) # fft welch axes[1].set(title='Spectral analysis. Seasonality = ' + str(round(df.shape[0]/2/np.pi, 2)) + ' x f') axes[1].psd(df['TOTAL'], NFFT=len(df.index), Fs=2, pad_to=len(df.index), label='PSD', ) axes[1].legend(loc='upper left') axes[1].grid() axes[2].set(title='Spectral analysis / Seasonality') axes[2].plot(frec, Pxx_den, label='PSD Welch') axes[2].legend(loc='upper left') axes[2].set_xlabel(df.index.freq) axes[2].set_ylabel('Welch') axes[2].grid() fig.tight_layout() plt.show() fig, ax = plt.subplots(1, 2, figsize=(13, 5)) fig.suptitle(titulo,fontdict=font) plot_acf(df, lags=40, ax=ax[0], title='Autocorrelation MA') plot_pacf(df, lags=40, ax=ax[1], title='Partial Autocorrelation AR') ax[0].grid() ax[1].grid() plt.show() def test_adf(local_log, df): # Test de Dickey-Fuller aumented #from statsmodels.tsa.stattools import adfuller # Dickey Fuller aumenado abstract = ''' Augmented Dickey-Fuller test is un test estadístico llamado test de raiz unitaria. Test de estacionariedad. Hipotesis nula (H0): Serie temporal tiene raiz unitaria. NO ESTACIONARIA. Tiene alguna estructura dependiente del tiempo. Hipotesis alterativa (H1): Serie temporal no tiene raiz unitaria. ESTACIONARIA. No tiene dependencia del tiempo su estructura. p-value > 0.05 aceptamos H0, la serie tiene raiz unitaria, por tanto es NO ESTACIONARIA. p-value <= 0.05 rechazamos H0, la serie no tiene raiza unitaria, por tanto es ESTACIONARIA. ''' logger = logging.getLogger('&test_adf') logger.disabled = local_log logger.info('Augmented Dickey-Fuller') try: r = adfuller(df, autolag='AIC') print('CONTRASTE de DICKEY-FULLER\b') print('ADF Statistic: %f' % r[0]) print('p-value: %f' % r[1]) print('Critical Values:') for key, value in r[4].items(): print('\t%s: %.3f' % (key, value)) ESTACIONARIA = True # Si la serie es estacionaria if r[1] > 0.05: ESTACIONARIA = False return ESTACIONARIA except ValueError as e: logging.exception('ValueError:', e) except: # catch *all* exceptions e = sys.exc_info() logging.exception("Error: [{}]".format(e)) def data_statistic(df, titulo): # Datos estadísticos serie temporal ''' Pinta la grafica y saca los estadisticos principales Returns ------- None. ''' pinta(df, titulo) print('Tipo de datos:', df.dtypes) print('Shape: ', df.shape) print('Index Frecuency: ', df.index.freq) print('Estadisticos principales: ', df.describe()) print(df) print('HO: Serie independiente y estadisticamente distribuida (iid): ', bds(df)) df.hist(grid=True, legend = True, figsize=(13,7)) # Histograma plt.title(titulo) plt.show() def date_recessions(local_log): # Recesiones USA ''' Parameters ---------- local_log : logging local disabled Returns ------- dfyrc : dataframe, serie temporal, años de recesion dfmrc : dataframe, serie temporal, meses de recesion ''' logger = logging.getLogger('&date_recessions') logger.disabled = local_log dict_recessions = { 1961 : 'Superávit - Sube: Tipos de Interés', 1970 : 'Guerra Vietnan - Sube: Deficit, Infraccón y Tipo de Interés', 1974 : 'Petróleo - se cuadriplica, Estanflacción', 1975 : 'Petróleo - se cuadriplica, Estanflacción', 1980 : 'W Doble Inmersión - Sube: Tipos de Interés', 1981 : 'Revolución Iraní - Sube: Petroleo', 1982 : 'Revolución Iraní - Sube: Petroleo', 1990 : 'Tras una larga expansión - Sube: Inflacción y Tipos de Interés, pesimismo consumidores', 1991 : 'Tras una larga expansión - Sube: Inflacción y Tipos de Interés, pesimismo consumidores', 2001 : 'Burbuja Puntocom y 11 S', 2007 : 'Burbuja Inmobiliaria', 2008 : 'Burbuja Inmobiliaria', 2020 : 'Coronavirus' } try: logger.info('Años recesión USA') # YEAR recessions yrc = [1961, 1970, 1974, 1975, 1980, 1981, 1982, 1990, 1991, 2001, 2007, 2008, 2020] dfyrc = pd.DataFrame(yrc, columns=['PERIODO']) dfyrc['RC'] = 1 dfyrc['PERIODO'] = pd.to_datetime(dfyrc['PERIODO'], format="%Y") dfyrc.set_index('PERIODO', inplace=True) # Serie de tiempo # print(dfyrc) # MONTHS recessions mrc = [] for y in yrc: for m in range(1, 13): e = [(str(y) + '-' + str(m))] mrc = mrc + e dfmrc = pd.DataFrame(mrc, columns=['PERIODO']) dfmrc['RC'] = 1 dfmrc['PERIODO'] = pd.to_datetime(dfmrc['PERIODO'], format="%Y-%m") dfmrc.set_index('PERIODO', inplace=True) # Serie de tiempo # print(dfmrc) return dfyrc, dfmrc, dict_recessions except ValueError as e: logging.exception('ValueError:', e) except: # catch *all* exceptions e = sys.exc_info() logging.exception("Error: [{}]".format(e)) def regression(local_log, df, dfmrc, titulo): # Regresion ''' Regresion por HPfilter, uc, uc-arima' Parameters ---------- df : dataframe, serie tiempo dfmrc : dataframe, serie de tiempo, con los datos y recesiones Returns ------- None. ''' #import statsmodels.api as sm logger = logging.getLogger('®resion') logger.disabled = local_log logger.info('regresiones y gráficas') try: column_names = ['RA'] df = df.set_axis(column_names, axis = 1) # Cambia el nombre de las columnas column_names = ['RA', 'RC'] dfmrc = dfmrc.set_axis(column_names, axis = 1) # Cambia el nombre de las columnas df.sort_index(ascending=True, inplace=True) # Ordena por fechas ascendente dfmrc.sort_index(ascending=True, inplace=True) # Ordena por fechas ascendente hp_cycle, hp_trend = sm.tsa.filters.hpfilter(df, lamb=129600) # Con 1600 filtra bien SS y TA # lamb sugerido 1600 para datos trimestrales, 6.5 (1600/4^4) anuales y 129600 (1600*3*3^4) mensuales # Cuanto más bajo es lamb mayor rizado en la tendencia mod_ucarima = sm.tsa.UnobservedComponents(df, 'rwalk', autoregressive=12) # Con 12 da mejor R2 que con 4 # Here the powell method is used, since it achieves a # higher loglikelihood than the default L-BFGS method res_ucarima = mod_ucarima.fit(method='powell', disp=False) print(res_ucarima.summary()) mod_uc = sm.tsa.UnobservedComponents( df, 'rwalk', cycle=True, stochastic_cycle=True, damped_cycle=True, ) # Here the powell method gets close to the optimum res_uc = mod_uc.fit(method='powell', disp=False) # but to get to the highest loglikelihood we do a # second round using the L-BFGS method. res_uc = mod_uc.fit(res_uc.params, disp=False) print(res_uc.summary()) font = {'family': 'serif', 'color': 'darkblue', 'weight': 'bold', 'size': 16, } fig, axes = plt.subplots(2, figsize=(13, 7)) fig.suptitle(titulo,fontdict=font) axes[0].set(title='Level/trend component') axes[0].plot(df.index, res_uc.level.smoothed, label='UC') axes[0].plot(df.index, res_ucarima.level.smoothed, label='UC-ARIMA(4,0)') axes[0].plot(hp_trend, label='HP Filter') axes[0].plot(dfmrc['RA'], label='Signal') axes[0].plot(dfmrc['RC'], label='recessions') axes[0].legend(loc='upper left') axes[0].grid() axes[1].set(title='Cycle component') axes[1].plot(df.index, res_uc.cycle.smoothed, label='UC') axes[1].plot(df.index, res_ucarima.autoregressive.smoothed, label='UC-ARIMA(4,0)') axes[1].plot(hp_cycle, label='HP Filter') axes[1].legend(loc='upper left') axes[1].grid() fig.tight_layout() plt.show() # Perform prediction and forecasting 12 meses ************ logger.info('PREDICTION & FORECAST') predict_uc_get = res_uc.get_prediction(alpha=0.5) forecast_uc_get = res_uc.get_forecast(steps=12, alpha=0.5) predict_ucarima_get = res_ucarima.get_prediction(alpha=0.5).summary_frame() forecast_ucarima_get = res_ucarima.get_forecast(steps=12, alpha=0.5).summary_frame() predict_uc = res_uc.predict(steps=12) predict_ucarima = res_ucarima.predict(steps=12) forecast_uc = res_uc.forecast(steps=12) forecast_ucarima = res_ucarima.forecast(steps=12) # Indice de fechas de forecast f_fistforecast = max(df.index) + relativedelta(months=1) index_forecast = [] for i in range(12): index_forecast.append(f_fistforecast + relativedelta(months=i)) # Plot results together fig, axes = plt.subplots(2, figsize=(13, 7)) fig.suptitle(titulo,fontdict=font) axes[0].set(title='Predict') axes[0].plot(dfmrc['RA'], label='Signal') axes[0].plot(res_uc.predict(steps=12)[12:], label='Predict UC') axes[0].plot(res_ucarima.predict(steps=12)[12:], label='Predict UC-ARIMA(4,0)') axes[0].plot(dfmrc['RC'], label='recessions') axes[0].legend(loc='upper left') axes[0].grid() axes[1].set(title='Forecast') axes[1].plot(dfmrc['RA'][-12:], label='Signal') axes[1].plot(index_forecast, res_uc.forecast(steps=12)[0:], label='Forecast UC') axes[1].plot(index_forecast, res_ucarima.forecast(steps=12)[0:], label='Forecast UC-ARIMA(4,0)') axes[1].legend(loc='upper left') axes[1].grid() fig.tight_layout() plt.show() # Plot the results prediction and forecast UC *************** predict = res_uc.get_prediction(alpha=0.5) forecast = res_uc.get_forecast(steps=12, alpha=0.5) fig, ax = plt.subplots(2, figsize=(13, 7)) fig.suptitle(titulo,fontdict=font) ax[0].set(title='Prediction & Forecast (5%) -- UC --') df['RA'][4:].plot(ax=ax[0], style='k.', label='Signal') predict.predicted_mean[4:].plot(ax=ax[0], label='UC Prediction') predict_ci = predict.conf_int(alpha=0.05) predict_index = np.arange(len(predict_ci)) ax[0].fill_between(predict_ci.index[4:], predict_ci.iloc[4:, 0], predict_ci.iloc[4:, 1], alpha=0.1) forecast.predicted_mean.plot(ax=ax[0], style='r', label='UC Forecast') forecast_ci = forecast.conf_int() forecast_index = np.arange(len(predict_ci), len(predict_ci) + len(forecast_ci)) ax[0].fill_between(forecast_ci.index, forecast_ci.iloc[:, 0], forecast_ci.iloc[:, 1], alpha=0.1) ax[0].legend(loc='upper left') ax[0].grid() ax[1].set(title='Prediction & Forecast (5%) -- UC > 2019 --') df['RA'].loc['2019-01-01':].plot(ax=ax[1], style='k.', label='Signal') # Construct prediction predict = res_uc.get_prediction(alpha=0.05).summary_frame()['2019-01-01':] predict['mean'].plot(ax=ax[1], label='UC Prediction') ax[1].fill_between(predict.index, predict['mean_ci_lower'], predict['mean_ci_upper'], alpha=0.1) # Construct the forecasts fcast = res_uc.get_forecast(steps=12, alpha=0.05).summary_frame() fcast['mean'].plot(ax=ax[1], style='r', label='UC Forecast') ax[1].fill_between(fcast.index, fcast['mean_ci_lower'], fcast['mean_ci_upper'], alpha=0.1) # Cleanup the image #ax[1].set_ylim((df['RA'].loc['2019-01-01':].min()*0.9, df['RA'].loc['2019-01-01':].max()*1.1)) ax[1].legend(loc='lower left') ax[1].grid() fig.tight_layout() plt.show() # Plot the results prediction and forecast UC-ARIMA *************** predict = res_ucarima.get_prediction(alpha=0.5) forecast = res_ucarima.get_forecast(steps=12, alpha=0.5) fig, ax = plt.subplots(2, figsize=(13, 7)) fig.suptitle(titulo,fontdict=font) ax[0].set(title='Prediction & Forecast (5%) -- UC-ARIMA --') df['RA'][4:].plot(ax=ax[0], style='k.', label='Signal') predict.predicted_mean[4:].plot(ax=ax[0], label='UC-ARIMA Prediction') predict_ci = predict.conf_int(alpha=0.05) predict_index = np.arange(len(predict_ci)) ax[0].fill_between(predict_ci.index[4:], predict_ci.iloc[4:, 0], predict_ci.iloc[4:, 1], alpha=0.1) forecast.predicted_mean.plot(ax=ax[0], style='r', label='UC-ARIMA Forecast') forecast_ci = forecast.conf_int() forecast_index = np.arange(len(predict_ci), len(predict_ci) + len(forecast_ci)) ax[0].fill_between(forecast_ci.index, forecast_ci.iloc[:, 0], forecast_ci.iloc[:, 1], alpha=0.1) ax[0].legend(loc='upper left') ax[0].grid() ax[1].set(title='Prediction & Forecast (5%) -- UC-ARIMA > 2019 --') df['RA'].loc['2019-01-01':].plot(ax=ax[1], style='k.', label='Signal') # Construct prediction predict = res_ucarima.get_prediction(alpha=0.05).summary_frame()['2019-01-01':] predict['mean'].plot(ax=ax[1], label='UC-ARIMA Prediction') ax[1].fill_between(predict.index, predict['mean_ci_lower'], predict['mean_ci_upper'], alpha=0.1) # Construct the forecasts fcast = res_ucarima.get_forecast(steps=12, alpha=0.05).summary_frame() fcast['mean'].plot(ax=ax[1], style='r', label='UC-ARIMA Forecast') ax[1].fill_between(fcast.index, fcast['mean_ci_lower'], fcast['mean_ci_upper'], alpha=0.1) # Cleanup the image # ax[1].set_ylim((df['RA'].loc['2019-01-01':].min()*0.9, df['RA'].loc['2019-01-01':].max()*1.1)) ax[1].legend(loc='lower left') ax[1].grid() fig.tight_layout() plt.show() logger.info('CALCULO R2') print('CALCULO - R2') y = res_uc.fittedvalues[4:]+res_uc.resid[4:] R2_UC = 1 - np.sum(res_uc.resid[4:]**2)/np.sum((y-y.mean())**2) print('R2...... UC........', R2_UC) y = res_ucarima.fittedvalues[4:]+res_ucarima.resid[4:] R2_UCARIMA = 1 - np.sum(res_ucarima.resid[4:]**2)/np.sum((y-y.mean())**2) print('R2...... UCARIMA........', R2_UCARIMA) R2 = {'R2_UC': R2_UC, 'R2_UCARIMA': R2_UCARIMA} df_R2 = pd.DataFrame(list(R2.items()), columns=['R2', 'VALUE']) f = 'R2_'+ titulo.split(' ')[0] + '.xlsx' df_R2.to_excel(f, index = False) forecast = np.e**fcast # Forecast UCARIMA en totales return forecast except ValueError as e: logging.exception('ValueError:', e) except: # catch *all* exceptions e = sys.exc_info() logging.exception("Error: [{}]".format(e)) def main_regression(local_log, ficheros, unidades): # Main de regresiones ''' Regresion Returns ------- None. ''' logger = logging.getLogger('&main_regression') logger.disabled = local_log try: # Grafica los ficheros for fichero in ficheros: logger.info('Data processing ' + fichero) titulo = fichero + ' (' + unidades[ficheros.index(fichero)] + ')' fm = 'dtm_{}.xlsx'.format(fichero) df = pd.read_excel(fm) # file to dataframe df = df[['PERIODO', 'TOTAL']] # Selecciono columnas de df df.set_index('PERIODO', inplace=True) # Serie de tiempo #data_statistic(df) # Aplicamos logaritmo neperiano a los datos df['TOTAL'] = np.log(df['TOTAL']) # Pinta y saca estadisticas de los datos y contrastes data_statistic(df, titulo) # Dickey Fuller test print('La serie es estacionaria: ', test_adf(local_log, df)) # Años y meses de recesión en USA yrc, mrc, dict_recessions = date_recessions(local_log) df_recessions = pd.DataFrame(list(dict_recessions.items()), columns=['YEAR', 'RECESSION']) df_recessions.to_excel('RecesionesUSA.xlsx', index = False) # Añadimos la columna recesion RC al fichero index fecha, y solo los meses con recesion df_rc = pd.merge(df, mrc, on='PERIODO', how='left') # Relleno columna RC para que la pinte bien df_rc.fillna(0, inplace=True) # Rellena los blancos con ceros # Rellena con el máximo valor y el mínimo para pintar bien las recesiones df_rc['RC'] = df_rc['RC'].map({1: df_rc['TOTAL'].max(), 0: df_rc['TOTAL'].min()}, na_action=None) # Regresamos. Aplicamos modelo. forecast = regression(local_log, df, df_rc, titulo) print(forecast) print(forecast['mean']) # Exportamos el forecast a excel ultima_fecha = max(df.index).strftime("%Y%m") f = 'forecast_'+ fichero + '_'+ ultima_fecha + '.xlsx' forecast.to_excel(f, index = True) except: # catch *all* exceptions logging.exception('Error: [{}]'.format(sys.exc_info())) def main_transform(local_log, ficheros, unidades, carga = 0): # Main de download y transformación de datos ''' Parameters ---------- local_log : TYPE: Boolean, False - activa el logging carga : TYPE: chart, optional, 1 : carga y grafica resto o nada : grafica The default is 0. Returns ------- None. ''' try: # Carga los ficheros de la web y los transforma if carga == 1: # Carga los ficheros originales de la web data_download(local_log) # Toma los ficheros originales los transforma en dos uno anual y otro mensual for fichero in ficheros: f = 'd_{}.xlsx'.format(fichero) data_transform(local_log, f) # Grafica los ficheros for fichero in ficheros: fa = 'dta_{}.xlsx'.format(fichero) fm = 'dtm_{}.xlsx'.format(fichero) titulo_grafica = fichero + ' (' + unidades[ficheros.index(fichero)] + ')' grafica(fa, fm, titulo_grafica) except: # catch *all* exceptions logging.exception('Error: [{}]'.format(sys.exc_info())) return None def main (local_log): # Fun: Main ''' Parameters ---------- local_log : TYPE: Boolean, False - activa el logging Returns ------- None. ''' try: f = 'd_turismo.xlsx' df = transform_file_df(local_log, f) cod = 'EOT1' xy, x, y = transform_filter_cat (local_log, df, cod, 24) print(xy) except: # catch *all* exceptions logging.exception('Error: [{}]'.format(sys.exc_info())) return None if __name__ == "__main__": # Main start = time.perf_counter() assert sys.version_info >= (3, 7),"Script requires Python 3.7+." here = pathlib.Path(__file__).parent logger.disabled = False logger.info(here) logger.info('Initium novum') # ********** INICIO ********** local_log_disabled = False # False activa el logging main(local_log_disabled) # ********** FIN ********** logger.info('Ave verum') elapsed = time.perf_counter() - start logger.info(f"Program completed in {elapsed:0.5f} seconds.")