Homec4science
Diffusion bilan_CO2 (master) Browse

bilan_CO2/scripts/functions.py
bcab7faf52b9master

Last Change
Raw File

functions.py
View Options

import numpy as np
import plotly.graph_objects as go
from plotly.subplots import make_subplots
import pandas as pd
import datetime
import os
import shutil
def read_GRI(path):
elec_df = pd.read_excel(path, skiprows=19, sheet_name="Bilans énergétiques")
df = elec_df.T
mask = ~df.index.astype(str).str.contains("Unnamed")
df1 = df.iloc[mask]
df1.columns = df1.iloc[0]
df_format = df1.drop(df1.index[0])
list_campus = ["EPFL - Vaud", "EPFL - Valais", "EPFL - Genève", "EPFL - Neuchâtel", "EPFL - Divers"]
years = list(df_format.index[1:])
#Metadata: L0: raw data, L1A: bought and sold electricity, L1B: tot by campus
#ELECTRICITY
L0_ID_elec = [3,4,5,6,8,9,10,12,13,14,15,16,17,18,19,20,21]
campus_ID_elec = [1,22,23,24,25]
df_elec_L0 = parse_df_from_ID(df_format, L0_ID_elec)/1000 #MWh
df_elec_L1B = parse_df_from_ID(df_format, campus_ID_elec) #MWh
df_elec_L1A = parse_df_from_ID(df_format, [2,7,11])
#CAD
L0_ID_CAD = [31,33,34,35,36]
L1_ID_CAD = [31,33,34,35,36]
df_CAD_L0 = parse_df_from_ID(df_format, L0_ID_CAD) #MWh
df_CAD_L1B = parse_df_from_ID(df_format, L1_ID_CAD) #MWh
#Gaz
L0_ID_gaz = [43,44,45,46,47]
L1_ID_gaz = [42,48,49,50,51]
df_gaz_L0 = parse_df_from_ID(df_format, L0_ID_gaz)/1000 #MWh
df_gaz_L1B = parse_df_from_ID(df_format, L1_ID_gaz) #MWh
#Mazout
L0_ID_mazout = [58,59,60,61]
L1_ID_mazout = [57,62,63,64,65]
df_mazout_L0 = parse_df_from_ID(df_format, L0_ID_mazout) #MWh
df_mazout_L1B = parse_df_from_ID(df_format, L1_ID_mazout) #MWh
#Eau potable
L0_ID_eau_p = [82,83,84,85]
L1_ID_eau_p = [81,86,87,88,89]
df_eau_p_L0 = parse_df_from_ID(df_format, L0_ID_eau_p) #m3
df_eau_p_L1B = parse_df_from_ID(df_format, L1_ID_eau_p) #m3
#Eau industrielle
L0_ID_eau_i = [72,73,74,75,76]
L1_ID_eau_i = [71]
df_eau_i_L0 = parse_df_from_ID(df_format, L0_ID_eau_i) #m3
df_eau_i_L1 = parse_df_from_ID(df_format, L1_ID_eau_i) #m3
#Surface de référence energetique
L1_ID_surf_e = [94,95,96,97,98]
df_surf_e_L1 = parse_df_from_ID(df_format, L1_ID_surf_e)#m2
#Surface plancher
df_surf_plancher = parse_df_from_ID(df_format, [102])
elec_db = df_elec_L1B.stack().reset_index()
elec_db.columns = ["year", "campus", "value"]
elec_db["category"] = "Electricite"
elec_db["unit"] = "MWh"
mazout_db = df_mazout_L1B.stack().reset_index()
mazout_db.columns = ["year", "campus", "value"]
mazout_db["category"] = "Mazout"
mazout_db["unit"] = "MWh"
gaz_db = df_gaz_L1B.stack().reset_index()
gaz_db.columns = ["year", "campus", "value"]
gaz_db["category"] = "Gaz"
gaz_db["unit"] = "MWh"
CAD_db = df_CAD_L1B.stack().reset_index()
CAD_db.columns = ["year", "campus", "value"]
CAD_db["category"] = "CAD"
CAD_db["unit"] = "MWh"
eaup_db = df_eau_p_L1B.stack().reset_index()
eaup_db.columns = ["year", "campus", "value"]
eaup_db["category"] = "Eau potable"
eaup_db["unit"] = "m3"
eaui_db = df_eau_i_L1.stack().reset_index()
eaui_db.columns = ["year", "campus", "value"]
eaui_db["category"] = "Eau industrielle"
eaui_db["unit"] = "m3"
surf_db = df_surf_e_L1.stack().reset_index()
surf_db.columns = ["year", "campus", "value"]
surf_db["category"] = "surface de reference energetique"
surf_db["unit"] = "m2"
surf_plancher_db = df_surf_plancher.stack().reset_index()
surf_plancher_db.columns = ["year", "campus", "value"]
surf_plancher_db["category"] = "Surface de plancher"
surf_plancher_db["unit"] = "m2"
db_energy = pd.concat([elec_db, mazout_db, gaz_db, CAD_db], ignore_index=True)
sold_elec = [11]
df_sold_elec = parse_df_from_ID(df_format, sold_elec)
df_reporting = db_energy.groupby(["year", "category"])["value"].sum().reset_index()
#Add
db_eau = pd.concat([eaup_db, eaui_db], ignore_index=True)
db_surf = pd.concat([surf_db, surf_plancher_db], ignore_index=True)
#Clean a bit the data
db_energy["campus"] = db_energy["campus"].str.split("EPFL - ", expand=True)[1].str.split("[", expand=True)[0].str.split("(", expand=True)[0].str.replace(" ", "")
db_eau["campus"] = db_eau["campus"].str.split("EPFL - ", expand=True)[1].str.split("[", expand=True)[0].str.split("(", expand=True)[0].str.replace(" ", "")
db_surf["campus"] = db_surf["campus"].str.split("EPFL - ", expand=True)[1].str.split("[", expand=True)[0].str.split("(", expand=True)[0].str.replace(" ", "")
#Add 2006 values
Energy_2006 = pd.DataFrame({"year":2006, "campus":"Vaud", "category":["Electricite", "Mazout", "Gaz"], "unit":"MWh", "value":[57179, 4736, 6768]})
Energy_2010 = pd.DataFrame({"year":2010, "campus":"Vaud", "category":["Electricite", "Mazout", "Gaz"], "unit":"MWh", "value":[61150, 13720, 15686]})
#ADD FRIBOURG ELECTRICITE BECAUSE NOT IN GRI
Fribourg_electricite = (36003.81+ 206631.15)/1000 #MWh
Energy_Fribourg = pd.DataFrame({"year":2022, "campus":"Fribourg", "category":["Electricite"], "unit":"MWh", "value":[Fribourg_electricite]})
db_energy = pd.concat([db_energy, Energy_2006, Energy_2010, Energy_Fribourg], ignore_index=True)
db_energy["Path"] = path
db_eau["Path"] = path
db_surf["Path"] = path
return db_energy
def export_database(data, path):
data.to_excel(path, index=False)
def extrapolate_student_plane(data, EPT_year, Stud_years, Category):
## Student plane interpolation
for year in Stud_years:
mask = (data["Year"]==year)&(data["Category"]==Category)
nearest_year = year
while (data.loc[(data["Year"]==nearest_year)&(data["Category"]==Category), "CO2"].isna().all()):
nearest_year += 1
serie = pd.Series()
serie["Year"] = year
ratio_evolution = ((EPT_year.loc[year]-EPT_year.loc[nearest_year])/(EPT_year.loc[nearest_year]))
serie["CO2"] = data.loc[(data["Year"]==nearest_year)&(data["Category"]==Category)&(data["Category"]==Category), "CO2"].sum()* (1+ratio_evolution)
serie["Value"] = data.loc[(data["Year"]==nearest_year)&(data["Category"]==Category)&(data["Category"]==Category), "Value"].sum()
serie["Data description"] = "Estimé"
serie["Scope"] = data.loc[(data["Year"]==nearest_year)&(data["Category"]==Category), "Scope"].median()
serie["Campus"] = "EPFL"
serie["Category"] = Category
serie["Theme"] = "Voyages professionnels"
data = data.append(serie, ignore_index=True)
return data
def objectives_2024(data_objectives, year_reference):
mask = (data_objectives["Category"] == "Electricite")&(data_objectives["Year"]==2019)
Electricity_2019 = data_objectives.loc[mask].sum()
Objective_2024_Electricity = Electricity_2019["CO2"]*0.85 #CO2 et kWh varient lineairement, le facteur de reduction est donc le meme
Objective_2024_Electricity_value = Electricity_2019["Value"]*0.85
Electricity_ref_year = data_objectives.loc[(data_objectives["Category"] == "Electricite")&(data_objectives["Year"]==year_reference)].sum()
Obj_2024= np.interp([2023], [year_reference, 2024], [Electricity_ref_year["CO2"], Objective_2024_Electricity])
Obj_2024_value = np.interp([2023], [year_reference, 2024], [Electricity_ref_year["Value"], Objective_2024_Electricity_value])
#Append Objective 2024 to obj_2023
df_2024 = pd.DataFrame()
df_2024["Theme"] = ["Electricite","Electricite"]
df_2024["Year"] = [2023, 2024]
df_2024["Campus"] = ["EPFL", "EPFL"]
df_2024["CO2"] = np.append(Obj_2024, Objective_2024_Electricity)
df_2024["Data description"] = "Objectif EPFL 15% de diminution d'ici 2024"
df_2024["Category"] = "Electricite"
df_2024["Scope"] = "2"
df_2024["Unit"] = "kg CO2"
df_2024["Value"] = np.append(Obj_2024_value, Objective_2024_Electricity_value)
return df_2024
def Check_join_data_category(data, join_factor, ignore_column = ["Plane staff", "Plane students"], ignore_year = 2006):
data = data[data["Year"]!=ignore_year]
NaN_factor = join_factor[join_factor["factor name"].isna()]["Category"]
mask_data_NaN = ~data["Category"].isin(NaN_factor)
filtered_nan_data = data[mask_data_NaN]
NaN_categories = data[~mask_data_NaN]["Category"].unique()
missing_category = filtered_nan_data[~filtered_nan_data["Category"].isin(join_factor[~join_factor["factor name"].isna()]["Category"])]["Category"]
missing_category = missing_category[~missing_category.isin(ignore_column)]
#Print if filtered_nan_data["Category"] is not in join_factor["Category"] and reversed
if len(missing_category.unique())>0:
print("Missing category in join_factor")
#Substract NaN_categories from missing_category
missing_category = missing_category[~missing_category.isin(NaN_categories)]
print(missing_category.unique())
ValueError("Check coherency between join_factor and data")
if len(join_factor[~join_factor["factor name"].isna()]["Category"].unique()) != len(join_factor[~join_factor["factor name"].isna()]["Category"]):
print("Duplicate category in join_factor")
ValueError("Check coherency between join_factor and data")
def print_missing_factor(df, emission_factor):
#Remove None
df = df[~df["factor name"].isna()]
if not df["factor name"].isin(emission_factor["Data name mapping"]).all():
print(df["factor name"][~df["factor name"].isin(emission_factor["Data name mapping"])])
raise ValueError("Some factor name are not found in the emission factor dataframe")
def parse_df_from_ID(df_format, L0_ID_elec):
df_elec_L0 = df_format[L0_ID_elec]
df_elec_L0.columns = df_elec_L0.iloc[0]
df_elec_L0 = df_elec_L0[1:]
df_elec_L0=df_elec_L0.replace("-", np.nan)
return df_elec_L0
c4science · Help