pandas-fin-idioms.ipynb

{
 "cells": [
  {
   "cell_type": "code",
   "execution_count": 2,
   "metadata": {},
   "outputs": [],
   "source": [
    "import numpy as np\n",
    "import pandas as pd"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Miscellaneous"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "metadata": {},
   "outputs": [],
   "source": [
    "pd.set_option(\"display.precision\", 2)    # set display of dataframe to 2 decimal places"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "\n",
    "Some good tricks! especially testing data and introspecting groups by iteration\n",
    "\n",
    "https://realpython.com/python-pandas-tricks/\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Data Presentation\n",
    "\n",
    "The typical case with fianancial data is that you have three dataframes\n",
    "\n",
    "- A single dataframe of pricing data; could have missing dates, not all symbols exist for all dates\n",
    "- A dataframe with reference data (e.g., industry)\n",
    "- A dataframe with some other time indexed data; could be sparse in time\n",
    "\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "metadata": {},
   "outputs": [],
   "source": [
    "import pandas.util.testing as tm"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "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>A</th>\n",
       "      <th>B</th>\n",
       "      <th>C</th>\n",
       "      <th>D</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>2000-01-31</th>\n",
       "      <td>0.45</td>\n",
       "      <td>0.15</td>\n",
       "      <td>-0.14</td>\n",
       "      <td>0.77</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2000-02-29</th>\n",
       "      <td>-1.59</td>\n",
       "      <td>0.51</td>\n",
       "      <td>-0.22</td>\n",
       "      <td>-0.32</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2000-03-31</th>\n",
       "      <td>0.41</td>\n",
       "      <td>-0.54</td>\n",
       "      <td>-1.57</td>\n",
       "      <td>-0.80</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2000-04-30</th>\n",
       "      <td>-1.54</td>\n",
       "      <td>-0.61</td>\n",
       "      <td>-0.67</td>\n",
       "      <td>0.93</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2000-05-31</th>\n",
       "      <td>0.26</td>\n",
       "      <td>1.10</td>\n",
       "      <td>-0.45</td>\n",
       "      <td>-0.19</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "               A     B     C     D\n",
       "2000-01-31  0.45  0.15 -0.14  0.77\n",
       "2000-02-29 -1.59  0.51 -0.22 -0.32\n",
       "2000-03-31  0.41 -0.54 -1.57 -0.80\n",
       "2000-04-30 -1.54 -0.61 -0.67  0.93\n",
       "2000-05-31  0.26  1.10 -0.45 -0.19"
      ]
     },
     "execution_count": 9,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "tm.makeTimeDataFrame(freq='M').head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 17,
   "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></th>\n",
       "      <th>C0</th>\n",
       "      <th>C_l0_g0</th>\n",
       "      <th>C_l0_g1</th>\n",
       "      <th>C_l0_g2</th>\n",
       "      <th>C_l0_g3</th>\n",
       "      <th>C_l0_g4</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>R0</th>\n",
       "      <th>R1</th>\n",
       "      <th>R2</th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>R_l0_g0</th>\n",
       "      <th>R_l1_g0</th>\n",
       "      <th>R_l2_g0</th>\n",
       "      <td>NaN</td>\n",
       "      <td>R0C1</td>\n",
       "      <td>R0C2</td>\n",
       "      <td>R0C3</td>\n",
       "      <td>R0C4</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>R_l0_g1</th>\n",
       "      <th>R_l1_g1</th>\n",
       "      <th>R_l2_g1</th>\n",
       "      <td>R1C0</td>\n",
       "      <td>R1C1</td>\n",
       "      <td>R1C2</td>\n",
       "      <td>R1C3</td>\n",
       "      <td>R1C4</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>R_l0_g2</th>\n",
       "      <th>R_l1_g2</th>\n",
       "      <th>R_l2_g2</th>\n",
       "      <td>R2C0</td>\n",
       "      <td>R2C1</td>\n",
       "      <td>R2C2</td>\n",
       "      <td>R2C3</td>\n",
       "      <td>R2C4</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>R_l0_g3</th>\n",
       "      <th>R_l1_g3</th>\n",
       "      <th>R_l2_g3</th>\n",
       "      <td>R3C0</td>\n",
       "      <td>R3C1</td>\n",
       "      <td>R3C2</td>\n",
       "      <td>R3C3</td>\n",
       "      <td>R3C4</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>R_l0_g4</th>\n",
       "      <th>R_l1_g4</th>\n",
       "      <th>R_l2_g4</th>\n",
       "      <td>R4C0</td>\n",
       "      <td>R4C1</td>\n",
       "      <td>R4C2</td>\n",
       "      <td>R4C3</td>\n",
       "      <td>R4C4</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>R_l0_g5</th>\n",
       "      <th>R_l1_g5</th>\n",
       "      <th>R_l2_g5</th>\n",
       "      <td>R5C0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>R5C3</td>\n",
       "      <td>R5C4</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>R_l0_g6</th>\n",
       "      <th>R_l1_g6</th>\n",
       "      <th>R_l2_g6</th>\n",
       "      <td>R6C0</td>\n",
       "      <td>R6C1</td>\n",
       "      <td>R6C2</td>\n",
       "      <td>R6C3</td>\n",
       "      <td>R6C4</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>R_l0_g7</th>\n",
       "      <th>R_l1_g7</th>\n",
       "      <th>R_l2_g7</th>\n",
       "      <td>R7C0</td>\n",
       "      <td>R7C1</td>\n",
       "      <td>R7C2</td>\n",
       "      <td>R7C3</td>\n",
       "      <td>R7C4</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>R_l0_g8</th>\n",
       "      <th>R_l1_g8</th>\n",
       "      <th>R_l2_g8</th>\n",
       "      <td>NaN</td>\n",
       "      <td>R8C1</td>\n",
       "      <td>R8C2</td>\n",
       "      <td>R8C3</td>\n",
       "      <td>R8C4</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>R_l0_g9</th>\n",
       "      <th>R_l1_g9</th>\n",
       "      <th>R_l2_g9</th>\n",
       "      <td>R9C0</td>\n",
       "      <td>R9C1</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>R9C4</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>R_l0_g10</th>\n",
       "      <th>R_l1_g10</th>\n",
       "      <th>R_l2_g10</th>\n",
       "      <td>R10C0</td>\n",
       "      <td>R10C1</td>\n",
       "      <td>R10C2</td>\n",
       "      <td>R10C3</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>R_l0_g11</th>\n",
       "      <th>R_l1_g11</th>\n",
       "      <th>R_l2_g11</th>\n",
       "      <td>R11C0</td>\n",
       "      <td>R11C1</td>\n",
       "      <td>R11C2</td>\n",
       "      <td>R11C3</td>\n",
       "      <td>R11C4</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>R_l0_g12</th>\n",
       "      <th>R_l1_g12</th>\n",
       "      <th>R_l2_g12</th>\n",
       "      <td>R12C0</td>\n",
       "      <td>R12C1</td>\n",
       "      <td>R12C2</td>\n",
       "      <td>R12C3</td>\n",
       "      <td>R12C4</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>R_l0_g13</th>\n",
       "      <th>R_l1_g13</th>\n",
       "      <th>R_l2_g13</th>\n",
       "      <td>R13C0</td>\n",
       "      <td>R13C1</td>\n",
       "      <td>R13C2</td>\n",
       "      <td>R13C3</td>\n",
       "      <td>R13C4</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>R_l0_g14</th>\n",
       "      <th>R_l1_g14</th>\n",
       "      <th>R_l2_g14</th>\n",
       "      <td>R14C0</td>\n",
       "      <td>R14C1</td>\n",
       "      <td>NaN</td>\n",
       "      <td>R14C3</td>\n",
       "      <td>R14C4</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>R_l0_g15</th>\n",
       "      <th>R_l1_g15</th>\n",
       "      <th>R_l2_g15</th>\n",
       "      <td>NaN</td>\n",
       "      <td>R15C1</td>\n",
       "      <td>R15C2</td>\n",
       "      <td>R15C3</td>\n",
       "      <td>R15C4</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>R_l0_g16</th>\n",
       "      <th>R_l1_g16</th>\n",
       "      <th>R_l2_g16</th>\n",
       "      <td>R16C0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>R16C2</td>\n",
       "      <td>R16C3</td>\n",
       "      <td>R16C4</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>R_l0_g17</th>\n",
       "      <th>R_l1_g17</th>\n",
       "      <th>R_l2_g17</th>\n",
       "      <td>R17C0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>R17C2</td>\n",
       "      <td>R17C3</td>\n",
       "      <td>R17C4</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>R_l0_g18</th>\n",
       "      <th>R_l1_g18</th>\n",
       "      <th>R_l2_g18</th>\n",
       "      <td>R18C0</td>\n",
       "      <td>R18C1</td>\n",
       "      <td>R18C2</td>\n",
       "      <td>R18C3</td>\n",
       "      <td>R18C4</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>R_l0_g19</th>\n",
       "      <th>R_l1_g19</th>\n",
       "      <th>R_l2_g19</th>\n",
       "      <td>R19C0</td>\n",
       "      <td>R19C1</td>\n",
       "      <td>R19C2</td>\n",
       "      <td>R19C3</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>R_l0_g20</th>\n",
       "      <th>R_l1_g20</th>\n",
       "      <th>R_l2_g20</th>\n",
       "      <td>R20C0</td>\n",
       "      <td>R20C1</td>\n",
       "      <td>R20C2</td>\n",
       "      <td>R20C3</td>\n",
       "      <td>R20C4</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>R_l0_g21</th>\n",
       "      <th>R_l1_g21</th>\n",
       "      <th>R_l2_g21</th>\n",
       "      <td>R21C0</td>\n",
       "      <td>R21C1</td>\n",
       "      <td>R21C2</td>\n",
       "      <td>R21C3</td>\n",
       "      <td>R21C4</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>R_l0_g22</th>\n",
       "      <th>R_l1_g22</th>\n",
       "      <th>R_l2_g22</th>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>R22C2</td>\n",
       "      <td>R22C3</td>\n",
       "      <td>R22C4</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>R_l0_g23</th>\n",
       "      <th>R_l1_g23</th>\n",
       "      <th>R_l2_g23</th>\n",
       "      <td>R23C0</td>\n",
       "      <td>R23C1</td>\n",
       "      <td>R23C2</td>\n",
       "      <td>R23C3</td>\n",
       "      <td>R23C4</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>R_l0_g24</th>\n",
       "      <th>R_l1_g24</th>\n",
       "      <th>R_l2_g24</th>\n",
       "      <td>R24C0</td>\n",
       "      <td>R24C1</td>\n",
       "      <td>R24C2</td>\n",
       "      <td>R24C3</td>\n",
       "      <td>R24C4</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>R_l0_g25</th>\n",
       "      <th>R_l1_g25</th>\n",
       "      <th>R_l2_g25</th>\n",
       "      <td>R25C0</td>\n",
       "      <td>R25C1</td>\n",
       "      <td>R25C2</td>\n",
       "      <td>R25C3</td>\n",
       "      <td>R25C4</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>R_l0_g26</th>\n",
       "      <th>R_l1_g26</th>\n",
       "      <th>R_l2_g26</th>\n",
       "      <td>NaN</td>\n",
       "      <td>R26C1</td>\n",
       "      <td>R26C2</td>\n",
       "      <td>R26C3</td>\n",
       "      <td>R26C4</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>R_l0_g27</th>\n",
       "      <th>R_l1_g27</th>\n",
       "      <th>R_l2_g27</th>\n",
       "      <td>R27C0</td>\n",
       "      <td>R27C1</td>\n",
       "      <td>R27C2</td>\n",
       "      <td>R27C3</td>\n",
       "      <td>R27C4</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>R_l0_g28</th>\n",
       "      <th>R_l1_g28</th>\n",
       "      <th>R_l2_g28</th>\n",
       "      <td>R28C0</td>\n",
       "      <td>R28C1</td>\n",
       "      <td>R28C2</td>\n",
       "      <td>R28C3</td>\n",
       "      <td>R28C4</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>R_l0_g29</th>\n",
       "      <th>R_l1_g29</th>\n",
       "      <th>R_l2_g29</th>\n",
       "      <td>R29C0</td>\n",
       "      <td>R29C1</td>\n",
       "      <td>R29C2</td>\n",
       "      <td>R29C3</td>\n",
       "      <td>R29C4</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <th>...</th>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>R_l0_g70</th>\n",
       "      <th>R_l1_g70</th>\n",
       "      <th>R_l2_g70</th>\n",
       "      <td>R70C0</td>\n",
       "      <td>R70C1</td>\n",
       "      <td>R70C2</td>\n",
       "      <td>R70C3</td>\n",
       "      <td>R70C4</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>R_l0_g71</th>\n",
       "      <th>R_l1_g71</th>\n",
       "      <th>R_l2_g71</th>\n",
       "      <td>R71C0</td>\n",
       "      <td>R71C1</td>\n",
       "      <td>R71C2</td>\n",
       "      <td>NaN</td>\n",
       "      <td>R71C4</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>R_l0_g72</th>\n",
       "      <th>R_l1_g72</th>\n",
       "      <th>R_l2_g72</th>\n",
       "      <td>R72C0</td>\n",
       "      <td>R72C1</td>\n",
       "      <td>R72C2</td>\n",
       "      <td>R72C3</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>R_l0_g73</th>\n",
       "      <th>R_l1_g73</th>\n",
       "      <th>R_l2_g73</th>\n",
       "      <td>R73C0</td>\n",
       "      <td>R73C1</td>\n",
       "      <td>R73C2</td>\n",
       "      <td>R73C3</td>\n",
       "      <td>R73C4</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>R_l0_g74</th>\n",
       "      <th>R_l1_g74</th>\n",
       "      <th>R_l2_g74</th>\n",
       "      <td>NaN</td>\n",
       "      <td>R74C1</td>\n",
       "      <td>R74C2</td>\n",
       "      <td>R74C3</td>\n",
       "      <td>R74C4</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>R_l0_g75</th>\n",
       "      <th>R_l1_g75</th>\n",
       "      <th>R_l2_g75</th>\n",
       "      <td>NaN</td>\n",
       "      <td>R75C1</td>\n",
       "      <td>R75C2</td>\n",
       "      <td>R75C3</td>\n",
       "      <td>R75C4</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>R_l0_g76</th>\n",
       "      <th>R_l1_g76</th>\n",
       "      <th>R_l2_g76</th>\n",
       "      <td>R76C0</td>\n",
       "      <td>R76C1</td>\n",
       "      <td>R76C2</td>\n",
       "      <td>R76C3</td>\n",
       "      <td>R76C4</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>R_l0_g77</th>\n",
       "      <th>R_l1_g77</th>\n",
       "      <th>R_l2_g77</th>\n",
       "      <td>R77C0</td>\n",
       "      <td>R77C1</td>\n",
       "      <td>R77C2</td>\n",
       "      <td>R77C3</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>R_l0_g78</th>\n",
       "      <th>R_l1_g78</th>\n",
       "      <th>R_l2_g78</th>\n",
       "      <td>R78C0</td>\n",
       "      <td>R78C1</td>\n",
       "      <td>R78C2</td>\n",
       "      <td>R78C3</td>\n",
       "      <td>R78C4</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>R_l0_g79</th>\n",
       "      <th>R_l1_g79</th>\n",
       "      <th>R_l2_g79</th>\n",
       "      <td>R79C0</td>\n",
       "      <td>R79C1</td>\n",
       "      <td>NaN</td>\n",
       "      <td>R79C3</td>\n",
       "      <td>R79C4</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>R_l0_g80</th>\n",
       "      <th>R_l1_g80</th>\n",
       "      <th>R_l2_g80</th>\n",
       "      <td>NaN</td>\n",
       "      <td>R80C1</td>\n",
       "      <td>R80C2</td>\n",
       "      <td>R80C3</td>\n",
       "      <td>R80C4</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>R_l0_g81</th>\n",
       "      <th>R_l1_g81</th>\n",
       "      <th>R_l2_g81</th>\n",
       "      <td>R81C0</td>\n",
       "      <td>R81C1</td>\n",
       "      <td>R81C2</td>\n",
       "      <td>R81C3</td>\n",
       "      <td>R81C4</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>R_l0_g82</th>\n",
       "      <th>R_l1_g82</th>\n",
       "      <th>R_l2_g82</th>\n",
       "      <td>R82C0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>R82C3</td>\n",
       "      <td>R82C4</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>R_l0_g83</th>\n",
       "      <th>R_l1_g83</th>\n",
       "      <th>R_l2_g83</th>\n",
       "      <td>R83C0</td>\n",
       "      <td>R83C1</td>\n",
       "      <td>R83C2</td>\n",
       "      <td>R83C3</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>R_l0_g84</th>\n",
       "      <th>R_l1_g84</th>\n",
       "      <th>R_l2_g84</th>\n",
       "      <td>R84C0</td>\n",
       "      <td>R84C1</td>\n",
       "      <td>R84C2</td>\n",
       "      <td>R84C3</td>\n",
       "      <td>R84C4</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>R_l0_g85</th>\n",
       "      <th>R_l1_g85</th>\n",
       "      <th>R_l2_g85</th>\n",
       "      <td>R85C0</td>\n",
       "      <td>R85C1</td>\n",
       "      <td>R85C2</td>\n",
       "      <td>R85C3</td>\n",
       "      <td>R85C4</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>R_l0_g86</th>\n",
       "      <th>R_l1_g86</th>\n",
       "      <th>R_l2_g86</th>\n",
       "      <td>R86C0</td>\n",
       "      <td>R86C1</td>\n",
       "      <td>R86C2</td>\n",
       "      <td>R86C3</td>\n",
       "      <td>R86C4</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>R_l0_g87</th>\n",
       "      <th>R_l1_g87</th>\n",
       "      <th>R_l2_g87</th>\n",
       "      <td>R87C0</td>\n",
       "      <td>R87C1</td>\n",
       "      <td>R87C2</td>\n",
       "      <td>NaN</td>\n",
       "      <td>R87C4</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>R_l0_g88</th>\n",
       "      <th>R_l1_g88</th>\n",
       "      <th>R_l2_g88</th>\n",
       "      <td>R88C0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>R88C2</td>\n",
       "      <td>R88C3</td>\n",
       "      <td>R88C4</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>R_l0_g89</th>\n",
       "      <th>R_l1_g89</th>\n",
       "      <th>R_l2_g89</th>\n",
       "      <td>R89C0</td>\n",
       "      <td>R89C1</td>\n",
       "      <td>NaN</td>\n",
       "      <td>R89C3</td>\n",
       "      <td>R89C4</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>R_l0_g90</th>\n",
       "      <th>R_l1_g90</th>\n",
       "      <th>R_l2_g90</th>\n",
       "      <td>R90C0</td>\n",
       "      <td>R90C1</td>\n",
       "      <td>R90C2</td>\n",
       "      <td>R90C3</td>\n",
       "      <td>R90C4</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>R_l0_g91</th>\n",
       "      <th>R_l1_g91</th>\n",
       "      <th>R_l2_g91</th>\n",
       "      <td>NaN</td>\n",
       "      <td>R91C1</td>\n",
       "      <td>R91C2</td>\n",
       "      <td>R91C3</td>\n",
       "      <td>R91C4</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>R_l0_g92</th>\n",
       "      <th>R_l1_g92</th>\n",
       "      <th>R_l2_g92</th>\n",
       "      <td>R92C0</td>\n",
       "      <td>R92C1</td>\n",
       "      <td>R92C2</td>\n",
       "      <td>R92C3</td>\n",
       "      <td>R92C4</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>R_l0_g93</th>\n",
       "      <th>R_l1_g93</th>\n",
       "      <th>R_l2_g93</th>\n",
       "      <td>R93C0</td>\n",
       "      <td>R93C1</td>\n",
       "      <td>R93C2</td>\n",
       "      <td>R93C3</td>\n",
       "      <td>R93C4</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>R_l0_g94</th>\n",
       "      <th>R_l1_g94</th>\n",
       "      <th>R_l2_g94</th>\n",
       "      <td>R94C0</td>\n",
       "      <td>R94C1</td>\n",
       "      <td>R94C2</td>\n",
       "      <td>NaN</td>\n",
       "      <td>R94C4</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>R_l0_g95</th>\n",
       "      <th>R_l1_g95</th>\n",
       "      <th>R_l2_g95</th>\n",
       "      <td>R95C0</td>\n",
       "      <td>R95C1</td>\n",
       "      <td>R95C2</td>\n",
       "      <td>R95C3</td>\n",
       "      <td>R95C4</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>R_l0_g96</th>\n",
       "      <th>R_l1_g96</th>\n",
       "      <th>R_l2_g96</th>\n",
       "      <td>R96C0</td>\n",
       "      <td>R96C1</td>\n",
       "      <td>R96C2</td>\n",
       "      <td>R96C3</td>\n",
       "      <td>R96C4</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>R_l0_g97</th>\n",
       "      <th>R_l1_g97</th>\n",
       "      <th>R_l2_g97</th>\n",
       "      <td>R97C0</td>\n",
       "      <td>R97C1</td>\n",
       "      <td>R97C2</td>\n",
       "      <td>R97C3</td>\n",
       "      <td>R97C4</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>R_l0_g98</th>\n",
       "      <th>R_l1_g98</th>\n",
       "      <th>R_l2_g98</th>\n",
       "      <td>R98C0</td>\n",
       "      <td>R98C1</td>\n",
       "      <td>NaN</td>\n",
       "      <td>R98C3</td>\n",
       "      <td>R98C4</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>R_l0_g99</th>\n",
       "      <th>R_l1_g99</th>\n",
       "      <th>R_l2_g99</th>\n",
       "      <td>R99C0</td>\n",
       "      <td>R99C1</td>\n",
       "      <td>R99C2</td>\n",
       "      <td>R99C3</td>\n",
       "      <td>R99C4</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>100 rows × 5 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "C0                         C_l0_g0 C_l0_g1 C_l0_g2 C_l0_g3 C_l0_g4\n",
       "R0       R1       R2                                              \n",
       "R_l0_g0  R_l1_g0  R_l2_g0      NaN    R0C1    R0C2    R0C3    R0C4\n",
       "R_l0_g1  R_l1_g1  R_l2_g1     R1C0    R1C1    R1C2    R1C3    R1C4\n",
       "R_l0_g2  R_l1_g2  R_l2_g2     R2C0    R2C1    R2C2    R2C3    R2C4\n",
       "R_l0_g3  R_l1_g3  R_l2_g3     R3C0    R3C1    R3C2    R3C3    R3C4\n",
       "R_l0_g4  R_l1_g4  R_l2_g4     R4C0    R4C1    R4C2    R4C3    R4C4\n",
       "R_l0_g5  R_l1_g5  R_l2_g5     R5C0     NaN     NaN    R5C3    R5C4\n",
       "R_l0_g6  R_l1_g6  R_l2_g6     R6C0    R6C1    R6C2    R6C3    R6C4\n",
       "R_l0_g7  R_l1_g7  R_l2_g7     R7C0    R7C1    R7C2    R7C3    R7C4\n",
       "R_l0_g8  R_l1_g8  R_l2_g8      NaN    R8C1    R8C2    R8C3    R8C4\n",
       "R_l0_g9  R_l1_g9  R_l2_g9     R9C0    R9C1     NaN     NaN    R9C4\n",
       "R_l0_g10 R_l1_g10 R_l2_g10   R10C0   R10C1   R10C2   R10C3     NaN\n",
       "R_l0_g11 R_l1_g11 R_l2_g11   R11C0   R11C1   R11C2   R11C3   R11C4\n",
       "R_l0_g12 R_l1_g12 R_l2_g12   R12C0   R12C1   R12C2   R12C3   R12C4\n",
       "R_l0_g13 R_l1_g13 R_l2_g13   R13C0   R13C1   R13C2   R13C3   R13C4\n",
       "R_l0_g14 R_l1_g14 R_l2_g14   R14C0   R14C1     NaN   R14C3   R14C4\n",
       "R_l0_g15 R_l1_g15 R_l2_g15     NaN   R15C1   R15C2   R15C3   R15C4\n",
       "R_l0_g16 R_l1_g16 R_l2_g16   R16C0     NaN   R16C2   R16C3   R16C4\n",
       "R_l0_g17 R_l1_g17 R_l2_g17   R17C0     NaN   R17C2   R17C3   R17C4\n",
       "R_l0_g18 R_l1_g18 R_l2_g18   R18C0   R18C1   R18C2   R18C3   R18C4\n",
       "R_l0_g19 R_l1_g19 R_l2_g19   R19C0   R19C1   R19C2   R19C3     NaN\n",
       "R_l0_g20 R_l1_g20 R_l2_g20   R20C0   R20C1   R20C2   R20C3   R20C4\n",
       "R_l0_g21 R_l1_g21 R_l2_g21   R21C0   R21C1   R21C2   R21C3   R21C4\n",
       "R_l0_g22 R_l1_g22 R_l2_g22     NaN     NaN   R22C2   R22C3   R22C4\n",
       "R_l0_g23 R_l1_g23 R_l2_g23   R23C0   R23C1   R23C2   R23C3   R23C4\n",
       "R_l0_g24 R_l1_g24 R_l2_g24   R24C0   R24C1   R24C2   R24C3   R24C4\n",
       "R_l0_g25 R_l1_g25 R_l2_g25   R25C0   R25C1   R25C2   R25C3   R25C4\n",
       "R_l0_g26 R_l1_g26 R_l2_g26     NaN   R26C1   R26C2   R26C3   R26C4\n",
       "R_l0_g27 R_l1_g27 R_l2_g27   R27C0   R27C1   R27C2   R27C3   R27C4\n",
       "R_l0_g28 R_l1_g28 R_l2_g28   R28C0   R28C1   R28C2   R28C3   R28C4\n",
       "R_l0_g29 R_l1_g29 R_l2_g29   R29C0   R29C1   R29C2   R29C3   R29C4\n",
       "...                            ...     ...     ...     ...     ...\n",
       "R_l0_g70 R_l1_g70 R_l2_g70   R70C0   R70C1   R70C2   R70C3   R70C4\n",
       "R_l0_g71 R_l1_g71 R_l2_g71   R71C0   R71C1   R71C2     NaN   R71C4\n",
       "R_l0_g72 R_l1_g72 R_l2_g72   R72C0   R72C1   R72C2   R72C3     NaN\n",
       "R_l0_g73 R_l1_g73 R_l2_g73   R73C0   R73C1   R73C2   R73C3   R73C4\n",
       "R_l0_g74 R_l1_g74 R_l2_g74     NaN   R74C1   R74C2   R74C3   R74C4\n",
       "R_l0_g75 R_l1_g75 R_l2_g75     NaN   R75C1   R75C2   R75C3   R75C4\n",
       "R_l0_g76 R_l1_g76 R_l2_g76   R76C0   R76C1   R76C2   R76C3   R76C4\n",
       "R_l0_g77 R_l1_g77 R_l2_g77   R77C0   R77C1   R77C2   R77C3     NaN\n",
       "R_l0_g78 R_l1_g78 R_l2_g78   R78C0   R78C1   R78C2   R78C3   R78C4\n",
       "R_l0_g79 R_l1_g79 R_l2_g79   R79C0   R79C1     NaN   R79C3   R79C4\n",
       "R_l0_g80 R_l1_g80 R_l2_g80     NaN   R80C1   R80C2   R80C3   R80C4\n",
       "R_l0_g81 R_l1_g81 R_l2_g81   R81C0   R81C1   R81C2   R81C3   R81C4\n",
       "R_l0_g82 R_l1_g82 R_l2_g82   R82C0     NaN     NaN   R82C3   R82C4\n",
       "R_l0_g83 R_l1_g83 R_l2_g83   R83C0   R83C1   R83C2   R83C3     NaN\n",
       "R_l0_g84 R_l1_g84 R_l2_g84   R84C0   R84C1   R84C2   R84C3   R84C4\n",
       "R_l0_g85 R_l1_g85 R_l2_g85   R85C0   R85C1   R85C2   R85C3   R85C4\n",
       "R_l0_g86 R_l1_g86 R_l2_g86   R86C0   R86C1   R86C2   R86C3   R86C4\n",
       "R_l0_g87 R_l1_g87 R_l2_g87   R87C0   R87C1   R87C2     NaN   R87C4\n",
       "R_l0_g88 R_l1_g88 R_l2_g88   R88C0     NaN   R88C2   R88C3   R88C4\n",
       "R_l0_g89 R_l1_g89 R_l2_g89   R89C0   R89C1     NaN   R89C3   R89C4\n",
       "R_l0_g90 R_l1_g90 R_l2_g90   R90C0   R90C1   R90C2   R90C3   R90C4\n",
       "R_l0_g91 R_l1_g91 R_l2_g91     NaN   R91C1   R91C2   R91C3   R91C4\n",
       "R_l0_g92 R_l1_g92 R_l2_g92   R92C0   R92C1   R92C2   R92C3   R92C4\n",
       "R_l0_g93 R_l1_g93 R_l2_g93   R93C0   R93C1   R93C2   R93C3   R93C4\n",
       "R_l0_g94 R_l1_g94 R_l2_g94   R94C0   R94C1   R94C2     NaN   R94C4\n",
       "R_l0_g95 R_l1_g95 R_l2_g95   R95C0   R95C1   R95C2   R95C3   R95C4\n",
       "R_l0_g96 R_l1_g96 R_l2_g96   R96C0   R96C1   R96C2   R96C3   R96C4\n",
       "R_l0_g97 R_l1_g97 R_l2_g97   R97C0   R97C1   R97C2   R97C3   R97C4\n",
       "R_l0_g98 R_l1_g98 R_l2_g98   R98C0   R98C1     NaN   R98C3   R98C4\n",
       "R_l0_g99 R_l1_g99 R_l2_g99   R99C0   R99C1   R99C2   R99C3   R99C4\n",
       "\n",
       "[100 rows x 5 columns]"
      ]
     },
     "execution_count": 17,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "tm.makeMissingCustomDataframe(nrows=100, ncols=5, r_idx_nlevels=3)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 11,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "['makeBoolIndex',\n",
       " 'makeCategoricalIndex',\n",
       " 'makeCustomDataframe',\n",
       " 'makeCustomIndex',\n",
       " 'makeDataFrame',\n",
       " 'makeDateIndex',\n",
       " 'makeFloatIndex',\n",
       " 'makeFloatSeries',\n",
       " 'makeIntIndex',\n",
       " 'makeIntervalIndex',\n",
       " 'makeMissingCustomDataframe',\n",
       " 'makeMissingDataframe',\n",
       " 'makeMixedDataFrame',\n",
       " 'makeMultiIndex',\n",
       " 'makeObjectSeries',\n",
       " 'makePanel',\n",
       " 'makePeriodFrame',\n",
       " 'makePeriodIndex',\n",
       " 'makePeriodPanel',\n",
       " 'makePeriodSeries',\n",
       " 'makeRangeIndex',\n",
       " 'makeStringIndex',\n",
       " 'makeStringSeries',\n",
       " 'makeTimeDataFrame',\n",
       " 'makeTimeSeries',\n",
       " 'makeTimedeltaIndex',\n",
       " 'makeUIntIndex',\n",
       " 'makeUnicodeIndex']"
      ]
     },
     "execution_count": 11,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "[i for i in dir(tm) if i.startswith('make')]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Modern Pandas tricks and tips\n",
    "\n",
    "Method chaining is the pandas was to stitch together multiple operations on the data. \n",
    "\n",
    "\n",
    "## `query`, `filter`\n",
    "\n",
    "http://jose-coto.com/query-method-pandas\n",
    "\n",
    "This is how you query for not null; Nan != Nan so this works\n",
    "\n",
    "\n",
    "#df.query('Team==\"Spain\" and Medal==Medal')\n",
    "\n",
    "## `assign` is mutate\n",
    "\n",
    "\n",
    "## pipe"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "metadata": {},
   "outputs": [],
   "source": [
    "s = pd.Series(data=[3.2,5.6,8.0], index=pd.to_datetime(['2018-01-31', '2018-02-28', '2018-09-30']))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "2018-01-31    3.2\n",
       "2018-02-28    5.6\n",
       "2018-09-30    8.0\n",
       "dtype: float64"
      ]
     },
     "execution_count": 3,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "s"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "2018-01-31    3.2\n",
       "2018-02-28    5.6\n",
       "2018-03-31    5.6\n",
       "2018-04-30    5.6\n",
       "2018-05-31    5.6\n",
       "2018-06-30    5.6\n",
       "2018-07-31    5.6\n",
       "2018-08-31    5.6\n",
       "2018-09-30    8.0\n",
       "2018-10-31    8.0\n",
       "2018-11-30    8.0\n",
       "Freq: M, dtype: float64"
      ]
     },
     "execution_count": 4,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "s.reindex(pd.date_range('2018-01', '2018-12', freq='M')).fillna(method='ffill')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "metadata": {},
   "outputs": [],
   "source": [
    "df = pd.DataFrame(data = {\n",
    "    'grp1': ['red','red','red','blue','blue'],\n",
    "    'grp2': [18, 20,20, 18, 22],\n",
    "    'dt': pd.to_datetime(['2018-01-31', '2018-02-28', '2018-09-30', '2018-01-31', '2018-09-30']),\n",
    "    's': [3.2, 4.8, 5.1, 5.3, 5.8],\n",
    "    'r': [1,2,3,4,5],\n",
    "    't': [7,8,9,10,11],\n",
    "})"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "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>grp1</th>\n",
       "      <th>grp2</th>\n",
       "      <th>dt</th>\n",
       "      <th>s</th>\n",
       "      <th>r</th>\n",
       "      <th>t</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>red</td>\n",
       "      <td>18</td>\n",
       "      <td>2018-01-31</td>\n",
       "      <td>3.2</td>\n",
       "      <td>1</td>\n",
       "      <td>7</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>red</td>\n",
       "      <td>20</td>\n",
       "      <td>2018-02-28</td>\n",
       "      <td>4.8</td>\n",
       "      <td>2</td>\n",
       "      <td>8</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>red</td>\n",
       "      <td>20</td>\n",
       "      <td>2018-09-30</td>\n",
       "      <td>5.1</td>\n",
       "      <td>3</td>\n",
       "      <td>9</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>blue</td>\n",
       "      <td>18</td>\n",
       "      <td>2018-01-31</td>\n",
       "      <td>5.3</td>\n",
       "      <td>4</td>\n",
       "      <td>10</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>blue</td>\n",
       "      <td>22</td>\n",
       "      <td>2018-09-30</td>\n",
       "      <td>5.8</td>\n",
       "      <td>5</td>\n",
       "      <td>11</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   grp1  grp2         dt    s  r   t\n",
       "0   red    18 2018-01-31  3.2  1   7\n",
       "1   red    20 2018-02-28  4.8  2   8\n",
       "2   red    20 2018-09-30  5.1  3   9\n",
       "3  blue    18 2018-01-31  5.3  4  10\n",
       "4  blue    22 2018-09-30  5.8  5  11"
      ]
     },
     "execution_count": 6,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "metadata": {},
   "outputs": [],
   "source": [
    "df = df.set_index(['grp1', 'grp2'])"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "metadata": {},
   "outputs": [],
   "source": [
    "idx = pd.date_range('2017-12', '2018-12', freq='M')\n",
    "def my_reindex(x):\n",
    "    #import pdb; pdb.set_trace()\n",
    "    tmp = x.set_index(x.dt).reindex(idx)\n",
    "    tmp['dt'] = tmp.index.get_level_values(0)\n",
    "    return tmp"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "metadata": {},
   "outputs": [],
   "source": [
    "df = df.groupby(['grp1', 'grp2']).apply(my_reindex)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 10,
   "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></th>\n",
       "      <th></th>\n",
       "      <th>dt</th>\n",
       "      <th>s</th>\n",
       "      <th>r</th>\n",
       "      <th>t</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>grp1</th>\n",
       "      <th>grp2</th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th rowspan=\"24\" valign=\"top\">blue</th>\n",
       "      <th rowspan=\"12\" valign=\"top\">18</th>\n",
       "      <th>2017-12-31</th>\n",
       "      <td>2017-12-31</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2018-01-31</th>\n",
       "      <td>2018-01-31</td>\n",
       "      <td>5.3</td>\n",
       "      <td>4.0</td>\n",
       "      <td>10.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2018-02-28</th>\n",
       "      <td>2018-02-28</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2018-03-31</th>\n",
       "      <td>2018-03-31</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2018-04-30</th>\n",
       "      <td>2018-04-30</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2018-05-31</th>\n",
       "      <td>2018-05-31</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2018-06-30</th>\n",
       "      <td>2018-06-30</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2018-07-31</th>\n",
       "      <td>2018-07-31</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2018-08-31</th>\n",
       "      <td>2018-08-31</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2018-09-30</th>\n",
       "      <td>2018-09-30</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2018-10-31</th>\n",
       "      <td>2018-10-31</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2018-11-30</th>\n",
       "      <td>2018-11-30</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th rowspan=\"12\" valign=\"top\">22</th>\n",
       "      <th>2017-12-31</th>\n",
       "      <td>2017-12-31</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2018-01-31</th>\n",
       "      <td>2018-01-31</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2018-02-28</th>\n",
       "      <td>2018-02-28</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2018-03-31</th>\n",
       "      <td>2018-03-31</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2018-04-30</th>\n",
       "      <td>2018-04-30</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2018-05-31</th>\n",
       "      <td>2018-05-31</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2018-06-30</th>\n",
       "      <td>2018-06-30</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2018-07-31</th>\n",
       "      <td>2018-07-31</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2018-08-31</th>\n",
       "      <td>2018-08-31</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2018-09-30</th>\n",
       "      <td>2018-09-30</td>\n",
       "      <td>5.8</td>\n",
       "      <td>5.0</td>\n",
       "      <td>11.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2018-10-31</th>\n",
       "      <td>2018-10-31</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2018-11-30</th>\n",
       "      <td>2018-11-30</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th rowspan=\"24\" valign=\"top\">red</th>\n",
       "      <th rowspan=\"12\" valign=\"top\">18</th>\n",
       "      <th>2017-12-31</th>\n",
       "      <td>2017-12-31</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2018-01-31</th>\n",
       "      <td>2018-01-31</td>\n",
       "      <td>3.2</td>\n",
       "      <td>1.0</td>\n",
       "      <td>7.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2018-02-28</th>\n",
       "      <td>2018-02-28</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2018-03-31</th>\n",
       "      <td>2018-03-31</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2018-04-30</th>\n",
       "      <td>2018-04-30</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2018-05-31</th>\n",
       "      <td>2018-05-31</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2018-06-30</th>\n",
       "      <td>2018-06-30</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2018-07-31</th>\n",
       "      <td>2018-07-31</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2018-08-31</th>\n",
       "      <td>2018-08-31</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2018-09-30</th>\n",
       "      <td>2018-09-30</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2018-10-31</th>\n",
       "      <td>2018-10-31</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2018-11-30</th>\n",
       "      <td>2018-11-30</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th rowspan=\"12\" valign=\"top\">20</th>\n",
       "      <th>2017-12-31</th>\n",
       "      <td>2017-12-31</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2018-01-31</th>\n",
       "      <td>2018-01-31</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2018-02-28</th>\n",
       "      <td>2018-02-28</td>\n",
       "      <td>4.8</td>\n",
       "      <td>2.0</td>\n",
       "      <td>8.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2018-03-31</th>\n",
       "      <td>2018-03-31</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2018-04-30</th>\n",
       "      <td>2018-04-30</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2018-05-31</th>\n",
       "      <td>2018-05-31</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2018-06-30</th>\n",
       "      <td>2018-06-30</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2018-07-31</th>\n",
       "      <td>2018-07-31</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2018-08-31</th>\n",
       "      <td>2018-08-31</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2018-09-30</th>\n",
       "      <td>2018-09-30</td>\n",
       "      <td>5.1</td>\n",
       "      <td>3.0</td>\n",
       "      <td>9.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2018-10-31</th>\n",
       "      <td>2018-10-31</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2018-11-30</th>\n",
       "      <td>2018-11-30</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                             dt    s    r     t\n",
       "grp1 grp2                                      \n",
       "blue 18   2017-12-31 2017-12-31  NaN  NaN   NaN\n",
       "          2018-01-31 2018-01-31  5.3  4.0  10.0\n",
       "          2018-02-28 2018-02-28  NaN  NaN   NaN\n",
       "          2018-03-31 2018-03-31  NaN  NaN   NaN\n",
       "          2018-04-30 2018-04-30  NaN  NaN   NaN\n",
       "          2018-05-31 2018-05-31  NaN  NaN   NaN\n",
       "          2018-06-30 2018-06-30  NaN  NaN   NaN\n",
       "          2018-07-31 2018-07-31  NaN  NaN   NaN\n",
       "          2018-08-31 2018-08-31  NaN  NaN   NaN\n",
       "          2018-09-30 2018-09-30  NaN  NaN   NaN\n",
       "          2018-10-31 2018-10-31  NaN  NaN   NaN\n",
       "          2018-11-30 2018-11-30  NaN  NaN   NaN\n",
       "     22   2017-12-31 2017-12-31  NaN  NaN   NaN\n",
       "          2018-01-31 2018-01-31  NaN  NaN   NaN\n",
       "          2018-02-28 2018-02-28  NaN  NaN   NaN\n",
       "          2018-03-31 2018-03-31  NaN  NaN   NaN\n",
       "          2018-04-30 2018-04-30  NaN  NaN   NaN\n",
       "          2018-05-31 2018-05-31  NaN  NaN   NaN\n",
       "          2018-06-30 2018-06-30  NaN  NaN   NaN\n",
       "          2018-07-31 2018-07-31  NaN  NaN   NaN\n",
       "          2018-08-31 2018-08-31  NaN  NaN   NaN\n",
       "          2018-09-30 2018-09-30  5.8  5.0  11.0\n",
       "          2018-10-31 2018-10-31  NaN  NaN   NaN\n",
       "          2018-11-30 2018-11-30  NaN  NaN   NaN\n",
       "red  18   2017-12-31 2017-12-31  NaN  NaN   NaN\n",
       "          2018-01-31 2018-01-31  3.2  1.0   7.0\n",
       "          2018-02-28 2018-02-28  NaN  NaN   NaN\n",
       "          2018-03-31 2018-03-31  NaN  NaN   NaN\n",
       "          2018-04-30 2018-04-30  NaN  NaN   NaN\n",
       "          2018-05-31 2018-05-31  NaN  NaN   NaN\n",
       "          2018-06-30 2018-06-30  NaN  NaN   NaN\n",
       "          2018-07-31 2018-07-31  NaN  NaN   NaN\n",
       "          2018-08-31 2018-08-31  NaN  NaN   NaN\n",
       "          2018-09-30 2018-09-30  NaN  NaN   NaN\n",
       "          2018-10-31 2018-10-31  NaN  NaN   NaN\n",
       "          2018-11-30 2018-11-30  NaN  NaN   NaN\n",
       "     20   2017-12-31 2017-12-31  NaN  NaN   NaN\n",
       "          2018-01-31 2018-01-31  NaN  NaN   NaN\n",
       "          2018-02-28 2018-02-28  4.8  2.0   8.0\n",
       "          2018-03-31 2018-03-31  NaN  NaN   NaN\n",
       "          2018-04-30 2018-04-30  NaN  NaN   NaN\n",
       "          2018-05-31 2018-05-31  NaN  NaN   NaN\n",
       "          2018-06-30 2018-06-30  NaN  NaN   NaN\n",
       "          2018-07-31 2018-07-31  NaN  NaN   NaN\n",
       "          2018-08-31 2018-08-31  NaN  NaN   NaN\n",
       "          2018-09-30 2018-09-30  5.1  3.0   9.0\n",
       "          2018-10-31 2018-10-31  NaN  NaN   NaN\n",
       "          2018-11-30 2018-11-30  NaN  NaN   NaN"
      ]
     },
     "execution_count": 10,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 11,
   "metadata": {},
   "outputs": [],
   "source": [
    "df[['s', 'r']] = df.groupby(level=[0,1])[['s','r']].fillna(method='ffill')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 12,
   "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></th>\n",
       "      <th></th>\n",
       "      <th>dt</th>\n",
       "      <th>s</th>\n",
       "      <th>r</th>\n",
       "      <th>t</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>grp1</th>\n",
       "      <th>grp2</th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th rowspan=\"24\" valign=\"top\">blue</th>\n",
       "      <th rowspan=\"12\" valign=\"top\">18</th>\n",
       "      <th>2017-12-31</th>\n",
       "      <td>2017-12-31</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2018-01-31</th>\n",
       "      <td>2018-01-31</td>\n",
       "      <td>5.3</td>\n",
       "      <td>4.0</td>\n",
       "      <td>10.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2018-02-28</th>\n",
       "      <td>2018-02-28</td>\n",
       "      <td>5.3</td>\n",
       "      <td>4.0</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2018-03-31</th>\n",
       "      <td>2018-03-31</td>\n",
       "      <td>5.3</td>\n",
       "      <td>4.0</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2018-04-30</th>\n",
       "      <td>2018-04-30</td>\n",
       "      <td>5.3</td>\n",
       "      <td>4.0</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2018-05-31</th>\n",
       "      <td>2018-05-31</td>\n",
       "      <td>5.3</td>\n",
       "      <td>4.0</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2018-06-30</th>\n",
       "      <td>2018-06-30</td>\n",
       "      <td>5.3</td>\n",
       "      <td>4.0</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2018-07-31</th>\n",
       "      <td>2018-07-31</td>\n",
       "      <td>5.3</td>\n",
       "      <td>4.0</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2018-08-31</th>\n",
       "      <td>2018-08-31</td>\n",
       "      <td>5.3</td>\n",
       "      <td>4.0</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2018-09-30</th>\n",
       "      <td>2018-09-30</td>\n",
       "      <td>5.3</td>\n",
       "      <td>4.0</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2018-10-31</th>\n",
       "      <td>2018-10-31</td>\n",
       "      <td>5.3</td>\n",
       "      <td>4.0</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2018-11-30</th>\n",
       "      <td>2018-11-30</td>\n",
       "      <td>5.3</td>\n",
       "      <td>4.0</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th rowspan=\"12\" valign=\"top\">22</th>\n",
       "      <th>2017-12-31</th>\n",
       "      <td>2017-12-31</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2018-01-31</th>\n",
       "      <td>2018-01-31</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2018-02-28</th>\n",
       "      <td>2018-02-28</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2018-03-31</th>\n",
       "      <td>2018-03-31</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2018-04-30</th>\n",
       "      <td>2018-04-30</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2018-05-31</th>\n",
       "      <td>2018-05-31</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2018-06-30</th>\n",
       "      <td>2018-06-30</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2018-07-31</th>\n",
       "      <td>2018-07-31</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2018-08-31</th>\n",
       "      <td>2018-08-31</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2018-09-30</th>\n",
       "      <td>2018-09-30</td>\n",
       "      <td>5.8</td>\n",
       "      <td>5.0</td>\n",
       "      <td>11.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2018-10-31</th>\n",
       "      <td>2018-10-31</td>\n",
       "      <td>5.8</td>\n",
       "      <td>5.0</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2018-11-30</th>\n",
       "      <td>2018-11-30</td>\n",
       "      <td>5.8</td>\n",
       "      <td>5.0</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th rowspan=\"24\" valign=\"top\">red</th>\n",
       "      <th rowspan=\"12\" valign=\"top\">18</th>\n",
       "      <th>2017-12-31</th>\n",
       "      <td>2017-12-31</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2018-01-31</th>\n",
       "      <td>2018-01-31</td>\n",
       "      <td>3.2</td>\n",
       "      <td>1.0</td>\n",
       "      <td>7.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2018-02-28</th>\n",
       "      <td>2018-02-28</td>\n",
       "      <td>3.2</td>\n",
       "      <td>1.0</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2018-03-31</th>\n",
       "      <td>2018-03-31</td>\n",
       "      <td>3.2</td>\n",
       "      <td>1.0</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2018-04-30</th>\n",
       "      <td>2018-04-30</td>\n",
       "      <td>3.2</td>\n",
       "      <td>1.0</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2018-05-31</th>\n",
       "      <td>2018-05-31</td>\n",
       "      <td>3.2</td>\n",
       "      <td>1.0</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2018-06-30</th>\n",
       "      <td>2018-06-30</td>\n",
       "      <td>3.2</td>\n",
       "      <td>1.0</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2018-07-31</th>\n",
       "      <td>2018-07-31</td>\n",
       "      <td>3.2</td>\n",
       "      <td>1.0</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2018-08-31</th>\n",
       "      <td>2018-08-31</td>\n",
       "      <td>3.2</td>\n",
       "      <td>1.0</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2018-09-30</th>\n",
       "      <td>2018-09-30</td>\n",
       "      <td>3.2</td>\n",
       "      <td>1.0</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2018-10-31</th>\n",
       "      <td>2018-10-31</td>\n",
       "      <td>3.2</td>\n",
       "      <td>1.0</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2018-11-30</th>\n",
       "      <td>2018-11-30</td>\n",
       "      <td>3.2</td>\n",
       "      <td>1.0</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th rowspan=\"12\" valign=\"top\">20</th>\n",
       "      <th>2017-12-31</th>\n",
       "      <td>2017-12-31</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2018-01-31</th>\n",
       "      <td>2018-01-31</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2018-02-28</th>\n",
       "      <td>2018-02-28</td>\n",
       "      <td>4.8</td>\n",
       "      <td>2.0</td>\n",
       "      <td>8.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2018-03-31</th>\n",
       "      <td>2018-03-31</td>\n",
       "      <td>4.8</td>\n",
       "      <td>2.0</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2018-04-30</th>\n",
       "      <td>2018-04-30</td>\n",
       "      <td>4.8</td>\n",
       "      <td>2.0</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2018-05-31</th>\n",
       "      <td>2018-05-31</td>\n",
       "      <td>4.8</td>\n",
       "      <td>2.0</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2018-06-30</th>\n",
       "      <td>2018-06-30</td>\n",
       "      <td>4.8</td>\n",
       "      <td>2.0</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2018-07-31</th>\n",
       "      <td>2018-07-31</td>\n",
       "      <td>4.8</td>\n",
       "      <td>2.0</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2018-08-31</th>\n",
       "      <td>2018-08-31</td>\n",
       "      <td>4.8</td>\n",
       "      <td>2.0</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2018-09-30</th>\n",
       "      <td>2018-09-30</td>\n",
       "      <td>5.1</td>\n",
       "      <td>3.0</td>\n",
       "      <td>9.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2018-10-31</th>\n",
       "      <td>2018-10-31</td>\n",
       "      <td>5.1</td>\n",
       "      <td>3.0</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2018-11-30</th>\n",
       "      <td>2018-11-30</td>\n",
       "      <td>5.1</td>\n",
       "      <td>3.0</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                             dt    s    r     t\n",
       "grp1 grp2                                      \n",
       "blue 18   2017-12-31 2017-12-31  NaN  NaN   NaN\n",
       "          2018-01-31 2018-01-31  5.3  4.0  10.0\n",
       "          2018-02-28 2018-02-28  5.3  4.0   NaN\n",
       "          2018-03-31 2018-03-31  5.3  4.0   NaN\n",
       "          2018-04-30 2018-04-30  5.3  4.0   NaN\n",
       "          2018-05-31 2018-05-31  5.3  4.0   NaN\n",
       "          2018-06-30 2018-06-30  5.3  4.0   NaN\n",
       "          2018-07-31 2018-07-31  5.3  4.0   NaN\n",
       "          2018-08-31 2018-08-31  5.3  4.0   NaN\n",
       "          2018-09-30 2018-09-30  5.3  4.0   NaN\n",
       "          2018-10-31 2018-10-31  5.3  4.0   NaN\n",
       "          2018-11-30 2018-11-30  5.3  4.0   NaN\n",
       "     22   2017-12-31 2017-12-31  NaN  NaN   NaN\n",
       "          2018-01-31 2018-01-31  NaN  NaN   NaN\n",
       "          2018-02-28 2018-02-28  NaN  NaN   NaN\n",
       "          2018-03-31 2018-03-31  NaN  NaN   NaN\n",
       "          2018-04-30 2018-04-30  NaN  NaN   NaN\n",
       "          2018-05-31 2018-05-31  NaN  NaN   NaN\n",
       "          2018-06-30 2018-06-30  NaN  NaN   NaN\n",
       "          2018-07-31 2018-07-31  NaN  NaN   NaN\n",
       "          2018-08-31 2018-08-31  NaN  NaN   NaN\n",
       "          2018-09-30 2018-09-30  5.8  5.0  11.0\n",
       "          2018-10-31 2018-10-31  5.8  5.0   NaN\n",
       "          2018-11-30 2018-11-30  5.8  5.0   NaN\n",
       "red  18   2017-12-31 2017-12-31  NaN  NaN   NaN\n",
       "          2018-01-31 2018-01-31  3.2  1.0   7.0\n",
       "          2018-02-28 2018-02-28  3.2  1.0   NaN\n",
       "          2018-03-31 2018-03-31  3.2  1.0   NaN\n",
       "          2018-04-30 2018-04-30  3.2  1.0   NaN\n",
       "          2018-05-31 2018-05-31  3.2  1.0   NaN\n",
       "          2018-06-30 2018-06-30  3.2  1.0   NaN\n",
       "          2018-07-31 2018-07-31  3.2  1.0   NaN\n",
       "          2018-08-31 2018-08-31  3.2  1.0   NaN\n",
       "          2018-09-30 2018-09-30  3.2  1.0   NaN\n",
       "          2018-10-31 2018-10-31  3.2  1.0   NaN\n",
       "          2018-11-30 2018-11-30  3.2  1.0   NaN\n",
       "     20   2017-12-31 2017-12-31  NaN  NaN   NaN\n",
       "          2018-01-31 2018-01-31  NaN  NaN   NaN\n",
       "          2018-02-28 2018-02-28  4.8  2.0   8.0\n",
       "          2018-03-31 2018-03-31  4.8  2.0   NaN\n",
       "          2018-04-30 2018-04-30  4.8  2.0   NaN\n",
       "          2018-05-31 2018-05-31  4.8  2.0   NaN\n",
       "          2018-06-30 2018-06-30  4.8  2.0   NaN\n",
       "          2018-07-31 2018-07-31  4.8  2.0   NaN\n",
       "          2018-08-31 2018-08-31  4.8  2.0   NaN\n",
       "          2018-09-30 2018-09-30  5.1  3.0   9.0\n",
       "          2018-10-31 2018-10-31  5.1  3.0   NaN\n",
       "          2018-11-30 2018-11-30  5.1  3.0   NaN"
      ]
     },
     "execution_count": 12,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "code",
   "execution_count": 31,
   "metadata": {},
   "outputs": [],
   "source": [
    "grp = list('a'*10 + 'b'*10)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 29,
   "metadata": {},
   "outputs": [],
   "source": [
    "a = np.random.random(20)\n",
    "b = np.random.random(20)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "code",
   "execution_count": 34,
   "metadata": {},
   "outputs": [],
   "source": [
    "df = pd.DataFrame(data={'a':a, 'b': b, 'grp':grp}).reindex()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 35,
   "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>a</th>\n",
       "      <th>b</th>\n",
       "      <th>grp</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>0.127549</td>\n",
       "      <td>0.519809</td>\n",
       "      <td>a</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>0.747064</td>\n",
       "      <td>0.578003</td>\n",
       "      <td>a</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>0.700345</td>\n",
       "      <td>0.669318</td>\n",
       "      <td>a</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>0.918301</td>\n",
       "      <td>0.640542</td>\n",
       "      <td>a</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>0.145638</td>\n",
       "      <td>0.385831</td>\n",
       "      <td>a</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>0.629985</td>\n",
       "      <td>0.543243</td>\n",
       "      <td>a</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>0.823524</td>\n",
       "      <td>0.337866</td>\n",
       "      <td>a</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>0.920285</td>\n",
       "      <td>0.450894</td>\n",
       "      <td>a</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8</th>\n",
       "      <td>0.504879</td>\n",
       "      <td>0.778842</td>\n",
       "      <td>a</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9</th>\n",
       "      <td>0.551221</td>\n",
       "      <td>0.711543</td>\n",
       "      <td>a</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>10</th>\n",
       "      <td>0.426566</td>\n",
       "      <td>0.824588</td>\n",
       "      <td>b</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>11</th>\n",
       "      <td>0.696181</td>\n",
       "      <td>0.560486</td>\n",
       "      <td>b</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>12</th>\n",
       "      <td>0.368793</td>\n",
       "      <td>0.495373</td>\n",
       "      <td>b</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>13</th>\n",
       "      <td>0.184642</td>\n",
       "      <td>0.597128</td>\n",
       "      <td>b</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>14</th>\n",
       "      <td>0.143368</td>\n",
       "      <td>0.053302</td>\n",
       "      <td>b</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>15</th>\n",
       "      <td>0.515057</td>\n",
       "      <td>0.931680</td>\n",
       "      <td>b</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>16</th>\n",
       "      <td>0.936834</td>\n",
       "      <td>0.158296</td>\n",
       "      <td>b</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>17</th>\n",
       "      <td>0.842991</td>\n",
       "      <td>0.551786</td>\n",
       "      <td>b</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>18</th>\n",
       "      <td>0.733862</td>\n",
       "      <td>0.175616</td>\n",
       "      <td>b</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>19</th>\n",
       "      <td>0.268917</td>\n",
       "      <td>0.114932</td>\n",
       "      <td>b</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "           a         b grp\n",
       "0   0.127549  0.519809   a\n",
       "1   0.747064  0.578003   a\n",
       "2   0.700345  0.669318   a\n",
       "3   0.918301  0.640542   a\n",
       "4   0.145638  0.385831   a\n",
       "5   0.629985  0.543243   a\n",
       "6   0.823524  0.337866   a\n",
       "7   0.920285  0.450894   a\n",
       "8   0.504879  0.778842   a\n",
       "9   0.551221  0.711543   a\n",
       "10  0.426566  0.824588   b\n",
       "11  0.696181  0.560486   b\n",
       "12  0.368793  0.495373   b\n",
       "13  0.184642  0.597128   b\n",
       "14  0.143368  0.053302   b\n",
       "15  0.515057  0.931680   b\n",
       "16  0.936834  0.158296   b\n",
       "17  0.842991  0.551786   b\n",
       "18  0.733862  0.175616   b\n",
       "19  0.268917  0.114932   b"
      ]
     },
     "execution_count": 35,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "This is common in alpha modeling. You want the percentile rank of a stock by some value. Assuming the dataframe is one day, stocks in rows, and grp is the, say, sector indicator. What is the percentile rank for each stock in each sector by some raw value \"a\"? In this case, we want the highest number to be at the lowest percentile."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 41,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "0     1.0\n",
       "1     0.4\n",
       "2     0.5\n",
       "3     0.2\n",
       "4     0.9\n",
       "5     0.6\n",
       "6     0.3\n",
       "7     0.1\n",
       "8     0.8\n",
       "9     0.7\n",
       "10    0.6\n",
       "11    0.4\n",
       "12    0.7\n",
       "13    0.9\n",
       "14    1.0\n",
       "15    0.5\n",
       "16    0.1\n",
       "17    0.2\n",
       "18    0.3\n",
       "19    0.8\n",
       "Name: a, dtype: float64"
      ]
     },
     "execution_count": 41,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df.groupby('grp')['a'].rank(ascending=False, pct=True)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 1,
   "metadata": {},
   "outputs": [],
   "source": [
    "#shifted = df.set_index([\"date\", \"cusip\"]).unstack().shift(7).stack()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "#data['lag_t28'] = data.groupby(['id'])['demand'].transform(lambda x: x.shift(28))"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Problem: Getting lags on irregular dates \n",
    "\n",
    "You want the value from 2 months ago per asset, but each asset doesn't have all the same dates."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 16,
   "metadata": {},
   "outputs": [],
   "source": [
    "df = pd.DataFrame(data = {\n",
    "    'item': ['red','red','red','blue','blue'],\n",
    "    'dt': pd.to_datetime(['2018-01-31', '2018-02-28', '2018-03-31', '2018-01-31', '2018-03-31']),\n",
    "    's': [3.2, 4.8, 5.1, 5.3, 5.8],\n",
    "    'r': [1,2,3,4,5],\n",
    "    't': [7,8,9,10,11],\n",
    "})"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 17,
   "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>item</th>\n",
       "      <th>dt</th>\n",
       "      <th>s</th>\n",
       "      <th>r</th>\n",
       "      <th>t</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>red</td>\n",
       "      <td>2018-01-31</td>\n",
       "      <td>3.2</td>\n",
       "      <td>1</td>\n",
       "      <td>7</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>red</td>\n",
       "      <td>2018-02-28</td>\n",
       "      <td>4.8</td>\n",
       "      <td>2</td>\n",
       "      <td>8</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>red</td>\n",
       "      <td>2018-03-31</td>\n",
       "      <td>5.1</td>\n",
       "      <td>3</td>\n",
       "      <td>9</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>blue</td>\n",
       "      <td>2018-01-31</td>\n",
       "      <td>5.3</td>\n",
       "      <td>4</td>\n",
       "      <td>10</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>blue</td>\n",
       "      <td>2018-03-31</td>\n",
       "      <td>5.8</td>\n",
       "      <td>5</td>\n",
       "      <td>11</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   item         dt    s  r   t\n",
       "0   red 2018-01-31  3.2  1   7\n",
       "1   red 2018-02-28  4.8  2   8\n",
       "2   red 2018-03-31  5.1  3   9\n",
       "3  blue 2018-01-31  5.3  4  10\n",
       "4  blue 2018-03-31  5.8  5  11"
      ]
     },
     "execution_count": 17,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 23,
   "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>item</th>\n",
       "      <th>dt</th>\n",
       "      <th>s</th>\n",
       "      <th>r</th>\n",
       "      <th>t</th>\n",
       "      <th>laggy</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>red</td>\n",
       "      <td>2018-01-31</td>\n",
       "      <td>3.2</td>\n",
       "      <td>1</td>\n",
       "      <td>7</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>red</td>\n",
       "      <td>2018-02-28</td>\n",
       "      <td>4.8</td>\n",
       "      <td>2</td>\n",
       "      <td>8</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>red</td>\n",
       "      <td>2018-03-31</td>\n",
       "      <td>5.1</td>\n",
       "      <td>3</td>\n",
       "      <td>9</td>\n",
       "      <td>3.2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>blue</td>\n",
       "      <td>2018-01-31</td>\n",
       "      <td>5.3</td>\n",
       "      <td>4</td>\n",
       "      <td>10</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>blue</td>\n",
       "      <td>2018-03-31</td>\n",
       "      <td>5.8</td>\n",
       "      <td>5</td>\n",
       "      <td>11</td>\n",
       "      <td>5.3</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   item         dt    s  r   t  laggy\n",
       "0   red 2018-01-31  3.2  1   7    NaN\n",
       "1   red 2018-02-28  4.8  2   8    NaN\n",
       "2   red 2018-03-31  5.1  3   9    3.2\n",
       "3  blue 2018-01-31  5.3  4  10    NaN\n",
       "4  blue 2018-03-31  5.8  5  11    5.3"
      ]
     },
     "execution_count": 23,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df.assign(laggy=lambda df: df.set_index(['dt']).groupby('item')['s'].shift(2, 'M').reindex(pd.MultiIndex.from_frame(df[['item','dt']])).values)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3",
   "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.6.6"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 2
}