powerplants-to-sql.ipynb

{
 "cells": [
  {
   "cell_type": "code",
   "execution_count": 1,
   "metadata": {},
   "outputs": [],
   "source": [
    "import pandas as pd\n",
    "import psycopg\n",
    "from sqlalchemy import create_engine\n",
    "from dotenv import load_dotenv\n",
    "import os"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "True"
      ]
     },
     "execution_count": 2,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "load_dotenv()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "metadata": {},
   "outputs": [],
   "source": [
    "ENDPOINT=os.environ.get(\"ENDPOINT\")\n",
    "USER=os.environ.get(\"USER\")\n",
    "PASS=os.environ.get(\"PASSWORD\")\n",
    "PORT=\"5432\"\n",
    "DBNAME=\"powerplants\""
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "metadata": {},
   "outputs": [],
   "source": [
    "aws_engine = create_engine(f\"postgresql+psycopg://{USER}:{PASS}@{ENDPOINT}:{PORT}/{DBNAME}\")\n",
    "aws_conn = f\"host={ENDPOINT} port={PORT} dbname={DBNAME} user={USER} password={PASS} sslrootcert={'SSLCERTIFICATE'}\""
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "metadata": {},
   "outputs": [],
   "source": [
    "local_engine = create_engine(\"postgresql+psycopg://postgres:postgresql@localhost:5432/powerplants\")\n",
    "local_conn = \"dbname=powerplants user=postgres password=postgresql\""
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "metadata": {},
   "outputs": [],
   "source": [
    "ou = pd.read_csv('Data/JRC-PPDB-OPEN.ver1.0/JRC_OPEN_UNITS.csv')\n",
    "op = pd.read_csv('Data/JRC-PPDB-OPEN.ver1.0/JRC_OPEN_PERFORMANCE.csv')\n",
    "ot = pd.read_csv('Data/JRC-PPDB-OPEN.ver1.0/JRC_OPEN_TEMPORAL.csv')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "metadata": {},
   "outputs": [],
   "source": [
    "ou['eic_p'].fillna(ou['eic_g'], inplace=True)\n",
    "ou['eic_g'].fillna(ou['eic_p'], inplace=True)\n",
    "ou['type_g'] = ou['type_g'].replace({'Fossil Hard coal': 'Fossil Hard Coal'})\n",
    "op['eic_g'].fillna(op['eic_p'], inplace=True)\n",
    "op['eic_p'].fillna(ou['eic_g'], inplace=True)\n",
    "# ot = ot.sort_values('cyear', ascending=False).drop_duplicates(subset=['eic_p', 'eic_g'])\n",
    "ot.sort_index(inplace=True)\n",
    "ot['type_g'] = ot['type_g'].replace({'Fossil Hard coal': 'Fossil Hard Coal'})"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "metadata": {},
   "outputs": [],
   "source": [
    "ou['year_commissioned'] = ou['year_commissioned'].astype('Int64')\n",
    "ou['year_decommissioned'] = ou['year_decommissioned'].astype('Int64')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "metadata": {},
   "outputs": [],
   "source": [
    "ou.to_csv('Data/JRC-PPDB-OPEN.ver1.0/JRC_OPEN_UNITS_R.csv', index=False)\n",
    "op.to_csv('Data/JRC-PPDB-OPEN.ver1.0/JRC_OPEN_PERFORMANCE_R.csv', index=False)\n",
    "ot.to_csv('Data/JRC-PPDB-OPEN.ver1.0/JRC_OPEN_TEMPORAL_R.csv', index=False)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 15,
   "metadata": {},
   "outputs": [],
   "source": [
    "with open('powerplants_db_create.sql', 'r') as file:\n",
    "        create_db = file.read().replace('\\n', ' ')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 11,
   "metadata": {},
   "outputs": [],
   "source": [
    "tables = {\n",
    "    \"UNITS\": \"JRC_OPEN_UNITS_R.csv\",\n",
    "    \"TEMPORAL\": \"JRC_OPEN_TEMPORAL_R.csv\",\n",
    "    \"PERFORMANCE\": \"JRC_OPEN_PERFORMANCE_R.csv\"\n",
    "}"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 16,
   "metadata": {},
   "outputs": [],
   "source": [
    "def upload_tables(cur, tables):\n",
    "    for name, path in tables.items():\n",
    "        with open(f'Data/JRC-PPDB-OPEN.ver1.0/{path}', 'r') as f:\n",
    "            with cur.copy(f\"COPY {name} FROM STDIN WITH (FORMAT CSV, HEADER TRUE)\") as copy:\n",
    "                while data := f.read():\n",
    "                    copy.write(data)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 17,
   "metadata": {},
   "outputs": [],
   "source": [
    "with psycopg.connect(local_conn) as conn:\n",
    "    \n",
    "    with conn.cursor() as cur:\n",
    "\n",
    "        cur.execute(create_db)\n",
    "        upload_tables(cur, tables)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 19,
   "metadata": {},
   "outputs": [],
   "source": [
    "with psycopg.connect(aws_conn) as conn:\n",
    "    \n",
    "    with conn.cursor() as cur:\n",
    "\n",
    "        cur.execute(create_db)\n",
    "        upload_tables(cur, tables)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 20,
   "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>eic_p</th>\n",
       "      <th>eic_g</th>\n",
       "      <th>name_p</th>\n",
       "      <th>name_g</th>\n",
       "      <th>capacity_p</th>\n",
       "      <th>capacity_g</th>\n",
       "      <th>type_g</th>\n",
       "      <th>lat</th>\n",
       "      <th>lon</th>\n",
       "      <th>country</th>\n",
       "      <th>NUTS2</th>\n",
       "      <th>status_g</th>\n",
       "      <th>year_commissioned</th>\n",
       "      <th>year_decommissioned</th>\n",
       "      <th>water_type</th>\n",
       "      <th>cooling_type</th>\n",
       "      <th>water_withdrawal</th>\n",
       "      <th>water_consumption</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>54W-KOMAN0000066</td>\n",
       "      <td>54W-KOMAN-G2008E</td>\n",
       "      <td>KOMANG</td>\n",
       "      <td>KOMANG2</td>\n",
       "      <td>600.0</td>\n",
       "      <td>150.0</td>\n",
       "      <td>Hydro Water Reservoir</td>\n",
       "      <td>42.103</td>\n",
       "      <td>19.822</td>\n",
       "      <td>Albania</td>\n",
       "      <td>AL01</td>\n",
       "      <td>COMMISSIONED</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>Freshwater</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>54W-KOMAN0000066</td>\n",
       "      <td>54W-KOMAN-G1007L</td>\n",
       "      <td>KOMANG</td>\n",
       "      <td>KOMANG1</td>\n",
       "      <td>600.0</td>\n",
       "      <td>150.0</td>\n",
       "      <td>Hydro Water Reservoir</td>\n",
       "      <td>42.103</td>\n",
       "      <td>19.822</td>\n",
       "      <td>Albania</td>\n",
       "      <td>AL01</td>\n",
       "      <td>COMMISSIONED</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>Freshwater</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>54W-KOMAN0000066</td>\n",
       "      <td>54W-KOMAN-G30097</td>\n",
       "      <td>KOMANG</td>\n",
       "      <td>KOMANG3</td>\n",
       "      <td>600.0</td>\n",
       "      <td>150.0</td>\n",
       "      <td>Hydro Water Reservoir</td>\n",
       "      <td>42.103</td>\n",
       "      <td>19.822</td>\n",
       "      <td>Albania</td>\n",
       "      <td>AL01</td>\n",
       "      <td>COMMISSIONED</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>Freshwater</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>54W-KOMAN0000066</td>\n",
       "      <td>54W-KOMAN-G4010H</td>\n",
       "      <td>KOMANG</td>\n",
       "      <td>KOMANG4</td>\n",
       "      <td>600.0</td>\n",
       "      <td>150.0</td>\n",
       "      <td>Hydro Water Reservoir</td>\n",
       "      <td>42.103</td>\n",
       "      <td>19.822</td>\n",
       "      <td>Albania</td>\n",
       "      <td>AL01</td>\n",
       "      <td>COMMISSIONED</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>Freshwater</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>54W-FIERZ000001A</td>\n",
       "      <td>54W-FIERZ-G1002P</td>\n",
       "      <td>FIERZAG</td>\n",
       "      <td>FIERZAG1</td>\n",
       "      <td>500.0</td>\n",
       "      <td>125.0</td>\n",
       "      <td>Hydro Water Reservoir</td>\n",
       "      <td>42.251</td>\n",
       "      <td>20.043</td>\n",
       "      <td>Albania</td>\n",
       "      <td>AL01</td>\n",
       "      <td>COMMISSIONED</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>Freshwater</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "              eic_p             eic_g   name_p    name_g  capacity_p  \\\n",
       "0  54W-KOMAN0000066  54W-KOMAN-G2008E   KOMANG   KOMANG2       600.0   \n",
       "1  54W-KOMAN0000066  54W-KOMAN-G1007L   KOMANG   KOMANG1       600.0   \n",
       "2  54W-KOMAN0000066  54W-KOMAN-G30097   KOMANG   KOMANG3       600.0   \n",
       "3  54W-KOMAN0000066  54W-KOMAN-G4010H   KOMANG   KOMANG4       600.0   \n",
       "4  54W-FIERZ000001A  54W-FIERZ-G1002P  FIERZAG  FIERZAG1       500.0   \n",
       "\n",
       "   capacity_g                 type_g     lat     lon  country NUTS2  \\\n",
       "0       150.0  Hydro Water Reservoir  42.103  19.822  Albania  AL01   \n",
       "1       150.0  Hydro Water Reservoir  42.103  19.822  Albania  AL01   \n",
       "2       150.0  Hydro Water Reservoir  42.103  19.822  Albania  AL01   \n",
       "3       150.0  Hydro Water Reservoir  42.103  19.822  Albania  AL01   \n",
       "4       125.0  Hydro Water Reservoir  42.251  20.043  Albania  AL01   \n",
       "\n",
       "       status_g year_commissioned year_decommissioned  water_type  \\\n",
       "0  COMMISSIONED              None                None  Freshwater   \n",
       "1  COMMISSIONED              None                None  Freshwater   \n",
       "2  COMMISSIONED              None                None  Freshwater   \n",
       "3  COMMISSIONED              None                None  Freshwater   \n",
       "4  COMMISSIONED              None                None  Freshwater   \n",
       "\n",
       "  cooling_type water_withdrawal water_consumption  \n",
       "0         None             None              None  \n",
       "1         None             None              None  \n",
       "2         None             None              None  \n",
       "3         None             None              None  \n",
       "4         None             None              None  "
      ]
     },
     "execution_count": 20,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "pd.read_sql_query('SELECT * FROM Units LIMIT 5', aws_engine)"
   ]
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "base",
   "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.4"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 2
}