3 BQL Basics.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "<a id=\"bql-top\"></a>\n",
    "<div style=\"width: 50px; height: 50px; border-radius: 50%; background: #27AE60; margin-bottom: 0px;\">\n",
    "    <img src=\"../img/Welcome Page/boot_camp.png\">\n",
    "</div>\n",
    "<h1 style=\"margin-top: 20px; margin-bottom: 5px;\">The Bloomberg Query Language (BQL)<br><span style=\"color: orange; margin-bottom: 0px;\">Use Python to Access Bloomberg Data</span></h1>\n",
    "<h4 style=\"color: orange; margin-top: 0px;\">(25-min Read)</h4>"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "By the end of this document, you will be able to:\n",
    "1. Explain the basics of how the `bql` library and `bql.Service()` object work\n",
    "1. Build and execute a BQL request to get Bloomberg data into your project\n",
    "1. Understand the basics of a `pandas` DataFrame - a fundamental structure for data analysis\n",
    "1. Use the BQL Editor to prepare BQL requests"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Contents\n",
    "<ul style=\"margin-bottom: 0px;\">\n",
    "    <li><a href=\"#project-flow-bql\">The BQuant Project Flow</a></li>\n",
    "    <li><a href=\"#bql-lib\">The <code>bql</code> Python library</a></li>\n",
    "        <ul>\n",
    "            <li><a href=\"#bql-service\">The <code>bql.Service()</code> Object</a></li>\n",
    "            <li><a href=\"#bql-univ\">BQL Universes, Data, and Functions</a></li>\n",
    "            <li><a href=\"#bql-req\">BQL Requests and Responses</a></li>\n",
    "            <li><a href=\"#bql-res\">Parsing a BQL Response</a></li>\n",
    "        </ul>\n",
    "    <li><a href=\"#bql-pandas\">Basics of the <code>pandas</code> DataFrame</a></li>\n",
    "    <ul>\n",
    "        <li><a href=\"#df-index\">DataFrame Index Column</a></li>\n",
    "        <li><a href=\"#df-ops\">Manipulate the DataFrame</a></li>\n",
    "        <li><a href=\"#df-concat\">Concatenate, Merge, and Pivot</a></li>\n",
    "    </ul>\n",
    "    <li><a href=\"#bql-editor\">Using the BQL Editor Tool</a></li>\n",
    "</ul>\n",
    "<br>\n",
    "<h5 style=\"margin-top: 0px;\">&emsp;Examples</h5>\n",
    "<ul>\n",
    "    <li><a href=\"#bql-example\">Example: One Universe, One Data Item</a></li>\n",
    "    <li><a href=\"#bql-example2\">Example: One Universe, Multiple Data Items</a></li>\n",
    "    <li><a href=\"#bql-example3\">Example: Multiple Universes, One Data Item</a></li>\n",
    "    <li><a href=\"#bql-example4\">Example: Multiple Universes, Multiple Data Items</a></li>\n",
    "</ul>\n",
    "\n",
    "<a href=\"2 Python Basics.ipynb\">&larr; Back to Python Basics</a>&emsp; | &emsp;\n",
    "<a href=\"4 Boot Camp Project.ipynb\">Continue to Boot Camp: Final Project &rarr;</a>"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "---\n",
    "<a id=\"project-flow-bql\"></a>\n",
    "<h1>BQuant Project Flow<br><span style=\"color: orange;\">5-Step Process for Developing Your Projects</span></h1>"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "The 5-step project flow for BQuant development was mentioned in the Intro to BQuant section, but it bears repeating here. Every project you develop in BQuant will follow the same basic process below.\n",
    "\n",
    "1. Setup Environment\n",
    "1. Get Data from Bloomberg using BQL\n",
    "1. Additional Data Munging (if necessary)\n",
    "1. User Interface & Visualizations\n",
    "1. Simplify, Document, & Publish\n",
    "\n",
    "In this notebook, we cover the use of the Bloomberg Query Language (BQL) in Steps 1 and 2."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "[&uarr; Return to Top](#bql-top)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "---\n",
    "<a id=\"bql-lib\"></a>\n",
    "<h1>The <code>bql</code> Python Library<br><span style=\"color: orange;\">Introduction</span></h1>"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "BQL is the main tool for retrieving data in the BQuant environment. In order to use BQL, your project needs access to the objects and functions stored in the `bql` library. Recall from the Intro to BQuant section that <a href=\"1 Intro to BQuant.ipynb#intro-1-5\">Python libraries</a> are pre-written code files stored separately from your project. Engineers at Bloomberg created the `bql` library specifically for BQuant, and you can access it with an `import bql` statement at the top of your project."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 1,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Run this cell with the Run button in the Jupyter toolbar\n",
    "# Step 1: Setup Environment\n",
    "import bql"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "&#9888; A quick note on the `import` statement. It's considered best practice in any Python/Jupyter file to import all the necessary libraries for the entire project at the top. As this is a tutorial, we will show the `import bql` statement commented out in each code cell to display the entire block of code necessary to carry out a task. In your projects, however, you will only need to call `import bql` one time, in one cell at the top of the notebook."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "[&uarr; Return to Top](#bql-top)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "<a id=\"bql-service\"></a>\n",
    "<h1><span style=\"color: orange;\">The <code style=\"background-color: transparent; color: orange;\">bql.Service()</code> Object</span></h1>"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Inside the `bql` library is an object called `Service`. You will use this service object to connect to Bloomberg's servers and send and receive requests for data. After you import the `bql` library, declare a variable to store the `bql.Service()` object. You can call this variable whatever you want, but the convention among most BQuant developers is to name it `bq`, so let's use that name."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Run this cell with the Run button in the Jupyter toolbar\n",
    "# Step 1: Setup Environment\n",
    "# import bql\n",
    "bq = bql.Service()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "&#9888; The same principle applies to the `bql.Service()` object: you only need to declare it once at the top of the project. We will comment out this code in the Python code cells that follow."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "[&uarr; Return to Top](#bql-top)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "<a id=\"bql-univ\"></a>\n",
    "<h1><span style=\"color: orange;\">BQL Universes, Fields, and Functions</span></h1>"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "When making a BQL request for data, you will need to pass two arguments: the type of data, and the universe. You can think of BQL queries as having two statements: `GET` and `FOR`. If you need daily trading volume for Apple stock, you are asking BQL to \"get\" volume data \"for\" Apple. All BQL queries will follow this basic structure: `GET <data type> FOR <universe>`.\n",
    "\n",
    "While the universe variable is fairly straightforward, the data type can get quite complex. This data type variable can be a combination of BQL \"fields\" and \"functions\" for the universe. For example, if you are only interested in the daily volume, you can simply ask BQL to retrieve one field (volume). But suppose you are interested in the average volume over a period of time. In this case, you would need to perform a function (average) on the field (volume). Oftentimes you will need a combination of fields and functions to get the right data.\n",
    "\n",
    "The `bql.Service()` object, normally named `bq`, gives you access to these fields and functions with the syntax `bq.field` and `bq.func`, respectively. These fields and functions can be chained together to perform calculations. The example below prepares two data types for a BQL request: `vol` for the daily trading volume for Apple stock over the last 30 days, and `avg_vol` for the average of those numbers. Running the cell below will not produce any output; we'll discuss how to make a request and parse a response in the next sections."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Run this cell with the Run button in the Jupyter toolbar\n",
    "# Step 1: Setup Environment\n",
    "# import bql\n",
    "# bq = bql.Service()\n",
    "\n",
    "# Step 2: Get data with BQL\n",
    "universe = 'AAPL US Equity'                         # <-- Bloomberg ticker for the security\n",
    "vol = bq.data.px_volume('-30D', '0D')               # <-- use bq.data.px_volume() to retrieve volume data\n",
    "avg_vol = vol.avg()                                 # <-- use a BQL function (bq.func.avg) to retrieve the average of the vol time series"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "[&uarr; Return to Top](#bql-top)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "<a id=\"bql-req\"></a>\n",
    "<h1><span style=\"color: orange;\">BQL Requests and Responses</span></h1>"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Once a universe and data type have been declared, it's time to write a BQL request, send it to Bloomberg servers, and receive a response. Use the `bql.Request` object to initiate the request, and the `bq.Service` object has an `execute` method that will send the request and receive a response."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "1"
      ]
     },
     "execution_count": 4,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Run this cell with the Run button in the Jupyter toolbar\n",
    "# Step 1: Setup Environment\n",
    "# import bql\n",
    "# bq = bql.Service()\n",
    "\n",
    "# Step 2: Get data with BQL\n",
    "universe = 'AAPL US Equity'            # <-- Bloomberg ticker for the security\n",
    "vol = bq.data.px_volume('-30D', '0D')  # <-- use bq.data.px_volume() to retrieve volume data\n",
    "req = bql.Request(universe, vol)       # <-- bql Request object, pass the universe first, then data\n",
    "res = bq.execute(req)                  # <-- use the bql.Service() object to execute the request and get a response\n",
    "\n",
    "# get the length of the response\n",
    "len(res)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Notice that this response object `res` has a length of 1. This is because we requested only one dataset - `vol`. In the example below, we pass a list to the `bql.Request` object with two datasets for the same universe. Now the length of the response object is 2."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "2"
      ]
     },
     "execution_count": 5,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Run this cell with the Run button in the Jupyter toolbar\n",
    "# Step 1: Setup Environment\n",
    "# import bql\n",
    "# bq = bql.Service()\n",
    "\n",
    "# Step 2: Get data with BQL\n",
    "universe = 'AAPL US Equity'\n",
    "vol = bq.data.px_volume('-30D', '0D')         # <-- last 30 days of volume\n",
    "price = bq.data.px_last('-30D', '0D')         # <-- last 30 days of prices\n",
    "req = bql.Request(universe, [price, vol])     # <-- pass a list as the data attribute\n",
    "res = bq.execute(req)\n",
    "\n",
    "# get the length of the response\n",
    "len(res)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Each item in the response object has a `name` property. By default, the name of each response item is a string version of the query you wrote in Python. See the example below."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "PX_LAST(-30D,0D)\n",
      "PX_VOLUME(-30D,0D)\n"
     ]
    }
   ],
   "source": [
    "# Run this cell with the Run button in the Jupyter toolbar\n",
    "# Step 1: Setup Environment\n",
    "# import bql\n",
    "# bq = bql.Service()\n",
    "\n",
    "# Step 2: Get data with BQL\n",
    "universe = 'AAPL US Equity'\n",
    "vol = bq.data.px_volume('-30D', '0D')         # <-- last 30 days of volume\n",
    "price = bq.data.px_last('-30D', '0D')         # <-- last 30 days of prices\n",
    "req = bql.Request(universe, [price, vol])     # <-- pass a list as the data attribute\n",
    "res = bq.execute(req)\n",
    "\n",
    "# print the name of each response item\n",
    "for item in res:\n",
    "    print(item.name)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "For complex queries, these names could stretch to several hundred characters. We can control the names of these items by passing a dictionary to the `bql.Request` object instead of a list. These names will be important when we extract the data in the next section."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Price\n",
      "Volume\n"
     ]
    }
   ],
   "source": [
    "# Run this cell with the Run button in the Jupyter toolbar\n",
    "# Step 1: Setup Environment\n",
    "# import bql\n",
    "# bq = bql.Service()\n",
    "\n",
    "# Step 2: Get data with BQL\n",
    "universe = 'AAPL US Equity'\n",
    "vol = bq.data.px_volume('-30D', '0D')         # <-- last 30 days of volume\n",
    "price = bq.data.px_last('-30D', '0D')         # <-- last 30 days of prices\n",
    "req = bql.Request(universe, {'Price': price,  # <-- pass a dictionary as the data attribute\n",
    "                             'Volume': vol})     \n",
    "res = bq.execute(req)\n",
    "\n",
    "# print the name of each response item\n",
    "for item in res:\n",
    "    print(item.name)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "[&uarr; Return to Top](#bql-top)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "<a id=\"bql-res\"></a>\n",
    "<h1><span style=\"color: orange;\">Parsing a BQL Response</span></h1>"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "So far we've explored some of the properties of the `bql.Response` object, but we haven't seen the actual data we requested. In order to use the data in the response object, we need to call the `df()` function on the response items. This function will extract the data into a DataFrame: a spreadsheet-like object that we'll discuss later in this notebook.\n",
    "\n",
    "The example below will extract the data into a DataFrame, and print the first 5 rows to the screen. Notice that the column header is the same as the key in our dictionary (\"Volume\")."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "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>Volume</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>ID</th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>AAPL US Equity</th>\n",
       "      <td>2021-03-09</td>\n",
       "      <td>129525780.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>AAPL US Equity</th>\n",
       "      <td>2021-03-10</td>\n",
       "      <td>111943326.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>AAPL US Equity</th>\n",
       "      <td>2021-03-11</td>\n",
       "      <td>103026514.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>AAPL US Equity</th>\n",
       "      <td>2021-03-12</td>\n",
       "      <td>88105050.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>AAPL US Equity</th>\n",
       "      <td>2021-03-13</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                     DATE       Volume\n",
       "ID                                    \n",
       "AAPL US Equity 2021-03-09  129525780.0\n",
       "AAPL US Equity 2021-03-10  111943326.0\n",
       "AAPL US Equity 2021-03-11  103026514.0\n",
       "AAPL US Equity 2021-03-12   88105050.0\n",
       "AAPL US Equity 2021-03-13          NaN"
      ]
     },
     "execution_count": 8,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Run this cell with the Run button in the Jupyter toolbar\n",
    "# Step 1: Setup Environment\n",
    "# import bql\n",
    "# bq = bql.Service()\n",
    "\n",
    "# Step 2: Get data with BQL\n",
    "universe = 'AAPL US Equity'                        # <-- Bloomberg ticker for the security\n",
    "avg_vol = bq.data.px_volume('-30D', '0D')          # <-- chain together 'bq.data.px_volume' and 'bq.func.avg'\n",
    "req = bql.Request(universe, {'Volume': avg_vol})   # <-- use a dictionary to change the column heading to 'Average 30D Volume'\n",
    "res = bq.execute(req)                              # <-- use the bql.Service() object to execute the request and get a response\n",
    "df = res[0].df()                                   # <-- parse the first item in the response as a DataFrame using the df() method\n",
    "df.head()                                          # <-- display the first 5 rows of the DataFrame to the screen\n",
    "\n"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "<div style=\"border: 1px dotted orange; padding-left: 10px; padding-bottom: 10px;\">\n",
    "    <h3>&#9998; Try it out</h3>\n",
    "    <p>\n",
    "        Practice building, executing, and parsing a BQL Request\n",
    "        <ol>\n",
    "            <li>Add a new Jupyter code cell below this one</li>\n",
    "            <li>Import the <code>bql</code> library and establish a connection with the <code>bql.Service()</code> object.</li>\n",
    "            <li>Set a universe variable to the Bloomberg Americas-World Index <code>'BWORLDUS Index'</code></li>\n",
    "            <li>Declare a variable named <code>price</code> and set it equal to <code>bq.data.px_last('-30D', '0D')</code> to retrieve prices for the last 30 days.</li>\n",
    "            <li>Declare a variable named <code>req</code> and set it equal to a <code>bql.Request</code> object using the universe and price variables declared in Steps 3 and 4. Use a dictionary to control the name of the data item.</li>\n",
    "            <li>Declare a variable named <code>res</code> and set it equal to the <code>bq.execute()</code> method to execute the BQL request and receive a response.</li>\n",
    "            <li>Extract the data from <code>res</code> into a DataFrame using the <code>df()</code> method on the first item in the response.</li>\n",
    "        </ol>\n",
    "    We've written one possible solution to this problem in a hidden Python cell below. When you're ready to get a peek at an answer, click the <img src=\"../img/Controls/hidden_cell.png\"> below.\n",
    "    </p>\n",
    "</div>"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "<p style=\"color: orange; font-size: 12px;\">&darr; Click below to expand answer</p>"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "jupyter": {
     "source_hidden": true
    }
   },
   "outputs": [],
   "source": [
    "# Step 2\n",
    "# import bql\n",
    "# bq = bql.Service()\n",
    "\n",
    "# Step 3\n",
    "universe = 'BWORLDUS Index'\n",
    "\n",
    "# Step 4\n",
    "price = bq.data.px_last('-30D', '0D')\n",
    "\n",
    "# Step 5\n",
    "req = bql.Request(universe, {'Closing Price': price})\n",
    "\n",
    "# Step 6\n",
    "res = bq.execute(req)\n",
    "\n",
    "# Step 7\n",
    "df = res[0].df()\n",
    "\n",
    "# show the first 5 rows\n",
    "df.head()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "[&uarr; Return to Top](#bql-top)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "---\n",
    "<a id=\"bql-pandas\"></a>\n",
    "<h1>Basics of the <code>pandas</code> DataFrame<span style=\"color: orange;\"><br>Using DataFrames for Analysis</span></h1>"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "When we call the `df()` method on a BQL response object, the data inside is unpacked into a row/column format that resembles an Excel spreadsheet. In Python, this row/column object is called a \"DataFrame.\" These DataFrames were invented by an engineer at AQR Capital Management and made public in 2009. Today any Python developer can access these DataFrame objects via an open-source library called `pandas`. The `pandas` library and its DataFrames are foundational to most data analysis projects in Python.\n",
    "\n",
    "To fully understand the data we're working with in BQL, we'll need a basic understanding of how these DataFrames work. The following code cells will cover the basics of building and manipulating a DataFrame for use in BQL."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "<br>\n",
    "<h4 style=\"color: orange;\">Build a Simple DataFrame</h4>\n",
    "In the example below, we build a simple DataFrame and display it as a Jupyter cell output."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 45,
   "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>column 1</th>\n",
       "      <th>column 2</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>1</td>\n",
       "      <td>100</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>2</td>\n",
       "      <td>200</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>3</td>\n",
       "      <td>300</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>4</td>\n",
       "      <td>400</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   column 1  column 2\n",
       "0         1       100\n",
       "1         2       200\n",
       "2         3       300\n",
       "3         4       400"
      ]
     },
     "execution_count": 45,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# import the pandas library\n",
    "import pandas as pd\n",
    "\n",
    "# build a 2-column DataFrame by using a dictionary\n",
    "df = pd.DataFrame(data={'column 1': [1, 2, 3, 4],\n",
    "                        'column 2': [100, 200, 300, 400]})\n",
    "\n",
    "# Jupyter cell output\n",
    "df"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "[&uarr; Return to Top](#bql-top)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "<a id=\"df-index\"></a>\n",
    "<h1><span style=\"color: orange;\">DataFrame Index Column</span></h1>"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Notice that the DataFrame automatically generated a column to the left that we didn't specify in our dictionary.\n",
    "<br><br>\n",
    "<img src=\"../img/Boot Camp/df_index.png\">\n",
    "<p>This column is the DataFrame's \"index\" which is responsible for uniquely identifying each row. A DataFrame index can be any list of values so long as the list is the same length as the DataFrame. We can also name the index similar to the way we named the columns. See the example below.</p>"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 46,
   "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>column 1</th>\n",
       "      <th>column 2</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>My Index</th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>ind1</th>\n",
       "      <td>1</td>\n",
       "      <td>100</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>ind2</th>\n",
       "      <td>2</td>\n",
       "      <td>200</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>ind3</th>\n",
       "      <td>3</td>\n",
       "      <td>300</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>ind4</th>\n",
       "      <td>4</td>\n",
       "      <td>400</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "          column 1  column 2\n",
       "My Index                    \n",
       "ind1             1       100\n",
       "ind2             2       200\n",
       "ind3             3       300\n",
       "ind4             4       400"
      ]
     },
     "execution_count": 46,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# import the pandas library\n",
    "# import pandas as pd\n",
    "\n",
    "# build a 2-column DataFrame with a custom index\n",
    "df = pd.DataFrame(data={'column 1': [1, 2, 3, 4],\n",
    "                        'column 2': [100, 200, 300, 400]},\n",
    "                  index=['ind1', 'ind2', 'ind3', 'ind4'])\n",
    "\n",
    "# name the index\n",
    "df.index.name = 'My Index'\n",
    "\n",
    "# show the DataFrame\n",
    "df"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "<br>\n",
    "<h4 style=\"color: orange;\">Reset a DataFrame's Index</h4>\n",
    "<p>BQL will automatically assign a list of ticker IDs to the DataFrame index. On occasion you may need to move this column from the index to use it like any other column in the DataFrame. DataFrames have a <code>reset_index</code> method that will move the current index over into a column and establish a new default index.</p>"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 47,
   "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>Original Index</th>\n",
       "      <th>column 1</th>\n",
       "      <th>column 2</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>100</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>1</td>\n",
       "      <td>2</td>\n",
       "      <td>200</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>2</td>\n",
       "      <td>3</td>\n",
       "      <td>300</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>3</td>\n",
       "      <td>4</td>\n",
       "      <td>400</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   Original Index  column 1  column 2\n",
       "0               0         1       100\n",
       "1               1         2       200\n",
       "2               2         3       300\n",
       "3               3         4       400"
      ]
     },
     "execution_count": 47,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# import the pandas library\n",
    "# import pandas as pd\n",
    "\n",
    "# build a 2-column DataFrame with a custom index\n",
    "df = pd.DataFrame(data={'column 1': [1, 2, 3, 4],\n",
    "                        'column 2': [100, 200, 300, 400]})\n",
    "df.index.name = 'Original Index'\n",
    "\n",
    "# reset the index\n",
    "df = df.reset_index()\n",
    "\n",
    "# show the DataFrame\n",
    "df"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "[&uarr; Return to Top](#bql-top)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "<a id=\"df-ops\"></a>\n",
    "<h1><span style=\"color: orange;\">Manipulate the DataFrame</span></h1>\n",
    "Run each of the code cells below to see some of the basic functions of a DataFrame."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 68,
   "metadata": {},
   "outputs": [],
   "source": [
    "# import the pandas library\n",
    "# import pandas as pd\n",
    "\n",
    "# build a 2-column DataFrame with a custom index\n",
    "df = pd.DataFrame(data={'column 1': [1, 2, 3, 4],\n",
    "                        'column 2': [100, 200, 300, 400]})\n",
    "\n"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "<h4 style=\"color: orange;\">Get a List of Column Names</h4>"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 69,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "['column 1', 'column 2']"
      ]
     },
     "execution_count": 69,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "list(df.columns)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "<h4 style=\"color: orange;\">Select the Columns</h4>"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 70,
   "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>column 1</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>3</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>4</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   column 1\n",
       "0         1\n",
       "1         2\n",
       "2         3\n",
       "3         4"
      ]
     },
     "execution_count": 70,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# get one column\n",
    "# use a list of column names to extract a smaller DataFrame\n",
    "col_1 = df[['column 1']]\n",
    "\n",
    "# show col_1\n",
    "col_1"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 71,
   "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>column 1</th>\n",
       "      <th>column 2</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>1</td>\n",
       "      <td>100</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>2</td>\n",
       "      <td>200</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>3</td>\n",
       "      <td>300</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>4</td>\n",
       "      <td>400</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   column 1  column 2\n",
       "0         1       100\n",
       "1         2       200\n",
       "2         3       300\n",
       "3         4       400"
      ]
     },
     "execution_count": 71,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# multiple columns\n",
    "col1_and_col2 = df[['column 1', 'column 2']]  # <-- pass a list of 2 column names\n",
    "\n",
    "# show col1_and_col2\n",
    "col1_and_col2"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 72,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "[1, 2, 3, 4]"
      ]
     },
     "execution_count": 72,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# get a single column as a list of values\n",
    "col1_list = df['column 1'].tolist()\n",
    "\n",
    "# show the list\n",
    "col1_list"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "<h4 style=\"color: orange;\">Add New Columns</h4>"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 51,
   "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>column 1</th>\n",
       "      <th>column 2</th>\n",
       "      <th>column 3</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>1</td>\n",
       "      <td>100</td>\n",
       "      <td>1000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>2</td>\n",
       "      <td>200</td>\n",
       "      <td>2000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>3</td>\n",
       "      <td>300</td>\n",
       "      <td>3000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>4</td>\n",
       "      <td>400</td>\n",
       "      <td>4000</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   column 1  column 2  column 3\n",
       "0         1       100      1000\n",
       "1         2       200      2000\n",
       "2         3       300      3000\n",
       "3         4       400      4000"
      ]
     },
     "execution_count": 51,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# add a new column by passing a list of data\n",
    "# name the new column 'column 3'\n",
    "df['column 3'] = [1000, 2000, 3000, 4000]\n",
    "\n",
    "# show the dataframe\n",
    "df"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "<h4 style=\"color: orange;\">Column Operations</h4>"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 52,
   "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>column 1</th>\n",
       "      <th>column 2</th>\n",
       "      <th>column 3</th>\n",
       "      <th>col1 + col2</th>\n",
       "      <th>col2 ^ col1</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>1</td>\n",
       "      <td>100</td>\n",
       "      <td>1000</td>\n",
       "      <td>101</td>\n",
       "      <td>100</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>2</td>\n",
       "      <td>200</td>\n",
       "      <td>2000</td>\n",
       "      <td>202</td>\n",
       "      <td>40000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>3</td>\n",
       "      <td>300</td>\n",
       "      <td>3000</td>\n",
       "      <td>303</td>\n",
       "      <td>27000000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>4</td>\n",
       "      <td>400</td>\n",
       "      <td>4000</td>\n",
       "      <td>404</td>\n",
       "      <td>25600000000</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   column 1  column 2  column 3  col1 + col2  col2 ^ col1\n",
       "0         1       100      1000          101          100\n",
       "1         2       200      2000          202        40000\n",
       "2         3       300      3000          303     27000000\n",
       "3         4       400      4000          404  25600000000"
      ]
     },
     "execution_count": 52,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# add a new column by adding two columns\n",
    "df['col1 + col2'] = df['column 1'] + df['column 2']   # <-- use single brackets; df['column 1'] + df['column 2']\n",
    "\n",
    "# add a new column by exponentiation\n",
    "df['col2 ^ col1'] = df['column 2'] ** df['column 1']\n",
    "\n",
    "# show df\n",
    "df"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "<h4 style=\"color: orange;\">Select Individual Cells</h4>"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 53,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "100"
      ]
     },
     "execution_count": 53,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# get the value in the first cell of column 2\n",
    "df['column 2'][0]"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "<h4 style=\"color: orange;\">Change Individual Cells</h4>"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 54,
   "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>column 1</th>\n",
       "      <th>column 2</th>\n",
       "      <th>column 3</th>\n",
       "      <th>col1 + col2</th>\n",
       "      <th>col2 ^ col1</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>1</td>\n",
       "      <td>1234</td>\n",
       "      <td>1000</td>\n",
       "      <td>101</td>\n",
       "      <td>100</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>2</td>\n",
       "      <td>200</td>\n",
       "      <td>2000</td>\n",
       "      <td>202</td>\n",
       "      <td>40000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>3</td>\n",
       "      <td>300</td>\n",
       "      <td>3000</td>\n",
       "      <td>303</td>\n",
       "      <td>27000000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>4</td>\n",
       "      <td>400</td>\n",
       "      <td>4000</td>\n",
       "      <td>404</td>\n",
       "      <td>25600000000</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   column 1  column 2  column 3  col1 + col2  col2 ^ col1\n",
       "0         1      1234      1000          101          100\n",
       "1         2       200      2000          202        40000\n",
       "2         3       300      3000          303     27000000\n",
       "3         4       400      4000          404  25600000000"
      ]
     },
     "execution_count": 54,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# change the value in the first cell of column 2\n",
    "# use the at function; pass an index row and column name\n",
    "df.at[0, 'column 2'] = 1234\n",
    "\n",
    "# show the DataFrame\n",
    "df"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "[&uarr; Return to Top](#bql-top)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "<a id=\"df-concat\"></a>\n",
    "<h1 style=\"margin-bottom: 15px;\"><span style=\"color: orange;\">Concatenate, Merge, and Pivot</span></h1>\n",
    "<p>Parsing some BQL requests will require you to alter the structure of the DataFrame. The concatenate, merge, and pivot functions of the <code>pandas</code> library could be useful tools depending on the query.</p>\n",
    "<ul>\n",
    "    <li><code>pd.concat()</code> - combine two or more DataFrames by either rows or columns.</li>\n",
    "    <li><code>pd.DataFrame.merge()</code> - join two DataFrames together based on values in a common column</li>\n",
    "    <li><code>pd.DataFrame.pivot()</code> - reshape a DataFrame using its values</li>\n",
    "</ul>"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "<h4 style=\"color: orange; margin-bottom: 10px;\">Concatenate DataFrame Columns</h4>\n",
    "<p>Read more about the <code>pd.concat()</code> function in the <a href=\"https://pandas.pydata.org/pandas-docs/stable/reference/api/pandas.concat.html\">pandas docs</a>.</p>"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 55,
   "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>df1_col1</th>\n",
       "      <th>df1_col2</th>\n",
       "      <th>df2_col1</th>\n",
       "      <th>df2_col2</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>1</td>\n",
       "      <td>4</td>\n",
       "      <td>7</td>\n",
       "      <td>10</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>2</td>\n",
       "      <td>5</td>\n",
       "      <td>8</td>\n",
       "      <td>11</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>3</td>\n",
       "      <td>6</td>\n",
       "      <td>9</td>\n",
       "      <td>12</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   df1_col1  df1_col2  df2_col1  df2_col2\n",
       "0         1         4         7        10\n",
       "1         2         5         8        11\n",
       "2         3         6         9        12"
      ]
     },
     "execution_count": 55,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# declare two DataFrames\n",
    "df1 = pd.DataFrame(data={'df1_col1': [1, 2, 3],\n",
    "                         'df1_col2': [4, 5, 6]})\n",
    "\n",
    "df2 = pd.DataFrame(data={'df2_col1': [7, 8, 9],\n",
    "                         'df2_col2': [10, 11, 12]})\n",
    "\n",
    "# concatenate the two DataFrames\n",
    "# pass a list of DataFrames to concatenate\n",
    "# use axis=1 to indicate that you are joining on the columns\n",
    "df3 = pd.concat([df1, df2], axis=1)\n",
    "\n",
    "# show the DataFrame\n",
    "df3\n"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "<h4 style=\"color: orange;\">Concatenate DataFrame Rows</h4>\n",
    "<p>Read more about the <code>pd.concat()</code> function in the <a href=\"https://pandas.pydata.org/pandas-docs/stable/reference/api/pandas.concat.html\">pandas docs</a>.</p>"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 56,
   "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>column 1</th>\n",
       "      <th>column 2</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>1</td>\n",
       "      <td>7</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>2</td>\n",
       "      <td>8</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>3</td>\n",
       "      <td>9</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>4</td>\n",
       "      <td>10</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>5</td>\n",
       "      <td>11</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>6</td>\n",
       "      <td>12</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   column 1  column 2\n",
       "0         1         7\n",
       "1         2         8\n",
       "2         3         9\n",
       "0         4        10\n",
       "1         5        11\n",
       "2         6        12"
      ]
     },
     "execution_count": 56,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# declare two DataFrames\n",
    "df1 = pd.DataFrame(data={'column 1': [1, 2, 3],\n",
    "                         'column 2': [7, 8, 9]})\n",
    "\n",
    "df2 = pd.DataFrame(data={'column 1': [4, 5, 6],\n",
    "                         'column 2': [10, 11, 12]})\n",
    "\n",
    "# concatenate the two DataFrames\n",
    "# pass a list of DataFrames to concatenate\n",
    "# use axis=0 to indicate that you are joining rows of the columns with the same name\n",
    "df3 = pd.concat([df1, df2], axis=0)\n",
    "\n",
    "# show the DataFrame\n",
    "df3\n"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "<h4 style=\"color: orange;\">Merge DataFrames</h4>\n",
    "<p>Read more about the <code>pd.DataFrame.merge()</code> function in the <a href=\"https://pandas.pydata.org/pandas-docs/stable/reference/api/pandas.DataFrame.merge.html\">pandas docs</a>.</p>"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 57,
   "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>column 1</th>\n",
       "      <th>column 2_df1</th>\n",
       "      <th>column 2_df2</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>1</td>\n",
       "      <td>7</td>\n",
       "      <td>10</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>2</td>\n",
       "      <td>8</td>\n",
       "      <td>11</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   column 1  column 2_df1  column 2_df2\n",
       "0         1             7            10\n",
       "1         2             8            11"
      ]
     },
     "execution_count": 57,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# declare two DataFrames\n",
    "df1 = pd.DataFrame(data={'column 1': [1, 2, 3],\n",
    "                         'column 2': [7, 8, 9]})\n",
    "\n",
    "df2 = pd.DataFrame(data={'column 1': [1, 2, 100],\n",
    "                         'column 2': [10, 11, 12]})\n",
    "\n",
    "# merge the two DataFrames on column 1\n",
    "# use an 'inner' join to only keep rows that have common column 1 values\n",
    "df3 = df1.merge(df2, on='column 1', how='inner', suffixes=['_df1', '_df2'])\n",
    "\n",
    "# show the DataFrame\n",
    "df3\n"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "<h4 style=\"color: orange; margin-bottom: 10px;\">Pivot a DataFrame</h4>\n",
    "<p>The example below is a common workflow for parsing a single time series dataset requested for multiple universes. Our DataFrame <code>df</code> contains three days of closing prices for the stocks of both AAPL and IBM. The raw response from BQL will place the rows for the two requests on top of each other. For analysis purposes, we want each ticker in its own column and each date to have its own row. The pivot function of the <code>pandas</code> library can help us accomplish this.</p>\n",
    "<p>Read more about the <code>pd.DataFrame.pivot()</code> function in the <a href=\"https://pandas.pydata.org/pandas-docs/stable/reference/api/pandas.DataFrame.pivot.html\">pandas docs</a>.</p>"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 58,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Before Pivot:\n",
      "      Closing Prices   Date\n",
      "ID                         \n",
      "AAPL           50.00  Jan 1\n",
      "AAPL           50.01  Jan 2\n",
      "AAPL           50.02  Jan 3\n",
      "IBM           100.00  Jan 1\n",
      "IBM           101.00  Jan 2\n",
      "IBM           105.00  Jan 3\n",
      "\n",
      "\n",
      "After Pivot:\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>ID</th>\n",
       "      <th>AAPL</th>\n",
       "      <th>IBM</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Date</th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>Jan 1</th>\n",
       "      <td>50.00</td>\n",
       "      <td>100.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Jan 2</th>\n",
       "      <td>50.01</td>\n",
       "      <td>101.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Jan 3</th>\n",
       "      <td>50.02</td>\n",
       "      <td>105.0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "ID      AAPL    IBM\n",
       "Date               \n",
       "Jan 1  50.00  100.0\n",
       "Jan 2  50.01  101.0\n",
       "Jan 3  50.02  105.0"
      ]
     },
     "execution_count": 58,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# declare two DataFrames\n",
    "df = pd.DataFrame(data={'Closing Prices': [50.00, 50.01, 50.02, 100.0, 101.0, 105.0],\n",
    "                        'Date': ['Jan 1', 'Jan 2', 'Jan 3', 'Jan 1', 'Jan 2', 'Jan 3']},\n",
    "                  index=['AAPL', 'AAPL', 'AAPL', 'IBM', 'IBM', 'IBM'])\n",
    "df.index.name = 'ID'\n",
    "\n",
    "# show the DataFrame before a pivot\n",
    "print('Before Pivot:')\n",
    "print(df)\n",
    "\n",
    "# pivot the DataFrame\n",
    "# first we reset the index to get 'ID' into a column\n",
    "pivot_df = df.reset_index()\n",
    "\n",
    "# pivot the dataframe so that each ticker has its own column\n",
    "# each date should have its own row\n",
    "pivot_df = pivot_df.pivot(index='Date', values='Closing Prices', columns='ID')\n",
    "\n",
    "# show the DataFrame\n",
    "print('\\n\\nAfter Pivot:')\n",
    "pivot_df"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "The `pandas` library is an extensive topic, and we've only covered the basics in this notebook. The library is open-source, so all the documentation is available online. See [pandas.pydata.org/docs/](https://pandas.pydata.org/docs/) for additional information, and if you're having trouble with a specific issue, check [StackOverflow](https://stackoverflow.com/questions/tagged/pandas) to see if another developer"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "<div style=\"border: 1px dotted orange; padding-left: 10px; padding-bottom: 10px;\">\n",
    "    <h3>&#9998; Try it out</h3>\n",
    "    <p>\n",
    "        Practice with DataFrames and the <code>pandas</code> library.\n",
    "        <ol>\n",
    "            <li>Add a new Jupyter code cell below this one</li>\n",
    "            <li>Import the <code>pandas</code> library.</li>\n",
    "            <li>Declare a variable named <code>my_df</code> and set it equal to a DataFrame with two columns of numerical data.</li>\n",
    "            <li>Add a third column whose values are the sum of the values in each row of the first two columns.</li>\n",
    "            <li>Declare a second DataFrame named <code>my_df2</code> with three columns using the same column names as <code>my_df</code>.</li>\n",
    "            <li>Use the <code>pd.concat()</code> function to create a third DataFrame <code>my_df3</code>, also with three columns, and combining all the rows of <code>my_df</code> and <code>my_df2</code>.</li>\n",
    "            <li>Give <code>my_df3</code> a new index by calling <code>my_df3.index = </code> and set it equal to a list of unique values.</li>\n",
    "        </ol>\n",
    "    We've written one possible solution to this problem in a hidden Python cell below. When you're ready to get a peek at an answer, click the <img src=\"../img/Controls/hidden_cell.png\"> below.\n",
    "    </p>\n",
    "</div>"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "<p style=\"color: orange; font-size: 12px;\">&darr; Click below to expand answer</p>"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "jupyter": {
     "source_hidden": true
    }
   },
   "outputs": [],
   "source": [
    "# Step 2\n",
    "import pandas as pd\n",
    "\n",
    "# Step 3\n",
    "my_df = pd.DataFrame(data={'col1': [1, 2, 3], \n",
    "                           'col2': [4, 5, 6]})\n",
    "\n",
    "# Step 4\n",
    "my_df['col3'] = my_df['col1'] + my_df['col2']\n",
    "\n",
    "# Step 5\n",
    "my_df2 = pd.DataFrame(data={'col1': [100, 200, 300],\n",
    "                            'col2': [400, 500, 600],\n",
    "                            'col3': [700, 800, 900]})\n",
    "\n",
    "# Step 6\n",
    "my_df3 = pd.concat([my_df, my_df2])\n",
    "\n",
    "# Step 7\n",
    "my_df3.index = list(range(len(my_df3)))  # <-- set the index to a list of values ranging from zero to the length of the DataFrame\n",
    "\n",
    "# Show DataFrame\n",
    "my_df3"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "[&uarr; Return to Top](#bql-top)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "---\n",
    "<a id=\"bql-editor\"></a>\n",
    "<h1>Using the BQL Editor Tool<span style=\"color: orange;\"><br>Search Fields, Universes, and Functions</span></h1>"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "BQuant provides a useful tool called the \"BQL Editor\" to help you write and implement BQL queries. \n",
    "\n",
    "The BQL Editor allows you to:\n",
    "   1. Search for Fields, Functions, and Universes\n",
    "   1. Run a test query and get a preview of the results\n",
    "   1. Copy the query's Python code so you can paste it in a Jupyter cell\n",
    "   \n",
    "You can access the BQL Editor in the toolbar at the top of the screen. Clicking the BQL Editor button will open up a separate tab in which you can write your test query.\n",
    "\n",
    "The example below demonstrates the use of the BQL Editor in building a query for the most recent S&P 500 price."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "<img src=\"../img/Boot Camp/bql_editor.gif\">"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "<div style=\"border: 1px dotted orange; padding-left: 10px; padding-bottom: 10px;\">\n",
    "    <h3>&#9998; Try it out</h3>\n",
    "    <p>\n",
    "        Practice with the BQL Editor\n",
    "        <ol>\n",
    "            <li>Add a new Jupyter code cell below this one.</li>\n",
    "            <li>Click into the BQL Editor and build a query for the latest daily high <code>PX_HIGH()</code> for <code>'AAPL US Equity'</code>.</li>\n",
    "            <li>Preview the results of the query with the Preview button in the toolbar.</li>\n",
    "            <li>Copy the Python code for the query by selecting Copy &rarr; BQL Object Model.</li>\n",
    "            <li>Paste the code in the Jupyter cell.</li>\n",
    "        </ol>\n",
    "    </p>\n",
    "    <br>\n",
    "</div>"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "[&uarr; Return to Top](#bql-top)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "---\n",
    "<a id=\"bql-example\"></a>\n",
    "<h1>BQL Example<span style=\"color: orange;\"><br>One Universe, One Data Item</span></h1>"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Get one time series dataset (daily closing prices) for one universe (S&P 500 Index)."
   ]
  },
  {
   "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>DATE</th>\n",
       "      <th>CURRENCY</th>\n",
       "      <th>Closing Price</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>BWORLDPR Index</th>\n",
       "      <td>2019-10-01</td>\n",
       "      <td>USD</td>\n",
       "      <td>182.33</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>BWORLDPR Index</th>\n",
       "      <td>2019-10-02</td>\n",
       "      <td>USD</td>\n",
       "      <td>181.88</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>BWORLDPR Index</th>\n",
       "      <td>2019-10-03</td>\n",
       "      <td>USD</td>\n",
       "      <td>180.92</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>BWORLDPR Index</th>\n",
       "      <td>2019-10-04</td>\n",
       "      <td>USD</td>\n",
       "      <td>181.19</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>BWORLDPR Index</th>\n",
       "      <td>2019-10-07</td>\n",
       "      <td>USD</td>\n",
       "      <td>181.11</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                     DATE CURRENCY  Closing Price\n",
       "ID                                               \n",
       "BWORLDPR Index 2019-10-01      USD         182.33\n",
       "BWORLDPR Index 2019-10-02      USD         181.88\n",
       "BWORLDPR Index 2019-10-03      USD         180.92\n",
       "BWORLDPR Index 2019-10-04      USD         181.19\n",
       "BWORLDPR Index 2019-10-07      USD         181.11"
      ]
     },
     "execution_count": 3,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Run this cell with the Run button in the Jupyter toolbar\n",
    "# Step 1: Setup Environment\n",
    "# import bql\n",
    "# bq = bql.Service()\n",
    "\n",
    "# Step 2: Use BQL to Get Data\n",
    "universe = 'BWORLDPR Index'\n",
    "date_range = bq.func.range('2019-10-01', '2019-12-31')        # <-- use bq.func.range() to specify a range of dates\n",
    "closing_prices = bq.data.px_last(dates=date_range).dropna()   # <-- use the dropna() BQL function to drop non-trading days\n",
    "req = bql.Request(universe, {'Closing Price': closing_prices})\n",
    "res = bq.execute(req)\n",
    "df = res[0].df()\n",
    "\n",
    "# use the head() function to display the first 5 rows\n",
    "df.head()\n"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "[&uarr; Return to Top](#bql-top)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "---\n",
    "<a id=\"bql-example2\"></a>\n",
    "<h1>BQL Example<span style=\"color: orange;\"><br>One Universe, Multiple Data Items</span></h1>"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Get two time series datasets (daily price and volume) for one universe (S&P 500 Index). Use `pd.DataFrame.merge()` to join the results."
   ]
  },
  {
   "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>DATE</th>\n",
       "      <th>CURRENCY</th>\n",
       "      <th>Closing Price</th>\n",
       "      <th>Volume</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>BWORLDPR Index</th>\n",
       "      <td>2019-10-01</td>\n",
       "      <td>USD</td>\n",
       "      <td>182.33</td>\n",
       "      <td>4.166384e+09</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>BWORLDPR Index</th>\n",
       "      <td>2019-10-02</td>\n",
       "      <td>USD</td>\n",
       "      <td>181.88</td>\n",
       "      <td>6.545258e+09</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>BWORLDPR Index</th>\n",
       "      <td>2019-10-03</td>\n",
       "      <td>USD</td>\n",
       "      <td>180.92</td>\n",
       "      <td>7.365833e+09</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>BWORLDPR Index</th>\n",
       "      <td>2019-10-04</td>\n",
       "      <td>USD</td>\n",
       "      <td>181.19</td>\n",
       "      <td>7.333393e+09</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>BWORLDPR Index</th>\n",
       "      <td>2019-10-07</td>\n",
       "      <td>USD</td>\n",
       "      <td>181.11</td>\n",
       "      <td>3.567906e+09</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                     DATE CURRENCY  Closing Price        Volume\n",
       "ID                                                             \n",
       "BWORLDPR Index 2019-10-01      USD         182.33  4.166384e+09\n",
       "BWORLDPR Index 2019-10-02      USD         181.88  6.545258e+09\n",
       "BWORLDPR Index 2019-10-03      USD         180.92  7.365833e+09\n",
       "BWORLDPR Index 2019-10-04      USD         181.19  7.333393e+09\n",
       "BWORLDPR Index 2019-10-07      USD         181.11  3.567906e+09"
      ]
     },
     "execution_count": 4,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Run this cell with the Run button in the Jupyter toolbar\n",
    "# Step 1: Setup Environment\n",
    "# import bql\n",
    "# bq = bql.Service()\n",
    "\n",
    "# Step 2: Use BQL to Get Data\n",
    "universe = 'BWORLDPR Index'\n",
    "date_range = bq.func.range('2019-10-01', '2019-12-31')\n",
    "closing_prices = bq.data.px_last(dates=date_range).dropna()\n",
    "vol = bq.data.px_volume(dates=date_range).dropna()                    # <-- use the same date range for the volume series\n",
    "req = bql.Request(universe, {'Closing Price': closing_prices,\n",
    "                             'Volume': vol})\n",
    "res = bq.execute(req)\n",
    "\n",
    "# Step 3: Additional Data Munging\n",
    "# extract each dataframe\n",
    "price_df = res[0].df()\n",
    "vol_df = res[1].df()\n",
    "\n",
    "# merge the two dataframes together on the DATE column\n",
    "df = price_df.merge(vol_df, how='inner', on='DATE', left_index=True)   # <-- left_index=True will keep the index from price_df\n",
    "\n",
    "# use the head() function to display the first 5 rows\n",
    "df.head()\n"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "[&uarr; Return to Top](#bql-top)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "---\n",
    "<a id=\"bql-example3\"></a>\n",
    "<h1>BQL Example<span style=\"color: orange;\"><br>Multiple Universes, One Data Item</span></h1>"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Get one dataset (daily prices) for multiple universes (AAPL and IBM). Use `pd.DataFrame.pivot()` to reshape the DataFrame."
   ]
  },
  {
   "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>ID</th>\n",
       "      <th>AAPL US Equity</th>\n",
       "      <th>IBM US Equity</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>DATE</th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>2019-10-01</th>\n",
       "      <td>56.1475</td>\n",
       "      <td>143.66</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2019-10-02</th>\n",
       "      <td>54.7400</td>\n",
       "      <td>141.69</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2019-10-03</th>\n",
       "      <td>55.2050</td>\n",
       "      <td>142.02</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2019-10-04</th>\n",
       "      <td>56.7525</td>\n",
       "      <td>142.99</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2019-10-07</th>\n",
       "      <td>56.7650</td>\n",
       "      <td>141.28</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "ID          AAPL US Equity  IBM US Equity\n",
       "DATE                                     \n",
       "2019-10-01         56.1475         143.66\n",
       "2019-10-02         54.7400         141.69\n",
       "2019-10-03         55.2050         142.02\n",
       "2019-10-04         56.7525         142.99\n",
       "2019-10-07         56.7650         141.28"
      ]
     },
     "execution_count": 61,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Run this cell with the Run button in the Jupyter toolbar\n",
    "# Step 1: Setup Environment\n",
    "# import bql\n",
    "# bq = bql.Service()\n",
    "\n",
    "# Step 2: Use BQL to Get Data\n",
    "universe = ['AAPL US Equity', 'IBM US Equity']                          # <-- use a list of strings as the universe\n",
    "date_range = bq.func.range('2019-10-01', '2019-12-31')\n",
    "closing_prices = bq.data.px_last(dates=date_range, per='D').dropna()\n",
    "req = bql.Request(universe, {'Closing Price': closing_prices})\n",
    "res = bq.execute(req)\n",
    "\n",
    "# Step 3: Additional Data Munging\n",
    "df = res[0].df().reset_index().pivot(index='DATE', values='Closing Price', columns='ID')     # <-- parse and pivot the dataframe in one line\n",
    "\n",
    "# use the head() function to display the first 5 rows\n",
    "df.head()\n"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "[&uarr; Return to Top](#bql-top)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "---\n",
    "<a id=\"bql-example4\"></a>\n",
    "<h1>BQL Example<span style=\"color: orange;\"><br>Multiple Universes, Multiple Data Items</span></h1>"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Get two datasets (daily price and volume) for multiple universes (AAPL and IBM). Use `pd.DataFrame.merge()` to join the DataFrames and then `pd.DataFrame.pivot()` to reshape.\n",
    "\n",
    "Read more about the `pandas.MultiIndex` object used in this example in the [pandas docs](https://pandas.pydata.org/pandas-docs/stable/reference/api/pandas.MultiIndex.html)."
   ]
  },
  {
   "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 tr th {\n",
       "        text-align: left;\n",
       "    }\n",
       "\n",
       "    .dataframe thead tr:last-of-type th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr>\n",
       "      <th></th>\n",
       "      <th colspan=\"2\" halign=\"left\">Closing Price</th>\n",
       "      <th colspan=\"2\" halign=\"left\">Volume</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th></th>\n",
       "      <th>AAPL US Equity</th>\n",
       "      <th>IBM US Equity</th>\n",
       "      <th>AAPL US Equity</th>\n",
       "      <th>IBM US Equity</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>2019-10-01</th>\n",
       "      <td>56.1475</td>\n",
       "      <td>143.66</td>\n",
       "      <td>144748652.0</td>\n",
       "      <td>2973714.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2019-10-02</th>\n",
       "      <td>54.7400</td>\n",
       "      <td>141.69</td>\n",
       "      <td>143069028.0</td>\n",
       "      <td>3347276.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2019-10-03</th>\n",
       "      <td>55.2050</td>\n",
       "      <td>142.02</td>\n",
       "      <td>121410744.0</td>\n",
       "      <td>3161718.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2019-10-04</th>\n",
       "      <td>56.7525</td>\n",
       "      <td>142.99</td>\n",
       "      <td>139022212.0</td>\n",
       "      <td>2449483.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2019-10-07</th>\n",
       "      <td>56.7650</td>\n",
       "      <td>141.28</td>\n",
       "      <td>123557076.0</td>\n",
       "      <td>2488799.0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "            Closing Price                       Volume              \n",
       "           AAPL US Equity IBM US Equity AAPL US Equity IBM US Equity\n",
       "DATE                                                                \n",
       "2019-10-01        56.1475        143.66    144748652.0     2973714.0\n",
       "2019-10-02        54.7400        141.69    143069028.0     3347276.0\n",
       "2019-10-03        55.2050        142.02    121410744.0     3161718.0\n",
       "2019-10-04        56.7525        142.99    139022212.0     2449483.0\n",
       "2019-10-07        56.7650        141.28    123557076.0     2488799.0"
      ]
     },
     "execution_count": 62,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Run this cell with the Run button in the Jupyter toolbar\n",
    "# Step 1: Setup Environment\n",
    "# import bql\n",
    "# import pandas as pd\n",
    "# bq = bql.Service()\n",
    "\n",
    "# Step 2: Use BQL to Get Data\n",
    "universe = ['AAPL US Equity', 'IBM US Equity']\n",
    "date_range = bq.func.range('2019-10-01', '2019-12-31')\n",
    "closing_prices = bq.data.px_last(dates=date_range, per='D').dropna()\n",
    "vol = bq.data.px_volume(dates=date_range).dropna()\n",
    "req = bql.Request(universe, {'Closing Price': closing_prices,\n",
    "                             'Volume': vol})\n",
    "res = bq.execute(req)\n",
    "\n",
    "# Step 3: Additional Data Munging\n",
    "# pivot each result\n",
    "price_df = res[0].df().reset_index().pivot(index='DATE', columns='ID', values='Closing Price')\n",
    "vol_df = res[1].df().reset_index().pivot(index='DATE', columns='ID', values='Volume')\n",
    "\n",
    "# reassign the columns with the additional label of price or volume\n",
    "price_df.columns = pd.MultiIndex.from_product([['Closing Price'], list(price_df.columns)])\n",
    "vol_df.columns = pd.MultiIndex.from_product([['Volume'], list(vol_df.columns)])\n",
    "\n",
    "# merge the two dataframes by common date\n",
    "df = price_df.merge(vol_df, how='inner', on='DATE')\n",
    "\n",
    "# use the head() function to display the first 5 rows\n",
    "df.head()\n",
    "\n"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "[&uarr; Return to Top](#bql-top)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "----\n",
    "<p style=\"text-align:center;\">\n",
    "    Click on the links below to continue learning.<br>\n",
    "    <a href=\"2 Python Basics.ipynb\">&larr; Back to Python Basics</a>&emsp;&emsp;\n",
    "    <a href=\"#bql-top\">&uarr; Return to Top</a>&emsp;&emsp;\n",
    "    <a href=\"4 Boot Camp Project.ipynb\">Continue to Boot Camp: Final Project &rarr;</a>\n",
    "    <br>\n",
    "    <br>\n",
    "    <a href=\"../Welcome.ipynb#welcome-top\" style=\"font-size: 12px;\">Return to the Welcome Page</a>\n",
    "</p>"
   ]
  }
 ],
 "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
}