Aula_8.ipynb

{
 "cells": [
  {
   "cell_type": "code",
   "execution_count": 60,
   "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>DATE</th>\n",
       "      <th>CURRENCY</th>\n",
       "      <th>PX_LAST(dates=RANGE(-5D,0D))</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>ID</th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>FB US Equity</th>\n",
       "      <td>2020-08-12</td>\n",
       "      <td>USD</td>\n",
       "      <td>259.890000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>FB US Equity</th>\n",
       "      <td>2020-08-13</td>\n",
       "      <td>USD</td>\n",
       "      <td>261.300000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>FB US Equity</th>\n",
       "      <td>2020-08-14</td>\n",
       "      <td>USD</td>\n",
       "      <td>261.240000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>FB US Equity</th>\n",
       "      <td>2020-08-15</td>\n",
       "      <td>USD</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>FB US Equity</th>\n",
       "      <td>2020-08-16</td>\n",
       "      <td>USD</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>FB US Equity</th>\n",
       "      <td>2020-08-17</td>\n",
       "      <td>USD</td>\n",
       "      <td>261.109985</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>IBM US Equity</th>\n",
       "      <td>2020-08-12</td>\n",
       "      <td>USD</td>\n",
       "      <td>126.700000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>IBM US Equity</th>\n",
       "      <td>2020-08-13</td>\n",
       "      <td>USD</td>\n",
       "      <td>125.030000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>IBM US Equity</th>\n",
       "      <td>2020-08-14</td>\n",
       "      <td>USD</td>\n",
       "      <td>125.270000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>IBM US Equity</th>\n",
       "      <td>2020-08-15</td>\n",
       "      <td>USD</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>IBM US Equity</th>\n",
       "      <td>2020-08-16</td>\n",
       "      <td>USD</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>IBM US Equity</th>\n",
       "      <td>2020-08-17</td>\n",
       "      <td>USD</td>\n",
       "      <td>124.504997</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                    DATE CURRENCY  PX_LAST(dates=RANGE(-5D,0D))\n",
       "ID                                                             \n",
       "FB US Equity  2020-08-12      USD                    259.890000\n",
       "FB US Equity  2020-08-13      USD                    261.300000\n",
       "FB US Equity  2020-08-14      USD                    261.240000\n",
       "FB US Equity  2020-08-15      USD                           NaN\n",
       "FB US Equity  2020-08-16      USD                           NaN\n",
       "FB US Equity  2020-08-17      USD                    261.109985\n",
       "IBM US Equity 2020-08-12      USD                    126.700000\n",
       "IBM US Equity 2020-08-13      USD                    125.030000\n",
       "IBM US Equity 2020-08-14      USD                    125.270000\n",
       "IBM US Equity 2020-08-15      USD                           NaN\n",
       "IBM US Equity 2020-08-16      USD                           NaN\n",
       "IBM US Equity 2020-08-17      USD                    124.504997"
      ]
     },
     "execution_count": 60,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "import bql\n",
    "import pandas as pd \n",
    "bq = bql.Service()\n",
    "\n",
    "#-------------------#\n",
    "#  Object oriented  #\n",
    "#-------------------#\n",
    "\n",
    "item_1 = bq.data.px_last(dates=bq.func.range('-5D', '0D'))\n",
    "item_2 = bq.data.px_open(dates=bq.func.range('-5D', '0D'))\n",
    "items = [item_1, item_2]\n",
    "univ = ['FB US Equity', 'IBM US Equity']\n",
    "request = bql.Request(univ, items)\n",
    "response = bq.execute(request)\n",
    "df = response[0].df()\n",
    "df"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 61,
   "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>DATE</th>\n",
       "      <th>CURRENCY</th>\n",
       "      <th>PX_LAST(dates=RANGE(2020-01-01,2020-08-17))</th>\n",
       "      <th>PX_OPEN(dates=RANGE(2020-01-01,2020-08-17))</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>ID</th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>TSLA US Equity</th>\n",
       "      <td>2020-08-08</td>\n",
       "      <td>USD</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>TSLA US Equity</th>\n",
       "      <td>2020-08-09</td>\n",
       "      <td>USD</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>TSLA US Equity</th>\n",
       "      <td>2020-08-10</td>\n",
       "      <td>USD</td>\n",
       "      <td>1418.570000</td>\n",
       "      <td>1448.00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>TSLA US Equity</th>\n",
       "      <td>2020-08-11</td>\n",
       "      <td>USD</td>\n",
       "      <td>1374.390000</td>\n",
       "      <td>1396.00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>TSLA US Equity</th>\n",
       "      <td>2020-08-12</td>\n",
       "      <td>USD</td>\n",
       "      <td>1554.760000</td>\n",
       "      <td>1470.00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>TSLA US Equity</th>\n",
       "      <td>2020-08-13</td>\n",
       "      <td>USD</td>\n",
       "      <td>1621.000000</td>\n",
       "      <td>1611.00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>TSLA US Equity</th>\n",
       "      <td>2020-08-14</td>\n",
       "      <td>USD</td>\n",
       "      <td>1650.710000</td>\n",
       "      <td>1664.99</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>TSLA US Equity</th>\n",
       "      <td>2020-08-15</td>\n",
       "      <td>USD</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>TSLA US Equity</th>\n",
       "      <td>2020-08-16</td>\n",
       "      <td>USD</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>TSLA US Equity</th>\n",
       "      <td>2020-08-17</td>\n",
       "      <td>USD</td>\n",
       "      <td>1811.105957</td>\n",
       "      <td>1677.00</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                     DATE CURRENCY  \\\n",
       "ID                                   \n",
       "TSLA US Equity 2020-08-08      USD   \n",
       "TSLA US Equity 2020-08-09      USD   \n",
       "TSLA US Equity 2020-08-10      USD   \n",
       "TSLA US Equity 2020-08-11      USD   \n",
       "TSLA US Equity 2020-08-12      USD   \n",
       "TSLA US Equity 2020-08-13      USD   \n",
       "TSLA US Equity 2020-08-14      USD   \n",
       "TSLA US Equity 2020-08-15      USD   \n",
       "TSLA US Equity 2020-08-16      USD   \n",
       "TSLA US Equity 2020-08-17      USD   \n",
       "\n",
       "                PX_LAST(dates=RANGE(2020-01-01,2020-08-17))  \\\n",
       "ID                                                            \n",
       "TSLA US Equity                                          NaN   \n",
       "TSLA US Equity                                          NaN   \n",
       "TSLA US Equity                                  1418.570000   \n",
       "TSLA US Equity                                  1374.390000   \n",
       "TSLA US Equity                                  1554.760000   \n",
       "TSLA US Equity                                  1621.000000   \n",
       "TSLA US Equity                                  1650.710000   \n",
       "TSLA US Equity                                          NaN   \n",
       "TSLA US Equity                                          NaN   \n",
       "TSLA US Equity                                  1811.105957   \n",
       "\n",
       "                PX_OPEN(dates=RANGE(2020-01-01,2020-08-17))  \n",
       "ID                                                           \n",
       "TSLA US Equity                                          NaN  \n",
       "TSLA US Equity                                          NaN  \n",
       "TSLA US Equity                                      1448.00  \n",
       "TSLA US Equity                                      1396.00  \n",
       "TSLA US Equity                                      1470.00  \n",
       "TSLA US Equity                                      1611.00  \n",
       "TSLA US Equity                                      1664.99  \n",
       "TSLA US Equity                                          NaN  \n",
       "TSLA US Equity                                          NaN  \n",
       "TSLA US Equity                                      1677.00  "
      ]
     },
     "execution_count": 61,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Example 2:\n",
    "date_range = bq.func.range('2020-01-01', '2020-08-17')\n",
    "item_1 = bq.data.px_last(dates=date_range)\n",
    "item_2 = bq.data.px_open(dates=date_range)\n",
    "items = [item_1, item_2]\n",
    "univ = ['FB US Equity', 'TSLA US Equity']\n",
    "request = bql.Request(univ, items)\n",
    "response = bq.execute(request)\n",
    "df = bql.combined_df(response)\n",
    "df.tail(10)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 62,
   "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>FB US Equity - PX_Open</th>\n",
       "      <th>TSLA US Equity - PX_Open</th>\n",
       "      <th>FB US Equity\\t- PX_Close</th>\n",
       "      <th>TSLA US Equity- PX_Close</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Date</th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>2020-08-04</th>\n",
       "      <td>251.560</td>\n",
       "      <td>1495.010</td>\n",
       "      <td>249.830000</td>\n",
       "      <td>1487.000000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2020-08-05</th>\n",
       "      <td>250.190</td>\n",
       "      <td>1492.990</td>\n",
       "      <td>249.120000</td>\n",
       "      <td>1485.020000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2020-08-06</th>\n",
       "      <td>249.040</td>\n",
       "      <td>1490.830</td>\n",
       "      <td>265.280000</td>\n",
       "      <td>1489.580000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2020-08-07</th>\n",
       "      <td>264.080</td>\n",
       "      <td>1499.536</td>\n",
       "      <td>268.440000</td>\n",
       "      <td>1452.710000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2020-08-10</th>\n",
       "      <td>268.040</td>\n",
       "      <td>1448.000</td>\n",
       "      <td>263.000000</td>\n",
       "      <td>1418.570000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2020-08-11</th>\n",
       "      <td>260.185</td>\n",
       "      <td>1396.000</td>\n",
       "      <td>256.130000</td>\n",
       "      <td>1374.390000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2020-08-12</th>\n",
       "      <td>258.970</td>\n",
       "      <td>1470.000</td>\n",
       "      <td>259.890000</td>\n",
       "      <td>1554.760000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2020-08-13</th>\n",
       "      <td>261.550</td>\n",
       "      <td>1611.000</td>\n",
       "      <td>261.300000</td>\n",
       "      <td>1621.000000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2020-08-14</th>\n",
       "      <td>262.305</td>\n",
       "      <td>1664.990</td>\n",
       "      <td>261.240000</td>\n",
       "      <td>1650.710000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2020-08-17</th>\n",
       "      <td>262.500</td>\n",
       "      <td>1677.000</td>\n",
       "      <td>261.100006</td>\n",
       "      <td>1811.105957</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "            FB US Equity - PX_Open  TSLA US Equity - PX_Open  \\\n",
       "Date                                                           \n",
       "2020-08-04                 251.560                  1495.010   \n",
       "2020-08-05                 250.190                  1492.990   \n",
       "2020-08-06                 249.040                  1490.830   \n",
       "2020-08-07                 264.080                  1499.536   \n",
       "2020-08-10                 268.040                  1448.000   \n",
       "2020-08-11                 260.185                  1396.000   \n",
       "2020-08-12                 258.970                  1470.000   \n",
       "2020-08-13                 261.550                  1611.000   \n",
       "2020-08-14                 262.305                  1664.990   \n",
       "2020-08-17                 262.500                  1677.000   \n",
       "\n",
       "            FB US Equity\\t- PX_Close  TSLA US Equity- PX_Close  \n",
       "Date                                                            \n",
       "2020-08-04                249.830000               1487.000000  \n",
       "2020-08-05                249.120000               1485.020000  \n",
       "2020-08-06                265.280000               1489.580000  \n",
       "2020-08-07                268.440000               1452.710000  \n",
       "2020-08-10                263.000000               1418.570000  \n",
       "2020-08-11                256.130000               1374.390000  \n",
       "2020-08-12                259.890000               1554.760000  \n",
       "2020-08-13                261.300000               1621.000000  \n",
       "2020-08-14                261.240000               1650.710000  \n",
       "2020-08-17                261.100006               1811.105957  "
      ]
     },
     "execution_count": 62,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Data wrangling:\n",
    "# PX_Last DataFrame\n",
    "\n",
    "df.dropna(inplace=True, axis=0)\n",
    "df = df.reset_index()\n",
    "df.columns = ['ID', 'Date', 'Currency', 'PX_Last', 'PX_Open']\n",
    "df_px_last = df.pivot('Date', 'ID', 'PX_Last')\n",
    "df_px_open = df.pivot('Date', 'ID', 'PX_Open')\n",
    "df = pd.concat([df_px_open, df_px_last], axis=1)\n",
    "df.columns = ['FB US Equity - PX_Open',\n",
    "              'TSLA US Equity - PX_Open',\n",
    "              'FB US Equity\t- PX_Close',\n",
    "              'TSLA US Equity- PX_Close']\n",
    "df.tail(10)"
   ]
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3 (sandboxed)",
   "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.7.1"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 4
}