{"nbformat_minor": 0, "cells": [{"execution_count": 37, "cell_type": "code", "source": "pricing = get_pricing(['AAPL', 'MSFT'], \n start_date='2014-01-01', \n end_date='2014-01-07', \n frequency='minute',\n fields='price')", "outputs": [], "metadata": {"collapsed": false, "trusted": true}}, {"source": "`pricing` is a `DataFrame` with the same structure as the return value of `history` on quantopian.", "cell_type": "markdown", "metadata": {}}, {"execution_count": 38, "cell_type": "code", "source": "pricing.head(10)", "outputs": [{"execution_count": 38, "output_type": "execute_result", "data": {"text/plain": " Security(24 [AAPL]) Security(5061 [MSFT])\n2014-01-02 14:31:00+00:00 79.446 37.340\n2014-01-02 14:32:00+00:00 79.424 37.375\n2014-01-02 14:33:00+00:00 79.490 37.260\n2014-01-02 14:34:00+00:00 79.502 37.260\n2014-01-02 14:35:00+00:00 79.252 37.280\n2014-01-02 14:36:00+00:00 79.184 37.283\n2014-01-02 14:37:00+00:00 79.260 37.270\n2014-01-02 14:38:00+00:00 79.300 37.300\n2014-01-02 14:39:00+00:00 79.259 37.300\n2014-01-02 14:40:00+00:00 79.222 37.280", "text/html": "
\n
\n \n \n | \n Security(24 [AAPL]) | \n Security(5061 [MSFT]) | \n
\n \n \n \n 2014-01-02 14:31:00+00:00 | \n 79.446 | \n 37.340 | \n
\n \n 2014-01-02 14:32:00+00:00 | \n 79.424 | \n 37.375 | \n
\n \n 2014-01-02 14:33:00+00:00 | \n 79.490 | \n 37.260 | \n
\n \n 2014-01-02 14:34:00+00:00 | \n 79.502 | \n 37.260 | \n
\n \n 2014-01-02 14:35:00+00:00 | \n 79.252 | \n 37.280 | \n
\n \n 2014-01-02 14:36:00+00:00 | \n 79.184 | \n 37.283 | \n
\n \n 2014-01-02 14:37:00+00:00 | \n 79.260 | \n 37.270 | \n
\n \n 2014-01-02 14:38:00+00:00 | \n 79.300 | \n 37.300 | \n
\n \n 2014-01-02 14:39:00+00:00 | \n 79.259 | \n 37.300 | \n
\n \n 2014-01-02 14:40:00+00:00 | \n 79.222 | \n 37.280 | \n
\n \n
\n
"}, "metadata": {}}], "metadata": {"collapsed": false, "trusted": true}}, {"source": "Pandas' built-in `groupby` and `apply` operations are extremely powerful. For more information on these features, see http://pandas.pydata.org/pandas-docs/stable/groupby.html.", "cell_type": "markdown", "metadata": {}}, {"execution_count": 39, "cell_type": "code", "source": "from pandas.tseries.tools import normalize_date\n\ndef my_grouper(ts):\n \"Function to apply to the index of the DataFrame to break it into groups.\"\n # Returns midnight of the supplied date.\n return normalize_date(ts)\n\n\ndef first_thirty_minutes(frame):\n \"Function to apply to the resulting groups.\"\n return frame.iloc[:30]", "outputs": [], "metadata": {"collapsed": true, "trusted": true}}, {"source": "The result of a `groupby` computation is a [Hierarchichally-Indexed DataFrame](http://pandas.pydata.org/pandas-docs/stable/advanced.html) where the outermost layer of the index is the groupby key, and the secondary layers are the values from the frame's original index.", "cell_type": "markdown", "metadata": {}}, {"execution_count": 40, "cell_type": "code", "source": "data = pricing.groupby(my_grouper).apply(first_thirty_minutes)\ndata.head(40)", "outputs": [{"execution_count": 40, "output_type": "execute_result", "data": {"text/plain": " Security(24 [AAPL]) \\\n2014-01-02 00:00:00+00:00 2014-01-02 14:31:00+00:00 79.446 \n 2014-01-02 14:32:00+00:00 79.424 \n 2014-01-02 14:33:00+00:00 79.490 \n 2014-01-02 14:34:00+00:00 79.502 \n 2014-01-02 14:35:00+00:00 79.252 \n 2014-01-02 14:36:00+00:00 79.184 \n 2014-01-02 14:37:00+00:00 79.260 \n 2014-01-02 14:38:00+00:00 79.300 \n 2014-01-02 14:39:00+00:00 79.259 \n 2014-01-02 14:40:00+00:00 79.222 \n 2014-01-02 14:41:00+00:00 79.187 \n 2014-01-02 14:42:00+00:00 79.069 \n 2014-01-02 14:43:00+00:00 79.160 \n 2014-01-02 14:44:00+00:00 79.186 \n 2014-01-02 14:45:00+00:00 79.213 \n 2014-01-02 14:46:00+00:00 79.152 \n 2014-01-02 14:47:00+00:00 79.183 \n 2014-01-02 14:48:00+00:00 79.202 \n 2014-01-02 14:49:00+00:00 79.159 \n 2014-01-02 14:50:00+00:00 79.190 \n 2014-01-02 14:51:00+00:00 79.150 \n 2014-01-02 14:52:00+00:00 79.064 \n 2014-01-02 14:53:00+00:00 79.134 \n 2014-01-02 14:54:00+00:00 79.074 \n 2014-01-02 14:55:00+00:00 79.139 \n 2014-01-02 14:56:00+00:00 79.164 \n 2014-01-02 14:57:00+00:00 79.163 \n 2014-01-02 14:58:00+00:00 79.217 \n 2014-01-02 14:59:00+00:00 79.165 \n 2014-01-02 15:00:00+00:00 79.144 \n2014-01-03 00:00:00+00:00 2014-01-03 14:31:00+00:00 78.937 \n 2014-01-03 14:32:00+00:00 78.702 \n 2014-01-03 14:33:00+00:00 78.756 \n 2014-01-03 14:34:00+00:00 78.552 \n 2014-01-03 14:35:00+00:00 78.573 \n 2014-01-03 14:36:00+00:00 78.616 \n 2014-01-03 14:37:00+00:00 78.693 \n 2014-01-03 14:38:00+00:00 78.630 \n 2014-01-03 14:39:00+00:00 78.589 \n 2014-01-03 14:40:00+00:00 78.543 \n\n Security(5061 [MSFT]) \n2014-01-02 00:00:00+00:00 2014-01-02 14:31:00+00:00 37.3400 \n 2014-01-02 14:32:00+00:00 37.3750 \n 2014-01-02 14:33:00+00:00 37.2600 \n 2014-01-02 14:34:00+00:00 37.2600 \n 2014-01-02 14:35:00+00:00 37.2800 \n 2014-01-02 14:36:00+00:00 37.2830 \n 2014-01-02 14:37:00+00:00 37.2700 \n 2014-01-02 14:38:00+00:00 37.3000 \n 2014-01-02 14:39:00+00:00 37.3000 \n 2014-01-02 14:40:00+00:00 37.2800 \n 2014-01-02 14:41:00+00:00 37.2500 \n 2014-01-02 14:42:00+00:00 37.2500 \n 2014-01-02 14:43:00+00:00 37.2300 \n 2014-01-02 14:44:00+00:00 37.2325 \n 2014-01-02 14:45:00+00:00 37.1950 \n 2014-01-02 14:46:00+00:00 37.1601 \n 2014-01-02 14:47:00+00:00 37.1800 \n 2014-01-02 14:48:00+00:00 37.1900 \n 2014-01-02 14:49:00+00:00 37.1550 \n 2014-01-02 14:50:00+00:00 37.1700 \n 2014-01-02 14:51:00+00:00 37.1600 \n 2014-01-02 14:52:00+00:00 37.2150 \n 2014-01-02 14:53:00+00:00 37.2500 \n 2014-01-02 14:54:00+00:00 37.2400 \n 2014-01-02 14:55:00+00:00 37.2350 \n 2014-01-02 14:56:00+00:00 37.1800 \n 2014-01-02 14:57:00+00:00 37.1600 \n 2014-01-02 14:58:00+00:00 37.1800 \n 2014-01-02 14:59:00+00:00 37.2001 \n 2014-01-02 15:00:00+00:00 37.2501 \n2014-01-03 00:00:00+00:00 2014-01-03 14:31:00+00:00 37.1800 \n 2014-01-03 14:32:00+00:00 37.1700 \n 2014-01-03 14:33:00+00:00 37.1301 \n 2014-01-03 14:34:00+00:00 37.1450 \n 2014-01-03 14:35:00+00:00 37.1500 \n 2014-01-03 14:36:00+00:00 37.1400 \n 2014-01-03 14:37:00+00:00 37.1100 \n 2014-01-03 14:38:00+00:00 37.1250 \n 2014-01-03 14:39:00+00:00 37.0900 \n 2014-01-03 14:40:00+00:00 37.1040 ", "text/html": "\n
\n \n \n | \n | \n Security(24 [AAPL]) | \n Security(5061 [MSFT]) | \n
\n \n \n \n 2014-01-02 00:00:00+00:00 | \n 2014-01-02 14:31:00+00:00 | \n 79.446 | \n 37.3400 | \n
\n \n 2014-01-02 14:32:00+00:00 | \n 79.424 | \n 37.3750 | \n
\n \n 2014-01-02 14:33:00+00:00 | \n 79.490 | \n 37.2600 | \n
\n \n 2014-01-02 14:34:00+00:00 | \n 79.502 | \n 37.2600 | \n
\n \n 2014-01-02 14:35:00+00:00 | \n 79.252 | \n 37.2800 | \n
\n \n 2014-01-02 14:36:00+00:00 | \n 79.184 | \n 37.2830 | \n
\n \n 2014-01-02 14:37:00+00:00 | \n 79.260 | \n 37.2700 | \n
\n \n 2014-01-02 14:38:00+00:00 | \n 79.300 | \n 37.3000 | \n
\n \n 2014-01-02 14:39:00+00:00 | \n 79.259 | \n 37.3000 | \n
\n \n 2014-01-02 14:40:00+00:00 | \n 79.222 | \n 37.2800 | \n
\n \n 2014-01-02 14:41:00+00:00 | \n 79.187 | \n 37.2500 | \n
\n \n 2014-01-02 14:42:00+00:00 | \n 79.069 | \n 37.2500 | \n
\n \n 2014-01-02 14:43:00+00:00 | \n 79.160 | \n 37.2300 | \n
\n \n 2014-01-02 14:44:00+00:00 | \n 79.186 | \n 37.2325 | \n
\n \n 2014-01-02 14:45:00+00:00 | \n 79.213 | \n 37.1950 | \n
\n \n 2014-01-02 14:46:00+00:00 | \n 79.152 | \n 37.1601 | \n
\n \n 2014-01-02 14:47:00+00:00 | \n 79.183 | \n 37.1800 | \n
\n \n 2014-01-02 14:48:00+00:00 | \n 79.202 | \n 37.1900 | \n
\n \n 2014-01-02 14:49:00+00:00 | \n 79.159 | \n 37.1550 | \n
\n \n 2014-01-02 14:50:00+00:00 | \n 79.190 | \n 37.1700 | \n
\n \n 2014-01-02 14:51:00+00:00 | \n 79.150 | \n 37.1600 | \n
\n \n 2014-01-02 14:52:00+00:00 | \n 79.064 | \n 37.2150 | \n
\n \n 2014-01-02 14:53:00+00:00 | \n 79.134 | \n 37.2500 | \n
\n \n 2014-01-02 14:54:00+00:00 | \n 79.074 | \n 37.2400 | \n
\n \n 2014-01-02 14:55:00+00:00 | \n 79.139 | \n 37.2350 | \n
\n \n 2014-01-02 14:56:00+00:00 | \n 79.164 | \n 37.1800 | \n
\n \n 2014-01-02 14:57:00+00:00 | \n 79.163 | \n 37.1600 | \n
\n \n 2014-01-02 14:58:00+00:00 | \n 79.217 | \n 37.1800 | \n
\n \n 2014-01-02 14:59:00+00:00 | \n 79.165 | \n 37.2001 | \n
\n \n 2014-01-02 15:00:00+00:00 | \n 79.144 | \n 37.2501 | \n
\n \n 2014-01-03 00:00:00+00:00 | \n 2014-01-03 14:31:00+00:00 | \n 78.937 | \n 37.1800 | \n
\n \n 2014-01-03 14:32:00+00:00 | \n 78.702 | \n 37.1700 | \n
\n \n 2014-01-03 14:33:00+00:00 | \n 78.756 | \n 37.1301 | \n
\n \n 2014-01-03 14:34:00+00:00 | \n 78.552 | \n 37.1450 | \n
\n \n 2014-01-03 14:35:00+00:00 | \n 78.573 | \n 37.1500 | \n
\n \n 2014-01-03 14:36:00+00:00 | \n 78.616 | \n 37.1400 | \n
\n \n 2014-01-03 14:37:00+00:00 | \n 78.693 | \n 37.1100 | \n
\n \n 2014-01-03 14:38:00+00:00 | \n 78.630 | \n 37.1250 | \n
\n \n 2014-01-03 14:39:00+00:00 | \n 78.589 | \n 37.0900 | \n
\n \n 2014-01-03 14:40:00+00:00 | \n 78.543 | \n 37.1040 | \n
\n \n
\n
"}, "metadata": {}}], "metadata": {"collapsed": false, "trusted": true}}, {"source": "Because our `DataFrame` is Hierarchically-Indexed, we can query it by our groupby keys.", "cell_type": "markdown", "metadata": {}}, {"execution_count": 41, "cell_type": "code", "source": "from pandas import Timestamp\n# This gives us the first thirty minutes of January 3rd.\ndata.loc[Timestamp('2014-01-03', tz='UTC')]", "outputs": [{"execution_count": 41, "output_type": "execute_result", "data": {"text/plain": " Security(24 [AAPL]) Security(5061 [MSFT])\n2014-01-03 14:31:00+00:00 78.937 37.1800\n2014-01-03 14:32:00+00:00 78.702 37.1700\n2014-01-03 14:33:00+00:00 78.756 37.1301\n2014-01-03 14:34:00+00:00 78.552 37.1450\n2014-01-03 14:35:00+00:00 78.573 37.1500\n2014-01-03 14:36:00+00:00 78.616 37.1400\n2014-01-03 14:37:00+00:00 78.693 37.1100\n2014-01-03 14:38:00+00:00 78.630 37.1250\n2014-01-03 14:39:00+00:00 78.589 37.0900\n2014-01-03 14:40:00+00:00 78.543 37.1040\n2014-01-03 14:41:00+00:00 78.424 37.1000\n2014-01-03 14:42:00+00:00 78.462 37.1400\n2014-01-03 14:43:00+00:00 78.539 37.1500\n2014-01-03 14:44:00+00:00 78.573 37.1600\n2014-01-03 14:45:00+00:00 78.509 37.1120\n2014-01-03 14:46:00+00:00 78.510 37.0600\n2014-01-03 14:47:00+00:00 78.477 37.0600\n2014-01-03 14:48:00+00:00 78.519 37.0600\n2014-01-03 14:49:00+00:00 78.501 37.0200\n2014-01-03 14:50:00+00:00 78.419 37.0100\n2014-01-03 14:51:00+00:00 78.392 37.0400\n2014-01-03 14:52:00+00:00 78.317 37.0450\n2014-01-03 14:53:00+00:00 78.287 37.0200\n2014-01-03 14:54:00+00:00 78.216 37.0300\n2014-01-03 14:55:00+00:00 78.230 37.0200\n2014-01-03 14:56:00+00:00 78.244 37.0150\n2014-01-03 14:57:00+00:00 78.299 37.0200\n2014-01-03 14:58:00+00:00 78.252 37.0301\n2014-01-03 14:59:00+00:00 78.344 37.0500\n2014-01-03 15:00:00+00:00 78.332 37.0372", "text/html": "\n
\n \n \n | \n Security(24 [AAPL]) | \n Security(5061 [MSFT]) | \n
\n \n \n \n 2014-01-03 14:31:00+00:00 | \n 78.937 | \n 37.1800 | \n
\n \n 2014-01-03 14:32:00+00:00 | \n 78.702 | \n 37.1700 | \n
\n \n 2014-01-03 14:33:00+00:00 | \n 78.756 | \n 37.1301 | \n
\n \n 2014-01-03 14:34:00+00:00 | \n 78.552 | \n 37.1450 | \n
\n \n 2014-01-03 14:35:00+00:00 | \n 78.573 | \n 37.1500 | \n
\n \n 2014-01-03 14:36:00+00:00 | \n 78.616 | \n 37.1400 | \n
\n \n 2014-01-03 14:37:00+00:00 | \n 78.693 | \n 37.1100 | \n
\n \n 2014-01-03 14:38:00+00:00 | \n 78.630 | \n 37.1250 | \n
\n \n 2014-01-03 14:39:00+00:00 | \n 78.589 | \n 37.0900 | \n
\n \n 2014-01-03 14:40:00+00:00 | \n 78.543 | \n 37.1040 | \n
\n \n 2014-01-03 14:41:00+00:00 | \n 78.424 | \n 37.1000 | \n
\n \n 2014-01-03 14:42:00+00:00 | \n 78.462 | \n 37.1400 | \n
\n \n 2014-01-03 14:43:00+00:00 | \n 78.539 | \n 37.1500 | \n
\n \n 2014-01-03 14:44:00+00:00 | \n 78.573 | \n 37.1600 | \n
\n \n 2014-01-03 14:45:00+00:00 | \n 78.509 | \n 37.1120 | \n
\n \n 2014-01-03 14:46:00+00:00 | \n 78.510 | \n 37.0600 | \n
\n \n 2014-01-03 14:47:00+00:00 | \n 78.477 | \n 37.0600 | \n
\n \n 2014-01-03 14:48:00+00:00 | \n 78.519 | \n 37.0600 | \n
\n \n 2014-01-03 14:49:00+00:00 | \n 78.501 | \n 37.0200 | \n
\n \n 2014-01-03 14:50:00+00:00 | \n 78.419 | \n 37.0100 | \n
\n \n 2014-01-03 14:51:00+00:00 | \n 78.392 | \n 37.0400 | \n
\n \n 2014-01-03 14:52:00+00:00 | \n 78.317 | \n 37.0450 | \n
\n \n 2014-01-03 14:53:00+00:00 | \n 78.287 | \n 37.0200 | \n
\n \n 2014-01-03 14:54:00+00:00 | \n 78.216 | \n 37.0300 | \n
\n \n 2014-01-03 14:55:00+00:00 | \n 78.230 | \n 37.0200 | \n
\n \n 2014-01-03 14:56:00+00:00 | \n 78.244 | \n 37.0150 | \n
\n \n 2014-01-03 14:57:00+00:00 | \n 78.299 | \n 37.0200 | \n
\n \n 2014-01-03 14:58:00+00:00 | \n 78.252 | \n 37.0301 | \n
\n \n 2014-01-03 14:59:00+00:00 | \n 78.344 | \n 37.0500 | \n
\n \n 2014-01-03 15:00:00+00:00 | \n 78.332 | \n 37.0372 | \n
\n \n
\n
"}, "metadata": {}}], "metadata": {"collapsed": false, "trusted": true}}, {"source": "If we want to query on the second layer of the index, we have to use `.xs` with a level argument instead of `.loc`. \n\nNote that `level=1` means the **second** level of the index, because the levels start at index 0.", "cell_type": "markdown", "metadata": {}}, {"execution_count": 42, "cell_type": "code", "source": "data.xs(Timestamp('2014-01-03 14:58:00', tz='UTC'), level=1)", "outputs": [{"execution_count": 42, "output_type": "execute_result", "data": {"text/plain": " Security(24 [AAPL]) Security(5061 [MSFT])\n2014-01-03 00:00:00+00:00 78.252 37.0301", "text/html": "\n
\n \n \n | \n Security(24 [AAPL]) | \n Security(5061 [MSFT]) | \n
\n \n \n \n 2014-01-03 00:00:00+00:00 | \n 78.252 | \n 37.0301 | \n
\n \n
\n
"}, "metadata": {}}], "metadata": {"collapsed": false, "trusted": true}}, {"source": "If we just want to work with the original index values, we can drop the extra level from our index.", "cell_type": "markdown", "metadata": {}}, {"execution_count": 43, "cell_type": "code", "source": "data_copy = data.copy()\ndata_copy.index = data_copy.index.droplevel(0)\ndata_copy.head()", "outputs": [{"execution_count": 43, "output_type": "execute_result", "data": {"text/plain": " Security(24 [AAPL]) Security(5061 [MSFT])\n2014-01-02 14:31:00+00:00 79.446 37.340\n2014-01-02 14:32:00+00:00 79.424 37.375\n2014-01-02 14:33:00+00:00 79.490 37.260\n2014-01-02 14:34:00+00:00 79.502 37.260\n2014-01-02 14:35:00+00:00 79.252 37.280", "text/html": "\n
\n \n \n | \n Security(24 [AAPL]) | \n Security(5061 [MSFT]) | \n
\n \n \n \n 2014-01-02 14:31:00+00:00 | \n 79.446 | \n 37.340 | \n
\n \n 2014-01-02 14:32:00+00:00 | \n 79.424 | \n 37.375 | \n
\n \n 2014-01-02 14:33:00+00:00 | \n 79.490 | \n 37.260 | \n
\n \n 2014-01-02 14:34:00+00:00 | \n 79.502 | \n 37.260 | \n
\n \n 2014-01-02 14:35:00+00:00 | \n 79.252 | \n 37.280 | \n
\n \n
\n
"}, "metadata": {}}], "metadata": {"collapsed": false, "trusted": true}}], "nbformat": 4, "metadata": {"kernelspec": {"display_name": "IPython (Python 2)", "name": "python2"}, "language_info": {"mimetype": "text/x-python", "pygments_lexer": "ipython2", "name": "python", "codemirror_mode": {"version": 2, "name": "ipython"}}, "signature": "sha256:f8ca5d9a28878a778ccc762cb4d3011af7dcb5d3604e3777a81ae42408e195c0"}}