SC02_Gruppe_10_01_gather_data.ipynb

{
 "cells": [
  {
   "cell_type": "code",
   "execution_count": 1,
   "metadata": {
    "id": "2t1w--PaKjq1"
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Requirement already satisfied: pandas==2.2.3 in c:\\users\\tobia\\git\\sc02_gruppe_10\\.venv\\lib\\site-packages (2.2.3)\n",
      "Requirement already satisfied: urllib3==2.2.3 in c:\\users\\tobia\\git\\sc02_gruppe_10\\.venv\\lib\\site-packages (2.2.3)\n",
      "Collecting openpyxl==3.1.0\n",
      "  Using cached openpyxl-3.1.0-py2.py3-none-any.whl.metadata (2.5 kB)\n",
      "Requirement already satisfied: numpy>=1.26.0 in c:\\users\\tobia\\git\\sc02_gruppe_10\\.venv\\lib\\site-packages (from pandas==2.2.3) (2.1.2)\n",
      "Requirement already satisfied: python-dateutil>=2.8.2 in c:\\users\\tobia\\git\\sc02_gruppe_10\\.venv\\lib\\site-packages (from pandas==2.2.3) (2.9.0.post0)\n",
      "Requirement already satisfied: pytz>=2020.1 in c:\\users\\tobia\\git\\sc02_gruppe_10\\.venv\\lib\\site-packages (from pandas==2.2.3) (2024.2)\n",
      "Requirement already satisfied: tzdata>=2022.7 in c:\\users\\tobia\\git\\sc02_gruppe_10\\.venv\\lib\\site-packages (from pandas==2.2.3) (2024.2)\n",
      "Requirement already satisfied: et-xmlfile in c:\\users\\tobia\\git\\sc02_gruppe_10\\.venv\\lib\\site-packages (from openpyxl==3.1.0) (2.0.0)\n",
      "Requirement already satisfied: six>=1.5 in c:\\users\\tobia\\git\\sc02_gruppe_10\\.venv\\lib\\site-packages (from python-dateutil>=2.8.2->pandas==2.2.3) (1.16.0)\n",
      "Using cached openpyxl-3.1.0-py2.py3-none-any.whl (250 kB)\n",
      "Installing collected packages: openpyxl\n",
      "  Attempting uninstall: openpyxl\n",
      "    Found existing installation: openpyxl 3.1.5\n",
      "    Uninstalling openpyxl-3.1.5:\n",
      "      Successfully uninstalled openpyxl-3.1.5\n",
      "Successfully installed openpyxl-3.1.0\n",
      "Note: you may need to restart the kernel to use updated packages.\n"
     ]
    }
   ],
   "source": [
    "# init - install and import all necessary libraries in a fixed version #reproducibility\n",
    "# important: every notebook has its own init cell including the required libraries. duplicates might occur and are intended\n",
    "%pip install pandas==2.2.3 urllib3==2.2.3 openpyxl==3.1.0\n",
    "%reset -f\n",
    "import os                           # operating system specific tasks incl. file handling\n",
    "import pandas as pd                 # pandas - handle dataframes (2d)\n",
    "import urllib.request               # pythons built in way to get stuff from the web\n",
    "import zipfile                      # for looking into and extracting files from a zip-archive"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## MISC Configration"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "metadata": {},
   "outputs": [],
   "source": [
    "# misc settings / configurations\n",
    "home_base = os.getcwd()\n",
    "\n",
    "# creating the base folder/file structure\n",
    "paths = [\n",
    "    \"data\",\n",
    "    \"data/raw\",\n",
    "    \"data/original\",\n",
    "    \"data/processed\",\n",
    "    \"logs\",\n",
    "]\n",
    "for path in paths:\n",
    "    os.makedirs(path, exist_ok=True)\n",
    "\n",
    "# display all columns and rows (do not worry, we do not have too many - we have checked)\n",
    "pd.set_option(\"display.max.columns\", None) # we know we do not have too many columns, so display all of them\n",
    "pd.set_option(\"display.max.rows\", None) # we know we do not have too many rows, so display all of them\n"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "id": "69Rseq2dKPJb"
   },
   "source": [
    "# SC02_Gruppe_10 | Basic Cost of Living\n",
    "## Problem Description\n",
    "The cost of living increases faster than the wages increase leading to a higher demand of cost of living optimization.\n",
    "Two of the biggest positions in the cost of living are rent and health care.\n",
    "\n",
    "### Thesis\n",
    "Visualizing the basic cost of living per canton in a heatmap (on a swiss geo map) along with charts that show the cost development over the past decade gives a clear indication and supports the financial aspect of the decision making process for citicens to choose an optimal canton of living."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## The data base\n",
    "The research is based on publicly available data from two sources:\n",
    "* BAG (hosted by admin.ch)\n",
    "* BFS (hosted by admin.ch)\n",
    "\n",
    "**<font color=\"red\">Disclaimer:</font> The state of the data which has proven to work with this project is part of the project delivery artifact to make sure to have a working script for validating its functionality.<br/>\n",
    "The code of this specific playbook allows for a download of the latest data but we cannot guarantee for the project to work as the format and contents may change and require different handling than currently implemented.**"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "metadata": {},
   "outputs": [
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "2024-12-09_084527:INFO - __main__:<module>: Program initialized at home_base='c:\\\\Users\\\\tobia\\\\git\\\\SC02_Gruppe_10'. Dependencies loaded.\n"
     ]
    }
   ],
   "source": [
    "# logging setup\n",
    "import logging\n",
    "\n",
    "# create custom log level TRACE with super-detailed information we would not usually need but might temporarily\n",
    "TRACE_LEVEL = 7\n",
    "logging.addLevelName(TRACE_LEVEL,'TRACE')\n",
    "\n",
    "# define te TRACE function for handling the new log level\n",
    "def trace(self, message, *args, **kwargs):\n",
    "    if self.isEnabledFor(TRACE_LEVEL):\n",
    "        self._log(TRACE_LEVEL, message, args, **kwargs)\n",
    "# Attach this method to the logger class\n",
    "logging.Logger.trace = trace\n",
    "\n",
    "\n",
    "# initialize a logger\n",
    "logger = logging.getLogger(__name__)\n",
    "\n",
    "# this line is needed because we might re-run code repeatedly within a notebook and are adding handlers over and over again,\n",
    "# if we do not clear them. This would result in repetitive log outputs. - kudos to Maurizio for this input\n",
    "logger.handlers.clear()\n",
    "\n",
    "# due to the analytical character of this project, we want to log a bit more than usual\n",
    "logger.setLevel(\"DEBUG\") # change to this if you would like even more details: logger.setLevel(\"TRACE\")\n",
    "\n",
    "# log handlers (how/whereto output logs)\n",
    "console_handler = logging.StreamHandler() # log to console\n",
    "file_handler = logging.FileHandler(\"logs/app.log\", mode=\"a\", encoding=\"utf-8\") # log more important info to a file\n",
    "logger.addHandler(console_handler)\n",
    "logger.addHandler(file_handler)\n",
    "\n",
    "# the format is currently the same for console and file. might make sense to change that in the future\n",
    "formatter = logging.Formatter(\n",
    "\"{asctime}:{levelname} - {name}:{funcName}: {message}\",\n",
    "    style=\"{\",\n",
    "    datefmt=\"%Y-%m-%d_%H%M%S\",\n",
    ")\n",
    "console_handler.setFormatter(formatter)\n",
    "file_handler.setFormatter(formatter)\n",
    "\n",
    "# only log info and higher to the console\n",
    "console_handler.setLevel(\"INFO\")\n",
    "\n",
    "# inform about the completion of the initialization\n",
    "logger.info(f\"Program initialized at {home_base=}. Dependencies loaded.\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Class to handle (potentially) repetitive file operations\n",
    "class File:\n",
    "    \"\"\" Class to handle (potentially) repetitive file operations \"\"\"\n",
    "    def download(self, url: str, file_path=None) -> str:\n",
    "        \"\"\" download a file from the internet and store it locally - if no file_path is given, the file will be stored in a temporary directory \"\"\"\n",
    "        try:\n",
    "            path, headers = urllib.request.urlretrieve(url,file_path)\n",
    "            logger.debug(\"File downloaded to path=%s\", path)\n",
    "            # sometimes we might get bad responses and need to look into the details of the response\n",
    "            for name, value in headers.items():\n",
    "                logger.trace(f'{name}:\\t{value}')\n",
    "            return path\n",
    "        except Exception as e:\n",
    "            logger.exception(\"FileDownloadError\")\n",
    "    \n",
    "    def extract(self, zip_file_path: str,file_in_archive: str, extract_to_filepath: str) -> bool:\n",
    "        \"\"\" extract one specific file from a local zip archive \"\"\"\n",
    "        zip_file = zipfile.ZipFile(zip_file_path)\n",
    "        logger.trace(zip_file.namelist())\n",
    "        logger.debug(f'File we are looking for: {file_in_archive}')\n",
    "        if file_in_archive in zip_file.namelist():\n",
    "            logger.debug(f'Found {file_in_archive} in archive {zip_file_path}.')\n",
    "        else:\n",
    "            logger.debug(f'Could not find {file_in_archive} in archive {zip_file_path}.')\n",
    "            return False\n",
    "        \n",
    "\n",
    "        # extract the file from the zip archive\n",
    "        f = zip_file.open(file_in_archive)\n",
    "        # save the extraced file\n",
    "        content = f.read()\n",
    "        f = open(extract_to_filepath, 'wb') # write binary - we have excel files\n",
    "        f.write(content)\n",
    "        f.close() # since we have not used the with statement, we need to close the file manually\n",
    "        logger.debug(f'Extracted file to {extract_to_filepath}')\n",
    "        return True\n",
    "\n",
    "# some testing of the class and its methods - we have not written any actual pytest tests\n",
    "# url = (\n",
    "#     \"https://api.worldbank.org/v2/en/indicator/\"\n",
    "#     \"NY.GDP.MKTP.CD?downloadformat=csv\"\n",
    "# )\n",
    "# filename = \"gdp_by_country.zip\"\n",
    "# temp_file_path = File.download(url)\n",
    "\n",
    "# initialize the file handler object here as it is stateless and can be reused\n",
    "file_handler = File()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Data Gathering / Retrieval"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "metadata": {
    "id": "pgV8H0n0K7il"
   },
   "outputs": [
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "2024-12-09_084529:INFO - __main__:<module>: We have data on rent of the following years:\n",
      "2024-12-09_084529:INFO - __main__:<module>: [2022, 2021, 2020, 2019, 2018, 2017, 2016, 2015, 2014, 2013, 2012, 2011, 2010, 2003, 2000]\n"
     ]
    }
   ],
   "source": [
    "# get rent data\n",
    "rent_years = set()\n",
    "source_price_rent_url = 'https://dam-api.bfs.admin.ch/hub/api/dam/assets/30885375/master'\n",
    "price_rent_map_name='data/original/rent_prices.xlsx' # where we want to store the extracted file\n",
    "\n",
    "price_rent_map_path = file_handler.download(url = source_price_rent_url, file_path = price_rent_map_name)\n",
    "\n",
    "# get a quick overview on the data; specifically the years we have data on\n",
    "xls = pd.ExcelFile(price_rent_map_path)\n",
    "rent_years = [int(x) for x in xls.sheet_names]\n",
    "logger.info('We have data on rent of the following years:')\n",
    "logger.info(rent_years)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "metadata": {},
   "outputs": [
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "2024-12-09_084529:INFO - __main__:<module>: We have data on health care of the following years:\n",
      "2024-12-09_084529:INFO - __main__:<module>: {1996, 1997, 1998, 1999, 2000, 2001, 2002, 2003, 2004, 2005, 2006, 2007, 2008, 2009, 2010, 2011, 2012, 2013, 2014, 2015, 2016, 2017, 2018, 2019, 2020, 2021, 2022}\n"
     ]
    }
   ],
   "source": [
    "# get health care data\n",
    "source_price_health_care_url = \"https://www.bag.admin.ch/dam/bag/de/dokumente/kuv-aufsicht/stat/publications-aos/stat_kv_22_xlsx.zip.download.zip/STATKV%202022%20XLSX%20v20231207.zip\"\n",
    "price_health_care_map_name = 'T 03 Prämien&mittl. Prämien je Versicherte_OKP - Primes&primes moy. par assuré_AOS/KV301N_STATKV2022_N.xlsx' # the file we are looking for in the zip archive\n",
    "price_health_care_map_path = 'data/original/health-care_premium.xlsx' # where we want to store the extracted file\n",
    "\n",
    "temp_file_path = file_handler.download(url=source_price_health_care_url)\n",
    "extract_success = file_handler.extract(zip_file_path=temp_file_path,file_in_archive=price_health_care_map_name,extract_to_filepath=price_health_care_map_path)\n",
    "if not extract_success:\n",
    "    logger.error(f\"Unable to find {price_health_care_map_name} in {temp_file_path}.\")\n",
    "else:\n",
    "    logger.debug('Healthcare data downloaded and stored successfully.')\n",
    "\n",
    "# get a quick overview on the data; specifically the years we have data on\n",
    "health_care_years = pd.read_excel(price_health_care_map_path, sheet_name='301d', usecols=\"A:AB\", nrows=1, skiprows=2).columns[1:]\n",
    "logger.info('We have data on health care of the following years:')\n",
    "logger.info(set(health_care_years))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "metadata": {},
   "outputs": [
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "2024-12-09_084529:INFO - __main__:<module>: We have date on the following years in both datasets:\n",
      "2024-12-09_084529:INFO - __main__:<module>: [2000, 2003, 2010, 2011, 2012, 2013, 2014, 2015, 2016, 2017, 2018, 2019, 2020, 2021, 2022]\n"
     ]
    }
   ],
   "source": [
    "years = sorted([x for x in rent_years if x in health_care_years])\n",
    "logger.info('We have date on the following years in both datasets:')\n",
    "logger.info(years)"
   ]
  }
 ],
 "metadata": {
  "colab": {
   "authorship_tag": "ABX9TyOuEzQpzA6EpX++eVmfNdb8",
   "provenance": []
  },
  "kernelspec": {
   "display_name": ".venv",
   "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.8"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 0
}