In [1]:
#1 Disaggregation:
import pandas as pd
import matplotlib.pyplot as plt
import numpy as np
import seaborn as sns
from matplotlib.lines import Line2D
# File paths and sheet configuration (this is mine!):
file_path = r"C:\Users\asbj20\OneDrive - University of Bath\Desktop\Disaggregation\Disaggregation\EXIOBASE 3.9.5\Aluminium and aluminium product\2022 EXIOBASE 3.9.5 - Aluminium and aluminium product.xlsx"
sheet_name = "Disaggregation "
# Read Excel data:
df = pd.read_excel(file_path, sheet_name=sheet_name, header=None)
# Define rows and columns (0-based indexing):
start_row = 18 # Excel row 19
end_row_Z = 9817 # Excel row 9818
row_X = 9818 # Excel row 9819
row_F = 9819 # Excel row 9820
row_M = 9821 # Excel row 9822
col_C = 2 # Column C
col_AJ = 35 # Column AJ (36th column)
# Extract data:
Z = df.iloc[start_row:end_row_Z, col_C]
X_total = df.iloc[row_X, col_C]
F_total = df.iloc[row_F, col_C]
M_total = df.iloc[row_M, col_C]
multiplier = df.iloc[start_row:end_row_Z, col_AJ]
# Base proportions (for most rows):
prop_primary_aluminum_X = 0.5000
prop_aluminium_bars_X = 0.1520
prop_aluminium_tubes_X = 0.0079
prop_aluminium_foil_X = 0.0470
prop_aluminium_plates_X = 0.2294
prop_other_aluminium_X = 0.0637
# Emissions proportions:
prop_primary_aluminum_F = 0.9645
prop_aluminium_bars_F = 0.0023
prop_aluminium_tubes_F = 0.0023
prop_aluminium_foil_F = 0.0212
prop_aluminium_plates_F = 0.0029
prop_other_aluminium_F = 0.0068
# Special row proportions (Excel rows converted to 0-based indices):
special_proportions = {
(5545, 5558): {"Primary Aluminium": 0.9090, "Aluminium bars, rods and profiles": 0.0095, "Aluminium tubes and pipes": 0.0005, "Aluminium foil": 0.0439, "Aluminium plates, sheets and strips": 0.0146, "Other Aluminium products": 0.0225}, # Electricity (rows 5546-5559)
# Disaggregating bauxite, where all goes to Primary Aluminium (Primary Aluminium = 1, others = 0). Rows are checked manually for "Aluminium ores and concentrates"
853: {"Primary Aluminium": 1.0, "Aluminium bars, rods and profiles": 0.0, "Aluminium tubes and pipes": 0.0, "Aluminium foil": 0.0, "Aluminium plates, sheets and strips": 0.0, "Other Aluminium products": 0.0}, # Aluminium ores and concentrates
2053: {"Primary Aluminium": 1.0, "Aluminium bars, rods and profiles": 0.0, "Aluminium tubes and pipes": 0.0, "Aluminium foil": 0.0, "Aluminium plates, sheets and strips": 0.0, "Other Aluminium products": 0.0}, # Aluminium ores and concentrates
6053: {"Primary Aluminium": 1.0, "Aluminium bars, rods and profiles": 0.0, "Aluminium tubes and pipes": 0.0, "Aluminium foil": 0.0, "Aluminium plates, sheets and strips": 0.0, "Other Aluminium products": 0.0}, # Aluminium ores and concentrates
6653: {"Primary Aluminium": 1.0, "Aluminium bars, rods and profiles": 0.0, "Aluminium tubes and pipes": 0.0, "Aluminium foil": 0.0, "Aluminium plates, sheets and strips": 0.0, "Other Aluminium products": 0.0}, # Aluminium ores and concentrates
6853: {"Primary Aluminium": 1.0, "Aluminium bars, rods and profiles": 0.0, "Aluminium tubes and pipes": 0.0, "Aluminium foil": 0.0, "Aluminium plates, sheets and strips": 0.0, "Other Aluminium products": 0.0}, # Aluminium ores and concentrates
7853: {"Primary Aluminium": 1.0, "Aluminium bars, rods and profiles": 0.0, "Aluminium tubes and pipes": 0.0, "Aluminium foil": 0.0, "Aluminium plates, sheets and strips": 0.0, "Other Aluminium products": 0.0}, # Aluminium ores and concentrates
8853: {"Primary Aluminium": 1.0, "Aluminium bars, rods and profiles": 0.0, "Aluminium tubes and pipes": 0.0, "Aluminium foil": 0.0, "Aluminium plates, sheets and strips": 0.0, "Other Aluminium products": 0.0}, # Aluminium ores and concentrates
9253: {"Primary Aluminium": 1.0, "Aluminium bars, rods and profiles": 0.0, "Aluminium tubes and pipes": 0.0, "Aluminium foil": 0.0, "Aluminium plates, sheets and strips": 0.0, "Other Aluminium products": 0.0}, # Aluminium ores and concentrates
#other sectors
5489: {"Primary Aluminium": 0.9176, "Aluminium bars, rods and profiles": 0.0354, "Aluminium tubes and pipes": 0.0018, "Aluminium foil": 0.0123, "Aluminium plates, sheets and strips": 0.0205, "Other Aluminium products": 0.0124}, #Gas/Diesel Oil (row 5490)
5446: {"Primary Aluminium": 0.8566, "Aluminium bars, rods and profiles": 0.0318, "Aluminium tubes and pipes": 0.0017, "Aluminium foil": 0.0315, "Aluminium plates, sheets and strips": 0.0547, "Other Aluminium products": 0.0237}, #Natural gas and services related to natural gas extraction, excluding surveying (row 5447)
5564: {"Primary Aluminium": 0.8566, "Aluminium bars, rods and profiles": 0.0318, "Aluminium tubes and pipes": 0.0017, "Aluminium foil": 0.0315, "Aluminium plates, sheets and strips": 0.0547, "Other Aluminium products": 0.0237}, # Distribution services of gaseous fuels through mains (row 5565)
}
def disaggregate_Z(level, include_all_special=False):
# Initialize the series with the same index as Z
Z_primary_aluminum = pd.Series(index=Z.index, dtype=float)
Z_aluminium_bars = pd.Series(index=Z.index, dtype=float)
Z_aluminium_tubes = pd.Series(index=Z.index, dtype=float)
Z_aluminium_foil = pd.Series(index=Z.index, dtype=float)
Z_aluminium_plates = pd.Series(index=Z.index, dtype=float)
Z_other_aluminium = pd.Series(index=Z.index, dtype=float)
if include_all_special:
allowed_keys = special_proportions.copy()
else:
allowed_keys = {}
if level >= 2:
allowed_keys.update({k:v for k,v in special_proportions.items() if isinstance(k, tuple) and k == (5545,5558)})
if level >= 3:
allowed_keys.update({
853: special_proportions[853],
2053: special_proportions[2053],
6053: special_proportions[6053],
6653: special_proportions[6653],
6853: special_proportions[6853],
7853: special_proportions[7853],
8853: special_proportions[8853],
9253: special_proportions[9253]
})
if level >= 4:
allowed_keys.update({5489: special_proportions[5489]})
if level >= 5:
allowed_keys.update({5446: special_proportions[5446]})
if level >= 6:
allowed_keys.update({5564: special_proportions[5564]})
for idx in Z.index:
custom_props = None
for key in allowed_keys:
if isinstance(key, tuple) and key[0] <= idx <= key[1]:
custom_props = allowed_keys[key]
break
if not custom_props and idx in allowed_keys:
custom_props = allowed_keys[idx]
if not custom_props:
custom_props = {
"Primary Aluminium": prop_primary_aluminum_X,
"Aluminium bars, rods and profiles": prop_aluminium_bars_X,
"Aluminium tubes and pipes": prop_aluminium_tubes_X,
"Aluminium foil": prop_aluminium_foil_X,
"Aluminium plates, sheets and strips": prop_aluminium_plates_X,
"Other Aluminium products": prop_other_aluminium_X
}
Z_primary_aluminum[idx] = Z[idx] * custom_props["Primary Aluminium"]
Z_aluminium_bars[idx] = Z[idx] * custom_props["Aluminium bars, rods and profiles"]
Z_aluminium_tubes[idx] = Z[idx] * custom_props["Aluminium tubes and pipes"]
Z_aluminium_foil[idx] = Z[idx] * custom_props["Aluminium foil"]
Z_aluminium_plates[idx] = Z[idx] * custom_props["Aluminium plates, sheets and strips"]
Z_other_aluminium[idx] = Z[idx] * custom_props["Other Aluminium products"]
return Z_primary_aluminum, Z_aluminium_bars, Z_aluminium_tubes, Z_aluminium_foil, Z_aluminium_plates, Z_other_aluminium
def calculate_embodied_carbon(level, include_all_special=False):
Z_primary_aluminum, Z_aluminium_bars, Z_aluminium_tubes, Z_aluminium_foil, Z_aluminium_plates, Z_other_aluminium = disaggregate_Z(level, include_all_special=include_all_special)
X_primary_aluminum = X_total * prop_primary_aluminum_X
X_aluminium_bars = X_total * prop_aluminium_bars_X
X_aluminium_tubes = X_total * prop_aluminium_tubes_X
X_aluminium_foil = X_total * prop_aluminium_foil_X
X_aluminium_plates = X_total * prop_aluminium_plates_X
X_other_aluminium = X_total * prop_other_aluminium_X
F_primary_aluminum = F_total * prop_primary_aluminum_F
F_aluminium_bars = F_total * prop_aluminium_bars_F
F_aluminium_tubes = F_total * prop_aluminium_tubes_F
F_aluminium_foil = F_total * prop_aluminium_foil_F
F_aluminium_plates = F_total * prop_aluminium_plates_F
F_other_aluminium = F_total * prop_other_aluminium_F
e_primary_aluminum = F_primary_aluminum / X_primary_aluminum if X_primary_aluminum != 0 else 0.0
e_aluminium_bars = F_aluminium_bars / X_aluminium_bars if X_aluminium_bars != 0 else 0.0
e_aluminium_tubes = F_aluminium_tubes / X_aluminium_tubes if X_aluminium_tubes != 0 else 0.0
e_aluminium_foil = F_aluminium_foil / X_aluminium_foil if X_aluminium_foil != 0 else 0.0
e_aluminium_plates = F_aluminium_plates / X_aluminium_plates if X_aluminium_plates != 0 else 0.0
e_other_aluminium = F_other_aluminium / X_other_aluminium if X_other_aluminium != 0 else 0.0
A_primary_aluminum = Z_primary_aluminum / X_primary_aluminum
A_aluminium_bars = Z_aluminium_bars / X_aluminium_bars
A_aluminium_tubes = Z_aluminium_tubes / X_aluminium_tubes
A_aluminium_foil = Z_aluminium_foil / X_aluminium_foil
A_aluminium_plates = Z_aluminium_plates / X_aluminium_plates
A_other_aluminium = Z_other_aluminium / X_other_aluminium
A_primary_aluminum_multiplied = A_primary_aluminum * multiplier
A_aluminium_bars_multiplied = A_aluminium_bars * multiplier
A_aluminium_tubes_multiplied = A_aluminium_tubes * multiplier
A_aluminium_foil_multiplied = A_aluminium_foil * multiplier
A_aluminium_plates_multiplied = A_aluminium_plates * multiplier
A_other_aluminium_multiplied = A_other_aluminium * multiplier
sum_A_primary_aluminum_total = A_primary_aluminum_multiplied.sum() + e_primary_aluminum
sum_A_aluminium_bars_total = A_aluminium_bars_multiplied.sum() + e_aluminium_bars
sum_A_aluminium_tubes_total = A_aluminium_tubes_multiplied.sum() + e_aluminium_tubes
sum_A_aluminium_foil_total = A_aluminium_foil_multiplied.sum() + e_aluminium_foil
sum_A_aluminium_plates_total = A_aluminium_plates_multiplied.sum() + e_aluminium_plates
sum_A_other_aluminium_total = A_other_aluminium_multiplied.sum() + e_other_aluminium
embodied_carbon_primary_aluminum_total = (sum_A_primary_aluminum_total / 1e6) / 0.85276
embodied_carbon_aluminium_bars_total = (sum_A_aluminium_bars_total / 1e6) / 0.85276
embodied_carbon_aluminium_tubes_total = (sum_A_aluminium_tubes_total / 1e6) / 0.85276
embodied_carbon_aluminium_foil_total = (sum_A_aluminium_foil_total / 1e6) / 0.85276
embodied_carbon_aluminium_plates_total = (sum_A_aluminium_plates_total / 1e6) / 0.85276
embodied_carbon_other_aluminium_total = (sum_A_other_aluminium_total / 1e6) / 0.85276
embodied_carbon_primary_aluminum_scope1 = (e_primary_aluminum / 1e6) / 0.85276
embodied_carbon_aluminium_bars_scope1 = (e_aluminium_bars / 1e6) / 0.85276
embodied_carbon_aluminium_tubes_scope1 = (e_aluminium_tubes / 1e6) / 0.85276
embodied_carbon_aluminium_foil_scope1 = (e_aluminium_foil / 1e6) / 0.85276
embodied_carbon_aluminium_plates_scope1 = (e_aluminium_plates / 1e6) / 0.85276
embodied_carbon_other_aluminium_scope1 = (e_other_aluminium / 1e6) / 0.85276
# Scope 2: Electricity and Steam/Hot Water
scope2_rows = []
for region in range(49):
offset = region * 200
# Electricity sectors (rows 146-157 in Excel, 145-156 in 0-based)
electricity_start = offset + 146 - 1
electricity_end = offset + 157 - 1
scope2_rows.extend(range(electricity_start, electricity_end + 1))
# Steam and hot water supply services (row 166 in Excel, 165 in 0-based)
steam_hot_water_row = offset + 166 - 1
scope2_rows.append(steam_hot_water_row)
scope2_rows = [r for r in scope2_rows if r in Z.index]
sum_A_primary_aluminum_scope2 = A_primary_aluminum_multiplied.loc[scope2_rows].sum()
sum_A_aluminium_bars_scope2 = A_aluminium_bars_multiplied.loc[scope2_rows].sum()
sum_A_aluminium_tubes_scope2 = A_aluminium_tubes_multiplied.loc[scope2_rows].sum()
sum_A_aluminium_foil_scope2 = A_aluminium_foil_multiplied.loc[scope2_rows].sum()
sum_A_aluminium_plates_scope2 = A_aluminium_plates_multiplied.loc[scope2_rows].sum()
sum_A_other_aluminium_scope2 = A_other_aluminium_multiplied.loc[scope2_rows].sum()
# Embodied carbon Scope 2 for aluminum
embodied_carbon_primary_aluminum_scope2 = (sum_A_primary_aluminum_scope2 / 1e6) / 0.85276
embodied_carbon_aluminium_bars_scope2 = (sum_A_aluminium_bars_scope2 / 1e6) / 0.85276
embodied_carbon_aluminium_tubes_scope2 = (sum_A_aluminium_tubes_scope2 / 1e6) / 0.85276
embodied_carbon_aluminium_foil_scope2 = (sum_A_aluminium_foil_scope2 / 1e6) / 0.85276
embodied_carbon_aluminium_plates_scope2 = (sum_A_aluminium_plates_scope2 / 1e6) / 0.85276
embodied_carbon_other_aluminium_scope2 = (sum_A_other_aluminium_scope2 / 1e6) / 0.85276
# Scope 3 embodied carbon for aluminum
embodied_carbon_primary_aluminum_scope3 = embodied_carbon_primary_aluminum_total - embodied_carbon_primary_aluminum_scope1 - embodied_carbon_primary_aluminum_scope2
embodied_carbon_aluminium_bars_scope3 = embodied_carbon_aluminium_bars_total - embodied_carbon_aluminium_bars_scope1 - embodied_carbon_aluminium_bars_scope2
embodied_carbon_aluminium_tubes_scope3 = embodied_carbon_aluminium_tubes_total - embodied_carbon_aluminium_tubes_scope1 - embodied_carbon_aluminium_tubes_scope2
embodied_carbon_aluminium_foil_scope3 = embodied_carbon_aluminium_foil_total - embodied_carbon_aluminium_foil_scope1 - embodied_carbon_aluminium_foil_scope2
embodied_carbon_aluminium_plates_scope3 = embodied_carbon_aluminium_plates_total - embodied_carbon_aluminium_plates_scope1 - embodied_carbon_aluminium_plates_scope2
embodied_carbon_other_aluminium_scope3 = embodied_carbon_other_aluminium_total - embodied_carbon_other_aluminium_scope1 - embodied_carbon_other_aluminium_scope2
# Original aggregated embodied carbon:
original_aggregated_embodied_carbon = (M_total / 1e6) / 0.85276
return {
"total": (
embodied_carbon_primary_aluminum_total,
embodied_carbon_aluminium_bars_total,
embodied_carbon_aluminium_tubes_total,
embodied_carbon_aluminium_foil_total,
embodied_carbon_aluminium_plates_total,
embodied_carbon_other_aluminium_total
),
"scope1": (
embodied_carbon_primary_aluminum_scope1,
embodied_carbon_aluminium_bars_scope1,
embodied_carbon_aluminium_tubes_scope1,
embodied_carbon_aluminium_foil_scope1,
embodied_carbon_aluminium_plates_scope1,
embodied_carbon_other_aluminium_scope1
),
"scope2": (
embodied_carbon_primary_aluminum_scope2,
embodied_carbon_aluminium_bars_scope2,
embodied_carbon_aluminium_tubes_scope2,
embodied_carbon_aluminium_foil_scope2,
embodied_carbon_aluminium_plates_scope2,
embodied_carbon_other_aluminium_scope2
),
"scope3": (
embodied_carbon_primary_aluminum_scope3,
embodied_carbon_aluminium_bars_scope3,
embodied_carbon_aluminium_tubes_scope3,
embodied_carbon_aluminium_foil_scope3,
embodied_carbon_aluminium_plates_scope3,
embodied_carbon_other_aluminium_scope3
),
"original_aggregated": original_aggregated_embodied_carbon,
"sum_A_Primary_Aluminium": sum_A_primary_aluminum_total,
"sum_A_Aluminium_bars_rods_and_profiles": sum_A_aluminium_bars_total,
"sum_A_Aluminium_tubes_and_pipes": sum_A_aluminium_tubes_total,
"sum_A_Aluminium_foil": sum_A_aluminium_foil_total,
"sum_A_Aluminium_plates_sheets_and_strips": sum_A_aluminium_plates_total,
"sum_A_Other_Aluminium_products": sum_A_other_aluminium_total,
"X_Primary_Aluminium": X_primary_aluminum,
"X_Aluminium_bars_rods_and_profiles": X_aluminium_bars,
"X_Aluminium_tubes_and_pipes": X_aluminium_tubes,
"X_Aluminium_foil": X_aluminium_foil,
"X_Aluminium_plates_sheets_and_strips": X_aluminium_plates,
"X_Other_Aluminium_products": X_other_aluminium
}
# Levels:
levels = {
1: "Total Economic Output & Direct emissions",
2: "+Electricity",
3: "+Aluminium Ores",
4: "+Gas/Diesel Oil",
5: "+Natural Gas",
6: "+Dis. of Gaseous Fuels"
}
results = []
print("Embodied Carbon Results at Different Disaggregation Levels:\n")
for lvl in range(1, 7):
res = calculate_embodied_carbon(lvl)
total = res["total"]
scope1 = res["scope1"]
scope2 = res["scope2"]
scope3 = res["scope3"]
results.append({
"Level": levels[lvl],
"Primary Aluminium": total[0],
"Aluminium bars, rods and profiles": total[1],
"Aluminium tubes and pipes": total[2],
"Aluminium foil": total[3],
"Aluminium plates, sheets and strips": total[4],
"Other Aluminium products": total[5],
"Scope1_Primary Aluminium": scope1[0],
"Scope1_Aluminium bars, rods and profiles": scope1[1],
"Scope1_Aluminium tubes and pipes": scope1[2],
"Scope1_Aluminium foil": scope1[3],
"Scope1_Aluminium plates, sheets and strips": scope1[4],
"Scope1_Other Aluminium products": scope1[5],
"Scope2_Primary Aluminium": scope2[0],
"Scope2_Aluminium bars, rods and profiles": scope2[1],
"Scope2_Aluminium tubes and pipes": scope2[2],
"Scope2_Aluminium foil": scope2[3],
"Scope2_Aluminium plates, sheets and strips": scope2[4],
"Scope2_Other Aluminium products": scope2[5],
"Scope3_Primary Aluminium": scope3[0],
"Scope3_Aluminium bars, rods and profiles": scope3[1],
"Scope3_Aluminium tubes and pipes": scope3[2],
"Scope3_Aluminium foil": scope3[3],
"Scope3_Aluminium plates, sheets and strips": scope3[4],
"Scope3_Other Aluminium products": scope3[5],
"sum_A_Primary_Aluminium": res["sum_A_Primary_Aluminium"],
"sum_A_Aluminium_bars_rods_and_profiles": res["sum_A_Aluminium_bars_rods_and_profiles"],
"sum_A_Aluminium_tubes_and_pipes": res["sum_A_Aluminium_tubes_and_pipes"],
"sum_A_Aluminium_foil": res["sum_A_Aluminium_foil"],
"sum_A_Aluminium_plates_sheets_and_strips": res["sum_A_Aluminium_plates_sheets_and_strips"],
"sum_A_Other_Aluminium_products": res["sum_A_Other_Aluminium_products"],
"X_Primary_Aluminium": res["X_Primary_Aluminium"],
"X_Aluminium_bars_rods_and_profiles": res["X_Aluminium_bars_rods_and_profiles"],
"X_Aluminium_tubes_and_pipes": res["X_Aluminium_tubes_and_pipes"],
"X_Aluminium_foil": res["X_Aluminium_foil"],
"X_Aluminium_plates_sheets_and_strips": res["X_Aluminium_plates_sheets_and_strips"],
"X_Other_Aluminium_products": res["X_Other_Aluminium_products"]
})
print(f"Level {lvl}: {levels[lvl]}\n Primary Aluminium: {total[0]:.4f}\n Aluminium Bars: {total[1]:.4f}\n Aluminium Tubes: {total[2]:.4f}\n Aluminium Foil: {total[3]:.4f}\n Aluminium Plates: {total[4]:.4f}\n Other Aluminium: {total[5]:.4f}")
print(f" Scope 1 Primary Aluminium: {scope1[0]:.4f}\n Scope 1 Aluminium Bars: {scope1[1]:.4f}\n Scope 1 Aluminium Tubes: {scope1[2]:.4f}\n Scope 1 Aluminium Foil: {scope1[3]:.4f}\n Scope 1 Aluminium Plates: {scope1[4]:.4f}\n Scope 1 Other Aluminium: {scope1[5]:.4f}")
print(f" Scope 2 Primary Aluminium: {scope2[0]:.4f}\n Scope 2 Aluminium Bars: {scope2[1]:.4f}\n Scope 2 Aluminium Tubes: {scope2[2]:.4f}\n Scope 2 Aluminium Foil: {scope2[3]:.4f}\n Scope 2 Aluminium Plates: {scope2[4]:.4f}\n Scope 2 Other Aluminium: {scope2[5]:.4f}")
print(f" Scope 3 Primary Aluminium: {scope3[0]:.4f}\n Scope 3 Aluminium Bars: {scope3[1]:.4f}\n Scope 3 Aluminium Tubes: {scope3[2]:.4f}\n Scope 3 Aluminium Foil: {scope3[3]:.4f}\n Scope 3 Aluminium Plates: {scope3[4]:.4f}\n Scope 3 Other Aluminium: {scope3[5]:.4f}\n")
df_results = pd.DataFrame(results)
last = results[-1]
original_M_by_X = M_total * X_total
disaggregated_M_by_X = (last["sum_A_Primary_Aluminium"] * last["X_Primary_Aluminium"]) + \
(last["sum_A_Aluminium_bars_rods_and_profiles"] * last["X_Aluminium_bars_rods_and_profiles"]) + \
(last["sum_A_Aluminium_tubes_and_pipes"] * last["X_Aluminium_tubes_and_pipes"]) + \
(last["sum_A_Aluminium_foil"] * last["X_Aluminium_foil"]) + \
(last["sum_A_Aluminium_plates_sheets_and_strips"] * last["X_Aluminium_plates_sheets_and_strips"]) + \
(last["sum_A_Other_Aluminium_products"] * last["X_Other_Aluminium_products"])
print("\nValidation:")
print(f"Original M by X: {original_M_by_X:.4e}")
print(f"Disaggregated M by disaggregated X: {disaggregated_M_by_X:.4e}")
validation_df = pd.DataFrame({
'Description': ['Original M by X', 'Disaggregated M by disaggregated X'],
'Value': [original_M_by_X, disaggregated_M_by_X]
})
output_path = r"C:\Users\asbj20\OneDrive - University of Bath\Desktop\Disaggregation\Disaggregation\EXIOBASE 3.9.5\Aluminium and aluminium product\Results.xlsx"
with pd.ExcelWriter(output_path) as writer:
# Disaggregated Z for last level:
Z_primary_aluminum, Z_aluminium_bars, Z_aluminium_tubes, Z_aluminium_foil, Z_aluminium_plates, Z_other_aluminium = disaggregate_Z(6)
pd.DataFrame({
'Excel Row': df.index[start_row:end_row_Z] + 1,
'Original Z': Z.values,
'Z_Primary_Aluminum': Z_primary_aluminum.values,
'Z_Aluminium_bars_rods_and_profiles': Z_aluminium_bars.values,
'Z_Aluminium_tubes_and_pipes': Z_aluminium_tubes.values,
'Z_Aluminium_foil': Z_aluminium_foil.values,
'Z_Aluminium_plates_sheets_and_strips': Z_aluminium_plates.values,
'Z_Other_Aluminium_products': Z_other_aluminium.values
}).to_excel(writer, sheet_name='Disaggregated Z', index=False)
# Matrix A:
A_primary_aluminum = Z_primary_aluminum / (X_total * prop_primary_aluminum_X)
A_aluminium_bars = Z_aluminium_bars / (X_total * prop_aluminium_bars_X)
A_aluminium_tubes = Z_aluminium_tubes / (X_total * prop_aluminium_tubes_X)
A_aluminium_foil = Z_aluminium_foil / (X_total * prop_aluminium_foil_X)
A_aluminium_plates = Z_aluminium_plates / (X_total * prop_aluminium_plates_X)
A_other_aluminium = Z_other_aluminium / (X_total * prop_other_aluminium_X)
pd.DataFrame({
'Excel Row': df.index[start_row:end_row_Z] + 1,
'A_Primary_Aluminum': A_primary_aluminum.values,
'A_Aluminium_bars_rods_and_profiles': A_aluminium_bars.values,
'A_Aluminium_tubes_and_pipes': A_aluminium_tubes.values,
'A_Aluminium_foil': A_aluminium_foil.values,
'A_Aluminium_plates_sheets_and_strips': A_aluminium_plates.values,
'A_Other_Aluminium_products': A_other_aluminium.values,
'Multiplier': multiplier.values,
}).to_excel(writer, sheet_name='Matrix A', index=False)
# Matrix A multiplied (separate sheet)
A_primary_aluminum_multiplied = A_primary_aluminum * multiplier
A_aluminium_bars_multiplied = A_aluminium_bars * multiplier
A_aluminium_tubes_multiplied = A_aluminium_tubes * multiplier
A_aluminium_foil_multiplied = A_aluminium_foil * multiplier
A_aluminium_plates_multiplied = A_aluminium_plates * multiplier
A_other_aluminium_multiplied = A_other_aluminium * multiplier
pd.DataFrame({
'Excel Row': df.index[start_row:end_row_Z] + 1,
'Multiplier': multiplier.values,
'A_Primary_Aluminum_Multiplied': A_primary_aluminum_multiplied.values,
'A_Aluminium_bars_rods_and_profiles_Multiplied': A_aluminium_bars_multiplied.values,
'A_Aluminium_tubes_and_pipes_Multiplied': A_aluminium_tubes_multiplied.values,
'A_Aluminium_foil_Multiplied': A_aluminium_foil_multiplied.values,
'A_Aluminium_plates_sheets_and_strips_Multiplied': A_aluminium_plates_multiplied.values,
'A_Other_Aluminium_products_Multiplied': A_other_aluminium_multiplied.values
}).to_excel(writer, sheet_name='Matrix A Multiplied', index=False)
# Results including Scope 1, 2 and 3:
pd.DataFrame({
'Material': ['Primary Aluminium', 'Aluminium bars, rods and profiles', 'Aluminium tubes and pipes', 'Aluminium foil', 'Aluminium plates, sheets and strips', 'Other Aluminium products'],
'Embodied Carbon Total (kg CO2e/£)': [last["Primary Aluminium"], last["Aluminium bars, rods and profiles"], last["Aluminium tubes and pipes"], last["Aluminium foil"], last["Aluminium plates, sheets and strips"], last["Other Aluminium products"]],
'Scope 1 (kgCO2e/£)': [last["Scope1_Primary Aluminium"], last["Scope1_Aluminium bars, rods and profiles"], last["Scope1_Aluminium tubes and pipes"], last["Scope1_Aluminium foil"], last["Scope1_Aluminium plates, sheets and strips"], last["Scope1_Other Aluminium products"]],
'Scope 2 (kgCO2e/£)': [last["Scope2_Primary Aluminium"], last["Scope2_Aluminium bars, rods and profiles"], last["Scope2_Aluminium tubes and pipes"], last["Scope2_Aluminium foil"], last["Scope2_Aluminium plates, sheets and strips"], last["Scope2_Other Aluminium products"]],
'Scope 3 (kgCO2e/£)': [last["Scope3_Primary Aluminium"], last["Scope3_Aluminium bars, rods and profiles"], last["Scope3_Aluminium tubes and pipes"], last["Scope3_Aluminium foil"], last["Scope3_Aluminium plates, sheets and strips"], last["Scope3_Other Aluminium products"]]
}).to_excel(writer, sheet_name='Results', index=False)
# Validation sheet:
validation_df.to_excel(writer, sheet_name='Validation', index=False)
print(f"All results exported to:\n{output_path}")
Embodied Carbon Results at Different Disaggregation Levels: Level 1: Total Economic Output & Direct emissions Primary Aluminium: 0.5162 Aluminium Bars: 0.4568 Aluminium Tubes: 0.4654 Aluminium Foil: 0.4703 Aluminium Plates: 0.4567 Other Aluminium: 0.4596 Scope 1 Primary Aluminium: 0.0599 Scope 1 Aluminium Bars: 0.0005 Scope 1 Aluminium Tubes: 0.0090 Scope 1 Aluminium Foil: 0.0140 Scope 1 Aluminium Plates: 0.0004 Scope 1 Other Aluminium: 0.0033 Scope 2 Primary Aluminium: 0.0882 Scope 2 Aluminium Bars: 0.0882 Scope 2 Aluminium Tubes: 0.0882 Scope 2 Aluminium Foil: 0.0882 Scope 2 Aluminium Plates: 0.0882 Scope 2 Other Aluminium: 0.0882 Scope 3 Primary Aluminium: 0.3681 Scope 3 Aluminium Bars: 0.3681 Scope 3 Aluminium Tubes: 0.3681 Scope 3 Aluminium Foil: 0.3681 Scope 3 Aluminium Plates: 0.3681 Scope 3 Other Aluminium: 0.3681 Level 2: +Electricity Primary Aluminium: 0.5702 Aluminium Bars: 0.3949 Aluminium Tubes: 0.4036 Aluminium Foil: 0.4660 Aluminium Plates: 0.3949 Other Aluminium: 0.4170 Scope 1 Primary Aluminium: 0.0599 Scope 1 Aluminium Bars: 0.0005 Scope 1 Aluminium Tubes: 0.0090 Scope 1 Aluminium Foil: 0.0140 Scope 1 Aluminium Plates: 0.0004 Scope 1 Other Aluminium: 0.0033 Scope 2 Primary Aluminium: 0.1365 Scope 2 Aluminium Bars: 0.0328 Scope 2 Aluminium Tubes: 0.0328 Scope 2 Aluminium Foil: 0.0843 Scope 2 Aluminium Plates: 0.0329 Scope 2 Other Aluminium: 0.0500 Scope 3 Primary Aluminium: 0.3738 Scope 3 Aluminium Bars: 0.3617 Scope 3 Aluminium Tubes: 0.3617 Scope 3 Aluminium Foil: 0.3677 Scope 3 Aluminium Plates: 0.3617 Scope 3 Other Aluminium: 0.3637 Level 3: +Aluminium Ores Primary Aluminium: 0.5702 Aluminium Bars: 0.3949 Aluminium Tubes: 0.4036 Aluminium Foil: 0.4660 Aluminium Plates: 0.3949 Other Aluminium: 0.4170 Scope 1 Primary Aluminium: 0.0599 Scope 1 Aluminium Bars: 0.0005 Scope 1 Aluminium Tubes: 0.0090 Scope 1 Aluminium Foil: 0.0140 Scope 1 Aluminium Plates: 0.0004 Scope 1 Other Aluminium: 0.0033 Scope 2 Primary Aluminium: 0.1365 Scope 2 Aluminium Bars: 0.0328 Scope 2 Aluminium Tubes: 0.0328 Scope 2 Aluminium Foil: 0.0843 Scope 2 Aluminium Plates: 0.0329 Scope 2 Other Aluminium: 0.0500 Scope 3 Primary Aluminium: 0.3738 Scope 3 Aluminium Bars: 0.3617 Scope 3 Aluminium Tubes: 0.3617 Scope 3 Aluminium Foil: 0.3677 Scope 3 Aluminium Plates: 0.3617 Scope 3 Other Aluminium: 0.3637 Level 4: +Gas/Diesel Oil Primary Aluminium: 0.5704 Aluminium Bars: 0.3948 Aluminium Tubes: 0.4034 Aluminium Foil: 0.4658 Aluminium Plates: 0.3947 Other Aluminium: 0.4168 Scope 1 Primary Aluminium: 0.0599 Scope 1 Aluminium Bars: 0.0005 Scope 1 Aluminium Tubes: 0.0090 Scope 1 Aluminium Foil: 0.0140 Scope 1 Aluminium Plates: 0.0004 Scope 1 Other Aluminium: 0.0033 Scope 2 Primary Aluminium: 0.1365 Scope 2 Aluminium Bars: 0.0328 Scope 2 Aluminium Tubes: 0.0328 Scope 2 Aluminium Foil: 0.0843 Scope 2 Aluminium Plates: 0.0329 Scope 2 Other Aluminium: 0.0500 Scope 3 Primary Aluminium: 0.3740 Scope 3 Aluminium Bars: 0.3615 Scope 3 Aluminium Tubes: 0.3615 Scope 3 Aluminium Foil: 0.3675 Scope 3 Aluminium Plates: 0.3615 Scope 3 Other Aluminium: 0.3635 Level 5: +Natural Gas Primary Aluminium: 0.5704 Aluminium Bars: 0.3947 Aluminium Tubes: 0.4034 Aluminium Foil: 0.4658 Aluminium Plates: 0.3947 Other Aluminium: 0.4168 Scope 1 Primary Aluminium: 0.0599 Scope 1 Aluminium Bars: 0.0005 Scope 1 Aluminium Tubes: 0.0090 Scope 1 Aluminium Foil: 0.0140 Scope 1 Aluminium Plates: 0.0004 Scope 1 Other Aluminium: 0.0033 Scope 2 Primary Aluminium: 0.1365 Scope 2 Aluminium Bars: 0.0328 Scope 2 Aluminium Tubes: 0.0328 Scope 2 Aluminium Foil: 0.0843 Scope 2 Aluminium Plates: 0.0329 Scope 2 Other Aluminium: 0.0500 Scope 3 Primary Aluminium: 0.3740 Scope 3 Aluminium Bars: 0.3615 Scope 3 Aluminium Tubes: 0.3615 Scope 3 Aluminium Foil: 0.3675 Scope 3 Aluminium Plates: 0.3615 Scope 3 Other Aluminium: 0.3635 Level 6: +Dis. of Gaseous Fuels Primary Aluminium: 0.5828 Aluminium Bars: 0.3810 Aluminium Tubes: 0.3897 Aluminium Foil: 0.4601 Aluminium Plates: 0.3815 Other Aluminium: 0.4059 Scope 1 Primary Aluminium: 0.0599 Scope 1 Aluminium Bars: 0.0005 Scope 1 Aluminium Tubes: 0.0090 Scope 1 Aluminium Foil: 0.0140 Scope 1 Aluminium Plates: 0.0004 Scope 1 Other Aluminium: 0.0033 Scope 2 Primary Aluminium: 0.1365 Scope 2 Aluminium Bars: 0.0328 Scope 2 Aluminium Tubes: 0.0328 Scope 2 Aluminium Foil: 0.0843 Scope 2 Aluminium Plates: 0.0329 Scope 2 Other Aluminium: 0.0500 Scope 3 Primary Aluminium: 0.3864 Scope 3 Aluminium Bars: 0.3478 Scope 3 Aluminium Tubes: 0.3479 Scope 3 Aluminium Foil: 0.3618 Scope 3 Aluminium Plates: 0.3482 Scope 3 Other Aluminium: 0.3526 Validation: Original M by X: 3.5415e+09 Disaggregated M by disaggregated X: 3.5415e+09 All results exported to: C:\Users\asbj20\OneDrive - University of Bath\Desktop\Disaggregation\Disaggregation\EXIOBASE 3.9.5\Aluminium and aluminium product\Results.xlsx
In [2]:
2# Plot 1: Stacked Bar Chart for Scope 1, 2 and 3:
from scipy.interpolate import make_interp_spline
#Original aggregated embodied carbon:
original_aggregated_embodied_carbon = (M_total / 1e6) / 0.85276
# Materials for Aluminium:
materials = ['Primary\nAluminium', 'Aluminium bars,\nRods and\nProfiles', 'Aluminium Tubes\nand Pipes', 'Aluminium\nFoil', 'Aluminium Plates,\nSheets and\nStrips', 'Other Aluminium\nProducts']
x = range(len(materials))
# Get the last result (level 6) from results list:
last = results[-1]
# Scope values for each Aluminium material:
scope1_values = [
last["Scope1_Primary Aluminium"],
last["Scope1_Aluminium bars, rods and profiles"],
last["Scope1_Aluminium tubes and pipes"],
last["Scope1_Aluminium foil"],
last["Scope1_Aluminium plates, sheets and strips"],
last["Scope1_Other Aluminium products"]
]
scope2_values = [
last["Scope2_Primary Aluminium"],
last["Scope2_Aluminium bars, rods and profiles"],
last["Scope2_Aluminium tubes and pipes"],
last["Scope2_Aluminium foil"],
last["Scope2_Aluminium plates, sheets and strips"],
last["Scope2_Other Aluminium products"]
]
scope3_values = [
last["Scope3_Primary Aluminium"],
last["Scope3_Aluminium bars, rods and profiles"],
last["Scope3_Aluminium tubes and pipes"],
last["Scope3_Aluminium foil"],
last["Scope3_Aluminium plates, sheets and strips"],
last["Scope3_Other Aluminium products"]
]
fig, ax = plt.subplots(figsize=(22, 18), facecolor='white')
# Plotting the bars for Scope 1, Scope 2, and Scope 3:
bars1 = ax.bar(x, scope1_values, color='#4c72b0', edgecolor='black', label='Scope 1')
bars2 = ax.bar(x, scope2_values, bottom=scope1_values, color='#dd8452', edgecolor='black', label='Scope 2')
bottom_scope3 = [i + j for i, j in zip(scope1_values, scope2_values)]
bars3 = ax.bar(x, scope3_values, bottom=bottom_scope3, color='#55a868', edgecolor='black', label='Scope 3')
ax.axhline(y=original_aggregated_embodied_carbon, color='darkred', linestyle='--', linewidth=2, label='Original Aggregated Value')
# Annotate total stack values:
for i in range(len(materials)):
total_stack = scope1_values[i] + scope2_values[i] + scope3_values[i]
ax.text(x[i], total_stack + 0.01, f'{total_stack:.2f}', ha='center', fontsize=20, color='black')
# Calculate percentages and add annotations for each bar:
total_stack = [i + j + k for i, j, k in zip(scope1_values, scope2_values, scope3_values)]
direct_percentages = [f"{(v / s) * 100:.1f}%" if s > 0 else "0%" for v, s in zip(scope1_values, total_stack)]
scope2_percentages = [f"{(v / s) * 100:.1f}%" if s > 0 else "0%" for v, s in zip(scope2_values, total_stack)]
scope3_percentages = [f"{(v / s) * 100:.1f}%" if s > 0 else "0%" for v, s in zip(scope3_values, total_stack)]
# Annotate percentage values on the bars:
for bar, perc in zip(bars1, direct_percentages):
ax.text(bar.get_x() + bar.get_width() / 2, bar.get_height() / 2, perc,
ha='center', va='center', fontsize=20, color='white', weight='bold')
for bar, perc, bottom in zip(bars2, scope2_percentages, scope1_values):
ax.text(bar.get_x() + bar.get_width() / 2, bottom + bar.get_height() / 2, perc,
ha='center', va='center', fontsize=20, color='white', weight='bold')
for bar, perc, bottom in zip(bars3, scope3_percentages, bottom_scope3):
ax.text(bar.get_x() + bar.get_width() / 2, bottom + bar.get_height() / 2, perc,
ha='center', va='center', fontsize=20, color='white', weight='bold')
# Set font style for labels and axes:
ax.set_xlabel('Materials', fontsize=20, fontweight='bold', family='serif', color='#2C3E50')
ax.set_ylabel('Total Embodied Carbon Intensity (kgCO$_2$e/£)', fontsize=20, fontweight='bold', family='serif', color='#2C3E50')
ax.set_xticks(x)
ax.set_xticklabels(materials, fontsize=20, family='serif', color='#34495E')
ax.tick_params(axis='y', labelsize=20, labelcolor='#34495E')
# Set y-axis:
ax.set_ylim(0, 0.6)
# Set legend:
ax.legend(fontsize=20, loc='upper right', frameon=True, fancybox=True, shadow=True, edgecolor='black')
# Set grid:
ax.grid(axis='y', linestyle='--', alpha=0.6, linewidth=0.8)
ax.yaxis.set_minor_locator(plt.MultipleLocator(0.1))
ax.grid(which='minor', axis='y', linestyle=':', alpha=0.4, linewidth=0.6)
ax.spines['top'].set_visible(False)
ax.spines['right'].set_visible(False)
# Save:
plot_path = r"C:\Users\asbj20\OneDrive - University of Bath\Desktop\Disaggregation\Disaggregation\EXIOBASE 3.9.5\Aluminium and aluminium product\stacked_bar_chart"
fig.savefig(f"{plot_path}.png", dpi=300, bbox_inches='tight') # High-quality PNG
fig.savefig(f"{plot_path}.eps", dpi=300, bbox_inches='tight') # High-quality EPS
plt.tight_layout()
plt.show()
The PostScript backend does not support transparency; partially transparent artists will be rendered opaque.
In [3]:
3# Plot 2: line chart:
# Calculate the original aggregated embodied carbon:
original_aggregated_embodied_carbon = (M_total / 1e6) / 0.85276
# Create dataframe with aggregated starting point:
df_enhanced = df_results.copy()
# Add aggregated starting point as first row:
aggregated_row = {
'Level': 'Aggregated Value',
'Primary Aluminium': original_aggregated_embodied_carbon,
'Aluminium bars, rods and profiles': original_aggregated_embodied_carbon,
'Aluminium tubes and pipes': original_aggregated_embodied_carbon,
'Aluminium foil': original_aggregated_embodied_carbon,
'Aluminium plates, sheets and strips': original_aggregated_embodied_carbon,
'Other Aluminium products': original_aggregated_embodied_carbon
}
# Insert aggregated row at the beginning:
df_enhanced = pd.concat([pd.DataFrame([aggregated_row]), df_enhanced], ignore_index=True)
# Create Level_num with aggregated point:
df_enhanced['Level_num'] = [i for i in range(len(df_enhanced))]
# Special color for the aggregated point:
colors = {
'Primary Aluminium': '#D64541', # red
'Aluminium bars, rods and profiles': '#27AE60', # green
'Aluminium tubes and pipes': '#2980B9', # blue
'Aluminium foil': '#F39C12', # yellow
'Aluminium plates, sheets and strips': '#8E44AD', # purple
'Other Aluminium products': '#2C3E50' # grey
}
# Markers and line styles:
markers = ['o', 's', '^', 'D', 'v', '<']
linestyles = ['-', '--', '-.', ':', '-', '--']
# Create the figure:
fig, ax = plt.subplots(figsize=(20, 14), facecolor='white')
# Material names for plotting:
material_columns = ['Primary Aluminium', 'Aluminium bars, rods and profiles', 'Aluminium tubes and pipes',
'Aluminium foil', 'Aluminium plates, sheets and strips', 'Other Aluminium products']
material_labels = ['Primary Aluminium', 'Aluminium bars, rods and profiles', 'Aluminium tubes and pipes',
'Aluminium foil', 'Aluminium plates, sheets and strips', 'Other Aluminium products']
# Data range for y-axis scaling:
data_min = df_enhanced[material_columns].min().min()
data_max = df_enhanced[material_columns].max().max()
data_range = data_max - data_min
# Set y-axis limits with appropriate padding:
y_padding = max(0.05, data_range * 0.12)
y_min = max(0, data_min - y_padding)
y_max = data_max + y_padding
# Plot the branching lines from aggregated point:
for i, (material_col, material_label) in enumerate(zip(material_columns, material_labels)):
y_data = df_enhanced[material_col]
x_data = df_enhanced["Level_num"]
# Interpolation for the full line:
if len(x_data) > 3: # Need at least 4 points for cubic spline
x_new = np.linspace(x_data.min(), x_data.max(), 400)
spline = make_interp_spline(x_data, y_data, k=3)
y_smooth = spline(x_new)
# Glow effect (soft wide line behind):
ax.plot(x_new, y_smooth, color=colors[material_col], linewidth=16, alpha=0.08, zorder=1)
# Main smooth line:
ax.plot(x_new, y_smooth, linestyle=linestyles[i], linewidth=4,
color=colors[material_col], zorder=3, alpha=0.9, label=material_label)
# Special styling for the aggregated point:
if i == 0: # plot the aggregated point once
ax.scatter(x_data.iloc[0], y_data.iloc[0], marker='o', s=300,
edgecolor='white', linewidth=4, facecolor='#34495E', zorder=6,
alpha=0.95, label='Aggregated Value')
# Add a subtle ring around the aggregated point:
ax.scatter(x_data.iloc[0], y_data.iloc[0], marker='o', s=400,
edgecolor=colors[material_col], linewidth=2, facecolor='none', zorder=5,
alpha=0.6)
# Regular markers for disaggregated points:
ax.scatter(x_data.iloc[1:], y_data.iloc[1:], marker=markers[i], s=180,
edgecolor='white', linewidth=3, facecolor=colors[material_col], zorder=4,
alpha=0.95)
# Annotations:
for i, row in df_enhanced.iterrows():
x_pos = row["Level_num"]
# Special annotation for aggregated point:
if i == 0:
val = row['Primary Aluminium'] # Use any material value
bbox = dict(boxstyle="round,pad=0.5",
facecolor='#ECF0F1', alpha=0.95, edgecolor='#34495E', linewidth=2)
ax.text(x_pos, val + (y_max - y_min) * 0.02, f"{val:.3f}",
ha='center', va='bottom', fontsize=12, fontweight='bold',
color='#2C3E50', bbox=bbox, zorder=7)
else:
# Special positioning for overlapping values at x+f (index 1)
for j, material_col in enumerate(material_columns):
val = row[material_col]
if i == 1: # x+f level - special positioning
if material_col == 'Primary Aluminium': # Red -
y_offset = (y_max - y_min) * 0.012
va = 'bottom'
elif material_col == 'Aluminium bars, rods and profiles': # Green - top right
y_offset = (y_max - y_min) * 0.015
x_pos_adj = x_pos + 0.05
va = 'bottom'
elif material_col == 'Aluminium tubes and pipes': # Blue
y_offset = (y_max - y_min) * 0.012
va = 'bottom'
elif material_col == 'Aluminium foil': # Yellow - left
y_offset = (y_max - y_min) * 0.005
x_pos_adj = x_pos - 0.25
va = 'bottom'
elif material_col == 'Aluminium plates, sheets and strips': # Purple - bottom
y_offset = (y_max - y_min) * -0.025
va = 'top'
else: # Other (black) - slightly up from bottom
y_offset = (y_max - y_min) * -0.015
va = 'top'
# Use adjusted x position if defined
if 'x_pos_adj' in locals():
final_x_pos = x_pos_adj
del x_pos_adj # Clean up for next iteration
else:
final_x_pos = x_pos
else: # Regular positioning for other levels
# All blue values go to bottom
if material_col == 'Aluminium tubes and pipes':
y_offset = (y_max - y_min) * 0.012
va = 'top'
# Green values go to bottom except at beginning
elif material_col == 'Aluminium bars, rods and profiles':
y_offset = (y_max - y_min) * -0.018
va = 'top'
else:
y_offset = (y_max - y_min) * 0.012
va = 'bottom'
final_x_pos = x_pos
bbox = dict(boxstyle="round,pad=0.4",
facecolor='white', alpha=0.9, edgecolor=colors[material_col], linewidth=1)
ax.text(final_x_pos, val + y_offset, f"{val:.3f}", ha='center', va=va,
fontsize=12, fontweight='semibold', color=colors[material_col],
bbox=bbox, zorder=5)
# spine styling:
for spine in ['left', 'bottom']:
ax.spines[spine].set_linewidth(3)
ax.spines[spine].set_color('#2C3E50')
ax.spines['top'].set_visible(False)
ax.spines['right'].set_visible(False)
ax.spines['bottom'].set_position(('data', y_min))
# labels:
ax.set_xlabel('Data', fontsize=18, fontweight='bold',
color='#2C3E50', family='serif')
ax.set_ylabel('Total Embodied Carbon Intensity (kgCO$_2$e/£)', fontsize=18, fontweight='bold',
color='#2C3E50', family='serif')
# grid:
ax.grid(which='major', linestyle=':', linewidth=1.6, color='#7F8C8D', alpha=0.5)
ax.grid(which='minor', linestyle='--', linewidth=1, color='#BDC3C7', alpha=0.3)
ax.minorticks_on()
# tick styling:
ax.tick_params(axis='both', which='major', labelsize=14, width=2.5, length=10, color='#2C3E50')
ax.tick_params(axis='both', which='minor', width=1.5, length=6, color='#7F8C8D')
#Set x-ticks with proper horizontal alignment and two-line labels:
x_labels = []
for label in df_enhanced["Level"]:
label = label.replace('emissions', 'Emissions')
if len(label) > 15: # Break long labels into two lines
words = label.split()
mid = len(words) // 2
line1 = ' '.join(words[:mid])
line2 = ' '.join(words[mid:])
x_labels.append(f"{line1}\n{line2}")
else:
x_labels.append(label)
ax.set_xticks([i for i in range(len(df_enhanced))])
ax.set_xticklabels(x_labels, fontweight='semibold', fontsize=14,
family='serif', color='#2C3E50', rotation=0, ha='center')
ax.set_xticks([i for i in range(len(df_enhanced))])
ax.set_xticklabels(x_labels, fontweight='semibold', fontsize=14,
family='serif', color='#2C3E50', rotation=0, ha='center')
#Y-ticks:
tick_interval = 0.025 if data_range < 0.15 else 0.05
y_ticks = np.arange(np.floor(y_min / tick_interval) * tick_interval,
np.ceil(y_max / tick_interval) * tick_interval + tick_interval,
tick_interval)
ax.set_yticks(y_ticks)
ax.set_ylim(y_min, y_max)
# Set x limits:
ax.set_xlim(-0.3, df_enhanced["Level_num"].max() + 0.5)
# Add Aggregated Value to the legend as a simple circle:
aggregated_legend = Line2D([0], [0], color='none', lw=0, marker='o',
markerfacecolor='#34495E', markeredgecolor='white', markeredgewidth=3,
markersize=12, label='Aggregated Value')
# Create the rest of the legend elements for materials:
legend_elements = [
Line2D([0], [0], color=colors[material_col], lw=3.5, marker=markers[i],
markerfacecolor=colors[material_col], markeredgecolor='white', markeredgewidth=2,
linestyle=linestyles[i], label=material_label)
for i, (material_col, material_label) in enumerate(zip(material_columns, material_labels))
]
# Add the Aggregated Value at the start of the legend elements:
legend_elements.insert(0, aggregated_legend)
# legend:
ax.legend(handles=legend_elements, loc='upper right', bbox_to_anchor=(1.04, 1.04),
frameon=True, fancybox=True, shadow=True, fontsize=13, framealpha=0.95, edgecolor='#34495E')
# layout:
plt.tight_layout(rect=[0, 0, 0.82, 0.95])
# Save:
plot_path = r"C:\Users\asbj20\OneDrive - University of Bath\Desktop\Disaggregation\Disaggregation\EXIOBASE 3.9.5\Aluminium and aluminium product\branching_plot"
fig.savefig(f"{plot_path}.png", dpi=400, bbox_inches='tight', facecolor='white')
fig.savefig(f"{plot_path}.eps", dpi=400, bbox_inches='tight', facecolor='white')
plt.show()
The PostScript backend does not support transparency; partially transparent artists will be rendered opaque.
In [4]:
#4: OAT Sensitivity Analysis
def adjust_props(base_tuple, focus_idx, change):
"""
Adjust proportions with improved handling of edge cases.
Args:
base_tuple: Original proportions
focus_idx: Index of material to vary
change: Fractional change (e.g., 0.1 for +10%)
Returns:
tuple: New proportions that sum to 1
"""
base_list = list(base_tuple)
n = len(base_list)
# Calculate new value for the focus material:
old_val = base_list[focus_idx]
# Handle the case where old_val is 0 and we want to increase it:
if old_val == 0 and change > 0:
# For zero values, treat change as absolute proportion:
# Map percentage changes to meaningful proportions:
# 10% change -> 0.05, 25% -> 0.1, 50% -> 0.2, 100% -> 0.4
new_val = min(1.0, abs(change) * 0.4)
else:
new_val = max(0, min(1, old_val * (1 + change)))
# Calculate remaining proportion to distribute:
remaining = 1 - new_val
# Get indices of other materials:
other_indices = [i for i in range(n) if i != focus_idx]
others_sum = sum(base_list[i] for i in other_indices)
# Create new proportions list:
new_list = [0] * n
new_list[focus_idx] = new_val
if remaining <= 0:
# If new_val is 1, all others become 0
pass # Others already set to 0
elif others_sum == 0:
# If all others were originally 0, distribute remaining equally
equal_share = remaining / len(other_indices)
for i in other_indices:
new_list[i] = equal_share
else:
# Normal case: redistribute proportionally
factor = remaining / others_sum
for i in other_indices:
new_list[i] = base_list[i] * factor
return tuple(new_list)
def run_full_oat_sensitivity(level=6,
base_X=(prop_primary_aluminum_X, prop_aluminium_bars_X, prop_aluminium_tubes_X,
prop_aluminium_foil_X, prop_aluminium_plates_X, prop_other_aluminium_X),
base_F=(prop_primary_aluminum_F, prop_aluminium_bars_F, prop_aluminium_tubes_F,
prop_aluminium_foil_F, prop_aluminium_plates_F, prop_other_aluminium_F),
special_props=special_proportions,
changes=[-1.0, -0.5, -0.25, -0.1, 0, 0.1, 0.25, 0.5, 1.0],
output_path=r"C:\Users\asbj20\OneDrive - University of Bath\Desktop\Disaggregation\Disaggregation\EXIOBASE 3.9.5\Aluminium and aluminium product\OAT_sensitivity_results.xlsx"):
"""
Run OAT sensitivity on:
- Base proportions X
- Base proportions F
- All special proportions
Save results to Excel at output_path.
"""
results = []
materials = ['Primary Aluminium', 'Aluminium bars, rods and profiles', 'Aluminium tubes and pipes',
'Aluminium foil', 'Aluminium plates, sheets and strips', 'Other Aluminium products']
def run_with_base_props(new_X=None, new_F=None, new_special=None):
global prop_primary_aluminum_X, prop_aluminium_bars_X, prop_aluminium_tubes_X, prop_aluminium_foil_X, prop_aluminium_plates_X, prop_other_aluminium_X
global prop_primary_aluminum_F, prop_aluminium_bars_F, prop_aluminium_tubes_F, prop_aluminium_foil_F, prop_aluminium_plates_F, prop_other_aluminium_F
global special_proportions
orig_prop_X = (prop_primary_aluminum_X, prop_aluminium_bars_X, prop_aluminium_tubes_X,
prop_aluminium_foil_X, prop_aluminium_plates_X, prop_other_aluminium_X)
orig_prop_F = (prop_primary_aluminum_F, prop_aluminium_bars_F, prop_aluminium_tubes_F,
prop_aluminium_foil_F, prop_aluminium_plates_F, prop_other_aluminium_F)
orig_special_props = special_proportions.copy()
try:
if new_X:
prop_primary_aluminum_X, prop_aluminium_bars_X, prop_aluminium_tubes_X, prop_aluminium_foil_X, prop_aluminium_plates_X, prop_other_aluminium_X = new_X
if new_F:
prop_primary_aluminum_F, prop_aluminium_bars_F, prop_aluminium_tubes_F, prop_aluminium_foil_F, prop_aluminium_plates_F, prop_other_aluminium_F = new_F
if new_special:
key, new_dict = new_special
sp_copy = special_proportions.copy()
sp_copy[key] = new_dict
special_proportions = sp_copy
return calculate_embodied_carbon(level)["total"]
finally:
prop_primary_aluminum_X, prop_aluminium_bars_X, prop_aluminium_tubes_X, prop_aluminium_foil_X, prop_aluminium_plates_X, prop_other_aluminium_X = orig_prop_X
prop_primary_aluminum_F, prop_aluminium_bars_F, prop_aluminium_tubes_F, prop_aluminium_foil_F, prop_aluminium_plates_F, prop_other_aluminium_F = orig_prop_F
special_proportions = orig_special_props
print(f"\n=== Running Full OAT Sensitivity at Level {level} ===\n")
# Base X
print("Varying Base X proportions:")
for i, mat in enumerate(materials):
for ch in changes:
new_X = adjust_props(base_X, i, ch)
total = run_with_base_props(new_X=new_X)
results.append({
"Input_Group": "Base_X",
"Varied_Material": mat,
"Change_%": ch * 100,
"Primary_Aluminium_prop": new_X[0],
"Aluminium_bars_prop": new_X[1],
"Aluminium_tubes_prop": new_X[2],
"Aluminium_foil_prop": new_X[3],
"Aluminium_plates_prop": new_X[4],
"Other_Aluminium_prop": new_X[5],
"Emb_Primary_Aluminium": total[0],
"Emb_Aluminium_bars": total[1],
"Emb_Aluminium_tubes": total[2],
"Emb_Aluminium_foil": total[3],
"Emb_Aluminium_plates": total[4],
"Emb_Other_Aluminium": total[5]
})
# Base F
print("Varying Base F proportions:")
for i, mat in enumerate(materials):
for ch in changes:
new_F = adjust_props(base_F, i, ch)
total = run_with_base_props(new_F=new_F)
results.append({
"Input_Group": "Base_F",
"Varied_Material": mat,
"Change_%": ch * 100,
"Primary_Aluminium_prop": new_F[0],
"Aluminium_bars_prop": new_F[1],
"Aluminium_tubes_prop": new_F[2],
"Aluminium_foil_prop": new_F[3],
"Aluminium_plates_prop": new_F[4],
"Other_Aluminium_prop": new_F[5],
"Emb_Primary_Aluminium": total[0],
"Emb_Aluminium_bars": total[1],
"Emb_Aluminium_tubes": total[2],
"Emb_Aluminium_foil": total[3],
"Emb_Aluminium_plates": total[4],
"Emb_Other_Aluminium": total[5]
})
# Special proportions (all keys):
print("Varying Special proportions:")
for key in list(special_props.keys()):
base_dict = special_props[key]
base_tuple = (base_dict['Primary Aluminium'], base_dict['Aluminium bars, rods and profiles'],
base_dict['Aluminium tubes and pipes'], base_dict['Aluminium foil'],
base_dict['Aluminium plates, sheets and strips'], base_dict['Other Aluminium products'])
for i, mat in enumerate(materials):
for ch in changes:
new_special_tuple = adjust_props(base_tuple, i, ch)
new_special_dict = {
'Primary Aluminium': new_special_tuple[0],
'Aluminium bars, rods and profiles': new_special_tuple[1],
'Aluminium tubes and pipes': new_special_tuple[2],
'Aluminium foil': new_special_tuple[3],
'Aluminium plates, sheets and strips': new_special_tuple[4],
'Other Aluminium products': new_special_tuple[5]
}
total = run_with_base_props(new_special=(key, new_special_dict))
results.append({
"Input_Group": f"Special_{key}",
"Varied_Material": mat,
"Change_%": ch * 100,
"Primary_Aluminium_prop": new_special_tuple[0],
"Aluminium_bars_prop": new_special_tuple[1],
"Aluminium_tubes_prop": new_special_tuple[2],
"Aluminium_foil_prop": new_special_tuple[3],
"Aluminium_plates_prop": new_special_tuple[4],
"Other_Aluminium_prop": new_special_tuple[5],
"Emb_Primary_Aluminium": total[0],
"Emb_Aluminium_bars": total[1],
"Emb_Aluminium_tubes": total[2],
"Emb_Aluminium_foil": total[3],
"Emb_Aluminium_plates": total[4],
"Emb_Other_Aluminium": total[5]
})
# Creating DataFrame from results:
df = pd.DataFrame(results)
df.to_excel(output_path, index=False)
print(f"\nOAT Sensitivity results saved to: {output_path}")
return df
# Call the function and get the results:
sensitivity_all = run_full_oat_sensitivity(level=6) # Or any level you prefer
# Convert the results into DataFrame and save it:
df_sens = pd.DataFrame(sensitivity_all)
print(df_sens)
df_sens.to_excel(r"C:\Users\asbj20\OneDrive - University of Bath\Desktop\Disaggregation\Disaggregation\EXIOBASE 3.9.5\Aluminium and aluminium product\OAT_sensitivity_results.xlsx", index=False)
=== Running Full OAT Sensitivity at Level 6 ===
Varying Base X proportions:
Varying Base F proportions:
Varying Special proportions:
OAT Sensitivity results saved to: C:\Users\asbj20\OneDrive - University of Bath\Desktop\Disaggregation\Disaggregation\EXIOBASE 3.9.5\Aluminium and aluminium product\OAT_sensitivity_results.xlsx
Input_Group Varied_Material Change_% Primary_Aluminium_prop \
0 Base_X Primary Aluminium -100.0 0.000000
1 Base_X Primary Aluminium -50.0 0.250000
2 Base_X Primary Aluminium -25.0 0.375000
3 Base_X Primary Aluminium -10.0 0.450000
4 Base_X Primary Aluminium 0.0 0.500000
.. ... ... ... ...
751 Special_5564 Other Aluminium products 0.0 0.856600
752 Special_5564 Other Aluminium products 10.0 0.854521
753 Special_5564 Other Aluminium products 25.0 0.851401
754 Special_5564 Other Aluminium products 50.0 0.846203
755 Special_5564 Other Aluminium products 100.0 0.835806
Aluminium_bars_prop Aluminium_tubes_prop Aluminium_foil_prop \
0 0.304000 0.015800 0.094000
1 0.228000 0.011850 0.070500
2 0.190000 0.009875 0.058750
3 0.167200 0.008690 0.051700
4 0.152000 0.007900 0.047000
.. ... ... ...
751 0.031800 0.001700 0.031500
752 0.031723 0.001696 0.031424
753 0.031607 0.001690 0.031309
754 0.031414 0.001679 0.031118
755 0.031028 0.001659 0.030735
Aluminium_plates_prop Other_Aluminium_prop Emb_Primary_Aluminium \
0 0.458800 0.127400 inf
1 0.344100 0.095550 0.792809
2 0.286750 0.079625 0.652787
3 0.252340 0.070070 0.606113
4 0.229400 0.063700 0.582776
.. ... ... ...
751 0.054700 0.023700 0.582776
752 0.054567 0.026070 0.582704
753 0.054368 0.029625 0.582596
754 0.054036 0.035550 0.582415
755 0.053372 0.047400 0.582055
Emb_Aluminium_bars Emb_Aluminium_tubes Emb_Aluminium_foil \
0 0.376883 0.381246 0.416410
1 0.378263 0.384081 0.430966
2 0.379368 0.386348 0.442610
3 0.380271 0.388203 0.452138
4 0.381024 0.389749 0.460077
.. ... ... ...
751 0.381024 0.389749 0.460077
752 0.381015 0.389740 0.460049
753 0.381002 0.389727 0.460007
754 0.380980 0.389704 0.459936
755 0.380936 0.389659 0.459795
Emb_Aluminium_plates Emb_Other_Aluminium
0 0.377119 0.389306
1 0.378578 0.394827
2 0.379745 0.399244
3 0.380700 0.402858
4 0.381496 0.405869
.. ... ...
751 0.381496 0.405869
752 0.381486 0.406514
753 0.381471 0.407482
754 0.381446 0.409094
755 0.381396 0.412319
[756 rows x 15 columns]
In [5]:
import seaborn as sns
from pathlib import Path
import matplotlib.pyplot as plt
plt.style.use('seaborn-v0_8-darkgrid')
sns.set_palette("husl")
file_path = r"C:\Users\asbj20\OneDrive - University of Bath\Desktop\Disaggregation\Disaggregation\EXIOBASE 3.9.5\Aluminium and aluminium product\OAT_sensitivity_results.xlsx"
df = pd.read_excel(file_path)
df = df.replace([np.inf, -np.inf], np.nan)
# Output colors for aluminum products:
output_colors = {
'Emb_Primary_Aluminium': '#D64541', # red
'Emb_Aluminium_bars': '#27AE60', # green
'Emb_Aluminium_tubes': '#2980B9', # blue
'Emb_Aluminium_foil': '#F39C12', # yellow
'Emb_Aluminium_plates': '#8E44AD', # purple
'Emb_Other_Aluminium': '#2C3E50' # grey
}
# material markers
material_markers = {
'Primary Aluminium': 'o',
'Aluminium bars, rods and profiles': 's',
'Aluminium tubes and pipes': '^',
'Aluminium foil': 'v',
'Aluminium plates, sheets and strips': 'D',
'Other Aluminium products': 'h',
'Electricity': 'p',
'Aluminium Ores': '*',
'Gas/Diesel Oil': 'X',
'Natural Gas': 'P',
'Average': '+'
}
line_styles = ['-', '--', '-.', ':', '-', '--']
def plot_panel(ax, data_subset, title):
materials = data_subset['Varied_Material'].unique()
for i, material in enumerate(materials):
material_data = data_subset[data_subset['Varied_Material'] == material].sort_values('Change_%')
marker = material_markers.get(material, 'o')
for j, output in enumerate(['Emb_Primary_Aluminium', 'Emb_Aluminium_bars', 'Emb_Aluminium_tubes',
'Emb_Aluminium_foil', 'Emb_Aluminium_plates', 'Emb_Other_Aluminium']):
if output in data_subset.columns:
valid_data = material_data.dropna(subset=[output])
if len(valid_data) > 0:
ax.plot(valid_data['Change_%'], valid_data[output],
linestyle=line_styles[j],
color=output_colors[output],
marker=marker,
markersize=6,
linewidth=2,
alpha=0.8)
ax.set_title(title, fontsize=18, fontweight='bold', pad=10)
ax.grid(True, alpha=0.3)
ax.yaxis.set_major_formatter(plt.FormatStrFormatter('%.2f'))
y_vals = []
for material in materials:
for output in ['Emb_Primary_Aluminium', 'Emb_Aluminium_bars', 'Emb_Aluminium_tubes',
'Emb_Aluminium_foil', 'Emb_Aluminium_plates', 'Emb_Other_Aluminium']:
if output in data_subset.columns:
y_vals.extend(data_subset[data_subset['Varied_Material'] == material][output].dropna())
#if y_vals and max(y_vals) / min([v for v in y_vals if v > 0]) > 1000:
#ax.set_yscale('log')
def plot_combined_ores_panel(ax, df, title):
ore_groups = ['Special_853', 'Special_2053', 'Special_6053', 'Special_6653',
'Special_6853', 'Special_7853', 'Special_8853', 'Special_9253']
ore_data = df[df['Input_Group'].isin(ore_groups)]
if ore_data.empty:
ax.text(0.5, 0.5, 'No ore data\navailable', ha='center', va='center',
transform=ax.transAxes, fontsize=16)
ax.set_title(title, fontsize=18, fontweight='bold', pad=10)
return
materials = ['Primary Aluminium', 'Aluminium bars, rods and profiles',
'Aluminium tubes and pipes', 'Aluminium foil',
'Aluminium plates, sheets and strips', 'Other Aluminium products']
outputs = ['Emb_Primary_Aluminium', 'Emb_Aluminium_bars', 'Emb_Aluminium_tubes',
'Emb_Aluminium_foil', 'Emb_Aluminium_plates', 'Emb_Other_Aluminium']
for material, output in zip(materials, outputs):
material_data = ore_data[ore_data['Varied_Material'] == material]
if material_data.empty:
continue
# Group by change percentage and average across all ore regions
avg_by_change = material_data.groupby('Change_%')[output].mean().reset_index().sort_values('Change_%').dropna(subset=[output])
if not avg_by_change.empty:
marker = material_markers.get(material, 'o')
color = output_colors[output]
line_style = line_styles[materials.index(material) % len(line_styles)]
ax.plot(avg_by_change['Change_%'], avg_by_change[output],
linestyle=line_style,
color=color,
marker=marker,
markersize=6,
linewidth=2,
alpha=0.8,
label=f'{material} ({output.replace("Emb_", "")})')
ax.set_title(title, fontsize=18, fontweight='bold', pad=10)
ax.grid(True, alpha=0.3)
ax.yaxis.set_major_formatter(plt.FormatStrFormatter('%.2f'))
def plot_average_panel(ax, df, title):
specific_groups = ['Base_X', 'Base_F', 'Special_(5545, 5558)'] + \
['Special_853', 'Special_2053', 'Special_6053', 'Special_6653',
'Special_6853', 'Special_7853', 'Special_8853', 'Special_9253'] + \
['Special_5489', 'Special_5446', 'Special_5564']
other_data = df[~df['Input_Group'].isin(specific_groups)]
if other_data.empty:
ax.text(0.5, 0.5, 'No trade data\nfor averaging', ha='center', va='center',
transform=ax.transAxes, fontsize=16)
ax.set_title(title, fontsize=18, fontweight='bold', pad=10)
return
materials = ['Primary Aluminium', 'Aluminium bars, rods and profiles',
'Aluminium tubes and pipes', 'Aluminium foil',
'Aluminium plates, sheets and strips', 'Other Aluminium products']
outputs = ['Emb_Primary_Aluminium', 'Emb_Aluminium_bars', 'Emb_Aluminium_tubes',
'Emb_Aluminium_foil', 'Emb_Aluminium_plates', 'Emb_Other_Aluminium']
for material, output in zip(materials, outputs):
material_data = other_data[other_data['Varied_Material'] == material]
if material_data.empty:
continue
avg_by_change = material_data.groupby('Change_%')[output].mean().reset_index().sort_values('Change_%').dropna(subset=[output])
if not avg_by_change.empty:
marker = material_markers.get(material, 'o')
color = output_colors[output]
line_style = line_styles[materials.index(material) % len(line_styles)]
ax.plot(avg_by_change['Change_%'], avg_by_change[output],
linestyle=line_style,
color=color,
marker=marker,
markersize=6,
linewidth=2,
alpha=0.8)
ax.set_title(title, fontsize=18, fontweight='bold', pad=10)
ax.grid(True, alpha=0.3)
ax.yaxis.set_major_formatter(plt.FormatStrFormatter('%.2f'))
fig, axes = plt.subplots(3, 3, figsize=(18, 15))
axes = axes.flatten()
panels_config = [
('Base_X', 'Total Economic Output (X)', 0),
('Base_F', 'Direct Emissions (F)', 1),
('Special_(5545, 5558)', 'Electricity', 2),
(None, 'Aluminium Ores', 3), # Combined ores panel
('Special_5489', 'Gas/Diesel Oil', 4),
('Special_5446', 'Natural Gas', 5),
('Special_5564', 'Dist. of Gaseous Fuels', 7) # CHANGED TO POSITION 7
]
# Plot all panels
for input_group, title, panel_idx in panels_config:
if panel_idx >= len(axes):
continue
if input_group is None:
if title == 'Aluminium Ores':
plot_combined_ores_panel(axes[panel_idx], df, title)
else:
subset = df[df['Input_Group'] == input_group]
if not subset.empty:
plot_panel(axes[panel_idx], subset, title)
else:
axes[panel_idx].text(0.5, 0.5, f'No data found\nfor {input_group}',
ha='center', va='center',
transform=axes[panel_idx].transAxes, fontsize=16)
axes[panel_idx].set_title(title, fontsize=18, fontweight='bold', pad=10)
# Hide specific unused axes (positions 6 and 8)
axes[6].set_visible(False) # Hide bottom-left panel
axes[8].set_visible(False) # Hide bottom-right panel
# Change the font size of the tick labels (numbers) on x and y axes
for ax in axes:
ax.tick_params(axis='both', labelsize=16)
# Set labels
# Single x-axis label only for bottom middle panel
axes[7].set_xlabel('Change (%)', fontsize=20, fontweight='bold')
# Single y-axis label only for middle-left panel
axes[3].set_ylabel('Total Embodied Carbon Intensity (kgCO$_2$e/£)', fontsize=20, fontweight='bold')
# Create legend
legend_elements = [
plt.Line2D([0], [0], color='white', linewidth=0, label='Outputs:')
] + [
plt.Line2D([0], [0], color=color, linewidth=3, label=f' {output.replace("Emb_", "").replace("_", " ")}')
for output, color in output_colors.items()
] + [
plt.Line2D([0], [0], color='white', linewidth=0, label=''),
plt.Line2D([0], [0], color='white', linewidth=0, label='Inputs:')
] + [
plt.Line2D([0], [0], color='gray', marker=material_markers[m], markersize=8,
linewidth=0, linestyle='None', label=f' {m}')
for m in ['Primary Aluminium', 'Aluminium bars, rods and profiles',
'Aluminium tubes and pipes', 'Aluminium foil',
'Aluminium plates, sheets and strips', 'Other Aluminium products']
]
fig.legend(handles=legend_elements, loc='center right', bbox_to_anchor=(1.08, 0.7), fontsize=14, frameon=True, fancybox=True, shadow=True)
plt.tight_layout()
plt.subplots_adjust(right=0.85)
# Save figures
save_path = Path(file_path).parent / 'OAT_Sensitivity_Analysis_MultiPanel_Aluminium.png'
plt.savefig(save_path.with_suffix('.png'), dpi=300, bbox_inches='tight')
plt.savefig(save_path.with_suffix('.eps'), dpi=300, bbox_inches='tight')
print(f"Saved PNG and EPS at: {save_path.parent}")
plt.show()
The PostScript backend does not support transparency; partially transparent artists will be rendered opaque.
Saved PNG and EPS at: C:\Users\asbj20\OneDrive - University of Bath\Desktop\Disaggregation\Disaggregation\EXIOBASE 3.9.5\Aluminium and aluminium product
In [6]:
import seaborn as sns
from pathlib import Path
import matplotlib.pyplot as plt
import pandas as pd
import numpy as np
plt.style.use('seaborn-v0_8-darkgrid')
sns.set_palette("husl")
file_path = r"C:\Users\asbj20\OneDrive - University of Bath\Desktop\Disaggregation\Disaggregation\EXIOBASE 3.9.5\Aluminium and aluminium product\OAT_sensitivity_results.xlsx"
df = pd.read_excel(file_path)
df = df.replace([np.inf, -np.inf], np.nan)
# Output colors for aluminum products:
output_colors = {
'Emb_Primary_Aluminium': '#D64541', # red
'Emb_Aluminium_bars': '#27AE60', # green
'Emb_Aluminium_tubes': '#2980B9', # blue
'Emb_Aluminium_foil': '#F39C12', # orange
'Emb_Aluminium_plates': '#8E44AD', # purple
'Emb_Other_Aluminium': '#2C3E50' # grey
}
# material markers
material_markers = {
'Primary Aluminium': 'o',
'Aluminium bars, rods and profiles': 's',
'Aluminium tubes and pipes': '^',
'Aluminium foil': 'v',
'Aluminium plates, sheets and strips': 'D',
'Other Aluminium products': 'h',
'Electricity': 'p',
'Aluminium Ores': '*',
'Gas/Diesel Oil': 'X',
'Natural Gas': 'P',
'Average': '+'
}
line_styles = ['-', '--', '-.', ':', '-', '--']
def plot_panel(ax, data_subset, title):
materials = data_subset['Varied_Material'].unique()
for i, material in enumerate(materials):
material_data = data_subset[data_subset['Varied_Material'] == material].sort_values('Change_%')
marker = material_markers.get(material, 'o')
for j, output in enumerate(['Emb_Primary_Aluminium', 'Emb_Aluminium_bars', 'Emb_Aluminium_tubes',
'Emb_Aluminium_foil', 'Emb_Aluminium_plates', 'Emb_Other_Aluminium']):
if output in data_subset.columns:
valid_data = material_data.dropna(subset=[output])
if len(valid_data) > 0:
ax.plot(valid_data['Change_%'], valid_data[output],
linestyle=line_styles[j],
color=output_colors[output],
marker=marker,
markersize=6,
linewidth=2,
alpha=0.8)
ax.set_title(title, fontsize=20, fontweight='bold', pad=10)
ax.grid(True, alpha=0.3)
# Collect y values for setting limits
y_vals = []
for material in materials:
for output in ['Emb_Primary_Aluminium', 'Emb_Aluminium_bars', 'Emb_Aluminium_tubes',
'Emb_Aluminium_foil', 'Emb_Aluminium_plates', 'Emb_Other_Aluminium']:
if output in data_subset.columns:
y_vals.extend(data_subset[data_subset['Varied_Material'] == material][output].dropna())
if y_vals and max(y_vals) / min([v for v in y_vals if v > 0]) > 100:
ax.set_yscale('log')
elif y_vals:
y_min, y_max = min(y_vals), max(y_vals)
y_range = y_max - y_min
ax.set_ylim(y_min - 0.1 * y_range, y_max + 0.1 * y_range)
def plot_combined_panel(ax, df, input_groups, title):
"""Plot combined data from multiple input groups"""
combined_data = df[df['Input_Group'].isin(input_groups)]
if combined_data.empty:
ax.text(0.5, 0.5, 'No data available', ha='center', va='center',
transform=ax.transAxes, fontsize=18)
ax.set_title(title, fontsize=20, fontweight='bold', pad=10)
return
materials = combined_data['Varied_Material'].unique()
for i, material in enumerate(materials):
material_data = combined_data[combined_data['Varied_Material'] == material].sort_values('Change_%')
marker = material_markers.get(material, 'o')
for j, output in enumerate(['Emb_Primary_Aluminium', 'Emb_Aluminium_bars', 'Emb_Aluminium_tubes',
'Emb_Aluminium_foil', 'Emb_Aluminium_plates', 'Emb_Other_Aluminium']):
if output in combined_data.columns:
valid_data = material_data.dropna(subset=[output])
if len(valid_data) > 0:
ax.plot(valid_data['Change_%'], valid_data[output],
linestyle=line_styles[j],
color=output_colors[output],
marker=marker,
markersize=6,
linewidth=2,
alpha=0.8)
ax.set_title(title, fontsize=20, fontweight='bold', pad=10)
ax.grid(True, alpha=0.3)
# Collect y values for setting limits
y_vals = []
for material in materials:
for output in ['Emb_Primary_Aluminium', 'Emb_Aluminium_bars', 'Emb_Aluminium_tubes',
'Emb_Aluminium_foil', 'Emb_Aluminium_plates', 'Emb_Other_Aluminium']:
if output in combined_data.columns:
y_vals.extend(combined_data[combined_data['Varied_Material'] == material][output].dropna())
if y_vals and max(y_vals) / min([v for v in y_vals if v > 0]) > 100:
ax.set_yscale('log')
elif y_vals:
y_min, y_max = min(y_vals), max(y_vals)
y_range = y_max - y_min
ax.set_ylim(y_min - 0.15 * y_range, y_max + 0.15 * y_range)
# Create figure with 4 columns for better centering
fig = plt.figure(figsize=(18, 15))
gs = fig.add_gridspec(3, 4, hspace=0.25, wspace=0.15)
# Top row: X and F
ax1 = fig.add_subplot(gs[0, 0:2]) # Spans columns 0-1
ax2 = fig.add_subplot(gs[0, 2:4]) # Spans columns 2-3
# Middle row: Electricity and Distribution of Gaseous Fuels
ax3 = fig.add_subplot(gs[1, 0:2])
ax4 = fig.add_subplot(gs[1, 2:4])
# Bottom row: Combined panel (centered, columns 1-2)
ax5 = fig.add_subplot(gs[2, 1:3])
axes = [ax1, ax2, ax3, ax4, ax5]
# Plot individual panels
panels_config = [
('Base_X', 'Total Economic Output (X)', 0),
('Base_F', 'Direct Emissions (F)', 1),
('Special_(5545, 5558)', 'Electricity', 2),
('Special_5564', 'Distribution of Gaseous Fuels', 3),
]
for input_group, title, panel_idx in panels_config:
subset = df[df['Input_Group'] == input_group]
if not subset.empty:
plot_panel(axes[panel_idx], subset, title)
else:
axes[panel_idx].text(0.5, 0.5, f'No data found\nfor {input_group}',
ha='center', va='center', transform=axes[panel_idx].transAxes, fontsize=18)
axes[panel_idx].set_title(title, fontsize=20, fontweight='bold', pad=10)
# Plot combined panel for Aluminium Ores/Gas/Diesel/Natural Gas
ore_groups = ['Special_853', 'Special_2053', 'Special_6053', 'Special_6653',
'Special_6853', 'Special_7853', 'Special_8853', 'Special_9253']
fuel_groups = ['Special_5489', 'Special_5446'] # Gas/Diesel Oil and Natural Gas
combined_groups = ore_groups + fuel_groups
specific_groups = ['Base_X', 'Base_F', 'Special_(5545, 5558)', 'Special_5564'] + ore_groups + fuel_groups
trade_data = df[~df['Input_Group'].isin(specific_groups)]
# Combine ore, fuel, and natural gas data
ore_fuel_data = df[df['Input_Group'].isin(combined_groups)]
combined_df = pd.concat([ore_fuel_data, trade_data])
plot_combined_panel(axes[4], combined_df, combined_groups + [g for g in df['Input_Group'].unique() if g not in specific_groups],
'Aluminium Ores/Gas & Diesel Oil/Natural Gas')
for ax in axes:
ax.tick_params(axis='both', labelsize=18)
from matplotlib.ticker import FormatStrFormatter
ax.yaxis.set_major_formatter(FormatStrFormatter('%.1f'))
# Set x-axis label for bottom panel
axes[4].set_xlabel('Change (%)', fontsize=22, fontweight='bold')
# Set y-axis label for middle-left panel
axes[2].set_ylabel('Total Embodied Carbon Intensity (kgCO$_2$e/£)', fontsize=22, fontweight='bold')
# Create legend with LARGER font size
legend_elements = [
plt.Line2D([0], [0], color='white', linewidth=0, label='Outputs:')
] + [
plt.Line2D([0], [0], color=color, linewidth=3, label=f' {output.replace("Emb_", "").replace("_", " ")}')
for output, color in output_colors.items()
] + [
plt.Line2D([0], [0], color='white', linewidth=0, label=''),
plt.Line2D([0], [0], color='white', linewidth=0, label='Inputs:')
] + [
plt.Line2D([0], [0], color='gray', marker=material_markers[m], markersize=10,
linewidth=0, linestyle='None', label=f' {m}')
for m in ['Primary Aluminium', 'Aluminium bars, rods and profiles',
'Aluminium tubes and pipes', 'Aluminium foil',
'Aluminium plates, sheets and strips', 'Other Aluminium products']
]
fig.legend(handles=legend_elements, loc='center right', bbox_to_anchor=(1.14, 0.62),
fontsize=16, frameon=True, fancybox=True, shadow=True)
plt.subplots_adjust(right=0.88)
save_path = Path(file_path).parent / 'OAT_Sensitivity_Analysis_5Panel_Aluminium.png'
plt.savefig(save_path.with_suffix('.png'), dpi=300, bbox_inches='tight')
plt.savefig(save_path.with_suffix('.eps'), dpi=300, bbox_inches='tight')
print(f"Saved PNG and EPS at: {save_path.parent}")
plt.show()
The PostScript backend does not support transparency; partially transparent artists will be rendered opaque.
Saved PNG and EPS at: C:\Users\asbj20\OneDrive - University of Bath\Desktop\Disaggregation\Disaggregation\EXIOBASE 3.9.5\Aluminium and aluminium product
In [7]:
import matplotlib.pyplot as plt
import pandas as pd
import numpy as np
from pathlib import Path
from matplotlib.ticker import MultipleLocator
plt.style.use('seaborn-v0_8-whitegrid')
# Load your data
file_path = r"C:\Users\asbj20\OneDrive - University of Bath\Desktop\Disaggregation\Disaggregation\EXIOBASE 3.9.5\Aluminium and aluminium product\OAT_sensitivity_results.xlsx"
df = pd.read_excel(file_path)
df = df.replace([np.inf, -np.inf], np.nan)
# Color scheme for aluminum products
output_colors = {
'Emb_Primary_Aluminium': '#D64541', # red
'Emb_Aluminium_bars': '#27AE60', # green
'Emb_Aluminium_tubes': '#2980B9', # blue
'Emb_Aluminium_foil': '#F39C12', # orange
'Emb_Aluminium_plates': '#8E44AD', # purple
'Emb_Other_Aluminium': '#1B4F72' # dark blue
}
# Define input groups
input_groups = {
'Economic\nOutput (X)': 'Base_X',
'Direct\nEmissions (F)': 'Base_F',
'Electricity': 'Special_(5545, 5558)',
'Aluminium\nOres': ['Special_853', 'Special_2053', 'Special_6053', 'Special_6653',
'Special_6853', 'Special_7853', 'Special_8853', 'Special_9253'],
'Natural\nGas': 'Special_5446',
'Dist. of\nGaseous Fuels': 'Special_5564',
}
# Function to get values at -100% and +100% for each input group
def get_ranges_at_extremes(df, input_group, output_col):
"""Get values at exactly -100% and +100%"""
subset = df[df['Input_Group'] == input_group]
if subset.empty:
return None, None
# Get values at -100% and +100%
val_minus_100 = subset[subset['Change_%'] == -100][output_col].dropna()
val_plus_100 = subset[subset['Change_%'] == 100][output_col].dropna()
if len(val_minus_100) == 0 or len(val_plus_100) == 0:
return None, None
# Take min and max of these two extremes
min_val = min(val_minus_100.min(), val_plus_100.min())
max_val = max(val_minus_100.max(), val_plus_100.max())
return min_val, max_val
def get_combined_ranges_at_extremes(df, input_groups_list, output_col):
"""Get ranges for combined input groups (like multiple ore regions) at -100% and +100%"""
combined_data = df[df['Input_Group'].isin(input_groups_list)]
if combined_data.empty:
return None, None
# Get average at -100% and +100%
avg_minus_100 = combined_data[combined_data['Change_%'] == -100].groupby('Change_%')[output_col].mean()
avg_plus_100 = combined_data[combined_data['Change_%'] == 100].groupby('Change_%')[output_col].mean()
if len(avg_minus_100) == 0 or len(avg_plus_100) == 0:
return None, None
# Drop NaN values
vals = pd.concat([avg_minus_100, avg_plus_100]).dropna()
if len(vals) == 0:
return None, None
return vals.min(), vals.max()
def get_baseline(df, input_group, output_col):
"""Get baseline value at 0% change"""
subset = df[df['Input_Group'] == input_group]
if subset.empty:
return None
baseline = subset[subset['Change_%'] == 0][output_col]
if len(baseline) == 0:
return None
return baseline.values[0]
# Collect ranges for all inputs
input_names = list(input_groups.keys())
outputs = ['Emb_Primary_Aluminium', 'Emb_Aluminium_bars', 'Emb_Aluminium_tubes',
'Emb_Aluminium_foil', 'Emb_Aluminium_plates', 'Emb_Other_Aluminium']
ranges_data = {output: [] for output in outputs}
# Get baseline values (at 0% change) - using Base_X as reference
baselines = {output: get_baseline(df, 'Base_X', output) for output in outputs}
# Get ranges for defined input groups
for inp_name, inp_group in input_groups.items():
if isinstance(inp_group, list):
# Combined groups (like Aluminium Ores)
for output in outputs:
min_val, max_val = get_combined_ranges_at_extremes(df, inp_group, output)
ranges_data[output].append([min_val, max_val] if min_val is not None else [np.nan, np.nan])
else:
# Single groups
for output in outputs:
min_val, max_val = get_ranges_at_extremes(df, inp_group, output)
ranges_data[output].append([min_val, max_val] if min_val is not None else [np.nan, np.nan])
# Sort by Primary Aluminium range (most variable)
primary_ranges_for_sort = [(r[1] - r[0]) if not np.isnan(r[0]) else 0 for r in ranges_data['Emb_Primary_Aluminium']]
sorted_indices = np.argsort(primary_ranges_for_sort)[::-1]
inputs_sorted = [input_names[i] for i in sorted_indices]
sorted_ranges = {output: [ranges_data[output][i] for i in sorted_indices] for output in outputs}
# Create figure with much larger size
fig, ax = plt.subplots(figsize=(18, 12))
y_positions = np.arange(len(inputs_sorted))
n_outputs = len(outputs)
offset_step = 0.12
offsets = [(i - (n_outputs - 1) / 2) * offset_step for i in range(n_outputs)]
# Plot ranges as horizontal lines with markers
for output_idx, output in enumerate(outputs):
for i, (inp, range_vals) in enumerate(zip(inputs_sorted, sorted_ranges[output])):
if not np.isnan(range_vals[0]) and not np.isnan(range_vals[1]):
y_pos = i + offsets[output_idx]
ax.plot([range_vals[0], range_vals[1]], [y_pos, y_pos],
color=output_colors[output], linewidth=5, solid_capstyle='round', alpha=0.85)
ax.scatter([range_vals[0], range_vals[1]], [y_pos, y_pos],
color=output_colors[output], s=120, zorder=5, edgecolors='white', linewidth=2)
# Add vertical reference lines for baseline values (shortened to end near last input)
y_baseline_end = len(inputs_sorted) - 1 + 0.6
for output in outputs:
if baselines[output] is not None and not np.isnan(baselines[output]):
ax.plot([baselines[output], baselines[output]], [-0.5, y_baseline_end],
color=output_colors[output], linestyle='--', linewidth=2.5, alpha=0.5, zorder=1)
# Add horizontal separator lines between inputs
for i in range(len(inputs_sorted)):
ax.axhline(y=i + 0.5, color='gray', linestyle='-', linewidth=1.2, alpha=0.5, zorder=0)
# Add baseline labels just slightly above the baseline line tips
y_top = y_baseline_end + 0.05
# Determine which baselines need arrows based on spacing
baseline_values = [(output, baselines[output]) for output in outputs if baselines[output] is not None]
baseline_values.sort(key=lambda x: x[1]) # Sort by value
# Add baseline labels with smart positioning to avoid overcrowding
for idx, (output, value) in enumerate(baseline_values):
# Identify specific outputs for custom arrow treatment
is_primary = output == 'Emb_Primary_Aluminium' # Red (0.58)
is_aluminium_bars = output == 'Emb_Aluminium_bars' # Green (0.38)
is_aluminium_tubes = output == 'Emb_Aluminium_tubes' # Blue (0.39)
is_aluminium_foil = output == 'Emb_Aluminium_foil' # Orange (0.46)
is_aluminium_plates = output == 'Emb_Aluminium_plates' # Purple (0.38)
is_other = output == 'Emb_Other_Aluminium' # Dark blue (0.41)
if is_primary:
# Red (0.58) - no arrow
ax.text(value, y_top, f'{value:.2f}',
ha='center', va='bottom', fontsize=13, fontweight='bold',
color=output_colors[output])
elif is_aluminium_foil:
# Orange (0.46) - no arrow
ax.text(value, y_top, f'{value:.2f}',
ha='center', va='bottom', fontsize=13, fontweight='bold',
color=output_colors[output])
elif is_other:
# Dark blue (0.41) - no arrow, shifted slightly to the right
ax.text(value + 0.004, y_top, f'{value:.2f}',
ha='center', va='bottom', fontsize=13, fontweight='bold',
color=output_colors[output])
elif is_aluminium_tubes:
# Blue (0.39) - LONGER straight arrow
ax.annotate(f'{value:.2f}',
xy=(value, y_top), xytext=(value, y_top + 0.18),
ha='center', va='bottom', fontsize=13, fontweight='bold',
color=output_colors[output],
arrowprops=dict(arrowstyle='->', color=output_colors[output],
lw=2.5, shrinkA=0, shrinkB=0))
elif is_aluminium_plates:
# Purple (0.38) - arrow tilted slightly to the RIGHT
ax.annotate(f'{value:.2f}',
xy=(value, y_top), xytext=(value - 0.02, y_top + 0.13),
ha='center', va='bottom', fontsize=13, fontweight='bold',
color=output_colors[output],
arrowprops=dict(arrowstyle='->', color=output_colors[output],
lw=2.5, shrinkA=0, shrinkB=0))
elif is_aluminium_bars:
# Green (0.38) - arrow tilted slightly to the LEFT
ax.annotate(f'{value:.2f}',
xy=(value, y_top), xytext=(value - 0.06, y_top + 0.13),
ha='center', va='bottom', fontsize=13, fontweight='bold',
color=output_colors[output],
arrowprops=dict(arrowstyle='->', color=output_colors[output],
lw=2.5, shrinkA=0, shrinkB=0))
else:
# Fallback for any other outputs
ax.text(value, y_top, f'{value:.2f}',
ha='center', va='bottom', fontsize=13, fontweight='bold',
color=output_colors[output])
# Styling
ax.set_yticks(y_positions)
ax.set_yticklabels(inputs_sorted, fontsize=18, fontweight='bold')
ax.set_ylabel('Input Parameters', fontsize=20, fontweight='bold')
ax.set_xlabel('Total Embodied Carbon Intensity (kgCO$_2$e/£)', fontsize=20, fontweight='bold')
# X-axis with proper major and minor ticks
ax.set_xlim(0, 1.2)
ax.set_xticks([0, 0.2, 0.4, 0.6, 0.8, 1.0, 1.2]) # Major ticks every 0.2
ax.xaxis.set_minor_locator(MultipleLocator(0.1)) # Minor ticks every 0.1 (between major ticks)
ax.tick_params(axis='both', which='major', labelsize=16, length=8, width=1.5)
ax.tick_params(axis='x', which='minor', labelsize=14, length=5, color='gray', width=1)
#y-limits
ax.set_ylim(-0.7, len(inputs_sorted) + 0.35)
ax.grid(True, alpha=0.3, axis='x')
# Remove top and right spines
ax.spines['top'].set_visible(False)
ax.spines['right'].set_visible(False)
# Legend
legend_elements = [
plt.Line2D([0], [0], color=output_colors['Emb_Primary_Aluminium'], linewidth=5,
marker='o', markersize=11, label='Primary Aluminium', markeredgecolor='white', markeredgewidth=2),
plt.Line2D([0], [0], color=output_colors['Emb_Aluminium_bars'], linewidth=5,
marker='o', markersize=11, label='Aluminium bars', markeredgecolor='white', markeredgewidth=2),
plt.Line2D([0], [0], color=output_colors['Emb_Aluminium_tubes'], linewidth=5,
marker='o', markersize=11, label='Aluminium tubes', markeredgecolor='white', markeredgewidth=2),
plt.Line2D([0], [0], color=output_colors['Emb_Aluminium_foil'], linewidth=5,
marker='o', markersize=11, label='Aluminium foil', markeredgecolor='white', markeredgewidth=2),
plt.Line2D([0], [0], color=output_colors['Emb_Aluminium_plates'], linewidth=5,
marker='o', markersize=11, label='Aluminium plates', markeredgecolor='white', markeredgewidth=2),
plt.Line2D([0], [0], color=output_colors['Emb_Other_Aluminium'], linewidth=5,
marker='o', markersize=11, label='Other Aluminium', markeredgecolor='white', markeredgewidth=2)
]
# Add single baseline indicator
legend_elements.append(plt.Line2D([0], [0], color='gray', linestyle='--', linewidth=2.5, alpha=0.5,
label='Baseline values'))
legend = ax.legend(handles=legend_elements, loc='upper right', fontsize=15,
frameon=True, shadow=True, fancybox=True, ncol=1)
legend.get_frame().set_facecolor('white')
legend.get_frame().set_alpha(0.95)
plt.tight_layout()
# Save
save_path = Path(file_path).parent / 'Range_Sensitivity_Analysis_Aluminium.png'
plt.savefig(save_path.with_suffix('.png'), dpi=300, bbox_inches='tight', facecolor='white')
plt.savefig(save_path.with_suffix('.eps'), dpi=300, bbox_inches='tight')
print(f"✓ Saved PNG and EPS at: {save_path.parent}")
print(f" Baseline values:")
for output in outputs:
if baselines[output] is not None:
print(f" {output.replace('Emb_', '')}: {baselines[output]:.2f}")
# Print some example ranges to verify
print(f"\n Example ranges (Economic Output X):")
for output in outputs:
idx = list(input_groups.keys()).index('Economic\nOutput (X)')
range_vals = ranges_data[output][idx]
if not np.isnan(range_vals[0]):
print(f" {output.replace('Emb_', '')}: [{range_vals[0]:.4f}, {range_vals[1]:.4f}]")
plt.show()
The PostScript backend does not support transparency; partially transparent artists will be rendered opaque.
✓ Saved PNG and EPS at: C:\Users\asbj20\OneDrive - University of Bath\Desktop\Disaggregation\Disaggregation\EXIOBASE 3.9.5\Aluminium and aluminium product
Baseline values:
Primary_Aluminium: 0.58
Aluminium_bars: 0.38
Aluminium_tubes: 0.39
Aluminium_foil: 0.46
Aluminium_plates: 0.38
Other_Aluminium: 0.41
Example ranges (Economic Output X):
Primary_Aluminium: [0.4778, 0.6718]
Aluminium_bars: [0.3769, 0.3845]
Aluminium_tubes: [0.3812, 0.3970]
Aluminium_foil: [0.4164, 0.4971]
Aluminium_plates: [0.3771, 0.3834]
Other_Aluminium: [0.3893, 0.4199]
In [ ]: