pct_exchange.ipynb

{
 "cells": [
  {
   "cell_type": "code",
   "execution_count": 1,
   "id": "3e58924a",
   "metadata": {},
   "outputs": [],
   "source": [
    "import requests\n",
    "import pandas as pd\n",
    "import numpy as np\n",
    "import datetime as dt\n",
    "from datetime import datetime, timezone\n",
    "import time"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "id": "8a736e57",
   "metadata": {},
   "outputs": [],
   "source": [
    "# Measure exec time\n",
    "start = datetime.now()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "id": "d9fe1bcf",
   "metadata": {},
   "outputs": [],
   "source": [
    "### Calculation exchange rate of reference to further math on pct_change:\n",
    "def exchange_reference(df, date_reference):\n",
    "    \n",
    "    memory = {}\n",
    "        \n",
    "    for currency_code in df[\"currency_code\"].unique():\n",
    "        \n",
    "        if currency_code != \"VED\":\n",
    "            date_reference = date_reference\n",
    "        else:\n",
    "            date_reference = \"2021-10-06\"\n",
    "\n",
    "    \n",
    "        ## Select currency for calculations:\n",
    "        df_exchange = df[df['currency_code'] == currency_code ]\n",
    "\n",
    "        ## Select date to calculate reference\n",
    "\n",
    "        df_date_reference = df_exchange.loc[(df_exchange.date).dt.date == pd.Timestamp(date_reference)]\n",
    "\n",
    "        ### Averare exchange rate for day of reference:\n",
    "\n",
    "        implicit_exchange_reference = df_date_reference.implicit_exchange.mean()\n",
    "        \n",
    "        memory[currency_code] = implicit_exchange_reference\n",
    "\n",
    "    ### Percentual Variation\n",
    "\n",
    "#     equation = ((df_exchange.implicit_exchange / implicit_exchange_reference)-1)*100\n",
    "    \n",
    "    \n",
    "    return memory"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "id": "1e203e92",
   "metadata": {},
   "outputs": [],
   "source": [
    "from sqlalchemy import Column, Integer, Float, String, DateTime,Boolean, ForeignKey\n",
    "from sqlalchemy.orm import declarative_base\n",
    "from sqlalchemy import create_engine\n",
    "from sqlalchemy.orm import sessionmaker\n",
    "\n",
    "path_to_currencies = \"sqlite:///data/CurrenciesDataBase.db\"\n",
    "df_market = pd.read_sql('currencies_vs_btc', path_to_currencies)\n",
    "\n",
    "### The pct calculations are not properly saved on the dataset unless I upload one previously saved. Why?\n",
    "# The pipeline defined solve the issue by saving the calculated data on a new dataset but the calculations\n",
    "# are done in the full dataset everytime the code runs.\n",
    "# This practice is not scalable\n",
    "\n",
    "# The pct could be calculated on the web interface for a given date selected by the users but this implies \n",
    "# more processing time"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "id": "5fb64a70",
   "metadata": {},
   "outputs": [
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "/home/becode/.local/lib/python3.8/site-packages/pandas/core/ops/array_ops.py:73: FutureWarning: Comparison of Timestamp with datetime.date is deprecated in order to match the standard library behavior.  In a future version these will be considered non-comparable.Use 'ts == pd.Timestamp(date)' or 'ts.date() == date' instead.\n",
      "  result = libops.scalar_compare(x.ravel(), y, op)\n"
     ]
    },
    {
     "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>0</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>ADA</th>\n",
       "      <td>0.448517</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>AED</th>\n",
       "      <td>3.677105</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>AOA</th>\n",
       "      <td>727.218777</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>ARS</th>\n",
       "      <td>179.293196</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>AUD</th>\n",
       "      <td>1.417418</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "              0\n",
       "ADA    0.448517\n",
       "AED    3.677105\n",
       "AOA  727.218777\n",
       "ARS  179.293196\n",
       "AUD    1.417418"
      ]
     },
     "execution_count": 5,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df = df_market.groupby(by=[\"date\",\"currency_code\"]).mean()\n",
    "df.reset_index(inplace=True)\n",
    "\n",
    "### Dataset with implicit exchange of reference by currency:\n",
    "### The exchange of reference corresponds to the first datapoint on the dataset\n",
    "date_reference = \"2021-10-03\"\n",
    "\n",
    "exchange = exchange_reference(df, date_reference)\n",
    "exchange = pd.DataFrame.from_dict(exchange, orient=\"index\")\n",
    "\n",
    "exchange.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "id": "85b7f621",
   "metadata": {},
   "outputs": [],
   "source": [
    "df['pct'] = df.apply(lambda x : ((x['implicit_exchange']/exchange.loc[x[\"currency_code\"]])-1)*100, axis = 1 )\n",
    "\n",
    "### Drop Null values:\n",
    "\n",
    "df.dropna(subset=['pct'], inplace=True)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "id": "91e7d5a4",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "<class 'pandas.core.frame.DataFrame'>\n",
      "Int64Index: 12323 entries, 0 to 13347\n",
      "Data columns (total 12 columns):\n",
      " #   Column             Non-Null Count  Dtype         \n",
      "---  ------             --------------  -----         \n",
      " 0   date               12323 non-null  datetime64[ns]\n",
      " 1   currency_code      12323 non-null  object        \n",
      " 2   time_stamp         12323 non-null  float64       \n",
      " 3   volume_btc         12323 non-null  float64       \n",
      " 4   avg_24h            12323 non-null  float64       \n",
      " 5   avg_12h            11269 non-null  float64       \n",
      " 6   avg_6h             10027 non-null  float64       \n",
      " 7   avg_1h             6015 non-null   float64       \n",
      " 8   altcoin            12323 non-null  float64       \n",
      " 9   avg_24h_usd        12323 non-null  float64       \n",
      " 10  implicit_exchange  12323 non-null  float64       \n",
      " 11  pct                12323 non-null  float64       \n",
      "dtypes: datetime64[ns](1), float64(10), object(1)\n",
      "memory usage: 1.2+ MB\n"
     ]
    }
   ],
   "source": [
    "df.info()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "id": "15f79873",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "<class 'sqlalchemy.engine.base.Engine'>\n"
     ]
    }
   ],
   "source": [
    "### Saving on DB Version 2\n",
    "\n",
    "engine = create_engine('sqlite:///data/CurrenciesDataBase_V2.db')\n",
    "print(type(engine))\n",
    "df.to_sql(name=\"currencies_vs_btc\", con=engine, if_exists=\"append\", index=False)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "id": "dad330ba",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Last Execution: \n",
      "2022-02-07 20:57:06.821287\n",
      "Completed\n"
     ]
    }
   ],
   "source": [
    "end = dt.datetime.now()\n",
    "print(\"Last Execution: \")\n",
    "print(str(end))\n",
    "print('Completed')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 10,
   "id": "ee760f5f",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Execution time: 0:00:07.157500\n"
     ]
    }
   ],
   "source": [
    "print(\"Execution time:\", end-start)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "e6f799f0",
   "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.8"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 5
}