{
 "cells": [
  {
   "cell_type": "code",
   "execution_count": 1,
   "metadata": {},
   "outputs": [
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "c:\\ProgramData\\Anaconda3\\lib\\site-packages\\pandas\\core\\computation\\expressions.py:21: UserWarning: Pandas requires version '2.8.0' or newer of 'numexpr' (version '2.7.3' currently installed).\n",
      "  from pandas.core.computation.check import NUMEXPR_INSTALLED\n",
      "c:\\ProgramData\\Anaconda3\\lib\\site-packages\\pandas\\core\\arrays\\masked.py:62: UserWarning: Pandas requires version '1.3.4' or newer of 'bottleneck' (version '1.3.2' currently installed).\n",
      "  from pandas.core import (\n"
     ]
    }
   ],
   "source": [
    "import pandas as pd\n",
    "import numpy as np"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>intensity</th>\n",
       "      <th>year</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>0.517203</td>\n",
       "      <td>2011</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>0.516045</td>\n",
       "      <td>2012</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>0.516379</td>\n",
       "      <td>2013</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>0.514491</td>\n",
       "      <td>2014</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>0.510113</td>\n",
       "      <td>2015</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>0.507194</td>\n",
       "      <td>2016</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>0.505843</td>\n",
       "      <td>2017</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>0.504636</td>\n",
       "      <td>2018</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8</th>\n",
       "      <td>0.515194</td>\n",
       "      <td>2019</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9</th>\n",
       "      <td>0.515194</td>\n",
       "      <td>2020</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>10</th>\n",
       "      <td>0.515194</td>\n",
       "      <td>2021</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>11</th>\n",
       "      <td>0.515194</td>\n",
       "      <td>2022</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>12</th>\n",
       "      <td>0.515194</td>\n",
       "      <td>2023</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "    intensity  year\n",
       "0    0.517203  2011\n",
       "1    0.516045  2012\n",
       "2    0.516379  2013\n",
       "3    0.514491  2014\n",
       "4    0.510113  2015\n",
       "5    0.507194  2016\n",
       "6    0.505843  2017\n",
       "7    0.504636  2018\n",
       "8    0.515194  2019\n",
       "9    0.515194  2020\n",
       "10   0.515194  2021\n",
       "11   0.515194  2022\n",
       "12   0.515194  2023"
      ]
     },
     "execution_count": 3,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "intensity = pd.read_csv('intensity.csv') # intensity\tyear\n",
    "intensity"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 110,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>cambridge</th>\n",
       "      <th>year</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Date and Time</th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>2010-12-31</th>\n",
       "      <td>1.453766e+03</td>\n",
       "      <td>2010</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2011-12-31</th>\n",
       "      <td>7.755366e+04</td>\n",
       "      <td>2011</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2012-12-31</th>\n",
       "      <td>5.489394e+04</td>\n",
       "      <td>2012</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2013-12-31</th>\n",
       "      <td>5.920221e+05</td>\n",
       "      <td>2013</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2014-12-31</th>\n",
       "      <td>2.626312e+06</td>\n",
       "      <td>2014</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2015-12-31</th>\n",
       "      <td>1.962968e+06</td>\n",
       "      <td>2015</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2016-12-31</th>\n",
       "      <td>3.052266e+06</td>\n",
       "      <td>2016</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2017-12-31</th>\n",
       "      <td>6.855055e+06</td>\n",
       "      <td>2017</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2018-12-31</th>\n",
       "      <td>2.282154e+07</td>\n",
       "      <td>2018</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2019-12-31</th>\n",
       "      <td>2.695055e+07</td>\n",
       "      <td>2019</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2020-12-31</th>\n",
       "      <td>3.340410e+07</td>\n",
       "      <td>2020</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2021-12-31</th>\n",
       "      <td>4.780382e+07</td>\n",
       "      <td>2021</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2022-12-31</th>\n",
       "      <td>4.843909e+07</td>\n",
       "      <td>2022</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2023-12-31</th>\n",
       "      <td>6.190480e+07</td>\n",
       "      <td>2023</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2024-12-31</th>\n",
       "      <td>8.055568e+07</td>\n",
       "      <td>2024</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                  cambridge  year\n",
       "Date and Time                    \n",
       "2010-12-31     1.453766e+03  2010\n",
       "2011-12-31     7.755366e+04  2011\n",
       "2012-12-31     5.489394e+04  2012\n",
       "2013-12-31     5.920221e+05  2013\n",
       "2014-12-31     2.626312e+06  2014\n",
       "2015-12-31     1.962968e+06  2015\n",
       "2016-12-31     3.052266e+06  2016\n",
       "2017-12-31     6.855055e+06  2017\n",
       "2018-12-31     2.282154e+07  2018\n",
       "2019-12-31     2.695055e+07  2019\n",
       "2020-12-31     3.340410e+07  2020\n",
       "2021-12-31     4.780382e+07  2021\n",
       "2022-12-31     4.843909e+07  2022\n",
       "2023-12-31     6.190480e+07  2023\n",
       "2024-12-31     8.055568e+07  2024"
      ]
     },
     "execution_count": 110,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "cambridge = pd.read_csv('orig/02_cambridge.csv') # Date and Time,\"Hydro-only, MtCO2e\",\"Estimated, MtCO2e\",\"Coal-only, MtCO2e\"\n",
    "cambridge['Date and Time'] = pd.to_datetime(cambridge['Date and Time'])\n",
    "cambridge.index = cambridge['Date and Time']\n",
    "cambridge = cambridge[[\"Estimated, MtCO2e\"]]\n",
    "#yearly avg\n",
    "cambridge = cambridge.resample('Y').mean()\n",
    "cambridge[\"year\"] = cambridge.index.year\n",
    "cambridge.rename(columns={\"Estimated, MtCO2e\": \"cambridge\"}, inplace=True)\n",
    "cambridge[\"cambridge\"] = cambridge[\"cambridge\"] * 1e6\n",
    "cambridge.to_csv('2_cambridge.csv',index=False)\n",
    "cambridge"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 111,
   "metadata": {},
   "outputs": [],
   "source": [
    "digiconomist = pd.read_csv('orig/03_digiconomist.csv') # \"DateTime\",\"Estimated TWh per Year\",\"Minimum TWh per Year\"\n",
    "digiconomist['DateTime'] = pd.to_datetime(digiconomist['DateTime'])\n",
    "digiconomist.index = digiconomist['DateTime']\n",
    "digiconomist = digiconomist[[\"Estimated TWh per Year\"]]\n",
    "\n",
    "#yearly avg\n",
    "digiconomist = digiconomist.resample('Y').mean()\n",
    "digiconomist[\"year\"] = digiconomist.index.year\n",
    "digiconomist.rename(columns={\"Estimated TWh per Year\": \"digiconomist\"}, inplace=True)\n",
    "\n",
    "#convert to co2\n",
    "digiconomist = digiconomist.merge(intensity, on=\"year\", how=\"left\")\n",
    "digiconomist[\"digiconomist\"] = digiconomist[\"digiconomist\"] * digiconomist[\"intensity\"] * 1e6\n",
    "digiconomist.drop(columns=[\"intensity\"], inplace=True)\n",
    "\n",
    "digiconomist.to_csv('3_digiconomist.csv',index=False)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 112,
   "metadata": {},
   "outputs": [],
   "source": [
    "coinmetrics = pd.read_csv('orig/04_coinmetrics.csv') # year,coinmetrics\n",
    "#convert gw to Twh per year\n",
    "coinmetrics[\"coinmetrics\"] = coinmetrics[\"coinmetrics\"] * 8.76\n",
    "#convert to co2\n",
    "coinmetrics = coinmetrics.merge(intensity, on=\"year\", how=\"left\")\n",
    "coinmetrics[\"coinmetrics\"] = coinmetrics[\"coinmetrics\"] * coinmetrics[\"intensity\"] * 1e6\n",
    "coinmetrics.drop(columns=[\"intensity\"], inplace=True)\n",
    "\n",
    "coinmetrics.to_csv('4_coinmetrics.csv',index=False)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "metadata": {},
   "outputs": [],
   "source": [
    "rest = pd.read_csv('table/table.csv') # Author,Publication date,Title,Estimate (TWh)\n",
    "\n",
    "# for each row, create a csv with the author name\n",
    "for index, row in rest.iterrows():\n",
    "    name = row[\"Author\"].split(\",\")[0]\n",
    "    twh_values = str(row[\"Estimate (TWh)\"]).split(\" to \")\n",
    "    twh_values = np.array([float(x) for x in twh_values])\n",
    "    year = row[\"Publication date\"].split(\" \")[-1]\n",
    "\n",
    "    #convert to co2\n",
    "    intensity_year = float(intensity[intensity[\"year\"] == int(year)][\"intensity\"].values[0])\n",
    "    co2 = twh_values * intensity_year * 1e6\n",
    "\n",
    "    # co2 = np.mean(co2)\n",
    "    co2 = str(co2[0]) + (\" to \" + str(co2[1]) if len(co2) > 1 else \"\")\n",
    "\n",
    "\n",
    "    with open(f'{index+10}_{name}.csv', 'w') as f:\n",
    "        f.write(f'year,{name}\\n')\n",
    "        f.write(f'{year},{co2}\\n')"
   ]
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "py310",
   "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.9.7"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 2
}