{
 "cells": [
  {
   "cell_type": "markdown",
   "id": "5765bcff",
   "metadata": {},
   "source": [
    "MBS Example queries\n",
    "==========================\n",
    "\n",
    "Load iris\n",
    "----------"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 1,
   "id": "04f0d4f4",
   "metadata": {},
   "outputs": [],
   "source": [
    "load_ext iris"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "7e9e9098",
   "metadata": {},
   "source": [
    "Import libraries\n",
    "------------------"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "id": "93228e85",
   "metadata": {},
   "outputs": [],
   "source": [
    "import py1010\n",
    "import tenFrame as tf\n",
    "import pandas as pd\n",
    "import numpy as np\n",
    "session = py1010.Session(\"1010data URL\", \"USERNAME\", \"PASSWORD\", py1010.POSSESS)"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "bc674356",
   "metadata": {},
   "source": [
    "MBS query examples\n",
    "--------------------------\n",
    "[Example 1: Agency pools](#Example-1:-Agency-pools)\n",
    "\n",
    "[Example 2: Historical cohort](#Example-2:-Historical-cohort)\n",
    "\n",
    "[Example 3: Cohort and S curve](#Example-3:-Cohort-and-S-curve)\n",
    "\n",
    "[Example 4: CPR by occupancy type](#Example-4:-CPR-by-occupancy-type)\n",
    "\n",
    "[Example 5: GNMA prepayment and buyout CPR](#Example-5:-GNMA-prepayment-and-buyout-CPR)\n",
    "\n",
    "[Example 6: Loan level FHLMC CPR](#Example-6:-Loan-level-FHLMC-CPR)\n"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "cfd6cbcd",
   "metadata": {},
   "source": [
    "Example 1: Agency pools\n",
    "-----------------------------\n",
    "\n",
    "This example computes the number of Agency Pools for each agency/gse (FNMA/FHLMC/GNMA) that have loans in each state for each month. The results show the outstanding balance of those loans and the number of loans. For instance, in Sept of 2022, there were 106,117 Freddie Mac Pools that had at least one active loan in CA, there was an approximately $510 billion balance and 1.578 million loans."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "id": "ec2748da",
   "metadata": {},
   "outputs": [
    {
     "ename": "TentenException",
     "evalue": "('function run failed, returned 1 (Table is no longer accessible: pub.fin.embs.combined.stnd_pool.sec)', 1)",
     "output_type": "error",
     "traceback": [
      "\u001b[0;31m---------------------------------------------------------------------------\u001b[0m",
      "\u001b[0;31mTentenException\u001b[0m                           Traceback (most recent call last)",
      "\u001b[0;32m/var/folders/5v/clfmrnm52rl5p8570x__xm140000gq/T/ipykernel_12008/279990301.py\u001b[0m in \u001b[0;36m<module>\u001b[0;34m\u001b[0m\n\u001b[1;32m      3\u001b[0m \u001b[0mgeo\u001b[0m \u001b[0;34m=\u001b[0m \u001b[0mgeo\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0mmerge\u001b[0m\u001b[0;34m(\u001b[0m\u001b[0;34m\"pub.fin.embs.combined.stnd_pool.sec\"\u001b[0m\u001b[0;34m,\u001b[0m \u001b[0mon\u001b[0m\u001b[0;34m=\u001b[0m\u001b[0;34m\"issueid\"\u001b[0m\u001b[0;34m)\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[1;32m      4\u001b[0m \u001b[0mgeo\u001b[0m \u001b[0;34m=\u001b[0m \u001b[0mgeo\u001b[0m\u001b[0;34m[\u001b[0m\u001b[0mgeo\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0mcolltype\u001b[0m \u001b[0;34m==\u001b[0m \u001b[0;34m\"LOAN\"\u001b[0m\u001b[0;34m]\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[0;32m----> 5\u001b[0;31m \u001b[0mgeo\u001b[0m \u001b[0;34m=\u001b[0m \u001b[0mgeo\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0mgroupby\u001b[0m\u001b[0;34m(\u001b[0m\u001b[0;34m[\u001b[0m\u001b[0;34m\"value\"\u001b[0m\u001b[0;34m,\u001b[0m \u001b[0;34m\"agency\"\u001b[0m\u001b[0;34m,\u001b[0m \u001b[0;34m\"effdt\"\u001b[0m\u001b[0;34m]\u001b[0m\u001b[0;34m)\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0magg\u001b[0m\u001b[0;34m(\u001b[0m\u001b[0;34m{\u001b[0m\u001b[0;34m\"value\"\u001b[0m\u001b[0;34m:\u001b[0m \u001b[0;34m\"cnt\"\u001b[0m\u001b[0;34m,\u001b[0m \u001b[0;34m\"rpb\"\u001b[0m\u001b[0;34m:\u001b[0m \u001b[0;34m\"sum\"\u001b[0m\u001b[0;34m,\u001b[0m \u001b[0;34m\"loans\"\u001b[0m\u001b[0;34m:\u001b[0m \u001b[0;34m\"sum\"\u001b[0m\u001b[0;34m}\u001b[0m\u001b[0;34m)\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[0m\u001b[1;32m      6\u001b[0m \u001b[0mgeo2\u001b[0m \u001b[0;34m=\u001b[0m \u001b[0mgeo\u001b[0m\u001b[0;34m[\u001b[0m\u001b[0mgeo\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0mrpb_sum\u001b[0m \u001b[0;34m==\u001b[0m \u001b[0mgeo\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0mgroupby\u001b[0m\u001b[0;34m(\u001b[0m\u001b[0;34m[\u001b[0m\u001b[0;34m\"value\"\u001b[0m\u001b[0;34m,\u001b[0m \u001b[0;34m\"agency\"\u001b[0m\u001b[0;34m]\u001b[0m\u001b[0;34m)\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0mrpb_sum\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0mmax\u001b[0m\u001b[0;34m(\u001b[0m\u001b[0madjoin\u001b[0m\u001b[0;34m=\u001b[0m\u001b[0;32mTrue\u001b[0m\u001b[0;34m)\u001b[0m\u001b[0;34m[\u001b[0m\u001b[0;34m'rpb_sum_max'\u001b[0m\u001b[0;34m]\u001b[0m\u001b[0;34m]\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[1;32m      7\u001b[0m \u001b[0;31m# Hide that last column.\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n",
      "\u001b[0;32m~/opt/anaconda3/lib/python3.9/site-packages/tenFrame/tenFrame.py\u001b[0m in \u001b[0;36magg\u001b[0;34m(self, agg_info, newOp, breaks, columnparams, rollup, breakelts, **kwargs)\u001b[0m\n\u001b[1;32m   3727\u001b[0m             \u001b[0mkwargs\u001b[0m\u001b[0;34m[\u001b[0m\u001b[0;34m'cbreaks'\u001b[0m\u001b[0;34m]\u001b[0m \u001b[0;34m=\u001b[0m \u001b[0moneorlist\u001b[0m\u001b[0;34m(\u001b[0m\u001b[0mself\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0mcbreaks\u001b[0m\u001b[0;34m,\u001b[0m \u001b[0mjoin\u001b[0m\u001b[0;34m=\u001b[0m\u001b[0;32mFalse\u001b[0m\u001b[0;34m,\u001b[0m \u001b[0mframe\u001b[0m\u001b[0;34m=\u001b[0m\u001b[0mself\u001b[0m\u001b[0;34m)\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[1;32m   3728\u001b[0m         \u001b[0mbreaks\u001b[0m \u001b[0;34m=\u001b[0m \u001b[0moneorlist\u001b[0m\u001b[0;34m(\u001b[0m\u001b[0mbreaks\u001b[0m\u001b[0;34m,\u001b[0m \u001b[0mjoin\u001b[0m\u001b[0;34m=\u001b[0m\u001b[0;32mFalse\u001b[0m\u001b[0;34m,\u001b[0m \u001b[0mframe\u001b[0m\u001b[0;34m=\u001b[0m\u001b[0mself\u001b[0m\u001b[0;34m)\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[0;32m-> 3729\u001b[0;31m         rv = self._core_agg(agg_info, breaks=breaks, columnparams=columnparams,\n\u001b[0m\u001b[1;32m   3730\u001b[0m                             rollup=rollup, breakelts=breakelts, **kwargs)\n\u001b[1;32m   3731\u001b[0m         \u001b[0;31m## Aggregation clears grouping!!  In the return value only?\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n",
      "\u001b[0;32m~/opt/anaconda3/lib/python3.9/site-packages/tenFrame/tenFrame.py\u001b[0m in \u001b[0;36m_core_agg\u001b[0;34m(self, agg_info, breaks, select, order, columnparams, rollup, breakelts, forcetab, noself, failonU, adjoin, *args, **kwargs)\u001b[0m\n\u001b[1;32m   3988\u001b[0m         \u001b[0meverorder\u001b[0m \u001b[0;34m=\u001b[0m \u001b[0;34m(\u001b[0m\u001b[0morder\u001b[0m \u001b[0;32mis\u001b[0m \u001b[0;32mnot\u001b[0m \u001b[0;32mNone\u001b[0m\u001b[0;34m)\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[1;32m   3989\u001b[0m         \u001b[0;32mif\u001b[0m \u001b[0;32mnot\u001b[0m \u001b[0mtab_gfun\u001b[0m\u001b[0;34m:\u001b[0m                  \u001b[0;31m# still unforced\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[0;32m-> 3990\u001b[0;31m             \u001b[0;32mif\u001b[0m \u001b[0mself\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0msegbybreaks\u001b[0m\u001b[0;34m(\u001b[0m\u001b[0mbreaks\u001b[0m\u001b[0;34m)\u001b[0m\u001b[0;34m:\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[0m\u001b[1;32m   3991\u001b[0m                 \u001b[0mtab_gfun\u001b[0m \u001b[0;34m=\u001b[0m \u001b[0;34m\"gfun\"\u001b[0m \u001b[0;31m# that right?  what about things sharing names?\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[1;32m   3992\u001b[0m         \u001b[0;32mif\u001b[0m \u001b[0mtab_gfun\u001b[0m \u001b[0;34m==\u001b[0m \u001b[0;34m\"gfun\"\u001b[0m\u001b[0;34m:\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n",
      "\u001b[0;32m~/opt/anaconda3/lib/python3.9/site-packages/tenFrame/tenFrame.py\u001b[0m in \u001b[0;36msegbybreaks\u001b[0;34m(self, breaks)\u001b[0m\n\u001b[1;32m   4088\u001b[0m             \u001b[0;32mreturn\u001b[0m \u001b[0;32mFalse\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[1;32m   4089\u001b[0m         \u001b[0;32mtry\u001b[0m\u001b[0;34m:\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[0;32m-> 4090\u001b[0;31m             \u001b[0;32mif\u001b[0m \u001b[0mself\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0mnumsegs\u001b[0m\u001b[0;34m(\u001b[0m\u001b[0;34m)\u001b[0m \u001b[0;34m<=\u001b[0m \u001b[0;36m1\u001b[0m\u001b[0;34m:\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[0m\u001b[1;32m   4091\u001b[0m                 \u001b[0;32mreturn\u001b[0m \u001b[0;32mTrue\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[1;32m   4092\u001b[0m         \u001b[0;32mexcept\u001b[0m \u001b[0mRuntimeError\u001b[0m\u001b[0;34m:\u001b[0m              \u001b[0;31m# presumably offline.\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n",
      "\u001b[0;32m~/opt/anaconda3/lib/python3.9/site-packages/tenFrame/tenFrame.py\u001b[0m in \u001b[0;36mnumsegs\u001b[0;34m(self)\u001b[0m\n\u001b[1;32m   5156\u001b[0m             \u001b[0;32mreturn\u001b[0m \u001b[0;36m1\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[1;32m   5157\u001b[0m         \u001b[0mtab\u001b[0m \u001b[0;34m=\u001b[0m \u001b[0mself\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0mbasetableinfo\u001b[0m\u001b[0;34m(\u001b[0m\u001b[0;34m)\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[0;32m-> 5158\u001b[0;31m         \u001b[0;32mreturn\u001b[0m \u001b[0mtab\u001b[0m\u001b[0;34m[\u001b[0m\u001b[0;34m'segs'\u001b[0m\u001b[0;34m]\u001b[0m\u001b[0;34m[\u001b[0m\u001b[0;36m0\u001b[0m\u001b[0;34m]\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[0m\u001b[1;32m   5159\u001b[0m \u001b[0;34m\u001b[0m\u001b[0m\n\u001b[1;32m   5160\u001b[0m     \u001b[0;32mdef\u001b[0m \u001b[0m_numsegs_metadata\u001b[0m\u001b[0;34m(\u001b[0m\u001b[0mself\u001b[0m\u001b[0;34m)\u001b[0m\u001b[0;34m:\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n",
      "\u001b[0;32m~/opt/anaconda3/lib/python3.9/site-packages/tenFrame/tenFrame.py\u001b[0m in \u001b[0;36m__getitem__\u001b[0;34m(self, index)\u001b[0m\n\u001b[1;32m   7199\u001b[0m         \u001b[0;32mif\u001b[0m \u001b[0;32mnot\u001b[0m \u001b[0mself\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0monline\u001b[0m\u001b[0;34m(\u001b[0m\u001b[0;34m)\u001b[0m\u001b[0;34m:\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[1;32m   7200\u001b[0m             \u001b[0;32mraise\u001b[0m \u001b[0mRuntimeError\u001b[0m\u001b[0;34m(\u001b[0m\u001b[0;34m\"Values not available offline\"\u001b[0m\u001b[0;34m)\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[0;32m-> 7201\u001b[0;31m         \u001b[0;32mreturn\u001b[0m \u001b[0mself\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0mcolumn\u001b[0m\u001b[0;34m[\u001b[0m\u001b[0mindex\u001b[0m\u001b[0;34m]\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[0m\u001b[1;32m   7202\u001b[0m \u001b[0;34m\u001b[0m\u001b[0m\n\u001b[1;32m   7203\u001b[0m     \u001b[0;32mdef\u001b[0m \u001b[0m__getattr__\u001b[0m\u001b[0;34m(\u001b[0m\u001b[0mself\u001b[0m\u001b[0;34m,\u001b[0m \u001b[0mname\u001b[0m\u001b[0;34m)\u001b[0m\u001b[0;34m:\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n",
      "\u001b[0;32m~/opt/anaconda3/lib/python3.9/site-packages/tenFrame/tenFrame.py\u001b[0m in \u001b[0;36mcolumn\u001b[0;34m(self)\u001b[0m\n\u001b[1;32m   6967\u001b[0m         type to be 'i'.\"\"\"\n\u001b[1;32m   6968\u001b[0m         \u001b[0;31m# returns a py1010 Column object!\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[0;32m-> 6969\u001b[0;31m         \u001b[0mself\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0mframe\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0mrun\u001b[0m\u001b[0;34m(\u001b[0m\u001b[0;34m)\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[0m\u001b[1;32m   6970\u001b[0m         \u001b[0;31m# FOR SERVER: return a mock Column\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[1;32m   6971\u001b[0m         \u001b[0;32mif\u001b[0m \u001b[0mself\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0monline\u001b[0m\u001b[0;34m(\u001b[0m\u001b[0;34m)\u001b[0m\u001b[0;34m:\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n",
      "\u001b[0;32m~/opt/anaconda3/lib/python3.9/site-packages/tenFrame/tenFrame.py\u001b[0m in \u001b[0;36mrun\u001b[0;34m(self, force)\u001b[0m\n\u001b[1;32m   2347\u001b[0m         \u001b[0;32mif\u001b[0m \u001b[0;34m(\u001b[0m\u001b[0mself\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0mdirty\u001b[0m \u001b[0;32mor\u001b[0m \u001b[0mforce\u001b[0m\u001b[0;34m)\u001b[0m \u001b[0;32mand\u001b[0m \u001b[0mself\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0monline\u001b[0m\u001b[0;34m(\u001b[0m\u001b[0;34m)\u001b[0m\u001b[0;34m:\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[1;32m   2348\u001b[0m             \u001b[0mself\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0mrefreshquery\u001b[0m\u001b[0;34m(\u001b[0m\u001b[0;34m)\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[0;32m-> 2349\u001b[0;31m             \u001b[0mself\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0mquery_\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0mrun\u001b[0m\u001b[0;34m(\u001b[0m\u001b[0;34m)\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[0m\u001b[1;32m   2350\u001b[0m             \u001b[0mself\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0mdirty\u001b[0m \u001b[0;34m=\u001b[0m \u001b[0;32mFalse\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[1;32m   2351\u001b[0m             \u001b[0;31m# refresh column info.  the metadata came back as part of the run.\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n",
      "\u001b[0;32mpy1010.pyx\u001b[0m in \u001b[0;36mpy1010.logit.f\u001b[0;34m()\u001b[0m\n",
      "\u001b[0;32mpy1010.pyx\u001b[0m in \u001b[0;36mpy1010.logit.f\u001b[0;34m()\u001b[0m\n",
      "\u001b[0;32mpy1010.pyx\u001b[0m in \u001b[0;36mpy1010.except_zero.f\u001b[0;34m()\u001b[0m\n",
      "\u001b[0;31mTentenException\u001b[0m: ('function run failed, returned 1 (Table is no longer accessible: pub.fin.embs.combined.stnd_pool.sec)', 1)"
     ]
    }
   ],
   "source": [
    "geo = tf.TenFrame(session, \"pub.fin.embs.combined.stnd_pool.geo\")\n",
    "geo = geo[(geo.rectype == \"G\") & (geo.value != \"ZZ\")]\n",
    "geo = geo.merge(\"pub.fin.embs.combined.stnd_pool.sec\", on=\"issueid\")\n",
    "geo = geo[geo.colltype == \"LOAN\"]\n",
    "geo = geo.groupby([\"value\", \"agency\", \"effdt\"]).agg({\"value\": \"cnt\", \"rpb\": \"sum\", \"loans\": \"sum\"})\n",
    "geo2 = geo[geo.rpb_sum == geo.groupby([\"value\", \"agency\"]).rpb_sum.max(adjoin=True)['rpb_sum_max']]\n",
    "# Hide that last column.\n",
    "rv = geo2 = geo2[[\"value\", \"agency\", \"effdt\", \"value_cnt\", \"rpb_sum\", \"loans_sum\"]]\n",
    "geo2 = geo2.sort_values(by=[\"agency\", \"value\", \"effdt\"], ascending=True)\n",
    "geo2"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "71b75086",
   "metadata": {},
   "source": [
    "Example 2: Historical cohort\n",
    "----------------------------------\n",
    "This example shows the historical cohort (product, origination year, coupon) prepayment speeds (CPR) for the largest 10 cohorts (determined by the largest monthly outstanding balance for each cohort).\n",
    "\n",
    "**_Note:_** g_tshift() is a 1010data function that returns the value of the first row that is a specified time period before (or after) the current row within a given group."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "id": "158b236d",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<iframe height=\"500\" width=\"985\" src=\"https://www2.corp.1010data.com/cgi-bin/beta-latest/quickapp.k?sid_=1794223451&epw_=_______ec5908faf9a6a0c87a46b0b298db9205b88d5e8fa70841f9c8bbd4cd0ed2276c10ac3cc57eee40be903b16c28db0d029ecad1a3e8f54f7710c4f903c13651201&uid_=mbs_cstigum&tag_=3\"/>"
      ],
      "text/plain": [
       "        effdt  cpr_first  balance_sum_first       m0_0          m1_0  \\\n",
       "0  2023-05-01   7.551422       6.155347e+09   7.551422  6.155347e+09   \n",
       "1  2023-04-01  10.176298       6.219424e+09  10.176298  6.219424e+09   \n",
       "2  2023-03-01   6.923035       6.299078e+09   6.923035  6.299078e+09   \n",
       "3  2023-02-01   6.638849       6.360679e+09   6.638849  6.360679e+09   \n",
       "4  2023-01-01   8.560410       6.421076e+09   8.560410  6.421076e+09   \n",
       "5  2022-12-01   7.965184       6.493097e+09   7.965184  6.493097e+09   \n",
       "6  2022-11-01   9.171749       6.562180e+09   9.171749  6.562180e+09   \n",
       "7  2022-10-01   9.331575       6.639110e+09   9.331575  6.639110e+09   \n",
       "8  2022-09-01  12.208626       6.717747e+09  12.208626  6.717747e+09   \n",
       "9  2022-08-01  11.193285       6.815417e+09  11.193285  6.815417e+09   \n",
       "\n",
       "        m0_1          m1_1       m0_2          m1_2      m0_3  ...      m0_4  \\\n",
       "0   8.364425  3.414427e+09   7.940901  8.710077e+09  5.034537  ...  4.447350   \n",
       "1  10.253989  3.463122e+09   8.862895  8.803715e+09  5.577155  ...  4.767520   \n",
       "2   8.491458  3.518356e+09   7.014888  8.905567e+09  4.086964  ...  3.354534   \n",
       "3   8.200441  3.568420e+09   6.443514  8.993283e+09  3.633648  ...  2.899536   \n",
       "4   9.337242  3.617976e+09   7.983298  9.076968e+09  4.722760  ...  3.664120   \n",
       "5   8.737023  3.671774e+09   7.984983  9.173842e+09  5.044684  ...  3.839919   \n",
       "6   9.725223  3.724057e+09   8.897952  9.271504e+09  6.165357  ...  4.577213   \n",
       "7  10.927611  3.780252e+09   9.891982  9.377727e+09  7.503026  ...  5.663559   \n",
       "8  12.224130  3.841322e+09  11.732214  9.493578e+09  8.752840  ...  6.267672   \n",
       "9  11.680567  3.907882e+09  10.771443  9.627134e+09  8.938142  ...  6.026088   \n",
       "\n",
       "           m1_4       m0_5          m1_5      m0_6          m1_6      m0_7  \\\n",
       "0  2.773405e+11   7.458452  1.264448e+11  6.528973  2.097927e+11  5.196293   \n",
       "1  2.789069e+11   8.036866  1.275271e+11  6.883293  2.112335e+11  5.428822   \n",
       "2  2.805580e+11   6.112413  1.286847e+11  4.940399  2.126703e+11  4.079877   \n",
       "3  2.818702e+11   5.245387  1.296276e+11  4.286861  2.136593e+11  3.534672   \n",
       "4  2.830753e+11   6.563089  1.304762e+11  5.135386  2.148522e+11  4.658123   \n",
       "5  2.844705e+11   6.989057  1.314822e+11  5.459254  2.162104e+11  4.988750   \n",
       "6  2.859134e+11   9.110708  1.325453e+11  7.127829  2.176375e+11  6.060476   \n",
       "7  2.875456e+11  11.114502  1.338729e+11  9.000910  2.193973e+11  7.427075   \n",
       "8  2.894606e+11  13.363483  1.354641e+11  9.724405  2.215454e+11  8.870006   \n",
       "9  2.915363e+11  13.370939  1.373659e+11  9.287666  2.238616e+11  8.883702   \n",
       "\n",
       "           m1_7      m0_8          m1_8  \n",
       "0  2.219410e+11  4.481145  3.205426e+11  \n",
       "1  2.233633e+11  4.741509  3.223613e+11  \n",
       "2  2.248382e+11  3.356946  3.242612e+11  \n",
       "3  2.260537e+11  2.847907  3.257775e+11  \n",
       "4  2.271662e+11  3.613349  3.271547e+11  \n",
       "5  2.285050e+11  3.854936  3.287515e+11  \n",
       "6  2.299160e+11  4.693048  3.304224e+11  \n",
       "7  2.315523e+11  5.672905  3.323160e+11  \n",
       "8  2.334830e+11  6.510854  3.345310e+11  \n",
       "9  2.357358e+11  6.207307  3.370083e+11  \n",
       "\n",
       "[10 rows x 21 columns]"
      ]
     },
     "execution_count": 6,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "fh = tf.TenFrame(session, \"pub.fin.embs.combined.stnd_pool.fh\")\n",
    "fh = fh.merge(\"pub.fin.embs.combined.stnd_pool.sec\", on=\"issueid\")\n",
    "fh = fh[fh.colltype == 'LOAN']\n",
    "fh = fh.merge(\"pub.fin.embs.combined.stnd_pool.sc\", on=\"secid\")\n",
    "fh = fh[fh[\"coupontype\"] == 'FIX']\n",
    "fh[\"balance\"] = fh.issamt * fh.fctr\n",
    "fh[\"wam2\"] = tf.select([fh.product.str.endswith('BALL')],\n",
    "                    [fh.wam + (fh.waolterm - fh.origwam)], fh.wam)\n",
    "fh[\"n_schedbal\"] = fh.balance * fh.wac.amort(fh.wam2, 1)\n",
    "# Adding aggregated columns needs to be done via \"adjoin\"\n",
    "fh = fh.groupby(\"issueid\").effdt.tshift(\"M\", fh.n_schedbal, -1, name=\"schedbal\", adjoin=True)\n",
    "fh = fh[fh.schedbal.notnull()]\n",
    "fh = fh[fh.coupon - tf.func.int(fh.coupon) == 0.5]\n",
    "#Note the use of columnparams below, in order to supply the \"weight\"\n",
    "# parameter to the wavg (weighted average) function.  When there are\n",
    "# columnparams, any columns which don't have columnparams applied to them\n",
    "# are moved to the end, so we have to add extra entries to columnparams for\n",
    "# the first two columns to keep them in place.\n",
    "fh2 = fh.groupby([\"effdt\", \"coupon\", \"product\", \"productionyear\"]).agg(\n",
    "                 {\"balance\": \"sum\",\n",
    "                  \"schedbal\": \"sum\",\n",
    "                  \"wac\": \"wavg\",\n",
    "                  \"issueid\": \"cnt\"},\n",
    "                 columnparams=[\n",
    "                     {\"source\":\"balance\", \"fun\":\"sum\"},\n",
    "                     {\"source\":\"schedbal\", \"fun\":\"sum\"},\n",
    "                     {\"source\":\"wac\", \"fun\":\"wavg\", \"weight\":\"balance\"}])\n",
    "fh2[\"smm\"] = 1 - fh2.balance_sum / fh2.schedbal_sum\n",
    "fh2[\"cpr\"] = tf.func.smm2cpr(100 * fh.smm)\n",
    "fh2 = fh2.groupby([\"coupon\", \"product\", \"productionyear\"]).balance_sum.max(name=\"hibal\", adjoin=True)\n",
    "fh2 = fh2.hibal.rankuniq(name=\"rank\", adjoin=True)\n",
    "fh2 = fh2[fh2.rank < 10]\n",
    "fh3 = fh2.groupby(\"effdt\").agg({\"cpr\":\"first\", \"balance_sum\":\"first\"},\n",
    "   cbreaks=[\"coupon\", \"product\", \"productionyear\"])\n",
    "fh3 = fh3.sort_values(\"effdt\", ascending=False)\n",
    "fh3"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "c7f79a6b",
   "metadata": {},
   "source": [
    "Example 3: Cohort and S curve\n",
    "-------------------------------------\n",
    "This example shows cohort (product, origination year, and coupon) and S curve (CPR by rate incentive) with 50 basis point incentive buckets."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "id": "990470c8",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<iframe height=\"500\" width=\"985\" src=\"https://www2.corp.1010data.com/cgi-bin/beta-latest/quickapp.k?sid_=1794223451&epw_=_______ec5908faf9a6a0c87a46b0b298db9205b88d5e8fa70841f9c8bbd4cd0ed2276c10ac3cc57eee40be903b16c28db0d029ecad1a3e8f54f7710c4f903c13651201&uid_=mbs_cstigum&tag_=4\"/>"
      ],
      "text/plain": [
       "    product  coupon  productionyear  rincentive       cpr\n",
       "0  GNMII30M     1.5            2021        -5.0  3.455016\n",
       "1    FHLN30     1.5            2020        -4.5  3.149840\n",
       "2    FHLN20     1.5            2020        -4.5  2.234404\n",
       "3  FHLR30JM     1.5            2020        -4.5  1.261580\n",
       "4    FHLN30     1.5            2021        -4.5  2.487726\n",
       "5    FHLN20     1.5            2021        -4.5  2.401478\n",
       "6  FHLR30JM     1.5            2021        -4.5  1.186256\n",
       "7    FHLN30     2.0            2021        -4.5  2.844877\n",
       "8    FHLN15     1.0            2021        -4.5  2.409650\n",
       "9  GNMII30M     2.0            2020        -4.5  5.666838"
      ]
     },
     "execution_count": 7,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "fh = tf.TenFrame(session, \"pub.fin.embs.combined.stnd_pool.fh\")\n",
    "fh = fh.merge(\"pub.fin.embs.combined.stnd_pool.sec\", on=\"secid\")\n",
    "fh = fh.merge(\"pub.fin.embs.combined.stnd_pool.sc\", on=\"secid\")\n",
    "fh['linkmonth'] = fh.effdt.shiftmonths(-1,0).yrmo()\n",
    "fh = fh.merge(\"pub.fin.econ.rates.pmms.nm\", on=\"linkmonth\", right_on=\"month\")\n",
    "fh['origterm'] = fh.origwam + fh.origwala\n",
    "fh['benchmark_rate'] = tf.select([fh.coupontype == 'ARM', fh.origterm <= 180],\n",
    "                               [fh.r51, fh.r15], fh.r30)\n",
    "fh[\"incentive\"] = fh[\"wac\"] - fh[\"benchmark_rate\"]\n",
    "roundto = 0.5\n",
    "\n",
    "# 1010data .round() is not exactly the same as pandas .round().\n",
    "# it appears that pandas col.round(x) is equivalent to tenFrame\n",
    "# col.round(10**-x)\n",
    "\n",
    "fh[\"rincentive\"] = fh.incentive.round(0.5)\n",
    "fh[\"origmonth\"] = fh.effdt.shiftmonths(-fh.wala, 0)\n",
    "fh['origmonth'].setasdate()  # mark this column as a date.\n",
    "fh[\"vintage\"] = fh.origmonth.year()\n",
    "fh[\"curbal\"] =  fh.issamt * fh.fctr\n",
    "fh[\"amortfctr\"] = fh.wac.amort(fh.wam, 1)\n",
    "fh[\"schedbal\"] = fh.curbal * fh.amortfctr\n",
    "fh[\"smmdec\"] =  fh.cprm / 100\n",
    "fh2 = fh[fh.colltype == 'LOAN']\n",
    "fh3 = fh2.groupby([\"product\",\"coupon\",\"productionyear\",\"rincentive\"]).agg(\n",
    " {\"smmdec\" : \"wavg\", \"curbal\": \"sum\"},\n",
    " breakelts={\"col\":\"rincentive\", \"sort\":\"up\"},\n",
    " columnparams=[{\"source\":\"smmdec\", \"fun\":\"wavg\", \"weight\": \"schedbal\", \"name\": \"smm\"}])\n",
    "fh3['cpr'] = 100 * (1 - (1 - fh3.smm)**12)\n",
    "fh3 = fh3[fh3.cpr.notnull()]\n",
    "limit = 500000000\n",
    "fh3 = fh3[fh3.curbal_sum >= limit]\n",
    "fh3 = fh3[[\"product\", \"coupon\", \"productionyear\", \"rincentive\", \"cpr\"]]\n",
    "fh3.sort_values(\"rincentive\", inplace=True)\n",
    "rv = fh3[fh3.rincentive.notnull()]\n",
    "rv"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "1414da45",
   "metadata": {},
   "source": [
    "Example 4: CPR by occupancy type\n",
    "------------------------------------------\n",
    "This example shows prepayments (CPR) by occupancy type (owner/investor/second home) for FHLMC GOLD 30 Year 2012 Production 4% pools."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "id": "1437e56b",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<iframe height=\"500\" width=\"985\" src=\"https://www2.corp.1010data.com/cgi-bin/beta-latest/quickapp.k?sid_=1794223451&epw_=_______ec5908faf9a6a0c87a46b0b298db9205b88d5e8fa70841f9c8bbd4cd0ed2276c10ac3cc57eee40be903b16c28db0d029ecad1a3e8f54f7710c4f903c13651201&uid_=mbs_cstigum&tag_=5\"/>"
      ],
      "text/plain": [
       "        effdt  cpr_first        m0        m1        m2\n",
       "0  2012-01-01        NaN       NaN       NaN       NaN\n",
       "1  2012-02-01   0.138066  0.138066  0.631807 -0.149019\n",
       "2  2012-03-01   0.100135  0.100135  1.499251  1.216603\n",
       "3  2012-04-01   2.052755  2.052755  1.611028  1.795059\n",
       "4  2012-05-01   5.815703  5.815703  2.887672  1.819906\n",
       "5  2012-06-01   6.227530  6.227530  4.307487  2.624381\n",
       "6  2012-07-01   3.951870  3.951870  5.707675  5.448023\n",
       "7  2012-08-01   7.165242  7.165242  8.160101  7.995545\n",
       "8  2012-09-01   6.397460  6.397460  9.898887  8.490837\n",
       "9  2012-10-01   8.277151  8.277151  9.053104  9.126371"
      ]
     },
     "execution_count": 8,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "ldst = tf.TenFrame(session, \"pub.fin.embs.combined.stnd_pool.ldst_orig\")\n",
    "ldst = ldst[ldst.rectype == 'O']\n",
    "ldst = ldst[ldst.rpb > 0]\n",
    "ldst = ldst.merge(\"pub.fin.embs.combined.stnd_pool.fh\", on=[\"issueid\", \"effdt\"])\n",
    "ldst = tf.merge(ldst, \"pub.fin.embs.combined.stnd_pool.sec\", on=\"issueid\")\n",
    "ldst = ldst[(ldst.colltype == 'LOAN') & (ldst.product == 'FHLG30')]\n",
    "ldst = ldst.merge(\"pub.fin.embs.combined.stnd_pool.sc\", on=\"secid\")\n",
    "ldst = ldst[(ldst.coupon == 4) & (ldst.productionyear == 2012)]\n",
    "ldst[\"nschedbal\"] = ldst.rpb * ldst.wac.amort(ldst.wam, 1)\n",
    "ldst = ldst.groupby([\"issueid\", \"value1\"]).effdt.tshift('M', ldst.nschedbal, -1, adjoin=True, name=\"schedbal\")\n",
    "ldst[\"prepaybal\"] = ldst.schedbal.notnull() * ldst.rpb\n",
    "ldst2 = ldst.groupby([\"value1\", \"effdt\"]).agg({\"value1\":\"cnt\", \"schedbal\":\"sum\", \"prepaybal\":\"sum\"})\n",
    "ldst2[\"smm\"] = 1 - ldst2.prepaybal_sum / ldst2.schedbal_sum\n",
    "ldst2[\"cpr\"] = tf.func.smm2cpr(100 * ldst2.smm)\n",
    "rv = ldst3 = ldst2.groupby(\"effdt\").agg({\"cpr\":\"first\"}, cbreaks=\"value1\")\n",
    "ldst3"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "1d281572",
   "metadata": {},
   "source": [
    "Example 5: GNMA prepayment and buyout CPR\n",
    "--------------------------------------------------------\n",
    "This example shows loan level GNMA prepayment and buyout CPR and outstanding principal balance for GNMA 30 Year 4% coupon 2014 production year."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "id": "edab9a19",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<iframe height=\"500\" width=\"985\" src=\"https://www2.corp.1010data.com/cgi-bin/beta-latest/quickapp.k?sid_=1794223451&epw_=_______ec5908faf9a6a0c87a46b0b298db9205b88d5e8fa70841f9c8bbd4cd0ed2276c10ac3cc57eee40be903b16c28db0d029ecad1a3e8f54f7710c4f903c13651201&uid_=mbs_cstigum&tag_=6\"/>"
      ],
      "text/plain": [
       "       fctrdt       rpb_sum       cpr  cpr_buyout\n",
       "0  2014-02-01  5.116197e+07       NaN         NaN\n",
       "1  2014-03-01  3.578247e+08  0.000000    0.000000\n",
       "2  2014-04-01  8.940600e+08  1.447868    0.000000\n",
       "3  2014-05-01  1.155273e+09  1.610689    0.000000\n",
       "4  2014-06-01  1.486189e+09  2.449771    0.000000\n",
       "5  2014-07-01  1.613737e+09  2.665512    0.000000\n",
       "6  2014-08-01  1.777851e+09  3.843906    0.000000\n",
       "7  2014-09-01  2.186035e+09  6.041507    0.352740\n",
       "8  2014-10-01  2.632772e+09  7.235836    0.901366\n",
       "9  2014-11-01  2.788967e+09  7.805672    1.361744"
      ]
     },
     "execution_count": 9,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "gll = tf.TenFrame(session, \"pub.fin.embs.combined.loan.gll\")\n",
    "gll = gll[gll.updsrc == \"GLLM\"]\n",
    "# The original was a little more complicated; easier this way around.\n",
    "gll['rpb'] = tf.select([(gll.currmonliquidationflag == 'Y'), gll.currrpb.notnull(), gll.currrpb.isnull()],\n",
    "                    [0, gll.currrpb, gll.origloanamt * gll.noterate.amort(gll.origterm, gll.loanage)])\n",
    "gll = gll.merge(\"pub.fin.embs.combined.stnd_pool.sec\", on=\"secmnem\")\n",
    "gll = gll.merge(\"pub.fin.embs.combined.stnd_pool.sc\", on=\"secmnem\")\n",
    "gll = gll[(gll.product == \"GNM30\") & (gll.coupon == 4) & (gll.productionyear == 2014)]\n",
    "gll['sbal'] = gll.rpb * gll.noterate.amort(gll.remterm, 1)\n",
    "gll = gll.groupby([\"secmnem\", \"loanseqnum\"]).fctrdt.tshift(\"M\", gll.sbal, -1, adjoin=True, name=\"schedbal\")\n",
    "gll = gll.groupby([\"secmnem\", \"loanseqnum\"]).fctrdt.tshift(\"M\", gll.rpb, -1, adjoin=True, name=\"prev_rpb\")\n",
    "gll = gll.resetbreaks() # ?? needed??\n",
    "# For the following, could I just do (gll.currmonliquidationflag == \"Y\") * gll.prev_rpb?\n",
    "gll['prepays'] = tf.select([gll.currmonliquidationflag == \"Y\"], [gll.prev_rpb])\n",
    "# I'll even do it that way here:\n",
    "gll['buyouts'] = (gll.removalreasoncode == 2) * gll.currrpb\n",
    "gll2 = gll.groupby(\"fctrdt\").agg({\"rpb\":\"sum\", \"schedbal\":\"sum\", \"prepays\":\"sum\", \"buyouts\":\"sum\"},\n",
    "                             breakelts={\"col\":\"fctrdt\", \"sort\":\"up\"})\n",
    "gll2['smm'] = gll2.prepays_sum / gll2.schedbal_sum\n",
    "gll2['cpr'] = 100 * (1 - (1 - gll2.smm)**12)\n",
    "gll2['smm_buyout'] = gll2.buyouts_sum / gll2.schedbal_sum\n",
    "gll2['cpr_buyout'] = 100 * (1 - (1 - gll2.smm_buyout)**12)\n",
    "rv = gll2[['fctrdt', 'rpb_sum', 'cpr', 'cpr_buyout']]\n",
    "rv"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "7e21a7b8",
   "metadata": {},
   "source": [
    "Example 6: Loan level FHLMC CPR\n",
    "------------------------------------------\n",
    "This example displays loan level CPR. There is no 0 balance record supplied, so CPR is computed using a forward scheduled balance calculation compared with next months balance. If there is no record then a payoff has occured. The maximum factor date for the table is computed as to not consider empty records as a prepay."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 10,
   "id": "44b4ac38",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<iframe height=\"500\" width=\"985\" src=\"https://www2.corp.1010data.com/cgi-bin/beta-latest/quickapp.k?sid_=1794223451&epw_=_______ec5908faf9a6a0c87a46b0b298db9205b88d5e8fa70841f9c8bbd4cd0ed2276c10ac3cc57eee40be903b16c28db0d029ecad1a3e8f54f7710c4f903c13651201&uid_=mbs_cstigum&tag_=7\"/>"
      ],
      "text/plain": [
       "       fctrdt  fctrdt_cnt   nextbal_sum  schedbal_sum        cpr\n",
       "0  2006-08-01     1248295  2.237934e+11  2.254628e+11   8.531914\n",
       "1  2006-09-01     1405223  2.525813e+11  2.543747e+11   8.139850\n",
       "2  2006-10-01     1562566  2.792544e+11  2.819308e+11  10.815418\n",
       "3  2006-11-01     1707353  3.059594e+11  3.089015e+11  10.849283\n",
       "4  2006-12-01     1843773  3.298006e+11  3.336411e+11  12.971410\n",
       "5  2007-01-01     2000769  3.577633e+11  3.618557e+11  12.758205\n",
       "6  2007-02-01     2155735  3.903339e+11  3.939057e+11  10.354637\n",
       "7  2007-03-01     2326005  4.242724e+11  4.286674e+11  11.632607\n",
       "8  2007-04-01     2493779  4.555523e+11  4.603572e+11  11.830369\n",
       "9  2007-05-01     2647677  4.851136e+11  4.901698e+11  11.699629"
      ]
     },
     "execution_count": 10,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "ll = tf.TenFrame(session, \"pub.fin.embs.combined.loan.ll\")\n",
    "ll = ll[ll.updsrc == 'FHML']\n",
    "ll_2 = ll.agg({\"fctrdt\": [\"cnt\", \"max\"]})\n",
    "# merge with on=1 ??  That's what the XML needs, but I doubt it makes sense in pandas!\n",
    "ll = ll.merge(ll_2, on=1)\n",
    "ll['schedbal'] = ll.currrpb * ll.orignoterate.amort(ll.remterm, 1)\n",
    "ll = ll.groupby([\"secmnem\", \"loanseqnum\"]).fctrdt.tshift(\"M\", ll.currrpb, 1, adjoin=True, name=\"nextbal_raw\")\n",
    "# This just trims off a single row...\n",
    "ll = ll[ll.fctrdt < ll.fctrdt_max]\n",
    "# I guess I can't really use ll.nextbal_raw.isnull() * ll.nextbal_raw, because if it is\n",
    "# NA, I'll still get the NaN.\n",
    "ll['nextbal'] = tf.select([ll.nextbal_raw.notnull()], [ll.nextbal_raw], default=0)\n",
    "ll2 = ll.groupby(\"fctrdt\").agg({\"fctrdt\":\"cnt\", \"nextbal\":\"sum\", \"schedbal\":\"sum\"})\n",
    "ll2['cpr'] = tf.func.smm2cpr(100 * (1 - ll.nextbal_sum / ll.schedbal_sum))\n",
    "rv = ll2\n",
    "rv"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "0baeaccc",
   "metadata": {},
   "outputs": [],
   "source": []
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3 (ipykernel)",
   "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.9.13"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 5
}