FHFA Home Price Index.ipynb

{
 "cells": [
  {
   "cell_type": "code",
   "execution_count": 1,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "import pandas as pd"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 46,
   "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>state</th>\n",
       "      <th>yr</th>\n",
       "      <th>qtr</th>\n",
       "      <th>index_nsa</th>\n",
       "      <th>index_sa</th>\n",
       "      <th>Warning</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>104</th>\n",
       "      <td>AK</td>\n",
       "      <td>2017</td>\n",
       "      <td>1</td>\n",
       "      <td>248.92</td>\n",
       "      <td>252.49</td>\n",
       "      <td>* Note that this state has fewer than 15,000 t...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>209</th>\n",
       "      <td>AL</td>\n",
       "      <td>2017</td>\n",
       "      <td>1</td>\n",
       "      <td>207.16</td>\n",
       "      <td>208.34</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>314</th>\n",
       "      <td>AR</td>\n",
       "      <td>2017</td>\n",
       "      <td>1</td>\n",
       "      <td>208.40</td>\n",
       "      <td>207.81</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>419</th>\n",
       "      <td>AZ</td>\n",
       "      <td>2017</td>\n",
       "      <td>1</td>\n",
       "      <td>281.90</td>\n",
       "      <td>283.19</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>524</th>\n",
       "      <td>CA</td>\n",
       "      <td>2017</td>\n",
       "      <td>1</td>\n",
       "      <td>255.85</td>\n",
       "      <td>256.72</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>629</th>\n",
       "      <td>CO</td>\n",
       "      <td>2017</td>\n",
       "      <td>1</td>\n",
       "      <td>414.12</td>\n",
       "      <td>415.87</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>734</th>\n",
       "      <td>CT</td>\n",
       "      <td>2017</td>\n",
       "      <td>1</td>\n",
       "      <td>165.77</td>\n",
       "      <td>167.56</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>839</th>\n",
       "      <td>DC</td>\n",
       "      <td>2017</td>\n",
       "      <td>1</td>\n",
       "      <td>520.78</td>\n",
       "      <td>523.77</td>\n",
       "      <td>* Note that this state has fewer than 15,000 t...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>944</th>\n",
       "      <td>DE</td>\n",
       "      <td>2017</td>\n",
       "      <td>1</td>\n",
       "      <td>191.78</td>\n",
       "      <td>193.02</td>\n",
       "      <td>* Note that this state has fewer than 15,000 t...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1049</th>\n",
       "      <td>FL</td>\n",
       "      <td>2017</td>\n",
       "      <td>1</td>\n",
       "      <td>276.20</td>\n",
       "      <td>277.32</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "     state    yr  qtr  index_nsa  index_sa  \\\n",
       "104     AK  2017    1     248.92    252.49   \n",
       "209     AL  2017    1     207.16    208.34   \n",
       "314     AR  2017    1     208.40    207.81   \n",
       "419     AZ  2017    1     281.90    283.19   \n",
       "524     CA  2017    1     255.85    256.72   \n",
       "629     CO  2017    1     414.12    415.87   \n",
       "734     CT  2017    1     165.77    167.56   \n",
       "839     DC  2017    1     520.78    523.77   \n",
       "944     DE  2017    1     191.78    193.02   \n",
       "1049    FL  2017    1     276.20    277.32   \n",
       "\n",
       "                                                Warning  \n",
       "104   * Note that this state has fewer than 15,000 t...  \n",
       "209                                                 NaN  \n",
       "314                                                 NaN  \n",
       "419                                                 NaN  \n",
       "524                                                 NaN  \n",
       "629                                                 NaN  \n",
       "734                                                 NaN  \n",
       "839   * Note that this state has fewer than 15,000 t...  \n",
       "944   * Note that this state has fewer than 15,000 t...  \n",
       "1049                                                NaN  "
      ]
     },
     "execution_count": 46,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "cached = None\n",
    "\n",
    "def get_data():\n",
    "    \n",
    "    global cached\n",
    "    \n",
    "    if cached is None:\n",
    "        #url=\"https://www.fhfa.gov/DataTools/Downloads/Documents/HPI/HPI_EXP_state.txt\",sep=\"\\t\"\n",
    "        #url=\"https://www.fhfa.gov/DataTools/Downloads/Documents/HPI/HPI_AT_state.csv\"\n",
    "        url=\"https://www.fhfa.gov/DataTools/Downloads/Documents/HPI/HPI_PO_state.xls\"\n",
    "\n",
    "        cached = pd.read_excel(url)\n",
    "        \n",
    "    return cached\n",
    "\n",
    "fulldf = get_data()\n",
    "\n",
    "fulldf.sort_values(by=[\"yr\",\"qtr\"],ascending=False).head(10)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 56,
   "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>Region</th>\n",
       "      <th>Year</th>\n",
       "      <th>Quarter</th>\n",
       "      <th>Purchase-Only Index \n",
       "(1991Q1=100)</th>\n",
       "      <th>Seasonally-Adjusted Purchase-Only Index \n",
       "(1991Q1=100)</th>\n",
       "      <th>HPI\n",
       "% Change Over \n",
       "Previous 4 Quarters</th>\n",
       "      <th>HPI\n",
       "% Change Over \n",
       "Previous Quarter</th>\n",
       "      <th>Purchase-Only Index\n",
       "% Change Over \n",
       "Previous 4 Quarters</th>\n",
       "      <th>Purchase-Only Index\n",
       "% Change Over \n",
       "Previous Quarter</th>\n",
       "      <th>Seasonally-Adjusted Purchase-Only Index\n",
       "% Change Over \n",
       "Previous 4 Quarters</th>\n",
       "      <th>Seasonally-Adjusted Purchase-Only Index\n",
       "% Change Over \n",
       "Previous Quarter</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>104</th>\n",
       "      <td>USA</td>\n",
       "      <td>2017</td>\n",
       "      <td>1</td>\n",
       "      <td>239.58</td>\n",
       "      <td>241.17</td>\n",
       "      <td>0.0549</td>\n",
       "      <td>0.0076</td>\n",
       "      <td>0.0604</td>\n",
       "      <td>0.0078</td>\n",
       "      <td>0.0600</td>\n",
       "      <td>0.0139</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>103</th>\n",
       "      <td>USA</td>\n",
       "      <td>2016</td>\n",
       "      <td>4</td>\n",
       "      <td>237.73</td>\n",
       "      <td>237.86</td>\n",
       "      <td>0.0574</td>\n",
       "      <td>0.0087</td>\n",
       "      <td>0.0624</td>\n",
       "      <td>0.0017</td>\n",
       "      <td>0.0622</td>\n",
       "      <td>0.0154</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>102</th>\n",
       "      <td>USA</td>\n",
       "      <td>2016</td>\n",
       "      <td>3</td>\n",
       "      <td>237.32</td>\n",
       "      <td>234.26</td>\n",
       "      <td>0.0582</td>\n",
       "      <td>0.0175</td>\n",
       "      <td>0.0624</td>\n",
       "      <td>0.0158</td>\n",
       "      <td>0.0626</td>\n",
       "      <td>0.0160</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>101</th>\n",
       "      <td>USA</td>\n",
       "      <td>2016</td>\n",
       "      <td>2</td>\n",
       "      <td>233.62</td>\n",
       "      <td>230.57</td>\n",
       "      <td>0.0570</td>\n",
       "      <td>0.0201</td>\n",
       "      <td>0.0591</td>\n",
       "      <td>0.0340</td>\n",
       "      <td>0.0592</td>\n",
       "      <td>0.0134</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>100</th>\n",
       "      <td>USA</td>\n",
       "      <td>2016</td>\n",
       "      <td>1</td>\n",
       "      <td>225.93</td>\n",
       "      <td>227.52</td>\n",
       "      <td>0.0552</td>\n",
       "      <td>0.0100</td>\n",
       "      <td>0.0607</td>\n",
       "      <td>0.0097</td>\n",
       "      <td>0.0600</td>\n",
       "      <td>0.0160</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>99</th>\n",
       "      <td>USA</td>\n",
       "      <td>2015</td>\n",
       "      <td>4</td>\n",
       "      <td>223.77</td>\n",
       "      <td>223.94</td>\n",
       "      <td>0.0553</td>\n",
       "      <td>0.0094</td>\n",
       "      <td>0.0589</td>\n",
       "      <td>0.0017</td>\n",
       "      <td>0.0588</td>\n",
       "      <td>0.0158</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>98</th>\n",
       "      <td>USA</td>\n",
       "      <td>2015</td>\n",
       "      <td>3</td>\n",
       "      <td>223.38</td>\n",
       "      <td>220.46</td>\n",
       "      <td>0.0542</td>\n",
       "      <td>0.0163</td>\n",
       "      <td>0.0560</td>\n",
       "      <td>0.0127</td>\n",
       "      <td>0.0564</td>\n",
       "      <td>0.0128</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>97</th>\n",
       "      <td>USA</td>\n",
       "      <td>2015</td>\n",
       "      <td>2</td>\n",
       "      <td>220.59</td>\n",
       "      <td>217.68</td>\n",
       "      <td>0.0526</td>\n",
       "      <td>0.0184</td>\n",
       "      <td>0.0556</td>\n",
       "      <td>0.0356</td>\n",
       "      <td>0.0556</td>\n",
       "      <td>0.0141</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>96</th>\n",
       "      <td>USA</td>\n",
       "      <td>2015</td>\n",
       "      <td>1</td>\n",
       "      <td>213.01</td>\n",
       "      <td>214.64</td>\n",
       "      <td>0.0537</td>\n",
       "      <td>0.0101</td>\n",
       "      <td>0.0514</td>\n",
       "      <td>0.0080</td>\n",
       "      <td>0.0505</td>\n",
       "      <td>0.0149</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>95</th>\n",
       "      <td>USA</td>\n",
       "      <td>2014</td>\n",
       "      <td>4</td>\n",
       "      <td>211.32</td>\n",
       "      <td>211.50</td>\n",
       "      <td>0.0527</td>\n",
       "      <td>0.0084</td>\n",
       "      <td>0.0484</td>\n",
       "      <td>-0.0010</td>\n",
       "      <td>0.0486</td>\n",
       "      <td>0.0135</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>94</th>\n",
       "      <td>USA</td>\n",
       "      <td>2014</td>\n",
       "      <td>3</td>\n",
       "      <td>211.53</td>\n",
       "      <td>208.69</td>\n",
       "      <td>0.0533</td>\n",
       "      <td>0.0147</td>\n",
       "      <td>0.0460</td>\n",
       "      <td>0.0122</td>\n",
       "      <td>0.0462</td>\n",
       "      <td>0.0120</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>93</th>\n",
       "      <td>USA</td>\n",
       "      <td>2014</td>\n",
       "      <td>2</td>\n",
       "      <td>208.98</td>\n",
       "      <td>206.21</td>\n",
       "      <td>0.0547</td>\n",
       "      <td>0.0195</td>\n",
       "      <td>0.0517</td>\n",
       "      <td>0.0315</td>\n",
       "      <td>0.0516</td>\n",
       "      <td>0.0093</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>92</th>\n",
       "      <td>USA</td>\n",
       "      <td>2014</td>\n",
       "      <td>1</td>\n",
       "      <td>202.60</td>\n",
       "      <td>204.32</td>\n",
       "      <td>0.0509</td>\n",
       "      <td>0.0090</td>\n",
       "      <td>0.0654</td>\n",
       "      <td>0.0052</td>\n",
       "      <td>0.0646</td>\n",
       "      <td>0.0130</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>91</th>\n",
       "      <td>USA</td>\n",
       "      <td>2013</td>\n",
       "      <td>4</td>\n",
       "      <td>201.56</td>\n",
       "      <td>201.69</td>\n",
       "      <td>0.0470</td>\n",
       "      <td>0.0090</td>\n",
       "      <td>0.0722</td>\n",
       "      <td>-0.0033</td>\n",
       "      <td>0.0728</td>\n",
       "      <td>0.0112</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>90</th>\n",
       "      <td>USA</td>\n",
       "      <td>2013</td>\n",
       "      <td>3</td>\n",
       "      <td>202.23</td>\n",
       "      <td>199.47</td>\n",
       "      <td>0.0462</td>\n",
       "      <td>0.0161</td>\n",
       "      <td>0.0779</td>\n",
       "      <td>0.0178</td>\n",
       "      <td>0.0779</td>\n",
       "      <td>0.0172</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>89</th>\n",
       "      <td>USA</td>\n",
       "      <td>2013</td>\n",
       "      <td>2</td>\n",
       "      <td>198.70</td>\n",
       "      <td>196.10</td>\n",
       "      <td>0.0428</td>\n",
       "      <td>0.0159</td>\n",
       "      <td>0.0724</td>\n",
       "      <td>0.0449</td>\n",
       "      <td>0.0719</td>\n",
       "      <td>0.0218</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>88</th>\n",
       "      <td>USA</td>\n",
       "      <td>2013</td>\n",
       "      <td>1</td>\n",
       "      <td>190.17</td>\n",
       "      <td>191.92</td>\n",
       "      <td>0.0225</td>\n",
       "      <td>0.0053</td>\n",
       "      <td>0.0676</td>\n",
       "      <td>0.0116</td>\n",
       "      <td>0.0675</td>\n",
       "      <td>0.0208</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>87</th>\n",
       "      <td>USA</td>\n",
       "      <td>2012</td>\n",
       "      <td>4</td>\n",
       "      <td>187.99</td>\n",
       "      <td>188.01</td>\n",
       "      <td>0.0065</td>\n",
       "      <td>0.0082</td>\n",
       "      <td>0.0507</td>\n",
       "      <td>0.0020</td>\n",
       "      <td>0.0512</td>\n",
       "      <td>0.0160</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>86</th>\n",
       "      <td>USA</td>\n",
       "      <td>2012</td>\n",
       "      <td>3</td>\n",
       "      <td>187.61</td>\n",
       "      <td>185.06</td>\n",
       "      <td>0.0028</td>\n",
       "      <td>0.0129</td>\n",
       "      <td>0.0368</td>\n",
       "      <td>0.0126</td>\n",
       "      <td>0.0364</td>\n",
       "      <td>0.0116</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>85</th>\n",
       "      <td>USA</td>\n",
       "      <td>2012</td>\n",
       "      <td>2</td>\n",
       "      <td>185.28</td>\n",
       "      <td>182.94</td>\n",
       "      <td>-0.0024</td>\n",
       "      <td>-0.0039</td>\n",
       "      <td>0.0298</td>\n",
       "      <td>0.0402</td>\n",
       "      <td>0.0291</td>\n",
       "      <td>0.0175</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>84</th>\n",
       "      <td>USA</td>\n",
       "      <td>2012</td>\n",
       "      <td>1</td>\n",
       "      <td>178.12</td>\n",
       "      <td>179.79</td>\n",
       "      <td>-0.0157</td>\n",
       "      <td>-0.0105</td>\n",
       "      <td>0.0034</td>\n",
       "      <td>-0.0044</td>\n",
       "      <td>0.0039</td>\n",
       "      <td>0.0052</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>83</th>\n",
       "      <td>USA</td>\n",
       "      <td>2011</td>\n",
       "      <td>4</td>\n",
       "      <td>178.92</td>\n",
       "      <td>178.85</td>\n",
       "      <td>-0.0326</td>\n",
       "      <td>0.0045</td>\n",
       "      <td>-0.0236</td>\n",
       "      <td>-0.0112</td>\n",
       "      <td>-0.0233</td>\n",
       "      <td>0.0016</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>82</th>\n",
       "      <td>USA</td>\n",
       "      <td>2011</td>\n",
       "      <td>3</td>\n",
       "      <td>180.95</td>\n",
       "      <td>178.56</td>\n",
       "      <td>-0.0439</td>\n",
       "      <td>0.0076</td>\n",
       "      <td>-0.0338</td>\n",
       "      <td>0.0056</td>\n",
       "      <td>-0.0345</td>\n",
       "      <td>0.0045</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>81</th>\n",
       "      <td>USA</td>\n",
       "      <td>2011</td>\n",
       "      <td>2</td>\n",
       "      <td>179.93</td>\n",
       "      <td>177.76</td>\n",
       "      <td>-0.0420</td>\n",
       "      <td>-0.0172</td>\n",
       "      <td>-0.0541</td>\n",
       "      <td>0.0136</td>\n",
       "      <td>-0.0548</td>\n",
       "      <td>-0.0074</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>80</th>\n",
       "      <td>USA</td>\n",
       "      <td>2011</td>\n",
       "      <td>1</td>\n",
       "      <td>177.52</td>\n",
       "      <td>179.10</td>\n",
       "      <td>-0.0340</td>\n",
       "      <td>-0.0275</td>\n",
       "      <td>-0.0532</td>\n",
       "      <td>-0.0313</td>\n",
       "      <td>-0.0521</td>\n",
       "      <td>-0.0220</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>79</th>\n",
       "      <td>USA</td>\n",
       "      <td>2010</td>\n",
       "      <td>4</td>\n",
       "      <td>183.25</td>\n",
       "      <td>183.13</td>\n",
       "      <td>-0.0186</td>\n",
       "      <td>-0.0073</td>\n",
       "      <td>-0.0402</td>\n",
       "      <td>-0.0215</td>\n",
       "      <td>-0.0402</td>\n",
       "      <td>-0.0098</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>78</th>\n",
       "      <td>USA</td>\n",
       "      <td>2010</td>\n",
       "      <td>3</td>\n",
       "      <td>187.28</td>\n",
       "      <td>184.94</td>\n",
       "      <td>-0.0187</td>\n",
       "      <td>0.0096</td>\n",
       "      <td>-0.0303</td>\n",
       "      <td>-0.0155</td>\n",
       "      <td>-0.0312</td>\n",
       "      <td>-0.0167</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>77</th>\n",
       "      <td>USA</td>\n",
       "      <td>2010</td>\n",
       "      <td>2</td>\n",
       "      <td>190.23</td>\n",
       "      <td>188.08</td>\n",
       "      <td>-0.0537</td>\n",
       "      <td>-0.0089</td>\n",
       "      <td>-0.0192</td>\n",
       "      <td>0.0146</td>\n",
       "      <td>-0.0198</td>\n",
       "      <td>-0.0045</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>76</th>\n",
       "      <td>USA</td>\n",
       "      <td>2010</td>\n",
       "      <td>1</td>\n",
       "      <td>187.49</td>\n",
       "      <td>188.94</td>\n",
       "      <td>-0.0705</td>\n",
       "      <td>-0.0120</td>\n",
       "      <td>-0.0305</td>\n",
       "      <td>-0.0180</td>\n",
       "      <td>-0.0293</td>\n",
       "      <td>-0.0097</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>75</th>\n",
       "      <td>USA</td>\n",
       "      <td>2009</td>\n",
       "      <td>4</td>\n",
       "      <td>190.92</td>\n",
       "      <td>190.79</td>\n",
       "      <td>-0.0523</td>\n",
       "      <td>-0.0073</td>\n",
       "      <td>-0.0242</td>\n",
       "      <td>-0.0115</td>\n",
       "      <td>-0.0244</td>\n",
       "      <td>-0.0006</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>29</th>\n",
       "      <td>USA</td>\n",
       "      <td>1998</td>\n",
       "      <td>2</td>\n",
       "      <td>123.99</td>\n",
       "      <td>123.12</td>\n",
       "      <td>0.0517</td>\n",
       "      <td>0.0091</td>\n",
       "      <td>0.0456</td>\n",
       "      <td>0.0223</td>\n",
       "      <td>0.0454</td>\n",
       "      <td>0.0141</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>28</th>\n",
       "      <td>USA</td>\n",
       "      <td>1998</td>\n",
       "      <td>1</td>\n",
       "      <td>121.29</td>\n",
       "      <td>121.40</td>\n",
       "      <td>0.0509</td>\n",
       "      <td>0.0149</td>\n",
       "      <td>0.0396</td>\n",
       "      <td>0.0101</td>\n",
       "      <td>0.0396</td>\n",
       "      <td>0.0123</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>27</th>\n",
       "      <td>USA</td>\n",
       "      <td>1997</td>\n",
       "      <td>4</td>\n",
       "      <td>120.08</td>\n",
       "      <td>119.93</td>\n",
       "      <td>0.0442</td>\n",
       "      <td>0.0130</td>\n",
       "      <td>0.0330</td>\n",
       "      <td>0.0041</td>\n",
       "      <td>0.0333</td>\n",
       "      <td>0.0108</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>26</th>\n",
       "      <td>USA</td>\n",
       "      <td>1997</td>\n",
       "      <td>3</td>\n",
       "      <td>119.58</td>\n",
       "      <td>118.65</td>\n",
       "      <td>0.0388</td>\n",
       "      <td>0.0138</td>\n",
       "      <td>0.0280</td>\n",
       "      <td>0.0085</td>\n",
       "      <td>0.0280</td>\n",
       "      <td>0.0074</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>25</th>\n",
       "      <td>USA</td>\n",
       "      <td>1997</td>\n",
       "      <td>2</td>\n",
       "      <td>118.58</td>\n",
       "      <td>117.78</td>\n",
       "      <td>0.0293</td>\n",
       "      <td>0.0084</td>\n",
       "      <td>0.0277</td>\n",
       "      <td>0.0164</td>\n",
       "      <td>0.0273</td>\n",
       "      <td>0.0085</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>24</th>\n",
       "      <td>USA</td>\n",
       "      <td>1997</td>\n",
       "      <td>1</td>\n",
       "      <td>116.67</td>\n",
       "      <td>116.78</td>\n",
       "      <td>0.0225</td>\n",
       "      <td>0.0083</td>\n",
       "      <td>0.0256</td>\n",
       "      <td>0.0037</td>\n",
       "      <td>0.0256</td>\n",
       "      <td>0.0062</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>23</th>\n",
       "      <td>USA</td>\n",
       "      <td>1996</td>\n",
       "      <td>4</td>\n",
       "      <td>116.24</td>\n",
       "      <td>116.07</td>\n",
       "      <td>0.0255</td>\n",
       "      <td>0.0078</td>\n",
       "      <td>0.0280</td>\n",
       "      <td>-0.0007</td>\n",
       "      <td>0.0284</td>\n",
       "      <td>0.0056</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>22</th>\n",
       "      <td>USA</td>\n",
       "      <td>1996</td>\n",
       "      <td>3</td>\n",
       "      <td>116.32</td>\n",
       "      <td>115.42</td>\n",
       "      <td>0.0255</td>\n",
       "      <td>0.0045</td>\n",
       "      <td>0.0288</td>\n",
       "      <td>0.0081</td>\n",
       "      <td>0.0287</td>\n",
       "      <td>0.0067</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>21</th>\n",
       "      <td>USA</td>\n",
       "      <td>1996</td>\n",
       "      <td>2</td>\n",
       "      <td>115.39</td>\n",
       "      <td>114.65</td>\n",
       "      <td>0.0367</td>\n",
       "      <td>0.0017</td>\n",
       "      <td>0.0317</td>\n",
       "      <td>0.0143</td>\n",
       "      <td>0.0313</td>\n",
       "      <td>0.0069</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>20</th>\n",
       "      <td>USA</td>\n",
       "      <td>1996</td>\n",
       "      <td>1</td>\n",
       "      <td>113.76</td>\n",
       "      <td>113.87</td>\n",
       "      <td>0.0518</td>\n",
       "      <td>0.0113</td>\n",
       "      <td>0.0296</td>\n",
       "      <td>0.0060</td>\n",
       "      <td>0.0297</td>\n",
       "      <td>0.0089</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>19</th>\n",
       "      <td>USA</td>\n",
       "      <td>1995</td>\n",
       "      <td>4</td>\n",
       "      <td>113.08</td>\n",
       "      <td>112.86</td>\n",
       "      <td>0.0456</td>\n",
       "      <td>0.0079</td>\n",
       "      <td>0.0266</td>\n",
       "      <td>0.0001</td>\n",
       "      <td>0.0272</td>\n",
       "      <td>0.0059</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>18</th>\n",
       "      <td>USA</td>\n",
       "      <td>1995</td>\n",
       "      <td>3</td>\n",
       "      <td>113.07</td>\n",
       "      <td>112.20</td>\n",
       "      <td>0.0366</td>\n",
       "      <td>0.0154</td>\n",
       "      <td>0.0268</td>\n",
       "      <td>0.0110</td>\n",
       "      <td>0.0264</td>\n",
       "      <td>0.0093</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>17</th>\n",
       "      <td>USA</td>\n",
       "      <td>1995</td>\n",
       "      <td>2</td>\n",
       "      <td>111.84</td>\n",
       "      <td>111.17</td>\n",
       "      <td>0.0251</td>\n",
       "      <td>0.0163</td>\n",
       "      <td>0.0238</td>\n",
       "      <td>0.0122</td>\n",
       "      <td>0.0234</td>\n",
       "      <td>0.0053</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>16</th>\n",
       "      <td>USA</td>\n",
       "      <td>1995</td>\n",
       "      <td>1</td>\n",
       "      <td>110.49</td>\n",
       "      <td>110.58</td>\n",
       "      <td>0.0147</td>\n",
       "      <td>0.0053</td>\n",
       "      <td>0.0264</td>\n",
       "      <td>0.0031</td>\n",
       "      <td>0.0266</td>\n",
       "      <td>0.0065</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>15</th>\n",
       "      <td>USA</td>\n",
       "      <td>1994</td>\n",
       "      <td>4</td>\n",
       "      <td>110.15</td>\n",
       "      <td>109.87</td>\n",
       "      <td>0.0158</td>\n",
       "      <td>-0.0008</td>\n",
       "      <td>0.0286</td>\n",
       "      <td>0.0003</td>\n",
       "      <td>0.0292</td>\n",
       "      <td>0.0051</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>14</th>\n",
       "      <td>USA</td>\n",
       "      <td>1994</td>\n",
       "      <td>3</td>\n",
       "      <td>110.11</td>\n",
       "      <td>109.31</td>\n",
       "      <td>0.0250</td>\n",
       "      <td>0.0041</td>\n",
       "      <td>0.0343</td>\n",
       "      <td>0.0080</td>\n",
       "      <td>0.0338</td>\n",
       "      <td>0.0063</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>13</th>\n",
       "      <td>USA</td>\n",
       "      <td>1994</td>\n",
       "      <td>2</td>\n",
       "      <td>109.24</td>\n",
       "      <td>108.63</td>\n",
       "      <td>0.0291</td>\n",
       "      <td>0.0061</td>\n",
       "      <td>0.0354</td>\n",
       "      <td>0.0148</td>\n",
       "      <td>0.0350</td>\n",
       "      <td>0.0085</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>12</th>\n",
       "      <td>USA</td>\n",
       "      <td>1994</td>\n",
       "      <td>1</td>\n",
       "      <td>107.65</td>\n",
       "      <td>107.72</td>\n",
       "      <td>0.0326</td>\n",
       "      <td>0.0064</td>\n",
       "      <td>0.0364</td>\n",
       "      <td>0.0053</td>\n",
       "      <td>0.0367</td>\n",
       "      <td>0.0090</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>11</th>\n",
       "      <td>USA</td>\n",
       "      <td>1993</td>\n",
       "      <td>4</td>\n",
       "      <td>107.09</td>\n",
       "      <td>106.75</td>\n",
       "      <td>0.0265</td>\n",
       "      <td>0.0083</td>\n",
       "      <td>0.0273</td>\n",
       "      <td>0.0059</td>\n",
       "      <td>0.0278</td>\n",
       "      <td>0.0096</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>10</th>\n",
       "      <td>USA</td>\n",
       "      <td>1993</td>\n",
       "      <td>3</td>\n",
       "      <td>106.46</td>\n",
       "      <td>105.74</td>\n",
       "      <td>0.0232</td>\n",
       "      <td>0.0081</td>\n",
       "      <td>0.0265</td>\n",
       "      <td>0.0090</td>\n",
       "      <td>0.0260</td>\n",
       "      <td>0.0075</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9</th>\n",
       "      <td>USA</td>\n",
       "      <td>1993</td>\n",
       "      <td>2</td>\n",
       "      <td>105.51</td>\n",
       "      <td>104.95</td>\n",
       "      <td>0.0266</td>\n",
       "      <td>0.0095</td>\n",
       "      <td>0.0274</td>\n",
       "      <td>0.0158</td>\n",
       "      <td>0.0272</td>\n",
       "      <td>0.0101</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8</th>\n",
       "      <td>USA</td>\n",
       "      <td>1993</td>\n",
       "      <td>1</td>\n",
       "      <td>103.87</td>\n",
       "      <td>103.91</td>\n",
       "      <td>0.0167</td>\n",
       "      <td>0.0004</td>\n",
       "      <td>0.0156</td>\n",
       "      <td>-0.0036</td>\n",
       "      <td>0.0159</td>\n",
       "      <td>0.0004</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>USA</td>\n",
       "      <td>1992</td>\n",
       "      <td>4</td>\n",
       "      <td>104.24</td>\n",
       "      <td>103.87</td>\n",
       "      <td>0.0242</td>\n",
       "      <td>0.0050</td>\n",
       "      <td>0.0273</td>\n",
       "      <td>0.0051</td>\n",
       "      <td>0.0275</td>\n",
       "      <td>0.0079</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>USA</td>\n",
       "      <td>1992</td>\n",
       "      <td>3</td>\n",
       "      <td>103.71</td>\n",
       "      <td>103.06</td>\n",
       "      <td>0.0334</td>\n",
       "      <td>0.0114</td>\n",
       "      <td>0.0289</td>\n",
       "      <td>0.0099</td>\n",
       "      <td>0.0286</td>\n",
       "      <td>0.0087</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>USA</td>\n",
       "      <td>1992</td>\n",
       "      <td>2</td>\n",
       "      <td>102.69</td>\n",
       "      <td>102.17</td>\n",
       "      <td>0.0235</td>\n",
       "      <td>-0.0002</td>\n",
       "      <td>0.0217</td>\n",
       "      <td>0.0042</td>\n",
       "      <td>0.0216</td>\n",
       "      <td>-0.0011</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>USA</td>\n",
       "      <td>1992</td>\n",
       "      <td>1</td>\n",
       "      <td>102.27</td>\n",
       "      <td>102.28</td>\n",
       "      <td>0.0306</td>\n",
       "      <td>0.0077</td>\n",
       "      <td>0.0227</td>\n",
       "      <td>0.0079</td>\n",
       "      <td>0.0228</td>\n",
       "      <td>0.0118</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>USA</td>\n",
       "      <td>1991</td>\n",
       "      <td>4</td>\n",
       "      <td>101.47</td>\n",
       "      <td>101.08</td>\n",
       "      <td>0.0312</td>\n",
       "      <td>0.0141</td>\n",
       "      <td>NaN</td>\n",
       "      <td>0.0067</td>\n",
       "      <td>NaN</td>\n",
       "      <td>0.0089</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>USA</td>\n",
       "      <td>1991</td>\n",
       "      <td>3</td>\n",
       "      <td>100.79</td>\n",
       "      <td>100.19</td>\n",
       "      <td>0.0129</td>\n",
       "      <td>0.0017</td>\n",
       "      <td>NaN</td>\n",
       "      <td>0.0028</td>\n",
       "      <td>NaN</td>\n",
       "      <td>0.0018</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>USA</td>\n",
       "      <td>1991</td>\n",
       "      <td>2</td>\n",
       "      <td>100.52</td>\n",
       "      <td>100.01</td>\n",
       "      <td>0.0173</td>\n",
       "      <td>0.0068</td>\n",
       "      <td>NaN</td>\n",
       "      <td>0.0052</td>\n",
       "      <td>NaN</td>\n",
       "      <td>0.0001</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>USA</td>\n",
       "      <td>1991</td>\n",
       "      <td>1</td>\n",
       "      <td>100.00</td>\n",
       "      <td>100.00</td>\n",
       "      <td>0.0146</td>\n",
       "      <td>0.0083</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>105 rows × 11 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "    Region  Year  Quarter  Purchase-Only Index \\n(1991Q1=100)  \\\n",
       "104    USA  2017        1                              239.58   \n",
       "103    USA  2016        4                              237.73   \n",
       "102    USA  2016        3                              237.32   \n",
       "101    USA  2016        2                              233.62   \n",
       "100    USA  2016        1                              225.93   \n",
       "99     USA  2015        4                              223.77   \n",
       "98     USA  2015        3                              223.38   \n",
       "97     USA  2015        2                              220.59   \n",
       "96     USA  2015        1                              213.01   \n",
       "95     USA  2014        4                              211.32   \n",
       "94     USA  2014        3                              211.53   \n",
       "93     USA  2014        2                              208.98   \n",
       "92     USA  2014        1                              202.60   \n",
       "91     USA  2013        4                              201.56   \n",
       "90     USA  2013        3                              202.23   \n",
       "89     USA  2013        2                              198.70   \n",
       "88     USA  2013        1                              190.17   \n",
       "87     USA  2012        4                              187.99   \n",
       "86     USA  2012        3                              187.61   \n",
       "85     USA  2012        2                              185.28   \n",
       "84     USA  2012        1                              178.12   \n",
       "83     USA  2011        4                              178.92   \n",
       "82     USA  2011        3                              180.95   \n",
       "81     USA  2011        2                              179.93   \n",
       "80     USA  2011        1                              177.52   \n",
       "79     USA  2010        4                              183.25   \n",
       "78     USA  2010        3                              187.28   \n",
       "77     USA  2010        2                              190.23   \n",
       "76     USA  2010        1                              187.49   \n",
       "75     USA  2009        4                              190.92   \n",
       "..     ...   ...      ...                                 ...   \n",
       "29     USA  1998        2                              123.99   \n",
       "28     USA  1998        1                              121.29   \n",
       "27     USA  1997        4                              120.08   \n",
       "26     USA  1997        3                              119.58   \n",
       "25     USA  1997        2                              118.58   \n",
       "24     USA  1997        1                              116.67   \n",
       "23     USA  1996        4                              116.24   \n",
       "22     USA  1996        3                              116.32   \n",
       "21     USA  1996        2                              115.39   \n",
       "20     USA  1996        1                              113.76   \n",
       "19     USA  1995        4                              113.08   \n",
       "18     USA  1995        3                              113.07   \n",
       "17     USA  1995        2                              111.84   \n",
       "16     USA  1995        1                              110.49   \n",
       "15     USA  1994        4                              110.15   \n",
       "14     USA  1994        3                              110.11   \n",
       "13     USA  1994        2                              109.24   \n",
       "12     USA  1994        1                              107.65   \n",
       "11     USA  1993        4                              107.09   \n",
       "10     USA  1993        3                              106.46   \n",
       "9      USA  1993        2                              105.51   \n",
       "8      USA  1993        1                              103.87   \n",
       "7      USA  1992        4                              104.24   \n",
       "6      USA  1992        3                              103.71   \n",
       "5      USA  1992        2                              102.69   \n",
       "4      USA  1992        1                              102.27   \n",
       "3      USA  1991        4                              101.47   \n",
       "2      USA  1991        3                              100.79   \n",
       "1      USA  1991        2                              100.52   \n",
       "0      USA  1991        1                              100.00   \n",
       "\n",
       "     Seasonally-Adjusted Purchase-Only Index \\n(1991Q1=100)  \\\n",
       "104                                             241.17        \n",
       "103                                             237.86        \n",
       "102                                             234.26        \n",
       "101                                             230.57        \n",
       "100                                             227.52        \n",
       "99                                              223.94        \n",
       "98                                              220.46        \n",
       "97                                              217.68        \n",
       "96                                              214.64        \n",
       "95                                              211.50        \n",
       "94                                              208.69        \n",
       "93                                              206.21        \n",
       "92                                              204.32        \n",
       "91                                              201.69        \n",
       "90                                              199.47        \n",
       "89                                              196.10        \n",
       "88                                              191.92        \n",
       "87                                              188.01        \n",
       "86                                              185.06        \n",
       "85                                              182.94        \n",
       "84                                              179.79        \n",
       "83                                              178.85        \n",
       "82                                              178.56        \n",
       "81                                              177.76        \n",
       "80                                              179.10        \n",
       "79                                              183.13        \n",
       "78                                              184.94        \n",
       "77                                              188.08        \n",
       "76                                              188.94        \n",
       "75                                              190.79        \n",
       "..                                                 ...        \n",
       "29                                              123.12        \n",
       "28                                              121.40        \n",
       "27                                              119.93        \n",
       "26                                              118.65        \n",
       "25                                              117.78        \n",
       "24                                              116.78        \n",
       "23                                              116.07        \n",
       "22                                              115.42        \n",
       "21                                              114.65        \n",
       "20                                              113.87        \n",
       "19                                              112.86        \n",
       "18                                              112.20        \n",
       "17                                              111.17        \n",
       "16                                              110.58        \n",
       "15                                              109.87        \n",
       "14                                              109.31        \n",
       "13                                              108.63        \n",
       "12                                              107.72        \n",
       "11                                              106.75        \n",
       "10                                              105.74        \n",
       "9                                               104.95        \n",
       "8                                               103.91        \n",
       "7                                               103.87        \n",
       "6                                               103.06        \n",
       "5                                               102.17        \n",
       "4                                               102.28        \n",
       "3                                               101.08        \n",
       "2                                               100.19        \n",
       "1                                               100.01        \n",
       "0                                               100.00        \n",
       "\n",
       "     HPI\\n% Change Over \\nPrevious 4 Quarters  \\\n",
       "104                                    0.0549   \n",
       "103                                    0.0574   \n",
       "102                                    0.0582   \n",
       "101                                    0.0570   \n",
       "100                                    0.0552   \n",
       "99                                     0.0553   \n",
       "98                                     0.0542   \n",
       "97                                     0.0526   \n",
       "96                                     0.0537   \n",
       "95                                     0.0527   \n",
       "94                                     0.0533   \n",
       "93                                     0.0547   \n",
       "92                                     0.0509   \n",
       "91                                     0.0470   \n",
       "90                                     0.0462   \n",
       "89                                     0.0428   \n",
       "88                                     0.0225   \n",
       "87                                     0.0065   \n",
       "86                                     0.0028   \n",
       "85                                    -0.0024   \n",
       "84                                    -0.0157   \n",
       "83                                    -0.0326   \n",
       "82                                    -0.0439   \n",
       "81                                    -0.0420   \n",
       "80                                    -0.0340   \n",
       "79                                    -0.0186   \n",
       "78                                    -0.0187   \n",
       "77                                    -0.0537   \n",
       "76                                    -0.0705   \n",
       "75                                    -0.0523   \n",
       "..                                        ...   \n",
       "29                                     0.0517   \n",
       "28                                     0.0509   \n",
       "27                                     0.0442   \n",
       "26                                     0.0388   \n",
       "25                                     0.0293   \n",
       "24                                     0.0225   \n",
       "23                                     0.0255   \n",
       "22                                     0.0255   \n",
       "21                                     0.0367   \n",
       "20                                     0.0518   \n",
       "19                                     0.0456   \n",
       "18                                     0.0366   \n",
       "17                                     0.0251   \n",
       "16                                     0.0147   \n",
       "15                                     0.0158   \n",
       "14                                     0.0250   \n",
       "13                                     0.0291   \n",
       "12                                     0.0326   \n",
       "11                                     0.0265   \n",
       "10                                     0.0232   \n",
       "9                                      0.0266   \n",
       "8                                      0.0167   \n",
       "7                                      0.0242   \n",
       "6                                      0.0334   \n",
       "5                                      0.0235   \n",
       "4                                      0.0306   \n",
       "3                                      0.0312   \n",
       "2                                      0.0129   \n",
       "1                                      0.0173   \n",
       "0                                      0.0146   \n",
       "\n",
       "     HPI\\n% Change Over \\nPrevious Quarter  \\\n",
       "104                                 0.0076   \n",
       "103                                 0.0087   \n",
       "102                                 0.0175   \n",
       "101                                 0.0201   \n",
       "100                                 0.0100   \n",
       "99                                  0.0094   \n",
       "98                                  0.0163   \n",
       "97                                  0.0184   \n",
       "96                                  0.0101   \n",
       "95                                  0.0084   \n",
       "94                                  0.0147   \n",
       "93                                  0.0195   \n",
       "92                                  0.0090   \n",
       "91                                  0.0090   \n",
       "90                                  0.0161   \n",
       "89                                  0.0159   \n",
       "88                                  0.0053   \n",
       "87                                  0.0082   \n",
       "86                                  0.0129   \n",
       "85                                 -0.0039   \n",
       "84                                 -0.0105   \n",
       "83                                  0.0045   \n",
       "82                                  0.0076   \n",
       "81                                 -0.0172   \n",
       "80                                 -0.0275   \n",
       "79                                 -0.0073   \n",
       "78                                  0.0096   \n",
       "77                                 -0.0089   \n",
       "76                                 -0.0120   \n",
       "75                                 -0.0073   \n",
       "..                                     ...   \n",
       "29                                  0.0091   \n",
       "28                                  0.0149   \n",
       "27                                  0.0130   \n",
       "26                                  0.0138   \n",
       "25                                  0.0084   \n",
       "24                                  0.0083   \n",
       "23                                  0.0078   \n",
       "22                                  0.0045   \n",
       "21                                  0.0017   \n",
       "20                                  0.0113   \n",
       "19                                  0.0079   \n",
       "18                                  0.0154   \n",
       "17                                  0.0163   \n",
       "16                                  0.0053   \n",
       "15                                 -0.0008   \n",
       "14                                  0.0041   \n",
       "13                                  0.0061   \n",
       "12                                  0.0064   \n",
       "11                                  0.0083   \n",
       "10                                  0.0081   \n",
       "9                                   0.0095   \n",
       "8                                   0.0004   \n",
       "7                                   0.0050   \n",
       "6                                   0.0114   \n",
       "5                                  -0.0002   \n",
       "4                                   0.0077   \n",
       "3                                   0.0141   \n",
       "2                                   0.0017   \n",
       "1                                   0.0068   \n",
       "0                                   0.0083   \n",
       "\n",
       "     Purchase-Only Index\\n% Change Over \\nPrevious 4 Quarters  \\\n",
       "104                                             0.0604          \n",
       "103                                             0.0624          \n",
       "102                                             0.0624          \n",
       "101                                             0.0591          \n",
       "100                                             0.0607          \n",
       "99                                              0.0589          \n",
       "98                                              0.0560          \n",
       "97                                              0.0556          \n",
       "96                                              0.0514          \n",
       "95                                              0.0484          \n",
       "94                                              0.0460          \n",
       "93                                              0.0517          \n",
       "92                                              0.0654          \n",
       "91                                              0.0722          \n",
       "90                                              0.0779          \n",
       "89                                              0.0724          \n",
       "88                                              0.0676          \n",
       "87                                              0.0507          \n",
       "86                                              0.0368          \n",
       "85                                              0.0298          \n",
       "84                                              0.0034          \n",
       "83                                             -0.0236          \n",
       "82                                             -0.0338          \n",
       "81                                             -0.0541          \n",
       "80                                             -0.0532          \n",
       "79                                             -0.0402          \n",
       "78                                             -0.0303          \n",
       "77                                             -0.0192          \n",
       "76                                             -0.0305          \n",
       "75                                             -0.0242          \n",
       "..                                                 ...          \n",
       "29                                              0.0456          \n",
       "28                                              0.0396          \n",
       "27                                              0.0330          \n",
       "26                                              0.0280          \n",
       "25                                              0.0277          \n",
       "24                                              0.0256          \n",
       "23                                              0.0280          \n",
       "22                                              0.0288          \n",
       "21                                              0.0317          \n",
       "20                                              0.0296          \n",
       "19                                              0.0266          \n",
       "18                                              0.0268          \n",
       "17                                              0.0238          \n",
       "16                                              0.0264          \n",
       "15                                              0.0286          \n",
       "14                                              0.0343          \n",
       "13                                              0.0354          \n",
       "12                                              0.0364          \n",
       "11                                              0.0273          \n",
       "10                                              0.0265          \n",
       "9                                               0.0274          \n",
       "8                                               0.0156          \n",
       "7                                               0.0273          \n",
       "6                                               0.0289          \n",
       "5                                               0.0217          \n",
       "4                                               0.0227          \n",
       "3                                                  NaN          \n",
       "2                                                  NaN          \n",
       "1                                                  NaN          \n",
       "0                                                  NaN          \n",
       "\n",
       "     Purchase-Only Index\\n% Change Over \\nPrevious Quarter  \\\n",
       "104                                             0.0078       \n",
       "103                                             0.0017       \n",
       "102                                             0.0158       \n",
       "101                                             0.0340       \n",
       "100                                             0.0097       \n",
       "99                                              0.0017       \n",
       "98                                              0.0127       \n",
       "97                                              0.0356       \n",
       "96                                              0.0080       \n",
       "95                                             -0.0010       \n",
       "94                                              0.0122       \n",
       "93                                              0.0315       \n",
       "92                                              0.0052       \n",
       "91                                             -0.0033       \n",
       "90                                              0.0178       \n",
       "89                                              0.0449       \n",
       "88                                              0.0116       \n",
       "87                                              0.0020       \n",
       "86                                              0.0126       \n",
       "85                                              0.0402       \n",
       "84                                             -0.0044       \n",
       "83                                             -0.0112       \n",
       "82                                              0.0056       \n",
       "81                                              0.0136       \n",
       "80                                             -0.0313       \n",
       "79                                             -0.0215       \n",
       "78                                             -0.0155       \n",
       "77                                              0.0146       \n",
       "76                                             -0.0180       \n",
       "75                                             -0.0115       \n",
       "..                                                 ...       \n",
       "29                                              0.0223       \n",
       "28                                              0.0101       \n",
       "27                                              0.0041       \n",
       "26                                              0.0085       \n",
       "25                                              0.0164       \n",
       "24                                              0.0037       \n",
       "23                                             -0.0007       \n",
       "22                                              0.0081       \n",
       "21                                              0.0143       \n",
       "20                                              0.0060       \n",
       "19                                              0.0001       \n",
       "18                                              0.0110       \n",
       "17                                              0.0122       \n",
       "16                                              0.0031       \n",
       "15                                              0.0003       \n",
       "14                                              0.0080       \n",
       "13                                              0.0148       \n",
       "12                                              0.0053       \n",
       "11                                              0.0059       \n",
       "10                                              0.0090       \n",
       "9                                               0.0158       \n",
       "8                                              -0.0036       \n",
       "7                                               0.0051       \n",
       "6                                               0.0099       \n",
       "5                                               0.0042       \n",
       "4                                               0.0079       \n",
       "3                                               0.0067       \n",
       "2                                               0.0028       \n",
       "1                                               0.0052       \n",
       "0                                                  NaN       \n",
       "\n",
       "     Seasonally-Adjusted Purchase-Only Index\\n% Change Over \\nPrevious 4 Quarters  \\\n",
       "104                                             0.0600                              \n",
       "103                                             0.0622                              \n",
       "102                                             0.0626                              \n",
       "101                                             0.0592                              \n",
       "100                                             0.0600                              \n",
       "99                                              0.0588                              \n",
       "98                                              0.0564                              \n",
       "97                                              0.0556                              \n",
       "96                                              0.0505                              \n",
       "95                                              0.0486                              \n",
       "94                                              0.0462                              \n",
       "93                                              0.0516                              \n",
       "92                                              0.0646                              \n",
       "91                                              0.0728                              \n",
       "90                                              0.0779                              \n",
       "89                                              0.0719                              \n",
       "88                                              0.0675                              \n",
       "87                                              0.0512                              \n",
       "86                                              0.0364                              \n",
       "85                                              0.0291                              \n",
       "84                                              0.0039                              \n",
       "83                                             -0.0233                              \n",
       "82                                             -0.0345                              \n",
       "81                                             -0.0548                              \n",
       "80                                             -0.0521                              \n",
       "79                                             -0.0402                              \n",
       "78                                             -0.0312                              \n",
       "77                                             -0.0198                              \n",
       "76                                             -0.0293                              \n",
       "75                                             -0.0244                              \n",
       "..                                                 ...                              \n",
       "29                                              0.0454                              \n",
       "28                                              0.0396                              \n",
       "27                                              0.0333                              \n",
       "26                                              0.0280                              \n",
       "25                                              0.0273                              \n",
       "24                                              0.0256                              \n",
       "23                                              0.0284                              \n",
       "22                                              0.0287                              \n",
       "21                                              0.0313                              \n",
       "20                                              0.0297                              \n",
       "19                                              0.0272                              \n",
       "18                                              0.0264                              \n",
       "17                                              0.0234                              \n",
       "16                                              0.0266                              \n",
       "15                                              0.0292                              \n",
       "14                                              0.0338                              \n",
       "13                                              0.0350                              \n",
       "12                                              0.0367                              \n",
       "11                                              0.0278                              \n",
       "10                                              0.0260                              \n",
       "9                                               0.0272                              \n",
       "8                                               0.0159                              \n",
       "7                                               0.0275                              \n",
       "6                                               0.0286                              \n",
       "5                                               0.0216                              \n",
       "4                                               0.0228                              \n",
       "3                                                  NaN                              \n",
       "2                                                  NaN                              \n",
       "1                                                  NaN                              \n",
       "0                                                  NaN                              \n",
       "\n",
       "     Seasonally-Adjusted Purchase-Only Index\\n% Change Over \\nPrevious Quarter  \n",
       "104                                             0.0139                          \n",
       "103                                             0.0154                          \n",
       "102                                             0.0160                          \n",
       "101                                             0.0134                          \n",
       "100                                             0.0160                          \n",
       "99                                              0.0158                          \n",
       "98                                              0.0128                          \n",
       "97                                              0.0141                          \n",
       "96                                              0.0149                          \n",
       "95                                              0.0135                          \n",
       "94                                              0.0120                          \n",
       "93                                              0.0093                          \n",
       "92                                              0.0130                          \n",
       "91                                              0.0112                          \n",
       "90                                              0.0172                          \n",
       "89                                              0.0218                          \n",
       "88                                              0.0208                          \n",
       "87                                              0.0160                          \n",
       "86                                              0.0116                          \n",
       "85                                              0.0175                          \n",
       "84                                              0.0052                          \n",
       "83                                              0.0016                          \n",
       "82                                              0.0045                          \n",
       "81                                             -0.0074                          \n",
       "80                                             -0.0220                          \n",
       "79                                             -0.0098                          \n",
       "78                                             -0.0167                          \n",
       "77                                             -0.0045                          \n",
       "76                                             -0.0097                          \n",
       "75                                             -0.0006                          \n",
       "..                                                 ...                          \n",
       "29                                              0.0141                          \n",
       "28                                              0.0123                          \n",
       "27                                              0.0108                          \n",
       "26                                              0.0074                          \n",
       "25                                              0.0085                          \n",
       "24                                              0.0062                          \n",
       "23                                              0.0056                          \n",
       "22                                              0.0067                          \n",
       "21                                              0.0069                          \n",
       "20                                              0.0089                          \n",
       "19                                              0.0059                          \n",
       "18                                              0.0093                          \n",
       "17                                              0.0053                          \n",
       "16                                              0.0065                          \n",
       "15                                              0.0051                          \n",
       "14                                              0.0063                          \n",
       "13                                              0.0085                          \n",
       "12                                              0.0090                          \n",
       "11                                              0.0096                          \n",
       "10                                              0.0075                          \n",
       "9                                               0.0101                          \n",
       "8                                               0.0004                          \n",
       "7                                               0.0079                          \n",
       "6                                               0.0087                          \n",
       "5                                              -0.0011                          \n",
       "4                                               0.0118                          \n",
       "3                                               0.0089                          \n",
       "2                                               0.0018                          \n",
       "1                                               0.0001                          \n",
       "0                                                  NaN                          \n",
       "\n",
       "[105 rows x 11 columns]"
      ]
     },
     "execution_count": 56,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "def us_data():\n",
    "    return pd.read_excel(\"https://www.fhfa.gov/DataTools/Downloads/Documents/HPI/HPI_PO_summary.xls\")\n",
    "us_data().sort_values(by=[\"Year\",\"Quarter\"],ascending=False)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Compare q1 2016 with Q1 2017"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 47,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "df17q1 = fulldf[(fulldf[\"yr\"] == 2017)&(fulldf[\"qtr\"] == 1)].set_index(\"state\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 48,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "df16q1 = fulldf[(fulldf[\"yr\"] == 2016)&(fulldf[\"qtr\"] == 1)].set_index(\"state\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 49,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "df1617q1 = df16q1.join(df17q1,how=\"outer\",lsuffix=\"_16\",rsuffix=\"_17\").reset_index()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 50,
   "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>state</th>\n",
       "      <th>yr_16</th>\n",
       "      <th>qtr_16</th>\n",
       "      <th>index_nsa_16</th>\n",
       "      <th>index_sa_16</th>\n",
       "      <th>Warning_16</th>\n",
       "      <th>yr_17</th>\n",
       "      <th>qtr_17</th>\n",
       "      <th>index_nsa_17</th>\n",
       "      <th>index_sa_17</th>\n",
       "      <th>Warning_17</th>\n",
       "      <th>sa_diff</th>\n",
       "      <th>sa_growth</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>DC</td>\n",
       "      <td>2016</td>\n",
       "      <td>1</td>\n",
       "      <td>457.53</td>\n",
       "      <td>460.01</td>\n",
       "      <td>* Note that this state has fewer than 15,000 t...</td>\n",
       "      <td>2017</td>\n",
       "      <td>1</td>\n",
       "      <td>520.78</td>\n",
       "      <td>523.77</td>\n",
       "      <td>* Note that this state has fewer than 15,000 t...</td>\n",
       "      <td>63.76</td>\n",
       "      <td>13.860568</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>CO</td>\n",
       "      <td>2016</td>\n",
       "      <td>1</td>\n",
       "      <td>373.54</td>\n",
       "      <td>375.56</td>\n",
       "      <td>NaN</td>\n",
       "      <td>2017</td>\n",
       "      <td>1</td>\n",
       "      <td>414.12</td>\n",
       "      <td>415.87</td>\n",
       "      <td>NaN</td>\n",
       "      <td>40.31</td>\n",
       "      <td>10.733305</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9</th>\n",
       "      <td>FL</td>\n",
       "      <td>2016</td>\n",
       "      <td>1</td>\n",
       "      <td>252.99</td>\n",
       "      <td>254.11</td>\n",
       "      <td>NaN</td>\n",
       "      <td>2017</td>\n",
       "      <td>1</td>\n",
       "      <td>276.20</td>\n",
       "      <td>277.32</td>\n",
       "      <td>NaN</td>\n",
       "      <td>23.21</td>\n",
       "      <td>9.133840</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>CA</td>\n",
       "      <td>2016</td>\n",
       "      <td>1</td>\n",
       "      <td>238.39</td>\n",
       "      <td>239.29</td>\n",
       "      <td>NaN</td>\n",
       "      <td>2017</td>\n",
       "      <td>1</td>\n",
       "      <td>255.85</td>\n",
       "      <td>256.72</td>\n",
       "      <td>NaN</td>\n",
       "      <td>17.43</td>\n",
       "      <td>7.284049</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>AZ</td>\n",
       "      <td>2016</td>\n",
       "      <td>1</td>\n",
       "      <td>264.11</td>\n",
       "      <td>265.46</td>\n",
       "      <td>NaN</td>\n",
       "      <td>2017</td>\n",
       "      <td>1</td>\n",
       "      <td>281.90</td>\n",
       "      <td>283.19</td>\n",
       "      <td>NaN</td>\n",
       "      <td>17.73</td>\n",
       "      <td>6.678972</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>AL</td>\n",
       "      <td>2016</td>\n",
       "      <td>1</td>\n",
       "      <td>197.50</td>\n",
       "      <td>198.70</td>\n",
       "      <td>NaN</td>\n",
       "      <td>2017</td>\n",
       "      <td>1</td>\n",
       "      <td>207.16</td>\n",
       "      <td>208.34</td>\n",
       "      <td>NaN</td>\n",
       "      <td>9.64</td>\n",
       "      <td>4.851535</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>AR</td>\n",
       "      <td>2016</td>\n",
       "      <td>1</td>\n",
       "      <td>200.41</td>\n",
       "      <td>199.77</td>\n",
       "      <td>NaN</td>\n",
       "      <td>2017</td>\n",
       "      <td>1</td>\n",
       "      <td>208.40</td>\n",
       "      <td>207.81</td>\n",
       "      <td>NaN</td>\n",
       "      <td>8.04</td>\n",
       "      <td>4.024628</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>AK</td>\n",
       "      <td>2016</td>\n",
       "      <td>1</td>\n",
       "      <td>246.42</td>\n",
       "      <td>250.11</td>\n",
       "      <td>* Note that this state has fewer than 15,000 t...</td>\n",
       "      <td>2017</td>\n",
       "      <td>1</td>\n",
       "      <td>248.92</td>\n",
       "      <td>252.49</td>\n",
       "      <td>* Note that this state has fewer than 15,000 t...</td>\n",
       "      <td>2.38</td>\n",
       "      <td>0.951581</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>CT</td>\n",
       "      <td>2016</td>\n",
       "      <td>1</td>\n",
       "      <td>164.93</td>\n",
       "      <td>166.74</td>\n",
       "      <td>NaN</td>\n",
       "      <td>2017</td>\n",
       "      <td>1</td>\n",
       "      <td>165.77</td>\n",
       "      <td>167.56</td>\n",
       "      <td>NaN</td>\n",
       "      <td>0.82</td>\n",
       "      <td>0.491784</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8</th>\n",
       "      <td>DE</td>\n",
       "      <td>2016</td>\n",
       "      <td>1</td>\n",
       "      <td>192.00</td>\n",
       "      <td>193.40</td>\n",
       "      <td>* Note that this state has fewer than 15,000 t...</td>\n",
       "      <td>2017</td>\n",
       "      <td>1</td>\n",
       "      <td>191.78</td>\n",
       "      <td>193.02</td>\n",
       "      <td>* Note that this state has fewer than 15,000 t...</td>\n",
       "      <td>-0.38</td>\n",
       "      <td>-0.196484</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "  state  yr_16  qtr_16  index_nsa_16  index_sa_16  \\\n",
       "7    DC   2016       1        457.53       460.01   \n",
       "5    CO   2016       1        373.54       375.56   \n",
       "9    FL   2016       1        252.99       254.11   \n",
       "4    CA   2016       1        238.39       239.29   \n",
       "3    AZ   2016       1        264.11       265.46   \n",
       "1    AL   2016       1        197.50       198.70   \n",
       "2    AR   2016       1        200.41       199.77   \n",
       "0    AK   2016       1        246.42       250.11   \n",
       "6    CT   2016       1        164.93       166.74   \n",
       "8    DE   2016       1        192.00       193.40   \n",
       "\n",
       "                                          Warning_16  yr_17  qtr_17  \\\n",
       "7  * Note that this state has fewer than 15,000 t...   2017       1   \n",
       "5                                                NaN   2017       1   \n",
       "9                                                NaN   2017       1   \n",
       "4                                                NaN   2017       1   \n",
       "3                                                NaN   2017       1   \n",
       "1                                                NaN   2017       1   \n",
       "2                                                NaN   2017       1   \n",
       "0  * Note that this state has fewer than 15,000 t...   2017       1   \n",
       "6                                                NaN   2017       1   \n",
       "8  * Note that this state has fewer than 15,000 t...   2017       1   \n",
       "\n",
       "   index_nsa_17  index_sa_17  \\\n",
       "7        520.78       523.77   \n",
       "5        414.12       415.87   \n",
       "9        276.20       277.32   \n",
       "4        255.85       256.72   \n",
       "3        281.90       283.19   \n",
       "1        207.16       208.34   \n",
       "2        208.40       207.81   \n",
       "0        248.92       252.49   \n",
       "6        165.77       167.56   \n",
       "8        191.78       193.02   \n",
       "\n",
       "                                          Warning_17  sa_diff  sa_growth  \n",
       "7  * Note that this state has fewer than 15,000 t...    63.76  13.860568  \n",
       "5                                                NaN    40.31  10.733305  \n",
       "9                                                NaN    23.21   9.133840  \n",
       "4                                                NaN    17.43   7.284049  \n",
       "3                                                NaN    17.73   6.678972  \n",
       "1                                                NaN     9.64   4.851535  \n",
       "2                                                NaN     8.04   4.024628  \n",
       "0  * Note that this state has fewer than 15,000 t...     2.38   0.951581  \n",
       "6                                                NaN     0.82   0.491784  \n",
       "8  * Note that this state has fewer than 15,000 t...    -0.38  -0.196484  "
      ]
     },
     "execution_count": 50,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df1617q1[\"sa_diff\"] = df1617q1[\"index_sa_17\"] - df1617q1[\"index_sa_16\"]\n",
    "df1617q1[\"sa_growth\"] = df1617q1[\"sa_diff\"] * 100 / df1617q1[\"index_sa_16\"]\n",
    "df1617q1.head(10).sort_values(by=\"sa_growth\",ascending=False)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Note: The above numbers match this report: https://www.fhfa.gov/AboutUs/Reports/ReportDocuments/2017Q1_HPI.pdf\n",
    "\n",
    "Good. That means my naive guess about how to calculate growth is correct."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 53,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "count    51.000000\n",
       "mean      5.449829\n",
       "std       3.073880\n",
       "min      -0.196484\n",
       "25%       3.864937\n",
       "50%       5.557514\n",
       "75%       7.316548\n",
       "max      13.860568\n",
       "Name: sa_growth, dtype: float64"
      ]
     },
     "execution_count": 53,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df1617q1[\"sa_growth\"].describe()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 73,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Index([u'Region', u'Year', u'Quarter', u'Purchase-Only Index \\n(1991Q1=100)',\n",
      "       u'Seasonally-Adjusted Purchase-Only Index \\n(1991Q1=100)',\n",
      "       u'HPI\\n% Change Over \\nPrevious 4 Quarters',\n",
      "       u'HPI\\n% Change Over \\nPrevious Quarter',\n",
      "       u'Purchase-Only Index\\n% Change Over \\nPrevious 4 Quarters',\n",
      "       u'Purchase-Only Index\\n% Change Over \\nPrevious Quarter',\n",
      "       u'Seasonally-Adjusted Purchase-Only Index\\n% Change Over \\nPrevious 4 Quarters',\n",
      "       u'Seasonally-Adjusted Purchase-Only Index\\n% Change Over \\nPrevious Quarter'],\n",
      "      dtype='object')\n"
     ]
    }
   ],
   "source": [
    "us_df = us_data()\n",
    "print us_df.columns"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 130,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "def timeline(df,y_col=\"Year\",q_col=\"Quarter\",\n",
    "             val_col='Seasonally-Adjusted Purchase-Only Index \\n(1991Q1=100)',\n",
    "            index_col=\"Region\"):\n",
    "    ret = df.copy()\n",
    "    \n",
    "    #ret [\"y-q\"] = ret.apply(lambda x: str(x[\"Year\"]) + \"-\" + str(x[\"Quarter\"]),axis=1)\n",
    "\n",
    "    ret = ret[ret[q_col] == 1]\n",
    "    ret = ret.pivot(columns=y_col,\n",
    "                    values=val_col,\n",
    "                    index=index_col)\\\n",
    "    .reset_index()\\\n",
    "    .transpose()\n",
    "    \n",
    "    \n",
    "    ret = ret.reset_index()#.set_index(\"y-q\")\n",
    "\n",
    "    ret.columns = \"year\",\"index\"\n",
    "    ret = ret.drop(0)\n",
    "    \n",
    "    ret = ret.set_index(\"year\")\n",
    "    \n",
    "    return ret\n",
    "\n",
    "us_timeline = timeline(us_df)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 131,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "ct_timeline = timeline(fulldf[fulldf[\"state\"] == \"CT\"],q_col=\"qtr\",y_col=\"yr\",val_col=\"index_sa\",index_col=\"state\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 132,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "year\tindex_CT\tindex_US\n",
      "1991\t100.0\t100.0\n",
      "1992\t97.39\t102.28\n",
      "1993\t92.62\t103.91\n",
      "1994\t91.57\t107.72\n",
      "1995\t90.86\t110.58\n",
      "1996\t91.2\t113.87\n",
      "1997\t91.3\t116.78\n",
      "1998\t94.06\t121.4\n",
      "1999\t101.96\t128.65\n",
      "2000\t110.89\t136.99\n",
      "2001\t120.95\t146.68\n",
      "2002\t133.2\t156.29\n",
      "2003\t149.87\t168.41\n",
      "2004\t163.97\t182.48\n",
      "2005\t183.51\t201.53\n",
      "2006\t197.41\t219.98\n",
      "2007\t198.47\t224.54\n",
      "2008\t190.62\t212.38\n",
      "2009\t179.42\t194.63\n",
      "2010\t171.55\t188.94\n",
      "2011\t165.43\t179.1\n",
      "2012\t160.47\t179.79\n",
      "2013\t161.43\t191.92\n",
      "2014\t164.74\t204.32\n",
      "2015\t165.59\t214.64\n",
      "2016\t166.74\t227.52\n",
      "2017\t167.56\t241.17\n",
      "\n"
     ]
    }
   ],
   "source": [
    "print ct_timeline.join(us_timeline,how=\"outer\",lsuffix=\"_CT\",rsuffix=\"_US\").to_csv(sep=\"\\t\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 133,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "state\tsa_growth\n",
      "DE\t-0.2%\n",
      "ND\t-0.07%\n",
      "WV\t0.45%\n",
      "CT\t0.49%\n",
      "HI\t0.77%\n",
      "AK\t0.95%\n",
      "MS\t1.52%\n",
      "WY\t1.81%\n",
      "LA\t2.04%\n",
      "NJ\t2.69%\n",
      "SC\t3.39%\n",
      "VA\t3.7%\n",
      "MD\t3.84%\n",
      "MT\t3.89%\n",
      "MO\t3.91%\n",
      "OK\t3.93%\n",
      "PA\t3.95%\n",
      "AR\t4.02%\n",
      "OH\t4.51%\n",
      "IA\t4.53%\n",
      "IN\t4.62%\n",
      "ME\t4.73%\n",
      "AL\t4.85%\n",
      "NY\t4.92%\n",
      "KS\t5.35%\n",
      "WI\t5.56%\n",
      "SD\t5.63%\n",
      "NM\t5.69%\n",
      "IL\t5.7%\n",
      "MN\t6.01%\n",
      "KY\t6.33%\n",
      "NC\t6.34%\n",
      "RI\t6.47%\n",
      "MA\t6.56%\n",
      "GA\t6.59%\n",
      "AZ\t6.68%\n",
      "TX\t7.15%\n",
      "CA\t7.28%\n",
      "NE\t7.35%\n",
      "VT\t7.74%\n",
      "MI\t7.8%\n",
      "TN\t7.87%\n",
      "UT\t8.78%\n",
      "NV\t8.88%\n",
      "FL\t9.13%\n",
      "OR\t9.14%\n",
      "NH\t9.55%\n",
      "WA\t10.23%\n",
      "ID\t10.3%\n",
      "CO\t10.73%\n",
      "DC\t13.86%\n",
      "\n"
     ]
    }
   ],
   "source": [
    "states_pct_df = df1617q1.sort_values(by=\"sa_growth\",ascending=True)[[\"state\",\"sa_growth\"]].set_index(\"state\")\n",
    "states_pct_df[\"sa_growth\"] = states_pct_df[\"sa_growth\"].apply(lambda x: round(x,2))\n",
    "states_pct_df[\"sa_growth\"] = states_pct_df[\"sa_growth\"].astype(str) + \"%\"\n",
    "states_pct_df\n",
    "print states_pct_df.to_csv(sep=\"\\t\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 135,
   "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>state</th>\n",
       "      <th>yr</th>\n",
       "      <th>qtr</th>\n",
       "      <th>index_nsa</th>\n",
       "      <th>index_sa</th>\n",
       "      <th>Warning</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>694</th>\n",
       "      <td>CT</td>\n",
       "      <td>2007</td>\n",
       "      <td>1</td>\n",
       "      <td>196.72</td>\n",
       "      <td>198.47</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>690</th>\n",
       "      <td>CT</td>\n",
       "      <td>2006</td>\n",
       "      <td>1</td>\n",
       "      <td>195.62</td>\n",
       "      <td>197.41</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>691</th>\n",
       "      <td>CT</td>\n",
       "      <td>2006</td>\n",
       "      <td>2</td>\n",
       "      <td>199.98</td>\n",
       "      <td>197.33</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>696</th>\n",
       "      <td>CT</td>\n",
       "      <td>2007</td>\n",
       "      <td>3</td>\n",
       "      <td>199.34</td>\n",
       "      <td>196.90</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>695</th>\n",
       "      <td>CT</td>\n",
       "      <td>2007</td>\n",
       "      <td>2</td>\n",
       "      <td>198.91</td>\n",
       "      <td>196.18</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>693</th>\n",
       "      <td>CT</td>\n",
       "      <td>2006</td>\n",
       "      <td>4</td>\n",
       "      <td>194.63</td>\n",
       "      <td>195.53</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>692</th>\n",
       "      <td>CT</td>\n",
       "      <td>2006</td>\n",
       "      <td>3</td>\n",
       "      <td>197.71</td>\n",
       "      <td>195.27</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>689</th>\n",
       "      <td>CT</td>\n",
       "      <td>2005</td>\n",
       "      <td>4</td>\n",
       "      <td>194.02</td>\n",
       "      <td>194.79</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>697</th>\n",
       "      <td>CT</td>\n",
       "      <td>2007</td>\n",
       "      <td>4</td>\n",
       "      <td>193.26</td>\n",
       "      <td>194.09</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>688</th>\n",
       "      <td>CT</td>\n",
       "      <td>2005</td>\n",
       "      <td>3</td>\n",
       "      <td>193.87</td>\n",
       "      <td>191.27</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>698</th>\n",
       "      <td>CT</td>\n",
       "      <td>2008</td>\n",
       "      <td>1</td>\n",
       "      <td>188.73</td>\n",
       "      <td>190.62</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>687</th>\n",
       "      <td>CT</td>\n",
       "      <td>2005</td>\n",
       "      <td>2</td>\n",
       "      <td>189.75</td>\n",
       "      <td>187.58</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>699</th>\n",
       "      <td>CT</td>\n",
       "      <td>2008</td>\n",
       "      <td>2</td>\n",
       "      <td>190.09</td>\n",
       "      <td>187.40</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>700</th>\n",
       "      <td>CT</td>\n",
       "      <td>2008</td>\n",
       "      <td>3</td>\n",
       "      <td>186.18</td>\n",
       "      <td>183.89</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>686</th>\n",
       "      <td>CT</td>\n",
       "      <td>2005</td>\n",
       "      <td>1</td>\n",
       "      <td>181.76</td>\n",
       "      <td>183.51</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>701</th>\n",
       "      <td>CT</td>\n",
       "      <td>2008</td>\n",
       "      <td>4</td>\n",
       "      <td>180.53</td>\n",
       "      <td>181.10</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>702</th>\n",
       "      <td>CT</td>\n",
       "      <td>2009</td>\n",
       "      <td>1</td>\n",
       "      <td>177.31</td>\n",
       "      <td>179.42</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>685</th>\n",
       "      <td>CT</td>\n",
       "      <td>2004</td>\n",
       "      <td>4</td>\n",
       "      <td>178.59</td>\n",
       "      <td>179.01</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>703</th>\n",
       "      <td>CT</td>\n",
       "      <td>2009</td>\n",
       "      <td>2</td>\n",
       "      <td>178.57</td>\n",
       "      <td>175.97</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>684</th>\n",
       "      <td>CT</td>\n",
       "      <td>2004</td>\n",
       "      <td>3</td>\n",
       "      <td>177.53</td>\n",
       "      <td>175.02</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>704</th>\n",
       "      <td>CT</td>\n",
       "      <td>2009</td>\n",
       "      <td>3</td>\n",
       "      <td>177.03</td>\n",
       "      <td>174.80</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>705</th>\n",
       "      <td>CT</td>\n",
       "      <td>2009</td>\n",
       "      <td>4</td>\n",
       "      <td>173.03</td>\n",
       "      <td>173.26</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>706</th>\n",
       "      <td>CT</td>\n",
       "      <td>2010</td>\n",
       "      <td>1</td>\n",
       "      <td>169.00</td>\n",
       "      <td>171.55</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>707</th>\n",
       "      <td>CT</td>\n",
       "      <td>2010</td>\n",
       "      <td>2</td>\n",
       "      <td>173.37</td>\n",
       "      <td>170.65</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>683</th>\n",
       "      <td>CT</td>\n",
       "      <td>2004</td>\n",
       "      <td>2</td>\n",
       "      <td>170.85</td>\n",
       "      <td>169.18</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>708</th>\n",
       "      <td>CT</td>\n",
       "      <td>2010</td>\n",
       "      <td>3</td>\n",
       "      <td>170.43</td>\n",
       "      <td>168.19</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>733</th>\n",
       "      <td>CT</td>\n",
       "      <td>2016</td>\n",
       "      <td>4</td>\n",
       "      <td>166.78</td>\n",
       "      <td>167.72</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>734</th>\n",
       "      <td>CT</td>\n",
       "      <td>2017</td>\n",
       "      <td>1</td>\n",
       "      <td>165.77</td>\n",
       "      <td>167.56</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>709</th>\n",
       "      <td>CT</td>\n",
       "      <td>2010</td>\n",
       "      <td>4</td>\n",
       "      <td>166.79</td>\n",
       "      <td>166.84</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>730</th>\n",
       "      <td>CT</td>\n",
       "      <td>2016</td>\n",
       "      <td>1</td>\n",
       "      <td>164.93</td>\n",
       "      <td>166.74</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>631</th>\n",
       "      <td>CT</td>\n",
       "      <td>1991</td>\n",
       "      <td>2</td>\n",
       "      <td>97.82</td>\n",
       "      <td>98.04</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>660</th>\n",
       "      <td>CT</td>\n",
       "      <td>1998</td>\n",
       "      <td>3</td>\n",
       "      <td>98.60</td>\n",
       "      <td>97.53</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>634</th>\n",
       "      <td>CT</td>\n",
       "      <td>1992</td>\n",
       "      <td>1</td>\n",
       "      <td>97.30</td>\n",
       "      <td>97.39</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>632</th>\n",
       "      <td>CT</td>\n",
       "      <td>1991</td>\n",
       "      <td>3</td>\n",
       "      <td>97.08</td>\n",
       "      <td>96.28</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>633</th>\n",
       "      <td>CT</td>\n",
       "      <td>1991</td>\n",
       "      <td>4</td>\n",
       "      <td>96.58</td>\n",
       "      <td>95.89</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>659</th>\n",
       "      <td>CT</td>\n",
       "      <td>1998</td>\n",
       "      <td>2</td>\n",
       "      <td>96.38</td>\n",
       "      <td>95.59</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>635</th>\n",
       "      <td>CT</td>\n",
       "      <td>1992</td>\n",
       "      <td>2</td>\n",
       "      <td>95.22</td>\n",
       "      <td>95.42</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>637</th>\n",
       "      <td>CT</td>\n",
       "      <td>1992</td>\n",
       "      <td>4</td>\n",
       "      <td>96.03</td>\n",
       "      <td>95.37</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>636</th>\n",
       "      <td>CT</td>\n",
       "      <td>1992</td>\n",
       "      <td>3</td>\n",
       "      <td>95.11</td>\n",
       "      <td>94.24</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>658</th>\n",
       "      <td>CT</td>\n",
       "      <td>1998</td>\n",
       "      <td>1</td>\n",
       "      <td>93.41</td>\n",
       "      <td>94.06</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>657</th>\n",
       "      <td>CT</td>\n",
       "      <td>1997</td>\n",
       "      <td>4</td>\n",
       "      <td>93.39</td>\n",
       "      <td>93.33</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>638</th>\n",
       "      <td>CT</td>\n",
       "      <td>1993</td>\n",
       "      <td>1</td>\n",
       "      <td>92.40</td>\n",
       "      <td>92.62</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>656</th>\n",
       "      <td>CT</td>\n",
       "      <td>1997</td>\n",
       "      <td>3</td>\n",
       "      <td>93.53</td>\n",
       "      <td>92.48</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>655</th>\n",
       "      <td>CT</td>\n",
       "      <td>1997</td>\n",
       "      <td>2</td>\n",
       "      <td>92.65</td>\n",
       "      <td>92.00</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>643</th>\n",
       "      <td>CT</td>\n",
       "      <td>1994</td>\n",
       "      <td>2</td>\n",
       "      <td>91.89</td>\n",
       "      <td>91.84</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>644</th>\n",
       "      <td>CT</td>\n",
       "      <td>1994</td>\n",
       "      <td>3</td>\n",
       "      <td>92.82</td>\n",
       "      <td>91.75</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>639</th>\n",
       "      <td>CT</td>\n",
       "      <td>1993</td>\n",
       "      <td>2</td>\n",
       "      <td>91.59</td>\n",
       "      <td>91.70</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>642</th>\n",
       "      <td>CT</td>\n",
       "      <td>1994</td>\n",
       "      <td>1</td>\n",
       "      <td>91.20</td>\n",
       "      <td>91.57</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>651</th>\n",
       "      <td>CT</td>\n",
       "      <td>1996</td>\n",
       "      <td>2</td>\n",
       "      <td>91.95</td>\n",
       "      <td>91.46</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>645</th>\n",
       "      <td>CT</td>\n",
       "      <td>1994</td>\n",
       "      <td>4</td>\n",
       "      <td>91.85</td>\n",
       "      <td>91.45</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>640</th>\n",
       "      <td>CT</td>\n",
       "      <td>1993</td>\n",
       "      <td>3</td>\n",
       "      <td>92.38</td>\n",
       "      <td>91.41</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>641</th>\n",
       "      <td>CT</td>\n",
       "      <td>1993</td>\n",
       "      <td>4</td>\n",
       "      <td>91.93</td>\n",
       "      <td>91.39</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>654</th>\n",
       "      <td>CT</td>\n",
       "      <td>1997</td>\n",
       "      <td>1</td>\n",
       "      <td>90.70</td>\n",
       "      <td>91.30</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>650</th>\n",
       "      <td>CT</td>\n",
       "      <td>1996</td>\n",
       "      <td>1</td>\n",
       "      <td>90.63</td>\n",
       "      <td>91.20</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>652</th>\n",
       "      <td>CT</td>\n",
       "      <td>1996</td>\n",
       "      <td>3</td>\n",
       "      <td>91.99</td>\n",
       "      <td>90.92</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>646</th>\n",
       "      <td>CT</td>\n",
       "      <td>1995</td>\n",
       "      <td>1</td>\n",
       "      <td>90.37</td>\n",
       "      <td>90.86</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>649</th>\n",
       "      <td>CT</td>\n",
       "      <td>1995</td>\n",
       "      <td>4</td>\n",
       "      <td>91.02</td>\n",
       "      <td>90.79</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>648</th>\n",
       "      <td>CT</td>\n",
       "      <td>1995</td>\n",
       "      <td>3</td>\n",
       "      <td>91.89</td>\n",
       "      <td>90.79</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>653</th>\n",
       "      <td>CT</td>\n",
       "      <td>1996</td>\n",
       "      <td>4</td>\n",
       "      <td>90.84</td>\n",
       "      <td>90.73</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>647</th>\n",
       "      <td>CT</td>\n",
       "      <td>1995</td>\n",
       "      <td>2</td>\n",
       "      <td>90.68</td>\n",
       "      <td>90.43</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>105 rows × 6 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "    state    yr  qtr  index_nsa  index_sa Warning\n",
       "694    CT  2007    1     196.72    198.47     NaN\n",
       "690    CT  2006    1     195.62    197.41     NaN\n",
       "691    CT  2006    2     199.98    197.33     NaN\n",
       "696    CT  2007    3     199.34    196.90     NaN\n",
       "695    CT  2007    2     198.91    196.18     NaN\n",
       "693    CT  2006    4     194.63    195.53     NaN\n",
       "692    CT  2006    3     197.71    195.27     NaN\n",
       "689    CT  2005    4     194.02    194.79     NaN\n",
       "697    CT  2007    4     193.26    194.09     NaN\n",
       "688    CT  2005    3     193.87    191.27     NaN\n",
       "698    CT  2008    1     188.73    190.62     NaN\n",
       "687    CT  2005    2     189.75    187.58     NaN\n",
       "699    CT  2008    2     190.09    187.40     NaN\n",
       "700    CT  2008    3     186.18    183.89     NaN\n",
       "686    CT  2005    1     181.76    183.51     NaN\n",
       "701    CT  2008    4     180.53    181.10     NaN\n",
       "702    CT  2009    1     177.31    179.42     NaN\n",
       "685    CT  2004    4     178.59    179.01     NaN\n",
       "703    CT  2009    2     178.57    175.97     NaN\n",
       "684    CT  2004    3     177.53    175.02     NaN\n",
       "704    CT  2009    3     177.03    174.80     NaN\n",
       "705    CT  2009    4     173.03    173.26     NaN\n",
       "706    CT  2010    1     169.00    171.55     NaN\n",
       "707    CT  2010    2     173.37    170.65     NaN\n",
       "683    CT  2004    2     170.85    169.18     NaN\n",
       "708    CT  2010    3     170.43    168.19     NaN\n",
       "733    CT  2016    4     166.78    167.72     NaN\n",
       "734    CT  2017    1     165.77    167.56     NaN\n",
       "709    CT  2010    4     166.79    166.84     NaN\n",
       "730    CT  2016    1     164.93    166.74     NaN\n",
       "..    ...   ...  ...        ...       ...     ...\n",
       "631    CT  1991    2      97.82     98.04     NaN\n",
       "660    CT  1998    3      98.60     97.53     NaN\n",
       "634    CT  1992    1      97.30     97.39     NaN\n",
       "632    CT  1991    3      97.08     96.28     NaN\n",
       "633    CT  1991    4      96.58     95.89     NaN\n",
       "659    CT  1998    2      96.38     95.59     NaN\n",
       "635    CT  1992    2      95.22     95.42     NaN\n",
       "637    CT  1992    4      96.03     95.37     NaN\n",
       "636    CT  1992    3      95.11     94.24     NaN\n",
       "658    CT  1998    1      93.41     94.06     NaN\n",
       "657    CT  1997    4      93.39     93.33     NaN\n",
       "638    CT  1993    1      92.40     92.62     NaN\n",
       "656    CT  1997    3      93.53     92.48     NaN\n",
       "655    CT  1997    2      92.65     92.00     NaN\n",
       "643    CT  1994    2      91.89     91.84     NaN\n",
       "644    CT  1994    3      92.82     91.75     NaN\n",
       "639    CT  1993    2      91.59     91.70     NaN\n",
       "642    CT  1994    1      91.20     91.57     NaN\n",
       "651    CT  1996    2      91.95     91.46     NaN\n",
       "645    CT  1994    4      91.85     91.45     NaN\n",
       "640    CT  1993    3      92.38     91.41     NaN\n",
       "641    CT  1993    4      91.93     91.39     NaN\n",
       "654    CT  1997    1      90.70     91.30     NaN\n",
       "650    CT  1996    1      90.63     91.20     NaN\n",
       "652    CT  1996    3      91.99     90.92     NaN\n",
       "646    CT  1995    1      90.37     90.86     NaN\n",
       "649    CT  1995    4      91.02     90.79     NaN\n",
       "648    CT  1995    3      91.89     90.79     NaN\n",
       "653    CT  1996    4      90.84     90.73     NaN\n",
       "647    CT  1995    2      90.68     90.43     NaN\n",
       "\n",
       "[105 rows x 6 columns]"
      ]
     },
     "execution_count": 135,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "fulldf[(fulldf[\"state\"]==\"CT\")].sort_values(by=\"index_sa\",ascending=False)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": []
  },
  {
   "cell_type": "code",
   "execution_count": 151,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": []
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 2",
   "language": "python",
   "name": "python2"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 2
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython2",
   "version": "2.7.13"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 0
}