def getConnexion():
"""
Hace conexion con la base de datos, las credenciales en .env
return:
mySql conexion
"""
try:
return mysql.connector.connect(host=getVariable('host')[0],
user=getVariable('user')[0],
passwd=getVariable('passwd')[0])
print('Conexión exitosa')
except:
print('Error Conexion')
def sendEmail(pathPdf):
"""
Envía email con el informe en pdf
args:
pathPdf: String ruta relativa en la que se encuentra el informe en pdf
return:
None
"""
subject = "Informe adjunto en el Email"
body = "Report in pdf"
sender_email = getVariable('email')[0]
receiver_email = input('Insert the receiver email: ')
password = getVariable('password')[0]
# Cabecera del email
message = MIMEMultipart()
message["From"] = sender_email
message["To"] = receiver_email
message["Subject"] = subject
message["Bcc"] = receiver_email # Recommended for mass emails
# Cuerpo del email
message.attach(MIMEText(body, "plain"))
filename = pathPdf # el nombre va a ser el mismo que el del pdf
# Abro el pdf
with open(filename, "rb") as attachment:
part = MIMEBase("application", "octet-stream")
part.set_payload(attachment.read())
# Codificación en ASCII
encoders.encode_base64(part)
# Añado la cabecera
part.add_header(
"Content-Disposition",
f"attachment; filename= {filename}",
)
# Se agrega el archivo adjunto
message.attach(part)
text = message.as_string()
# Login usando protocolo seguro ssl y envío del mail
context = ssl.create_default_context()
with smtplib.SMTP_SSL("smtp.gmail.com", 465, context=context) as server:
server.login(sender_email, password)
server.sendmail(sender_email, receiver_email, text)
def getDfGas():
"""
Carga los datos de emisión de gases del efecto invernadero del dataset guardado en outputs, este dataset fue descargado
de una base de datos de la Comisión Europea
return:
df: Pandas DataFrame con los datos de emisiones con la forma adecuada.
"""
listCountry = getVariable("listCountry")
listYear = getVariable("listYear")
listCode = getVariable("listCountryCode")
listCodeGas = getVariable('listCodeGas')
cCode = 'Country Code'
df = pd.read_table("./../inputs/sdg_13_10.tsv")
df.columns = [
'Country Code', '1990', '1991', '1992', '1993', '1994', '1995', '1996',
'1997', '1998', '1999', '2000', '2001', '2002', '2003', '2004', '2005',
'2006', '2007', '2008', '2009', '2010', '2011', '2012', '2013', '2014',
'2015', '2016', '2017'
]
df = df.loc[df['Country Code'].isin(
(list(map((lambda code: 'GHG_T_HAB,' + code), listCodeGas))))]
df[cCode] = df[cCode].apply(lambda code: code.split(",")[-1])
df = df.drop(columns=['2017'])
df[cCode] = ['BEL', 'DEU', 'ESP', 'ITA', 'POL', 'ROU', 'TUR', 'GBR']
df.columns
list(df[cCode].values)
df = df.reset_index()
df = df.drop(columns=['index'])
newDf = pd.DataFrame()
listTupleValues = list(df.groupby([cCode] + listYear).groups.keys())
newDf[cCode] = [
listTupleValues[i][0] for i in range(len(listTupleValues))
for j in range(len(listYear))
]
newDf['YEAR'] = [ele for ele in (listYear * 8)]
newDf['gas/hab'] = [
listTupleValues[i][j] for i in range(len(listTupleValues))
for j in range(1, len(listTupleValues[i]))
]
newDf['gas/hab'] = list(
map(lambda ele: float(str(ele).split()[0]),
(list(newDf['gas/hab'].values))))
return newDf
def getDfPop():
"""
Computa el cuadro de datos de población de los países que integra la aplicación a partir de un dataset de Kaggle
que está guardado en la carpeta inputs, en la raíz del proyecto.
return:
df: Pandas DataFrame con los datos de población de los paises
"""
listCountry = getVariable("listCountry")
listYear = getVariable("listYear")
df = pd.read_csv("../inputs/world_pop.csv")
df = df[['Country Code', 'Country'] + listYear]
df = df.loc[df['Country'].isin(listCountry)]
df = df.reset_index()
df["PopAvg"] = df[listYear].mean(axis=1)
df["PopAvg"] = df["PopAvg"].apply(lambda num: int(round(num, 0)))
df = df.sort_values(by=['PopAvg'], ascending=False).reset_index()[[
'Country Code', 'Country', 'PopAvg'
]]
return df
def getFullYearReport():
"""
Muestra el gráfico de todos los años, guarda el informe en pdf y y el gráfico en png
return:
df: Pandas DataFrame
"""
df = getDfFromDB()
df['discharges/10**5hab']= df['discharges/10**5hab'].apply(lambda ele: ele/100)
df.columns = ['Country Code', 'Country', 'PopAvg', 'Year', 'dischargesPer1000hab','tonnesPerCap']
df = df[['Year', 'dischargesPer1000hab','tonnesPerCap']]
df = df[['Year', 'dischargesPer1000hab','tonnesPerCap']]
df = df.sort_values(['Year'])
df = df.reset_index()
df = df[['Year', 'dischargesPer1000hab','tonnesPerCap']]
df = df.groupby('Year').mean()
#Se quitan los outliers (valores extremos) porque todos los paises no tienen registros todos los años
stats = df.describe().transpose()
stats['IQR'] = stats['75%'] - stats['25%']
outliers = pd.DataFrame(columns=df.columns)
for col in stats.index:
iqr = stats.at[col,'IQR']
cutoff = iqr * 1.5
lower = stats.at[col,'25%'] - cutoff
upper = stats.at[col,'75%'] + cutoff
results = df[(df[col] < lower) |
(df[col] > upper)].copy()
results['Outlier'] = col
outliers = outliers.append(results)
results = results.reset_index()
df = df.reset_index()
dfWihoutOutliers=df[~df['Year'].isin(list(results['Year'].values))]
dfWihoutOutliers = dfWihoutOutliers.reset_index()
dfWihoutOutliers = dfWihoutOutliers[['Year', 'dischargesPer1000hab','tonnesPerCap']]
dfWihoutOutliers=dfWihoutOutliers.drop(dfWihoutOutliers.index[0])
getPlotbyCountry(dfWihoutOutliers," ".join(getVariable('listCountryCode')),'Year','dischargesPer1000hab','tonnesPerCap',True)
return dfWihoutOutliers
sys.exit()
parser = argparse.ArgumentParser(description='Options for your report')
parser.add_argument(
"-i",
"--integer",
type=int,
required=True,
help='0=Full Report 1=byYear 2=byCountry 3=Create or Update DB')
parser.add_argument("-s",
"--string",
type=str,
required=True,
help='Years:1990 to 2010 Countries: {}'.format(" ".join(
getVariable('listCountryCode'))))
args = parser.parse_args()
entrada = args.string
option = args.integer
if option == 0:
print(getFullYearReport())
if input('Send a email with report?(Y/other) ') == 'Y':
sendEmail('./../outputs/{}.pdf'.format('full'))
elif option == 1:
if entrada not in getVariable('listYear'):
wrongInput()
print(getFinalYearDf(entrada))
if input('Send a email with report?(Y/other) ') == 'Y':