Exercise_answers.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Exercise 1"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "In this exercise, you will reshape the analyst data to create a table summarizing supplies plant capacity, per plant, per year."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "First things first - import the packages you are expecting to use for table manipulation"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "import pandas as pd\n",
    "import numpy\n",
    "import matplotlib.pyplot as plt"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "1. Load the ETL dataset to be processed, and print the first 5 rows"
   ]
  },
  {
   "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>Column1</th>\n",
       "      <th>Name</th>\n",
       "      <th>Plant</th>\n",
       "      <th>Year</th>\n",
       "      <th>2016</th>\n",
       "      <th>Confidence level</th>\n",
       "      <th>Source</th>\n",
       "      <th>Source year</th>\n",
       "      <th>2017</th>\n",
       "      <th>Confidence level2</th>\n",
       "      <th>...</th>\n",
       "      <th>Source year66</th>\n",
       "      <th>2039</th>\n",
       "      <th>Confidence level67</th>\n",
       "      <th>Source68</th>\n",
       "      <th>Source year69</th>\n",
       "      <th>2040</th>\n",
       "      <th>Confidence level3</th>\n",
       "      <th>Source4</th>\n",
       "      <th>Source year</th>\n",
       "      <th>Column5</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>Unit</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>...</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>1.0</td>\n",
       "      <td>Country</td>\n",
       "      <td>Plant 1</td>\n",
       "      <td>-</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>...</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>2.0</td>\n",
       "      <td>Coordinates</td>\n",
       "      <td>Plant 1</td>\n",
       "      <td>#</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>...</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>4.0</td>\n",
       "      <td>Owner 1</td>\n",
       "      <td>Plant 1</td>\n",
       "      <td>-</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>...</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>5.0</td>\n",
       "      <td>Ownership share - Owner 1</td>\n",
       "      <td>Plant 1</td>\n",
       "      <td>%</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>...</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>5 rows × 105 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "   Column1                       Name    Plant  Year  2016  Confidence level  \\\n",
       "0      NaN                        NaN      NaN  Unit   NaN               NaN   \n",
       "1      1.0                    Country  Plant 1     -   NaN               NaN   \n",
       "2      2.0                Coordinates  Plant 1     #   NaN               NaN   \n",
       "3      4.0                    Owner 1  Plant 1     -   NaN               NaN   \n",
       "4      5.0  Ownership share - Owner 1  Plant 1     %   NaN               NaN   \n",
       "\n",
       "   Source  Source year  2017  Confidence level2  ...  Source year66  2039  \\\n",
       "0     NaN          NaN   NaN                NaN  ...            NaN   NaN   \n",
       "1     NaN          NaN   NaN                NaN  ...            NaN   NaN   \n",
       "2     NaN          NaN   NaN                NaN  ...            NaN   NaN   \n",
       "3     NaN          NaN   NaN                NaN  ...            NaN   NaN   \n",
       "4     NaN          NaN   NaN                NaN  ...            NaN   NaN   \n",
       "\n",
       "  Confidence level67  Source68  Source year69  2040  Confidence level3  \\\n",
       "0                NaN       NaN            NaN   NaN                NaN   \n",
       "1                NaN       NaN            NaN   NaN                NaN   \n",
       "2                NaN       NaN            NaN   NaN                NaN   \n",
       "3                NaN       NaN            NaN   NaN                NaN   \n",
       "4                NaN       NaN            NaN   NaN                NaN   \n",
       "\n",
       "   Source4   Source year  Column5  \n",
       "0      NaN           NaN      NaN  \n",
       "1      NaN           NaN      NaN  \n",
       "2      NaN           NaN      NaN  \n",
       "3      NaN           NaN      NaN  \n",
       "4      NaN           NaN      NaN  \n",
       "\n",
       "[5 rows x 105 columns]"
      ]
     },
     "execution_count": 3,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "### Answer here\n",
    "\n",
    "dataset = pd.read_excel(\"data/20240407_BI_ETL_34_sanitized.xlsx\")\n",
    "dataset.head()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "2. Find out (and print) the unique names of all plants researched in this dataset"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 48,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "array([nan, 'Plant 1', 'Plant 2'], dtype=object)"
      ]
     },
     "execution_count": 48,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "### Answer here\n",
    "\n",
    "dataset['Plant'].unique()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "   Filter only the rows that refer to \"Starting capacity\" and save it under a new name"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "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>Column1</th>\n",
       "      <th>Name</th>\n",
       "      <th>Plant</th>\n",
       "      <th>Year</th>\n",
       "      <th>2016</th>\n",
       "      <th>Confidence level</th>\n",
       "      <th>Source</th>\n",
       "      <th>Source year</th>\n",
       "      <th>2017</th>\n",
       "      <th>Confidence level2</th>\n",
       "      <th>...</th>\n",
       "      <th>Source year66</th>\n",
       "      <th>2039</th>\n",
       "      <th>Confidence level67</th>\n",
       "      <th>Source68</th>\n",
       "      <th>Source year69</th>\n",
       "      <th>2040</th>\n",
       "      <th>Confidence level3</th>\n",
       "      <th>Source4</th>\n",
       "      <th>Source year</th>\n",
       "      <th>Column5</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>21.0</td>\n",
       "      <td>Starting capacity</td>\n",
       "      <td>Plant 1</td>\n",
       "      <td>GWh</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>...</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>73.0</td>\n",
       "      <td>Starting capacity</td>\n",
       "      <td>Plant 2</td>\n",
       "      <td>GWh</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>...</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>125.0</td>\n",
       "      <td>Starting capacity</td>\n",
       "      <td>Plant 3</td>\n",
       "      <td>GWh</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>...</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>125.0</td>\n",
       "      <td>Starting capacity</td>\n",
       "      <td>Plant 4</td>\n",
       "      <td>GWh</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>...</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>4 rows × 105 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "   Column1               Name    Plant Year  2016  Confidence level  Source  \\\n",
       "0     21.0  Starting capacity  Plant 1  GWh   NaN               NaN     NaN   \n",
       "1     73.0  Starting capacity  Plant 2  GWh   NaN               NaN     NaN   \n",
       "2    125.0  Starting capacity  Plant 3  GWh   NaN               NaN     NaN   \n",
       "3    125.0  Starting capacity  Plant 4  GWh   NaN               NaN     NaN   \n",
       "\n",
       "   Source year  2017  Confidence level2  ...  Source year66  2039  \\\n",
       "0          NaN   NaN                NaN  ...            NaN   NaN   \n",
       "1          NaN   NaN                NaN  ...            NaN   NaN   \n",
       "2          NaN   NaN                NaN  ...            NaN   NaN   \n",
       "3          NaN   NaN                NaN  ...            NaN   NaN   \n",
       "\n",
       "  Confidence level67  Source68  Source year69  2040  Confidence level3  \\\n",
       "0                NaN       NaN            NaN   NaN                NaN   \n",
       "1                NaN       NaN            NaN   NaN                NaN   \n",
       "2                NaN       NaN            NaN   NaN                NaN   \n",
       "3                NaN       NaN            NaN   NaN                NaN   \n",
       "\n",
       "   Source4   Source year  Column5  \n",
       "0      NaN           NaN      NaN  \n",
       "1      NaN           NaN      NaN  \n",
       "2      NaN           NaN      NaN  \n",
       "3      NaN           NaN      NaN  \n",
       "\n",
       "[4 rows x 105 columns]"
      ]
     },
     "execution_count": 4,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "### Answer here\n",
    "\n",
    "dataset_starting_capacity = dataset[dataset[\"Name\"] == \"Starting capacity\"].reset_index(drop=True)\n",
    "dataset_starting_capacity.head()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Get data for supplies plant capacity, per plant, for each year that the data is available in.\n",
    "\n",
    "Hint: use melt function from the pandas package"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "metadata": {},
   "outputs": [],
   "source": [
    "### Answer here\n",
    "dataset_starting_capacity_melted = pd.melt(\n",
    "    dataset_starting_capacity,\n",
    "    id_vars=[\"Plant\"],\n",
    "    var_name=\"Year\",\n",
    "    value_vars=range(2016, 2035),\n",
    "    value_name=\"Starting capacity\",\n",
    ")"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Get the distribution of plant starting capacities"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "count     24\n",
       "unique    20\n",
       "top        5\n",
       "freq       3\n",
       "Name: Starting capacity, dtype: int64"
      ]
     },
     "execution_count": 9,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "### Answer here\n",
    "\n",
    "dataset_starting_capacity_melted[\"Starting capacity\"].describe()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Plot a histogram of plant starting capacities"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "### Answer here\n",
    "\n",
    "dataset_starting_capacity_melted[\"Starting capacity\"].plot(kind=\"hist\")"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "4. Save the dataset as a new sheet in the existing Excel"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 52,
   "metadata": {},
   "outputs": [],
   "source": [
    "dataset_starting_capacity_melted.to_excel(\"data/20240407_BI_ETL_34_sanitized_ETL.xlsx\", index=False, sheet_name=\"ETL_starting_capacity\")"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Bonus challenge: find out how to add a new sheet to existing Excel file. TTi"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "###\n",
    "with pd.ExcelWriter(\n",
    "    input_output_directory / dataset_filename[vehicle_type],\n",
    "    mode=\"a\",\n",
    "    if_sheet_exists=\"replace\",\n",
    ") as writer:\n",
    "    dataset_enriched.to_excel(\n",
    "        writer,\n",
    "        index=False,\n",
    "        sheet_name=f\"{vehicle_type}_enriched\",\n",
    "    )  # save dataset"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "###"
   ]
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3",
   "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.12.1"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 2
}