zip_1_load_data.ipynb

{
 "cells": [
  {
   "attachments": {},
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Introduction"
   ]
  },
  {
   "attachments": {},
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Provided the data downloaded from www.euets.info as zip file, we extract the data using the *ziploader* sub-module:"
   ]
  },
  {
   "attachments": {},
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Packages and options"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 1,
   "metadata": {},
   "outputs": [],
   "source": [
    "import pandas as pd\n",
    "import matplotlib as mpl\n",
    "import matplotlib.pyplot as plt\n",
    "from pyeutl.ziploader import (get_installations, get_accounts, get_transactions,\n",
    "                              get_compliance, get_account_holders) \n",
    "\n",
    "from pyeutl.ziploader.utils import (load_zipped_file, map_if_exists, get_mapper)\n",
    "from pyeutl.ziploader.category_mappings import (map_activity_category, map_account_category,\n",
    "                              map_nace_category)\n",
    "%load_ext autoreload\n",
    "%autoreload 2"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "metadata": {},
   "outputs": [],
   "source": [
    "fn_zip = \"./eutl_2023.zip\""
   ]
  },
  {
   "attachments": {},
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Get Data"
   ]
  },
  {
   "attachments": {},
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Get installation data from zip file. The function merges information from lookup tables into the installation table:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "<class 'pandas.core.frame.DataFrame'>\n",
      "RangeIndex: 17975 entries, 0 to 17974\n",
      "Data columns (total 33 columns):\n",
      " #   Column               Non-Null Count  Dtype  \n",
      "---  ------               --------------  -----  \n",
      " 0   id                   17975 non-null  object \n",
      " 1   name                 17975 non-null  object \n",
      " 2   tradingSystem_id     17975 non-null  object \n",
      " 3   registry_id          17975 non-null  object \n",
      " 4   activity_id          17975 non-null  int64  \n",
      " 5   eprtrID              5420 non-null   object \n",
      " 6   parentCompany        6471 non-null   object \n",
      " 7   subsidiaryCompany    3780 non-null   object \n",
      " 8   permitID             16310 non-null  object \n",
      " 9   designatorICAO       630 non-null    object \n",
      " 10  monitoringID         1636 non-null   object \n",
      " 11  monitoringExpiry     0 non-null      float64\n",
      " 12  monitoringFirstYear  1637 non-null   object \n",
      " 13  permitDateExpiry     5088 non-null   object \n",
      " 14  isAircraftOperator   17947 non-null  object \n",
      " 15  ec748_2009Code       1636 non-null   object \n",
      " 16  permitDateEntry      16310 non-null  object \n",
      " 17  addressMain          17947 non-null  object \n",
      " 18  addressSecondary     4114 non-null   object \n",
      " 19  postalCode           17944 non-null  object \n",
      " 20  city                 17944 non-null  object \n",
      " 21  country_id           17947 non-null  object \n",
      " 22  latitudeGoogle       16301 non-null  float64\n",
      " 23  longitudeGoogle      16301 non-null  float64\n",
      " 24  nace_id              16699 non-null  float64\n",
      " 25  euEntitlement        14109 non-null  float64\n",
      " 26  chEntitlement        93 non-null     float64\n",
      " 27  activity             17975 non-null  object \n",
      " 28  activityCategory     17947 non-null  object \n",
      " 29  registry             17975 non-null  object \n",
      " 30  country              17947 non-null  object \n",
      " 31  nace                 16699 non-null  object \n",
      " 32  naceCategory         16699 non-null  object \n",
      "dtypes: float64(6), int64(1), object(26)\n",
      "memory usage: 4.5+ MB\n"
     ]
    }
   ],
   "source": [
    "df_inst = get_installations(fn_zip, )\n",
    "df_inst.info()"
   ]
  },
  {
   "attachments": {},
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Get account holder informations"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "<class 'pandas.core.frame.DataFrame'>\n",
      "RangeIndex: 18238 entries, 0 to 18237\n",
      "Data columns (total 9 columns):\n",
      " #   Column                              Non-Null Count  Dtype \n",
      "---  ------                              --------------  ----- \n",
      " 0   id                                  18238 non-null  int64 \n",
      " 1   accountHolderTradingsystem_id       18238 non-null  object\n",
      " 2   accountHolderName                   18212 non-null  object\n",
      " 3   accountHolderAddressmain            18151 non-null  object\n",
      " 4   accountHolderPostalcode             18082 non-null  object\n",
      " 5   accountHolderCity                   18168 non-null  object\n",
      " 6   accountHolderLegalentityidentifier  2334 non-null   object\n",
      " 7   accountHolderCountry_id             18128 non-null  object\n",
      " 8   accountHolderCountry                18128 non-null  object\n",
      "dtypes: int64(1), object(8)\n",
      "memory usage: 1.3+ MB\n"
     ]
    }
   ],
   "source": [
    "df_accountHolder = get_account_holders(fn_zip)\n",
    "df_accountHolder = df_accountHolder.rename(columns = {\n",
    "    c: \"accountHolder\" + c.capitalize() for c in df_accountHolder.columns if c!= \"id\"})\n",
    "df_accountHolder.info() "
   ]
  },
  {
   "attachments": {},
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Next, load the account data. The account dataframe includes the information of related look-up tables. If a dataframe with installation or account holder information is passed to the function, this information is also merged into the account table:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "metadata": {},
   "outputs": [
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "e:\\GIT\\pyeutl\\venv\\lib\\site-packages\\pandas\\core\\reshape\\merge.py:1203: RuntimeWarning: invalid value encountered in cast\n",
      "  if not (lk == lk.astype(rk.dtype))[~np.isnan(lk)].all():\n"
     ]
    },
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "<class 'pandas.core.frame.DataFrame'>\n",
      "Int64Index: 43018 entries, 0 to 43017\n",
      "Data columns (total 35 columns):\n",
      " #   Column                              Non-Null Count  Dtype  \n",
      "---  ------                              --------------  -----  \n",
      " 0   id                                  43018 non-null  int64  \n",
      " 1   tradingSystem_id                    42153 non-null  object \n",
      " 2   accountIDESD                        226 non-null    object \n",
      " 3   yearValid                           224 non-null    float64\n",
      " 4   name                                42146 non-null  object \n",
      " 5   registry_id                         43018 non-null  object \n",
      " 6   accountHolder_id                    42149 non-null  float64\n",
      " 7   accountType_id                      42126 non-null  object \n",
      " 8   isOpen                              42153 non-null  object \n",
      " 9   openingDate                         41891 non-null  object \n",
      " 10  closingDate                         26706 non-null  object \n",
      " 11  commitmentPeriod                    40921 non-null  object \n",
      " 12  companyRegistrationNumber           37559 non-null  object \n",
      " 13  companyRegistrationNumberType       38741 non-null  object \n",
      " 14  isRegisteredEutl                    41923 non-null  object \n",
      " 15  installation_id                     29523 non-null  object \n",
      " 16  bvdId                               32084 non-null  object \n",
      " 17  registry                            43018 non-null  object \n",
      " 18  accountType                         42126 non-null  object \n",
      " 19  accountCategory                     41900 non-null  object \n",
      " 20  installationName                    29523 non-null  object \n",
      " 21  activity_id                         29523 non-null  float64\n",
      " 22  activity                            29523 non-null  object \n",
      " 23  activityCategory                    29299 non-null  object \n",
      " 24  nace_id                             27800 non-null  float64\n",
      " 25  nace                                27800 non-null  object \n",
      " 26  naceCategory                        27800 non-null  object \n",
      " 27  accountHolderTradingsystem_id       42149 non-null  object \n",
      " 28  accountHolderName                   42123 non-null  object \n",
      " 29  accountHolderAddressmain            41865 non-null  object \n",
      " 30  accountHolderPostalcode             41759 non-null  object \n",
      " 31  accountHolderCity                   41882 non-null  object \n",
      " 32  accountHolderLegalentityidentifier  7408 non-null   object \n",
      " 33  accountHolderCountry_id             41835 non-null  object \n",
      " 34  accountHolderCountry                41835 non-null  object \n",
      "dtypes: float64(4), int64(1), object(30)\n",
      "memory usage: 11.8+ MB\n"
     ]
    }
   ],
   "source": [
    "df_acc = get_accounts(fn_zip, \n",
    "                  df_installation=df_inst[[\"id\", \"name\", \"activity_id\", \"activity\", \"activityCategory\", \n",
    "                                                   \"nace_id\", \"nace\", \"naceCategory\"]],\n",
    "                    df_accountHolder=df_accountHolder)\n",
    "df_acc.info()\n",
    "# export the list with all accounts and related installation and account holder informations\n",
    "df_acc.to_excel(\"eutl_account_full_list.xlsx\", index=False)"
   ]
  },
  {
   "attachments": {},
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Compliance data follow the same logic:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "<class 'pandas.core.frame.DataFrame'>\n",
      "Int64Index: 481762 entries, 0 to 481761\n",
      "Data columns (total 49 columns):\n",
      " #   Column                Non-Null Count   Dtype  \n",
      "---  ------                --------------   -----  \n",
      " 0   id                    481762 non-null  object \n",
      " 1   installation_id       481762 non-null  object \n",
      " 2   year                  481762 non-null  int64  \n",
      " 3   reportedInSystem_id   481762 non-null  object \n",
      " 4   euetsPhase            481538 non-null  object \n",
      " 5   compliance_id         209237 non-null  object \n",
      " 6   allocatedFree         224431 non-null  float64\n",
      " 7   allocatedNewEntrance  6899 non-null    float64\n",
      " 8   allocatedTotal        481762 non-null  int64  \n",
      " 9   allocated10c          1548 non-null    float64\n",
      " 10  verified              200025 non-null  float64\n",
      " 11  verifiedCummulative   210478 non-null  float64\n",
      " 12  verifiedUpdated       481538 non-null  object \n",
      " 13  surrendered           182958 non-null  float64\n",
      " 14  balance               224 non-null     float64\n",
      " 15  penalty               196 non-null     float64\n",
      " 16  complianceCode        209237 non-null  object \n",
      " 17  name                  481762 non-null  object \n",
      " 18  tradingSystem_id      481762 non-null  object \n",
      " 19  registry_id           481762 non-null  object \n",
      " 20  activity_id           481762 non-null  int64  \n",
      " 21  eprtrID               140920 non-null  object \n",
      " 22  parentCompany         168246 non-null  object \n",
      " 23  subsidiaryCompany     98280 non-null   object \n",
      " 24  permitID              424060 non-null  object \n",
      " 25  designatorICAO        22518 non-null   object \n",
      " 26  monitoringID          57441 non-null   object \n",
      " 27  monitoringExpiry      0 non-null       float64\n",
      " 28  monitoringFirstYear   57478 non-null   object \n",
      " 29  permitDateExpiry      132288 non-null  object \n",
      " 30  isAircraftOperator    481538 non-null  object \n",
      " 31  ec748_2009Code        57441 non-null   object \n",
      " 32  permitDateEntry       424060 non-null  object \n",
      " 33  addressMain           481538 non-null  object \n",
      " 34  addressSecondary      106964 non-null  object \n",
      " 35  postalCode            481460 non-null  object \n",
      " 36  city                  481460 non-null  object \n",
      " 37  country_id            481538 non-null  object \n",
      " 38  latitudeGoogle        423826 non-null  float64\n",
      " 39  longitudeGoogle       423826 non-null  float64\n",
      " 40  nace_id               449090 non-null  float64\n",
      " 41  euEntitlement         380815 non-null  float64\n",
      " 42  chEntitlement         3441 non-null    float64\n",
      " 43  activity              481762 non-null  object \n",
      " 44  activityCategory      481538 non-null  object \n",
      " 45  registry              481762 non-null  object \n",
      " 46  country               481538 non-null  object \n",
      " 47  nace                  449090 non-null  object \n",
      " 48  naceCategory          449090 non-null  object \n",
      "dtypes: float64(14), int64(3), object(32)\n",
      "memory usage: 183.8+ MB\n"
     ]
    }
   ],
   "source": [
    "df_comp = get_compliance(fn_zip, df_installation=df_inst)\n",
    "df_comp.info()"
   ]
  },
  {
   "attachments": {},
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Transactions follow the same logic also providing the descriptions provided in the look-up tables. In addition, we can pass a table with account information. The transaction loader also allows to resample the transaction data to a higher frequency: "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "<class 'pandas.core.frame.DataFrame'>\n",
      "Int64Index: 1997164 entries, 0 to 1997163\n",
      "Data columns (total 45 columns):\n",
      " #   Column                           Dtype         \n",
      "---  ------                           -----         \n",
      " 0   date                             datetime64[ns]\n",
      " 1   id                               int64         \n",
      " 2   transactionID                    object        \n",
      " 3   transactionTypeMain_id           int64         \n",
      " 4   transactionTypeSupplementary_id  int64         \n",
      " 5   transferringAccount_id           float64       \n",
      " 6   acquiringAccount_id              float64       \n",
      " 7   unitType_id                      object        \n",
      " 8   tradingSystem_id                 object        \n",
      " 9   acquiringYear                    float64       \n",
      " 10  transferringYear                 float64       \n",
      " 11  amount                           int64         \n",
      " 12  transactionTypeMain              object        \n",
      " 13  transactionTypeSupplementary     object        \n",
      " 14  unitType                         object        \n",
      " 15  transferringName                 object        \n",
      " 16  transferringRegistry_id          object        \n",
      " 17  transferringAccountHolder_id     float64       \n",
      " 18  transferringAccountType_id       object        \n",
      " 19  transferringIsRegisteredEutl     object        \n",
      " 20  transferringInstallation_id      object        \n",
      " 21  transferringRegistry             object        \n",
      " 22  transferringAccountType          object        \n",
      " 23  transferringAccountCategory      object        \n",
      " 24  transferringInstallationName     object        \n",
      " 25  transferringActivity_id          float64       \n",
      " 26  transferringActivity             object        \n",
      " 27  transferringNace_id              float64       \n",
      " 28  transferringNace                 object        \n",
      " 29  transferringNaceCategory         object        \n",
      " 30  acquiringName                    object        \n",
      " 31  acquiringRegistry_id             object        \n",
      " 32  acquiringAccountHolder_id        float64       \n",
      " 33  acquiringAccountType_id          object        \n",
      " 34  acquiringIsRegisteredEutl        object        \n",
      " 35  acquiringInstallation_id         object        \n",
      " 36  acquiringRegistry                object        \n",
      " 37  acquiringAccountType             object        \n",
      " 38  acquiringAccountCategory         object        \n",
      " 39  acquiringInstallationName        object        \n",
      " 40  acquiringActivity_id             float64       \n",
      " 41  acquiringActivity                object        \n",
      " 42  acquiringNace_id                 float64       \n",
      " 43  acquiringNace                    object        \n",
      " 44  acquiringNaceCategory            object        \n",
      "dtypes: datetime64[ns](1), float64(10), int64(4), object(30)\n",
      "memory usage: 700.9+ MB\n"
     ]
    }
   ],
   "source": [
    "freq = \"M\"\n",
    "# get transactions merged with account information\n",
    "cols = ['id', 'name', 'registry_id', 'accountHolder_id', 'accountType_id',\n",
    "        'isRegisteredEutl', 'installation_id', 'registry', 'accountType', \n",
    "        'accountCategory', 'installationName', 'activity_id', 'activity', \n",
    "        'nace_id', 'nace', \"naceCategory\" ]\n",
    "df_trans = get_transactions(fn_zip, drop=[\"project_id\"], freq=freq, \n",
    "                      df_account=df_acc[cols])\n",
    "df_trans.info()"
   ]
  },
  {
   "attachments": {},
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "We can wrap all of this into a single function, e.g.:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "metadata": {
    "code_folding": []
   },
   "outputs": [],
   "source": [
    "def load_data(fn_zip, freq=None):\n",
    "    \"\"\"Load installation account and transaction data from zip file\n",
    "    :param fn_zip: <string> name of zip file with eutl data\n",
    "    :param freq: <string> frequency for resampling of transaction data\"\"\"\n",
    "    # get installations\n",
    "    df_inst = get_installations(fn_zip)\n",
    "    # get accounts with selected installation information\n",
    "    df_acc = get_accounts(fn_zip, \n",
    "                          df_installation=df_inst[[\"id\", \"name\", \"activity_id\", \"activity\", \"activityCategory\", \n",
    "                                                   \"nace_id\", \"nace\", \"naceCategory\"]]) \n",
    "    # get compliance data with installation information\n",
    "    df_comp = get_compliance(fn_zip, df_installation=df_inst)    \n",
    "    # get transactions merged with account information\n",
    "    cols = ['id', 'name', 'registry_id', 'accountHolder_id', 'accountType_id',\n",
    "            'isRegisteredEutl', 'installation_id', 'registry', 'accountType', \n",
    "            'accountCategory', 'installationName', 'activity_id', 'activity', \n",
    "            'nace_id', 'nace', \"naceCategory\" ]\n",
    "    df_trans = get_transactions(fn_zip, drop=[\"project_id\"], freq=freq, \n",
    "                          df_account=df_acc[cols])\n",
    "    return df_inst, df_acc, df_comp, df_trans\n",
    "\n",
    "df_inst, df_acc, df_comp, df_trans = load_data(fn_zip, freq=None)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "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.8.5"
  },
  "toc": {
   "base_numbering": 1,
   "nav_menu": {},
   "number_sections": true,
   "sideBar": true,
   "skip_h1_title": false,
   "title_cell": "Table of Contents",
   "title_sidebar": "Contents",
   "toc_cell": false,
   "toc_position": {},
   "toc_section_display": true,
   "toc_window_display": false
  },
  "varInspector": {
   "cols": {
    "lenName": 16,
    "lenType": 16,
    "lenVar": 40
   },
   "kernels_config": {
    "python": {
     "delete_cmd_postfix": "",
     "delete_cmd_prefix": "del ",
     "library": "var_list.py",
     "varRefreshCmd": "print(var_dic_list())"
    },
    "r": {
     "delete_cmd_postfix": ") ",
     "delete_cmd_prefix": "rm(",
     "library": "var_list.r",
     "varRefreshCmd": "cat(var_dic_list()) "
    }
   },
   "types_to_exclude": [
    "module",
    "function",
    "builtin_function_or_method",
    "instance",
    "_Feature"
   ],
   "window_display": false
  }
 },
 "nbformat": 4,
 "nbformat_minor": 4
}