Homec4science
Diffusion bilan_CO2 (master) Browse

bilan_CO2/scripts/Data_importer.py
bcab7faf52b9master

Last Change
Raw File

Data_importer.py
View Options

import pandas as pd
import numpy as np
from functions import *
class Data_importer():
# Constants for column names
YEAR = "Year"
CATEGORY = "Category"
VALUE = "Value"
def __init__(self, input_file, nomenclature):
self.category_name = {}
self.Theme_name = {}
self.ignore_column = []
self.general_attributes = {
"author": "Hugo Cruz",
"author_mail": "hugo.cruz@epfl.ch",
}
self.data = pd.DataFrame()
#Nomenclature utilisée, à gauche est le nom tel qu'il est dans le fichier d'entrée, à droite est le nom tel qu'il est écrit dans le fichier facteur emission
self.nomenclature = nomenclature
self.input_file = input_file
def run_population(self, EPT_infile):
#open EPT file, this EPT is not up to date, the evolution is not yet taken into account
EPT = pd.read_excel(EPT_infile)
self.EPT_year = EPT[EPT["Category"]== 'Population GRI'].groupby("Year").sum()["EPT"]
def run_energy_emission(self, elec_path):
df = read_GRI(elec_path)
df = df.rename(columns={"year": self.YEAR, "campus": "Campus", "category": "Category", "value": "Value"})
df["Theme"] = df["Category"] #We understand that the theme is Energy, however it's more relevant to separate when we visualize it
df["Scope"] = 2
df["Unit"] = df["unit"]
df["Path"] = elec_path
df.drop("unit", axis=1, inplace=True)
mask_mazout = df["Category"]=="Mazout"
mask_gaz = df["Category"]=="Gaz"
df.loc[df["Category"]=="Electricite","Category"] = self.nomenclature["Electricite"]
df.loc[mask_gaz|mask_mazout, "Scope"] = 1
self.data = pd.concat([self.data, df], ignore_index=True)
def run_dechets_emission(self, dechets_path):
#Dechets is not yet taken into account
df = pd.read_excel(dechets_path)
df = df.rename(columns={"waste_type":"Category", "kg":"Value", "year": YEAR})
df["Scope"] = 3
df["Unit"] = "kg"
df["Theme"] = "Dechets"
df["ID_category"] = df["Category"]
#Strategy ojective 2025
mask = (df["disposal_method"]=="Recycling")&(df["Year"]==2022)
recycled = df[(df["disposal_method"]=="Recycling")&(df["Year"]==2022)]["Value"].sum()
incineration = df[(df["disposal_method"]=="incineration")&(df["Year"]==2022)]["Value"].sum()
total = recycled+incineration
recycled_2025 = total*0.8
incineration_2025 = total*0.2
df["Path"] = dechets_path
df.drop("disposal_method", axis=1, inplace=True)
print("In 2025, the amount of recycled waste should be {} kg based on 80% recycled in 2022".format(recycled_2025))
self.data = pd.concat([self.data, df], ignore_index=True)
def run_plane_emission(self,path):
staff_emission = pd.read_excel(path, sheet_name="Avions (staff)", skiprows=1)
staff_CO2 = staff_emission.set_index("Data*").loc["CO2 footprint (tons) / Year Données: Atmosfair"]
staff_CO2.index = staff_CO2.index.astype(int)
staff_CO2 = staff_CO2.astype(float)
Students_emission = pd.read_excel(path, sheet_name="Avions (étudiant-e-s)")
Students_group = Students_emission.groupby("Year").sum()
Students_CO2 = Students_group["CO2 RFI2,7"]
CO2_staff = staff_CO2*1000 #Convert t to kg
CO2_students = Students_CO2*1000 #Convert t to kg
df_stud = pd.DataFrame(CO2_students).reset_index()
df_staff = pd.DataFrame(CO2_staff).reset_index()
df_staff["Category"] = self.nomenclature["Avion staff"]
df_stud["Category"] = self.nomenclature["Avion etudiants"]
df_staff.columns = ["Year", "CO2", "Category"]
df_stud.columns = ["Year", "CO2", "Category"]
df = pd.concat([df_stud, df_staff], ignore_index=True)
df["Campus"] = "EPFL"
df["Theme"] = "Voyages professionnels"
df["Unit"] = "kg CO2e"
df["Scope"] = 3
df["Path"] = path
df["Source"] = "Atmosfair"
self.ignore_column = np.append(self.ignore_column, df["Category"].unique())
self.data = pd.concat([self.data, df], ignore_index=True)
def run_train_emission(self, input_file):
#Open and format the data
df_train = pd.read_excel(input_file,skiprows=1, sheet_name="Train")
df_train = df_train.set_index("Km").stack().reset_index()
df_train.columns=["Category", "Year", "Value"]
#Select only interesting data
df = df_train[(df_train["Category"]=="Total Domestique")|(df_train["Category"]=="Total International")]
#Change df["Category"] == "Total Domestique" to self.nomenclature["Total Domestique"]
df.loc[df["Category"]=="Total Domestique", "Category"] = self.nomenclature["Total Domestique"]
df.loc[df["Category"]=="Total International", "Category"] = self.nomenclature["Total International"]
df["Year"] = df["Year"].astype(int)
df["Value"] = df["Value"].astype(float)
df["Unit"] = "km"
df["Theme"] = "Voyages professionnels"
df["Scope"] = 3
df["Path"] = input_file
self.data = pd.concat([self.data, df], ignore_index=True)
def run_cars_emission(self, input_file):
df_cars = pd.read_excel(input_file, skiprows=1, sheet_name="Voiture")
V_service = df_cars.iloc[0:5]
V_service.reset_index(drop=True, inplace=True)
V_service.iloc[3,0] = self.nomenclature["Véhicule de location"]
V_service.iloc[4,0] = self.nomenclature["Véhicules privés"]
Mobility = df_cars.iloc[9:12]
Mobility.reset_index(drop=True, inplace=True)
Mobility.iloc[0,0] = self.nomenclature["Mobility essence"]
Mobility.iloc[1,0] = self.nomenclature["Mobility diesel"]
Mobility.iloc[2,0] = self.nomenclature["Mobility electrique"]
Mobility.columns = df_cars.iloc[8]
col_int = list(Mobility.columns[1:].astype(str).str.replace(" ", "").str.split(".").str[0])
#Set Mobility.columns[1:] to col_int
Mobility.columns = ["Litres / kW pour électriques"] + col_int
db_Mobility = Mobility.set_index("Litres / kW pour électriques").stack().reset_index()
db_Mobility.columns=["Category", "Year", "Value"]
db_Mobility["Value"] = db_Mobility["Value"].astype(str).str.replace("-", "NaN").str.split(".").str[0].astype(float)
db_Mobility["Year"] = db_Mobility["Year"].astype(int)
db_Mobility["Unit"] = "L"
db_service = V_service.set_index("Kilomètres parcourus").stack().reset_index()
db_service.columns=["Category", "Year", "Value"]
db_service["Year"] = db_service["Year"].astype(int)
db_service["Value"] = db_service["Value"].astype(float)
db_service["Unit"] = "km"
V_service_L = df_cars.iloc[15:17]
V_service_L.reset_index(drop=True, inplace=True)
V_service_L.iloc[0,0] = self.nomenclature["Véhicules de service essence"]
V_service_L.iloc[1,0] = self.nomenclature["Véhicules de service diesel"]
V_service_L.columns = df_cars.iloc[14]
col_int = list(V_service_L.columns[1:].astype(str).str.replace(" ", "").str.split(".").str[0])
#Set Mobility.columns[1:] to col_int
V_service_L.columns = ["Litres"] + col_int
db_service_L = V_service_L.set_index("Litres").stack().reset_index()
db_service_L.columns=["Category", "Year", "Value"]
db_service_L["Year"] = db_service_L["Year"].astype(int)
db_service_L["Value"] = db_service_L["Value"].astype(float)
db_service_L["Unit"] = "L"
#Merge db_service db_Mobility and db_service_L into new dataframe
#Avoid a double count by selecting only the volume of L used in service vehicles:
db_service = db_service[~db_service["Category"].isin(["Véhicules de service essence", "Véhicules de service diesel", "Mobility carsharing"])]
db_Mobility = db_Mobility[~db_Mobility["Category"].isin(["Mobility électriques"])] #L'Electricite est achetée par l'EPFL et redistribuée, elle est donc deja comptabilisée
df = pd.concat([db_service, db_Mobility, db_service_L], ignore_index=True)
df["Theme"] = "Voyages professionnels"
df["Scope"] = 3
df.loc[df["Category"]==self.nomenclature["Véhicules de service essence"], "Scope"] = 1
df.loc[df["Category"]==self.nomenclature["Véhicules de service diesel"], "Scope"] = 1
df.loc[df["Category"]==self.nomenclature["Mobility diesel"], "Scope"] = 1
df.loc[df["Category"]==self.nomenclature["Mobility essence"], "Scope"] = 1
df["Path"] = input_file
self.data = pd.concat([self.data, df], ignore_index=True)
def run_mobilite_EPFL(self, pendulair_final):
df = pd.read_excel(pendulair_final, skiprows=2, index_col="Unnamed: 0")
#Select columns to keep in dataframe
col_dist=[]
for col in df.columns:
if "km" in col:
col_dist.append(col)
df_dist = df.iloc[:7][col_dist].T.replace("-", np.nan)
#if an index contain "jour" drop it
for idx in df_dist.index:
if "jour" in idx:
df_dist = df_dist.drop(idx)
if "Hiver" in idx:
#Replace "hiver" in index by ""
df_dist.loc[idx] = df_dist.loc[idx.replace("Hiver", "Ete")]+df_dist.loc[idx]
#Rename idx by idx.replace("Hiver", "")
df_dist = df_dist.rename(index={idx:idx.replace("Hiver", "")})
#Drop idx.replace("Hiver", "Ete")
df_dist = df_dist.drop(idx.replace("Hiver", "Ete"))
df_dist.index = df_dist.index.str.extract("(\d+)").squeeze()
#if index==np.nan drop it
df_dist = df_dist.loc[~df_dist.index.isna()]
df_dist = df_dist.reset_index().rename(columns={0:"Year"})
#Columns of df_dist are transport mode, convert df_dist to database
db = df_dist.set_index("Year").stack().reset_index()
db.rename(columns={"level_1":"Category", 0:"Value"}, inplace=True)
#Convert index to int
#Change db["Category"] == "voiture" to self.nomenclature["voiture"]
db.loc[db["Category"]=="voiture", "Category"] = self.nomenclature["voiture"]
db.loc[db["Category"]=="moto/scooter", "Category"] = self.nomenclature["moto/scooter"]
db["Year"] = db["Year"].astype(int)
db["Theme"] = "Mobilité pendulaire"
db["Scope"] = 3
db["Unit"] = "km"
#Filter Category=="autres"
db = db[db["Category"]!="autres"]
#Remove from db db["Year"] below 2018. Before 2018, the data collection has changed and is not comparable (3 order of magnitude lower)
self.raw_pendular = db.copy()
db.loc[db["Year"]<2018, "Value"] = np.nan
db["Path"] = pendulair_final
self.data = pd.concat([self.data, db], ignore_index=True)
def run_alimentation(self, alimentation_infile, sheet_name="Alimentation"):
df_alimentation = pd.read_excel(alimentation_infile, sheet_name=sheet_name, header=5) #Read file of computed CO2 by quantis
df_melted = df_alimentation.melt(id_vars=['Unnamed: 0'], var_name='Year', value_name='Value')
# Pivot the DataFrame to get the desired format
df_pivot = df_melted.pivot(index='Year', columns='Unnamed: 0', values='Value').reset_index()
# Rename the columns
df_pivot.columns = ['Year', 'repas', 'marchandise_CO2','marchandise_repas']
df_pivot["CO2"] = df_pivot["marchandise_CO2"]*df_pivot["marchandise_repas"]*df_pivot["repas"]
df = df_pivot[["Year", "CO2"]]
df["Campus"] = "Ecublens"
df["Theme"] = "Alimentation"
df["Scope"] = 3
df["Path"] = alimentation_infile
#df["Value"] = df_pivot["repas"] #You should not put this, because if there is a value, an emission factor will be applied which should not be the case here.
df["Unit"] = "Couverts"
df["Source"] = "Beelong"
df["Category"] = "Restauration EPFL"
#Filter all values before 2020 because the methodology has changed and extrapolation of 2019 seems incorrect:
#df = df[df["Year"]>=2020]
#append self.ignore_column with df["Category"].unique()
#self.ignore_column = np.append(self.ignore_column,df["Category"].unique())
self.data = pd.concat([self.data, df], ignore_index=True)
def add_manual_input(self):
self.Scope1_categories = ["Mazout", "Gaz"]
self.Scope2_categories = ["Electricite", "CAD"]
manual_input_path = self.input_file
inputs = pd.read_excel(manual_input_path, sheet_name="Manual input")
inputs.columns = ["Year", "Theme", "CO2"]
inputs["CO2"] = inputs["CO2"]*1000 #Convert to kg
inputs.loc[inputs["Theme"].isin(self.Scope1_categories), "Scope"] = 1
inputs.loc[inputs["Theme"].isin(self.Scope2_categories), "Scope"] = 2
inputs.loc[~inputs["Theme"].isin(self.Scope1_categories+self.Scope2_categories), "Scope"] = 3
inputs["Category"] = inputs["Theme"]
inputs["Path"] = manual_input_path
self.data = pd.concat([self.data, inputs], ignore_index=True)
c4science · Help