session3.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "------------------------------------------\n",
    "<a id='top'></a>\n",
    "## Session contents\n",
    "### [7. Map, ApplyMap, and Apply](#applying)\n",
    "### [8. Groupby](#aggregating)\n",
    "### [9. Merge/Join](#merging)\n",
    "### [Exercise set 3](#)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "import pandas as pd"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "pd.merge()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "----------------------\n",
    "<a id='applying'></a>\n",
    "## 7. Map, ApplyMap, and Apply\n",
    "\n",
    "We've seen that pandas objects come with simple aggregation methods, and that numpy functions allow numerical operations on pandas objects. You might wonder whether we can use functions from other packages, or apply our own user-defined functions, in a similar way.\n",
    "\n",
    "Pandas provides this functionality through the following methods:\n",
    "\n",
    "    srs.map(f) - apply a function f element-wise to a Series (or DataFrame column)\n",
    "    df.applymap(f) - apply a function f element-wise to the entire DataFrame (i.e. the DataFrame equivalent of .map)\n",
    "    df.apply(f, axis) - apply a function f along columns (axis=0) or rows (axis=1) of a DataFrame\n",
    "    \n",
    "<img src=\"https://i.stack.imgur.com/DL0iQ.jpg\" title=\"Apply\" />\n",
    "\n",
    "Many of the functions we covered in the previous sessions are shorthand for these more general methods:\n",
    "\n",
    "    np.log(df['A']) --> df['A'].map(np.log)\n",
    "    df * 2 --> df.applymap(lambda x: x*2)\n",
    "    df.sum(axis=1) --> df.apply(sum, axis=1)\n",
    "    \n",
    "Generally, you should use map, apply, and applymap only if there is no Series or DataFrame method available. In this way, your code will be more readable and use any optimisations that pandas may have for these methods."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "<div class=\"alert alert-block alert-info\">\n",
    "\n",
    "<span style=\"color:green\">Additional resources</span>\n",
    "\n",
    "http://pandas.pydata.org/pandas-docs/stable/generated/pandas.DataFrame.apply.html\n",
    "\n",
    "http://pandas.pydata.org/pandas-docs/stable/generated/pandas.DataFrame.applymap.html#pandas.DataFrame.applymap\n"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Let's first load up a test DataFrame."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "import numpy as np\n",
    "import pandas as pd"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "df = pd.DataFrame(columns=list('ABCD'), data=np.random.randn(4, 4)*10)\n",
    "df"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### ApplyMap"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "This is probably the simplest of the apply functions to understand. Let's convert every number in df to an int (dropping the decimal)."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "df = df.applymap(int)\n",
    "df"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "We can create our own functions to apply to the DataFrame as well. Simple one-line functions can be declared using lambda functions. \n",
    "\n",
    "See http://www.secnetix.de/olli/Python/lambda_functions.hawk\n",
    "\n",
    "In the example below, we have formatted each element of the DataFrame as a percent."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "df.applymap(lambda x: str(x)+'%')"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "More complex functions can be applied by defining a function in the usual way."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "def half_or_three_plus_one(x):\n",
    "    \"\"\"Halve if even, triple and add one if odd\"\"\"\n",
    "    if x==1:\n",
    "        y = 1\n",
    "    elif np.mod(x, 2)==0:\n",
    "        y = x / 2\n",
    "    else:\n",
    "        y = 3*x + 1\n",
    "    return int(y)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "df.abs().applymap(int).applymap(half_or_three_plus_one)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Map"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "This method provides the same functionality as applymap, but for Series objects. Try some of the same functions above but on a single column or row of the DataFrame only."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Apply"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "The apply method is a little more difficult to understand. It applies a particular function, often an aggregation, to each row or column independently. We've encountered a few examples of an apply-like method already, e.g., the df.sum() method."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "If we call .apply and use the sum function with axis=0, we will be summing up the rows (or, summing \"along the columns\") of our DataFrame."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "df.apply(sum, axis=0)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "To sum along rows, simply pass axis=1 instead."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "df.apply(sum, axis=1)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "When using .apply, the argument to your function is the row or column itself (which is of course a Series)."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "df"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "df.apply(lambda x: x['C'], axis=1) # x is each row of the DataFrame"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "df.apply(lambda x: x.iloc[-1], axis=0)  # x is each column of the DataFrame"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "def second_largest(srs):\n",
    "    srs = srs.copy()  # to make a local copy of the input\n",
    "    srs = srs.abs()\n",
    "    srs = srs.sort_values(ascending=True)\n",
    "    return srs.iloc[-2]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "df.apply(second_largest, axis=0)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "If function that we using in the apply method returns a dict or Series, the resulting output is a DataFrame."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "df.apply(lambda x: pd.Series({'Median': x.median(), 'Mean': x.mean()}), axis=0)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "df.apply(lambda x: pd.Series({'Median': x.median(), 'Mean': x.mean()}), axis=1)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "The **np.npv** function has 2 arguments, rate (a *float* which is the discount rate) and values (a *list* of future cashflows).\n",
    "\n",
    "For example,\n",
    "\n",
    "    np.npv(rate=0.05,values=[1,1,1,1,101])\n",
    "    \n",
    "will find the price of a 5 year bond with 1% annual coupons at a yield of 5%.\n",
    "\n",
    "Run the cell below to initialise the DataFrame we'll be working with next."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "df_bond = pd.DataFrame({'bond_name': ['Bank_2020', 'Retailer_2018', 'JGB_3Y'], 'yield': [0.0465, 0.0573, 0.00347],\n",
    "                        '2017_cashflow': [1.5, 2.5, 0.125], '2018_cashflow':[1.5, 102.5, 0.125],\n",
    "                        '2019_cashflow': [1.5, 0, 100.125], '2020_cashflow':[101.5, 0, 0]})\n",
    "df_bond.set_index('bond_name', drop=True, inplace=True)\n",
    "df_bond"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "collapsed": true
   },
   "source": [
    "Using the **.apply, lambda functions and the np.npv function**, calculate the price of each of the three bonds above.\n",
    "\n",
    "Hint: use a lambda function where the variable is a **row** from your DataFrame.\n",
    "\n",
    "This exercise is fairly difficult so take a look at the solution below if you get stuck."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "#solutions\n",
    "%load solutions/apply_sol1.py"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "collapsed": true
   },
   "source": [
    "[Back to top](#top)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "----------\n",
    "<a id='aggregating'></a>\n",
    "# 8. GroupBy"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "We have now looked at applying functions and performing aggregations over an entire DataFrame. Often though, we are interested in aggregations among particular subsets of the data. For example, finding the turnover for each security, or median latency of different proccesses in our trading systems. Pandas allows this type of aggregation through the __df.groupby()__ method, which implements a \"split-apply-combine\" paradigm. The process is explained in the diagram below, which groups by the key and applies the sum method.\n",
    "\n",
    "<img src=\"http://nbviewer.jupyter.org/github/jakevdp/PythonDataScienceHandbook/blob/master/notebooks/figures/03.08-split-apply-combine.png\" title=\"Group-by explained\" />\n",
    "\n",
    "<div class=\"alert alert-block alert-info\">\n",
    "\n",
    "<span style=\"color:green\">Additional resources</span>\n",
    "\n",
    "http://nbviewer.jupyter.org/github/jakevdp/PythonDataScienceHandbook/blob/master/notebooks/03.08-Aggregation-and-Grouping.ipynb"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "We'll be working with volatility data, df_vols, for this section. Run the code below to import and clean the data."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "import pandas as pd\n",
    "import numpy as np"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "df_vols = pd.read_csv('data/vols.csv')\n",
    "df_vols.TIMESTAMP = pd.to_datetime(df_vols.TIMESTAMP)\n",
    "df_vols = df_vols.set_index('TIMESTAMP', drop=True)\n",
    "df_vols.head()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Let's do some summarisation first with the .describe() method."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "df_vols.describe()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Suppose we want to find the mean of each column, but **per relative expiry**. We can first do a groupby on the DataFrame."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "g = df_vols.groupby('RELATIVE_EXPIRY')\n",
    "g"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "The output is a DataFrameGroupBy object. Let's look at the attributes of this object with the Tab button or by running the __dir__ function on g."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "You'll notice that it shares many of the same attributes and methods of the original DataFrame object. For instance, try running a few of the aggregation methods to see how they work."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "g.sum()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "If we want to perform an aggregation over a subset of columns, we can select those columns with the dict-like syntax in the usual way. Try a few of these below."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "g[['FUTURE', 'VOLATILITY']].mean()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "What if we wanted to group over __ranges__ of values instead? Pandas has very useful functions __pd.cut__ and __pd.qcut__ that can bin the data into value ranges and quantile ranges respectively. Let's create 10 bins around the minimum and maximum forward price."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "print df_vols['FUTURE'].min(), df_vols['FUTURE'].max()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "bins = np.linspace(210, 240, 11)\n",
    "bins"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "df_vols['FUTURE_RANGE'] = pd.cut(df_vols['FUTURE'], bins)\n",
    "df_vols.head()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "We can group by these to find the average ATM vol in each forward price bucket - is the result what you expected?"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "df_vols.groupby('FUTURE_RANGE')['VOLATILITY'].mean()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Now, the above calculation is actually not very informative, because we have lumped all relative expiries. We really should group over __both__ the future prices __and__ the expiries at the same time. All we need to do is to provide a list of keys/columns to groupby."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "avg_vol = df_vols.groupby(['RELATIVE_EXPIRY', 'FUTURE_RANGE'])['VOLATILITY'].mean()\n",
    "avg_vol"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "The output is a Series with a MultiIndex, where relative expiry and forward price are different levels of the index. This turns out to be a much easier way of working with data than a \"3D spreadsheet\" kind of structure."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "collapsed": true
   },
   "source": [
    "Whenever we get a stacked object like above, we can call the .unstack() method to turn it back into a DataFrame."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "df_avg_vol = avg_vol.unstack(level='RELATIVE_EXPIRY')  # or level=0\n",
    "df_avg_vol"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Groupby objects also have a __.apply__ method, except the apply acts on each key's DataFrame. For instance, calculating the daily change of a few columns for each relative expiry."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "df_vols.groupby('RELATIVE_EXPIRY')[['FUTURE', 'VOLATILITY']].apply(lambda df: df.iloc[-1] - df.iloc[0])"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "collapsed": true
   },
   "source": [
    "[Back to top](#top)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "----------------------------\n",
    "<a id='merging'></a>\n",
    "# 9. Merge/Join\n",
    "\n",
    "Sometimes we will want to complement one data set with information from another data set. For example, joining a DataFrame of trades (price, size, time) with a DataFrame of instrument properties (underlying, expiry date, strike price). Pandas' main method for joining two DataFrames is __pd.merge__:\n",
    "\n",
    "    pd.merge(df_left, df_right, on=..., how=...)\n",
    "    \n",
    "The 'on' argument determines which column(s) to join on. If left empty, the columns that df_left and df_right share will be used as join keys. If the columns to join on have different names between df_left and df_right, we can use the 'left_on' and 'right_on' arguments instead. To join on the index instead of a column, we use left_index=True and/or right_index=True. Alternatively, df_left.join(df_right) performs a join on the indexes.\n",
    "\n",
    "The ‘how’ argument determines the style of join to use. Options for this argument are ‘inner’, 'outer', 'left', and 'right'. An inner join contains the intersection of the two sets of inputs. An outer join returns a join over the union of the input columns, and fills in all missing values with NaNs. The left and right joins return joins over the left and right indices respectively. Note that a right join is identical to a left join with the left/right labels swapped - so we usually just use left joins.\n",
    "\n",
    "<img src=\"http://www.w3schools.com/sql/img_innerjoin.gif\" title=\"Inner join\" />\n",
    "<img src=\"http://www.w3schools.com/sql/img_leftjoin.gif\" title=\"Left join\" />\n",
    "<img src=\"http://www.w3schools.com/sql/img_rightjoin.gif\" title=\"Right join\" />\n",
    "<img src=\"http://www.w3schools.com/sql/img_fulljoin.gif\" title=\"Outer join\" />"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "<div class=\"alert alert-block alert-info\">\n",
    "\n",
    "<span style=\"color:green\">YouTube video</span>\n",
    "\n",
    "Watch the following video until the 1 hour 13 minute mark to get a better idea of these methods.\n",
    "\n",
    "https://www.youtube.com/watch?v=dye7rDktJ2E&start=3180"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "**Key methods covered:**\n",
    "\n",
    "    pd.concat - combines two objects into a single DataFrame\n",
    "    pd.merge - merges existing DataFrames"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "<div class=\"alert alert-block alert-info\">\n",
    "\n",
    "<span style=\"color:green\">Additional resources</span>\n",
    "\n",
    "http://nbviewer.jupyter.org/github/jakevdp/PythonDataScienceHandbook/blob/master/notebooks/03.07-Merge-and-Join.ipynb"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Let's load up the following data of prices and turnovers (assume a multiplier of 1)."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "import pandas as pd\n",
    "import numpy as np"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "df_turnovers = pd.DataFrame(columns=['Underlying', 'Month', 'Turnover'],\n",
    "    data={'Underlying': ['HSI']*3 + ['NK225']*3 + ['HHI']*3,\n",
    "          'Month': ['Jan', 'Feb', 'Mar']*3,\n",
    "          'Turnover': [1000, 1100,   900,\n",
    "                        300,  350,   400,\n",
    "                       6000, 7000, np.nan]})\n",
    "df_turnovers"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "df_prices = pd.DataFrame(columns=['Underlying', 'Month', 'Price'],\n",
    "    data={'Underlying': ['HSI']*3 + ['HHI']*3 + ['NK225']*3,\n",
    "          'Month': ['Jan', 'Feb', 'Mar']*3,\n",
    "          'Price': [28000, 29000, 30000,\n",
    "                    11000, 12000, 115000,\n",
    "                    22000, 21000, 20000]})\n",
    "df_prices"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Calculate the notional turnover (price times size) in local currency by joining on the appropriate key(s) with __pd.merge__."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "#solutions\n",
    "%load solutions/merge_sol1.py"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Now load the following currency data, and join them together to get the forex rates for each underlying."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "df_currency = pd.DataFrame({'Underlying': ['NK225', 'HSI', 'HHI'], 'Currency': ['JPY', 'HKD', 'HKD']})\n",
    "df_currency"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "df_forex = pd.DataFrame({'Currency': ['HKD', 'JPY', 'KRW'], 'Rate': [6, 80, 850]})\n",
    "df_forex"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "#solutions\n",
    "%load solutions/merge_sol2.py"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Finally, join currency data onto the notional turnover data and convert the notional turnover to AUD (in millions)."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "#solutions\n",
    "%load solutions/merge_sol3.py"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "[Back to top](#top)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Exercise set 3 (unavailable externally)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "For this exercise set we'll use our HSI options data. For now, just run the following cells to get our trade data from OneTick - we'll spend time learning how to use OneTick later. You'll need to install the following package first:\n",
    ">pip install optiver.etl"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "import pandas as pd\n",
    "from etl.onetick import otq, query\n",
    "import datetime as dt"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "start = dt.datetime(2018, 3, 13, 0, 0)\n",
    "end = dt.datetime(2018, 3, 13, 23, 59)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Trade data\n",
    "q = query.tick_query('Trade_Tick_Analysis', 'ATLAS_IN',\n",
    "                     start, end, 'Australia/Sydney',\n",
    "                     symbol_regex='ATLAS_IN::opa_in_hsi_tko_001.XHKF',\n",
    "                     columns=['EEID_TIMESTAMP', 'FEEDCODE', 'TRADE_PRICE', 'TRADE_VOLUME', 'THEO_PRICE', 'DELTA'])\n",
    "\n",
    "df_trd = otq.query(q)\n",
    "df_trd = df_trd.drop(columns=['Time', 'SYMBOL_NAME'])\n",
    "df_trd['EEID_TIMESTAMP'] = pd.to_datetime(df_trd['EEID_TIMESTAMP'])\n",
    "df_trd = df_trd.set_index('EEID_TIMESTAMP')\n",
    "df_trd = df_trd[df_trd['FEEDCODE'].str.startswith('HSI')]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Instrument data\n",
    "q = query.tick_query('Instrument', 'XHKF',\n",
    "                     start, end, 'Australia/Sydney',\n",
    "                     symbol_regex='XHKF::HSI',\n",
    "                     columns=['FEEDCODE', 'KIND', 'STRIKE_PRICE', 'EXPIRY_DATE'])\n",
    "\n",
    "df_ins = otq.query(q)\n",
    "df_ins = df_ins.drop(columns=['Time', 'SYMBOL_NAME'])"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "1. Add a new column EDGE to df_trd that contains the total edge of that trade in AUD.\n",
    "2. Merge the instrument data into the trade data.\n",
    "3. Calculate the total edge and trade volumes per delta bucket (delta 0-10, 10-20, 20-30, etc.), expiry date, and instrument kind.\n",
    "4. Unstack that dataframe so that it's easier to view.\n",
    "5. Sort the dataframe in descending order of edge.\n",
    "6. What was the total edge for the day?"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  }
 ],
 "metadata": {
  "anaconda-cloud": {},
  "kernelspec": {
   "display_name": "Python 2",
   "language": "python",
   "name": "python2"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 2
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython2",
   "version": "2.7.14"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 1
}