RollingTimeGroup.ipynb

{
 "cells": [
  {
   "cell_type": "code",
   "execution_count": 9,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "import pandas as pd\n",
    "import numpy as np"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 37,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "# Example\n",
    "n = 10\n",
    "\n",
    "ls = []\n",
    "for index in range(n):\n",
    "    hours = np.random.choice(np.arange(24), size=n)\n",
    "    minutes = np.random.choice(np.arange(60), size = n )\n",
    "    seconds = np.random.choice(np.arange(60), size = n)\n",
    "    \n",
    "    idx = [pd.datetime(2011, 2, 7, h,m,s) for h,m,s in zip(hours,minutes, seconds)]\n",
    "    values = np.random.choice([1,2,3], size=n)\n",
    "    \n",
    "    ls.append(pd.Series(values, index=pd.Index(idx), name=\"purchases\"))\n",
    "\n",
    "    \n",
    "df = pd.concat(ls, axis=0, keys=[\"cat\"+str(i) for i in range(n)])\n",
    "df.index.names = [\"cat\", \"time\"]\n",
    "df = df.reset_index()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 38,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>cat</th>\n",
       "      <th>time</th>\n",
       "      <th>purchases</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>cat0</td>\n",
       "      <td>2011-02-07 15:20:44</td>\n",
       "      <td>3</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>cat0</td>\n",
       "      <td>2011-02-07 08:42:11</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>cat0</td>\n",
       "      <td>2011-02-07 08:24:05</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>cat0</td>\n",
       "      <td>2011-02-07 18:17:30</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>cat0</td>\n",
       "      <td>2011-02-07 10:23:45</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "    cat                time  purchases\n",
       "0  cat0 2011-02-07 15:20:44          3\n",
       "1  cat0 2011-02-07 08:42:11          1\n",
       "2  cat0 2011-02-07 08:24:05          1\n",
       "3  cat0 2011-02-07 18:17:30          1\n",
       "4  cat0 2011-02-07 10:23:45          1"
      ]
     },
     "execution_count": 38,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 66,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "t = df.sort_values([\"cat\", \"time\"])\n",
    "cat = t.cat.unique()\n",
    "t = t[t.cat.isin(cat[:100000])].reset_index(drop=True)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 93,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Wall time: 11 ms\n"
     ]
    }
   ],
   "source": [
    "%%time\n",
    "##### Rolling window with my functions and groupby - fastest\n",
    "ls1 = t.groupby(\"cat\").apply(lambda df: (df.cat.unique()[0], rolling_sum(df.purchases.values, df.time.values, dt=np.timedelta64(1, 'm'))))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 98,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Wall time: 215 ms\n"
     ]
    }
   ],
   "source": [
    "%%time\n",
    "##### Rolling window with forward fill and groupby loop - less slow\n",
    "t2 = t.set_index([\"time\"])\n",
    "ls2 = t2.groupby(\"cat\")[\"purchases\"].apply(lambda df: (\n",
    "        df.resample(\"1s\")\n",
    "        .ffill(limit=0)\n",
    "        .rolling(window=60, min_periods=1)\n",
    "        .agg(np.nansum)\n",
    "        ))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 99,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "[2, 2, 1, 1, 1, 3, 1, 1, 1, 1]"
      ]
     },
     "execution_count": 99,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "ls1[0][1]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 91,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "(1090,)"
      ]
     },
     "execution_count": 91,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "ls2.dropna().shape"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 100,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "cat   time               \n",
       "cat0  2011-02-07 00:50:17    2.0\n",
       "      2011-02-07 00:50:18    2.0\n",
       "      2011-02-07 00:50:19    2.0\n",
       "      2011-02-07 00:50:20    2.0\n",
       "      2011-02-07 00:50:21    2.0\n",
       "dtype: float64"
      ]
     },
     "execution_count": 100,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "ls2.dropna().head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 14,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "# My cleanest code\n",
    "\n",
    "def window_index(times, dt):\n",
    "    \"\"\"\n",
    "    Returns two lists - forward window f and backward window p defined as follows:\n",
    "    For each index i in times: \n",
    "        f[i] is the smallest index j <= i such that (times[i]-times[j]) <= dt\n",
    "        p[i] is the largest index k >= i such that (times[k]-times[i]) <= dt\n",
    "    \n",
    "    Parameters\n",
    "    ------------------------\n",
    "    times: list of np.datetime64  of non decreasing times\n",
    "    dt: np.datetime64\n",
    "    \n",
    "    Returns\n",
    "    -------------------------\n",
    "    f: list of ints - forward window for each index as above\n",
    "    p: list of ints - past window for each index as above\n",
    "    \"\"\"\n",
    "    l = len(times)\n",
    "    f = np.arange(0, l)\n",
    "    p = np.arange(0, l)\n",
    "    \n",
    "    i,j = 0,0\n",
    "    while (j < l):\n",
    "        diff = times[j] - times[i]\n",
    "        if (diff <= dt):\n",
    "            p[j] = i\n",
    "            j = j+1\n",
    "        elif (diff > dt):\n",
    "            f[i] = (j-1)\n",
    "            i = i+1\n",
    "    return p,f\n",
    "\n",
    " \n",
    "def sum_future(values, jump):\n",
    "    \"\"\" Given list of forward window compute sum corresponding to that window for each index \"\"\"\n",
    "    totals = [np.sum(values[0:jump[0]+1])]\n",
    "    for i in range(1, len(values)):\n",
    "        totals.append(totals[i-1] - values[i-1] + np.sum(values[jump[i-1]+1:jump[i]+1]))\n",
    "    return totals\n",
    "\n",
    "   \n",
    "def sum_past(values, jump):\n",
    "    \"\"\" Given list of backward windows compute sum corresponding to that window for each index \"\"\"\n",
    "    l = len(values)-1\n",
    "    return sum_future(values[::-1], [l-j for j in jump[::-1]])[::-1]\n",
    "\n",
    "\n",
    "# From these three functions we can do lots of things including compute the rolling mean\n",
    "def rolling_mean_window(values, times, dt):\n",
    "    p,f = window_index(times, dt)\n",
    "    return (np.array(sum_future(values,f)) + np.array(sum_past(values, p)) - np.array(values))/(f-p+1)\n",
    "\n",
    "def rolling_sum(values, times, dt):\n",
    "    p,f = window_index(times, dt)\n",
    "    return sum_future(values,f)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "%%time\n",
    "##### Rolling window with my functions and for loops - slow\n",
    "\n",
    "t1 = trans.reset_index().sort_values([\"AdvisorID\", \"Date\"]).set_index(\"Date\")\n",
    "\n",
    "advisorID = t1.AdvisorID.unique()\n",
    "ls = []\n",
    "for a in advisorID[:1000]:\n",
    "    df = t1[t1.AdvisorID == a].Transactions\n",
    "    ls.append((\n",
    "      rolling_sum(df.values.ravel(), df.index.values.ravel(), dt=np.timedelta64(1, 'm')),\n",
    "        a))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "code_folding": [],
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "##### Rolling window with forward fill and for loop - SLOW\n",
    "t1 = trans.reset_index().sort_values([\"AdvisorID\", \"Date\"]).set_index(\"Date\")\n",
    "\n",
    "advisorID = t1.AdvisorID.unique()\n",
    "ls = []\n",
    "for a in advisorID[:1000]:\n",
    "    ls.append((\n",
    "        (t1[t1.AdvisorID == a].Transactions)\n",
    "     .resample(\"1M\")\n",
    "     .ffill(limit=0)\n",
    "     .rolling(window=12, min_periods=1)\n",
    "     .agg(np.nansum),\n",
    "        a)\n",
    "     )\n",
    "    \n",
    "    \n",
    "s = (l[0] for l in ls)\n",
    "n = (l[1] for l in ls)\n",
    "\n",
    "\n",
    "df = pd.concat(s, keys=n, axis=0,).reset_index(level=1)\n",
    "df.index.name = \"AdvisorID\"\n",
    "df = df.reset_index()\n",
    "df = pd.merge(t1.reset_index(), df, how=\"outer\", on=[\"AdvisorID\", \"Date\"], suffixes=[\"\", \"_agg\"])"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": true
   },
   "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.5.1"
  },
  "toc": {
   "toc_cell": false,
   "toc_number_sections": true,
   "toc_threshold": 6,
   "toc_window_display": false
  }
 },
 "nbformat": 4,
 "nbformat_minor": 0
}