{
 "cells": [
  {
   "cell_type": "code",
   "execution_count": 1,
   "id": "3fd8fe0e-8af2-4e69-8b1a-96ab0e46fb49",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Results saved to: C:\\Users\\asbj20\\OneDrive - University of Bath\\Desktop\\CF_Results.xlsx\n"
     ]
    }
   ],
   "source": [
    "import pandas as pd\n",
    "import numpy as np\n",
    "\n",
    "# File path\n",
    "file_path = r\"C:\\Users\\asbj20\\OneDrive - University of Bath\\Desktop\\AD MRIO 2022.xlsx\"\n",
    "\n",
    "# Read the Excel file\n",
    "df = pd.read_excel(file_path, sheet_name='Sheet1', header=None)\n",
    "\n",
    "# Extract Z matrix (intermediate transactions)\n",
    "Z_start_row = 7  # E8 is row 8, so index 7\n",
    "Z_start_col = 4  # Column E is index 4\n",
    "Z_end_row = 2561  # X is in row 2563, so Z ends at row 2562\n",
    "\n",
    "# Determine matrix size\n",
    "n_sectors = Z_end_row - Z_start_row + 1\n",
    "Z_end_col = Z_start_col + n_sectors - 1\n",
    "\n",
    "# Extract matrices\n",
    "Z = df.iloc[Z_start_row:Z_end_row+1, Z_start_col:Z_end_col+1].values.astype(float)\n",
    "X = df.iloc[2562, Z_start_col:Z_end_col+1].values.astype(float)  # Row 2563\n",
    "F = df.iloc[2563, Z_start_col:Z_end_col+1].values.astype(float)  # Row 2564\n",
    "\n",
    "# Calculate technical coefficients matrix A = Z * diag(X)^-1\n",
    "X_safe = np.where(X == 0, 1e-10, X)\n",
    "A = Z / X_safe\n",
    "\n",
    "# Calculate Leontief inverse L = (I - A)^-1\n",
    "I = np.eye(n_sectors)\n",
    "L = np.linalg.inv(I - A)\n",
    "\n",
    "# Calculate emission intensities e = F / X\n",
    "e = F / X_safe\n",
    "\n",
    "# Calculate carbon footprint multipliers CF = e * L\n",
    "CF = e @ L\n",
    "\n",
    "# Convert from kgCO2e/million USD to kgCO2e/£:\n",
    "# Original: kgCO2e per million USD\n",
    "# Need: kgCO2e per £1\n",
    "# Step 1: Convert to kgCO2e per 1 USD = CF / 1,000,000\n",
    "# Step 2: Convert USD to GBP: 1 USD = 0.8117 GBP, so 1 GBP = 1/0.8117 USD\n",
    "# Therefore: kgCO2e per £1 = (CF / 1,000,000) * (1 / 0.8117)\n",
    "# Convert from kgCO2e/million USD to kgCO2e/£1\n",
    "conversion_rate = 0.8117\n",
    "CF_GBP = (CF / 1_000_000) / conversion_rate\n",
    "\n",
    "# Extract header rows E5-E7\n",
    "header_row1 = df.iloc[4, Z_start_col:Z_end_col+1].values\n",
    "header_row2 = df.iloc[5, Z_start_col:Z_end_col+1].values\n",
    "header_row3 = df.iloc[6, Z_start_col:Z_end_col+1].values\n",
    "\n",
    "# Create output DataFrame with headers and CF results\n",
    "output_df = pd.DataFrame({\n",
    "    i: [header_row1[i], header_row2[i], header_row3[i], CF_GBP[i]] \n",
    "    for i in range(len(CF_GBP))\n",
    "})\n",
    "\n",
    "output_df.index = ['Row1_Header', 'Row2_Header', 'Row3_Header', 'CF_Multiplier_kgCO2e_per_£']\n",
    "\n",
    "# Save results\n",
    "output_file = r\"C:\\Users\\asbj20\\OneDrive - University of Bath\\Desktop\\CF_Results.xlsx\"\n",
    "output_df.to_excel(output_file)\n",
    "\n",
    "print(f\"Results saved to: {output_file}\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "f97fe2f2-6bf5-4665-9f5c-abd303a4a450",
   "metadata": {},
   "outputs": [],
   "source": []
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3 (ipykernel)",
   "language": "python",
   "name": "python3"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 3
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython3",
   "version": "3.11.5"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 5
}