Build_analogues.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Cathy NANGINI<br>\n",
    "Dataviz Scientist at LSCE (Climate and Environment Sciences Laboratory)<br>\n",
    "<img align=\"left\" width=\"40%\" src=\"http://www.lsce.ipsl.fr/Css/img/banniere_LSCE_75.png\"><br><br>\n",
    "<hr>\n",
    "\n",
    "Coded: 15.02.2016\n",
    "Last modified: 18.02.2016"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 1,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "import numpy as np\n",
    "import pandas as pd\n",
    "import collections\n",
    "import os"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "num_analogues = 20 #number of analogues searched for"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "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>date.an1</th>\n",
       "      <th>date.an2</th>\n",
       "      <th>date.an3</th>\n",
       "      <th>date.an4</th>\n",
       "      <th>date.an5</th>\n",
       "      <th>date.an6</th>\n",
       "      <th>date.an7</th>\n",
       "      <th>date.an8</th>\n",
       "      <th>date.an9</th>\n",
       "      <th>date.an10</th>\n",
       "      <th>...</th>\n",
       "      <th>cor11</th>\n",
       "      <th>cor12</th>\n",
       "      <th>cor13</th>\n",
       "      <th>cor14</th>\n",
       "      <th>cor15</th>\n",
       "      <th>cor16</th>\n",
       "      <th>cor17</th>\n",
       "      <th>cor18</th>\n",
       "      <th>cor19</th>\n",
       "      <th>cor20</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>date</th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>20151216</th>\n",
       "      <td>19481130</td>\n",
       "      <td>19481129</td>\n",
       "      <td>19831124</td>\n",
       "      <td>19481210</td>\n",
       "      <td>19721216</td>\n",
       "      <td>19771221</td>\n",
       "      <td>19481201</td>\n",
       "      <td>19711218</td>\n",
       "      <td>19941201</td>\n",
       "      <td>19721215</td>\n",
       "      <td>...</td>\n",
       "      <td>0.858417</td>\n",
       "      <td>0.600339</td>\n",
       "      <td>0.580164</td>\n",
       "      <td>0.667006</td>\n",
       "      <td>0.649544</td>\n",
       "      <td>0.706988</td>\n",
       "      <td>0.679815</td>\n",
       "      <td>0.613030</td>\n",
       "      <td>0.492617</td>\n",
       "      <td>0.566106</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>20151217</th>\n",
       "      <td>19831124</td>\n",
       "      <td>19741224</td>\n",
       "      <td>20001211</td>\n",
       "      <td>19971223</td>\n",
       "      <td>19911123</td>\n",
       "      <td>20001202</td>\n",
       "      <td>20061129</td>\n",
       "      <td>19481205</td>\n",
       "      <td>20091120</td>\n",
       "      <td>20001203</td>\n",
       "      <td>...</td>\n",
       "      <td>0.649169</td>\n",
       "      <td>0.625368</td>\n",
       "      <td>0.566788</td>\n",
       "      <td>0.691608</td>\n",
       "      <td>0.612326</td>\n",
       "      <td>0.447951</td>\n",
       "      <td>0.652282</td>\n",
       "      <td>0.725638</td>\n",
       "      <td>0.604856</td>\n",
       "      <td>0.457750</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>20151218</th>\n",
       "      <td>20091120</td>\n",
       "      <td>19741221</td>\n",
       "      <td>19741225</td>\n",
       "      <td>19741222</td>\n",
       "      <td>20061126</td>\n",
       "      <td>19761127</td>\n",
       "      <td>19911123</td>\n",
       "      <td>20091121</td>\n",
       "      <td>19911124</td>\n",
       "      <td>19981228</td>\n",
       "      <td>...</td>\n",
       "      <td>0.623932</td>\n",
       "      <td>0.685005</td>\n",
       "      <td>0.665350</td>\n",
       "      <td>0.556471</td>\n",
       "      <td>0.728755</td>\n",
       "      <td>0.601237</td>\n",
       "      <td>0.570927</td>\n",
       "      <td>0.565296</td>\n",
       "      <td>0.545892</td>\n",
       "      <td>0.773745</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>20151219</th>\n",
       "      <td>20091121</td>\n",
       "      <td>20070107</td>\n",
       "      <td>20000105</td>\n",
       "      <td>20061202</td>\n",
       "      <td>19741225</td>\n",
       "      <td>19911125</td>\n",
       "      <td>20131230</td>\n",
       "      <td>20050111</td>\n",
       "      <td>19681125</td>\n",
       "      <td>19620109</td>\n",
       "      <td>...</td>\n",
       "      <td>0.634742</td>\n",
       "      <td>0.718378</td>\n",
       "      <td>0.658484</td>\n",
       "      <td>0.674121</td>\n",
       "      <td>0.698979</td>\n",
       "      <td>0.833905</td>\n",
       "      <td>0.548934</td>\n",
       "      <td>0.684943</td>\n",
       "      <td>0.484342</td>\n",
       "      <td>0.761014</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>20151220</th>\n",
       "      <td>20070107</td>\n",
       "      <td>19901231</td>\n",
       "      <td>19900119</td>\n",
       "      <td>20070117</td>\n",
       "      <td>20061202</td>\n",
       "      <td>19721204</td>\n",
       "      <td>19990117</td>\n",
       "      <td>20061203</td>\n",
       "      <td>19891222</td>\n",
       "      <td>19761130</td>\n",
       "      <td>...</td>\n",
       "      <td>0.739972</td>\n",
       "      <td>0.742671</td>\n",
       "      <td>0.741633</td>\n",
       "      <td>0.609813</td>\n",
       "      <td>0.759362</td>\n",
       "      <td>0.825590</td>\n",
       "      <td>0.744857</td>\n",
       "      <td>0.658490</td>\n",
       "      <td>0.740220</td>\n",
       "      <td>0.680282</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>20151221</th>\n",
       "      <td>19930120</td>\n",
       "      <td>19930119</td>\n",
       "      <td>20050110</td>\n",
       "      <td>20061231</td>\n",
       "      <td>20050109</td>\n",
       "      <td>20061202</td>\n",
       "      <td>20050108</td>\n",
       "      <td>20131222</td>\n",
       "      <td>20070117</td>\n",
       "      <td>20131223</td>\n",
       "      <td>...</td>\n",
       "      <td>0.749093</td>\n",
       "      <td>0.810943</td>\n",
       "      <td>0.756179</td>\n",
       "      <td>0.791931</td>\n",
       "      <td>0.744660</td>\n",
       "      <td>0.784398</td>\n",
       "      <td>0.739902</td>\n",
       "      <td>0.738095</td>\n",
       "      <td>0.759026</td>\n",
       "      <td>0.818859</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>20151222</th>\n",
       "      <td>20131222</td>\n",
       "      <td>20050110</td>\n",
       "      <td>20131221</td>\n",
       "      <td>20150112</td>\n",
       "      <td>19890111</td>\n",
       "      <td>19930120</td>\n",
       "      <td>20131223</td>\n",
       "      <td>20050109</td>\n",
       "      <td>20150113</td>\n",
       "      <td>20050108</td>\n",
       "      <td>...</td>\n",
       "      <td>0.831017</td>\n",
       "      <td>0.805147</td>\n",
       "      <td>0.774792</td>\n",
       "      <td>0.737730</td>\n",
       "      <td>0.770455</td>\n",
       "      <td>0.805592</td>\n",
       "      <td>0.788998</td>\n",
       "      <td>0.795123</td>\n",
       "      <td>0.793576</td>\n",
       "      <td>0.837264</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>20151223</th>\n",
       "      <td>20050110</td>\n",
       "      <td>20131222</td>\n",
       "      <td>20150113</td>\n",
       "      <td>20050109</td>\n",
       "      <td>19481203</td>\n",
       "      <td>20070115</td>\n",
       "      <td>19930120</td>\n",
       "      <td>20050108</td>\n",
       "      <td>20150112</td>\n",
       "      <td>20050107</td>\n",
       "      <td>...</td>\n",
       "      <td>0.723753</td>\n",
       "      <td>0.813239</td>\n",
       "      <td>0.793027</td>\n",
       "      <td>0.822214</td>\n",
       "      <td>0.700882</td>\n",
       "      <td>0.756600</td>\n",
       "      <td>0.758402</td>\n",
       "      <td>0.737111</td>\n",
       "      <td>0.757936</td>\n",
       "      <td>0.776397</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>20151224</th>\n",
       "      <td>19750112</td>\n",
       "      <td>20050110</td>\n",
       "      <td>19861203</td>\n",
       "      <td>19990119</td>\n",
       "      <td>19481203</td>\n",
       "      <td>20050111</td>\n",
       "      <td>19830111</td>\n",
       "      <td>19741222</td>\n",
       "      <td>20001212</td>\n",
       "      <td>20070115</td>\n",
       "      <td>...</td>\n",
       "      <td>0.753615</td>\n",
       "      <td>0.756670</td>\n",
       "      <td>0.614877</td>\n",
       "      <td>0.640223</td>\n",
       "      <td>0.661410</td>\n",
       "      <td>0.659859</td>\n",
       "      <td>0.523267</td>\n",
       "      <td>0.694998</td>\n",
       "      <td>0.522598</td>\n",
       "      <td>0.645233</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>20151225</th>\n",
       "      <td>20080120</td>\n",
       "      <td>19990105</td>\n",
       "      <td>20061215</td>\n",
       "      <td>19970118</td>\n",
       "      <td>19771224</td>\n",
       "      <td>19571222</td>\n",
       "      <td>20111201</td>\n",
       "      <td>19921217</td>\n",
       "      <td>19531127</td>\n",
       "      <td>19760108</td>\n",
       "      <td>...</td>\n",
       "      <td>0.654542</td>\n",
       "      <td>0.650501</td>\n",
       "      <td>0.595649</td>\n",
       "      <td>0.632474</td>\n",
       "      <td>0.661503</td>\n",
       "      <td>0.522062</td>\n",
       "      <td>0.603675</td>\n",
       "      <td>0.479803</td>\n",
       "      <td>0.559736</td>\n",
       "      <td>0.655751</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>10 rows × 60 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "          date.an1  date.an2  date.an3  date.an4  date.an5  date.an6  \\\n",
       "date                                                                   \n",
       "20151216  19481130  19481129  19831124  19481210  19721216  19771221   \n",
       "20151217  19831124  19741224  20001211  19971223  19911123  20001202   \n",
       "20151218  20091120  19741221  19741225  19741222  20061126  19761127   \n",
       "20151219  20091121  20070107  20000105  20061202  19741225  19911125   \n",
       "20151220  20070107  19901231  19900119  20070117  20061202  19721204   \n",
       "20151221  19930120  19930119  20050110  20061231  20050109  20061202   \n",
       "20151222  20131222  20050110  20131221  20150112  19890111  19930120   \n",
       "20151223  20050110  20131222  20150113  20050109  19481203  20070115   \n",
       "20151224  19750112  20050110  19861203  19990119  19481203  20050111   \n",
       "20151225  20080120  19990105  20061215  19970118  19771224  19571222   \n",
       "\n",
       "          date.an7  date.an8  date.an9  date.an10    ...        cor11  \\\n",
       "date                                                 ...                \n",
       "20151216  19481201  19711218  19941201   19721215    ...     0.858417   \n",
       "20151217  20061129  19481205  20091120   20001203    ...     0.649169   \n",
       "20151218  19911123  20091121  19911124   19981228    ...     0.623932   \n",
       "20151219  20131230  20050111  19681125   19620109    ...     0.634742   \n",
       "20151220  19990117  20061203  19891222   19761130    ...     0.739972   \n",
       "20151221  20050108  20131222  20070117   20131223    ...     0.749093   \n",
       "20151222  20131223  20050109  20150113   20050108    ...     0.831017   \n",
       "20151223  19930120  20050108  20150112   20050107    ...     0.723753   \n",
       "20151224  19830111  19741222  20001212   20070115    ...     0.753615   \n",
       "20151225  20111201  19921217  19531127   19760108    ...     0.654542   \n",
       "\n",
       "             cor12     cor13     cor14     cor15     cor16     cor17  \\\n",
       "date                                                                   \n",
       "20151216  0.600339  0.580164  0.667006  0.649544  0.706988  0.679815   \n",
       "20151217  0.625368  0.566788  0.691608  0.612326  0.447951  0.652282   \n",
       "20151218  0.685005  0.665350  0.556471  0.728755  0.601237  0.570927   \n",
       "20151219  0.718378  0.658484  0.674121  0.698979  0.833905  0.548934   \n",
       "20151220  0.742671  0.741633  0.609813  0.759362  0.825590  0.744857   \n",
       "20151221  0.810943  0.756179  0.791931  0.744660  0.784398  0.739902   \n",
       "20151222  0.805147  0.774792  0.737730  0.770455  0.805592  0.788998   \n",
       "20151223  0.813239  0.793027  0.822214  0.700882  0.756600  0.758402   \n",
       "20151224  0.756670  0.614877  0.640223  0.661410  0.659859  0.523267   \n",
       "20151225  0.650501  0.595649  0.632474  0.661503  0.522062  0.603675   \n",
       "\n",
       "             cor18     cor19     cor20  \n",
       "date                                    \n",
       "20151216  0.613030  0.492617  0.566106  \n",
       "20151217  0.725638  0.604856  0.457750  \n",
       "20151218  0.565296  0.545892  0.773745  \n",
       "20151219  0.684943  0.484342  0.761014  \n",
       "20151220  0.658490  0.740220  0.680282  \n",
       "20151221  0.738095  0.759026  0.818859  \n",
       "20151222  0.795123  0.793576  0.837264  \n",
       "20151223  0.737111  0.757936  0.776397  \n",
       "20151224  0.694998  0.522598  0.645233  \n",
       "20151225  0.479803  0.559736  0.655751  \n",
       "\n",
       "[10 rows x 60 columns]"
      ]
     },
     "execution_count": 3,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Create dataframe from analogues file\n",
    "\n",
    "dfS = pd.DataFrame()\n",
    "dfS = pd.read_csv('analogues.tsv', delimiter=' ', index_col=0)\n",
    "#dfS.set_index('Date', inplace=True)\n",
    "\n",
    "dfS.tail(10)\n",
    "\n",
    "#dfS.index.name"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "date\n",
       "19480101    19480101\n",
       "19480102    19480102\n",
       "19480103    19480103\n",
       "19480104    19480104\n",
       "19480105    19480105\n",
       "19480106    19480106\n",
       "19480107    19480107\n",
       "19480108    19480108\n",
       "19480109    19480109\n",
       "19480110    19480110\n",
       "Name: dateRef, dtype: int64"
      ]
     },
     "execution_count": 4,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "#SAVE DATE INDEX\n",
    "#DF['id2'] = DF.index\n",
    "dfS['dateRef'] = dfS.index\n",
    "dfS['dateRef'].head(10)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "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>date.an1</th>\n",
       "      <th>date.an2</th>\n",
       "      <th>date.an3</th>\n",
       "      <th>date.an4</th>\n",
       "      <th>date.an5</th>\n",
       "      <th>date.an6</th>\n",
       "      <th>date.an7</th>\n",
       "      <th>date.an8</th>\n",
       "      <th>date.an9</th>\n",
       "      <th>date.an10</th>\n",
       "      <th>date.an11</th>\n",
       "      <th>date.an12</th>\n",
       "      <th>date.an13</th>\n",
       "      <th>date.an14</th>\n",
       "      <th>date.an15</th>\n",
       "      <th>date.an16</th>\n",
       "      <th>date.an17</th>\n",
       "      <th>date.an18</th>\n",
       "      <th>date.an19</th>\n",
       "      <th>date.an20</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>date</th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>19480101</th>\n",
       "      <td>19560126</td>\n",
       "      <td>19850131</td>\n",
       "      <td>20110114</td>\n",
       "      <td>19551224</td>\n",
       "      <td>19850130</td>\n",
       "      <td>19980114</td>\n",
       "      <td>19521221</td>\n",
       "      <td>20001211</td>\n",
       "      <td>19681220</td>\n",
       "      <td>19871224</td>\n",
       "      <td>19870101</td>\n",
       "      <td>19540119</td>\n",
       "      <td>19610109</td>\n",
       "      <td>19560125</td>\n",
       "      <td>20010108</td>\n",
       "      <td>20110112</td>\n",
       "      <td>19651223</td>\n",
       "      <td>19651213</td>\n",
       "      <td>20110113</td>\n",
       "      <td>20031209</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>19480102</th>\n",
       "      <td>20001211</td>\n",
       "      <td>19681221</td>\n",
       "      <td>19551224</td>\n",
       "      <td>20110112</td>\n",
       "      <td>19560126</td>\n",
       "      <td>19741220</td>\n",
       "      <td>19600121</td>\n",
       "      <td>19681220</td>\n",
       "      <td>19521221</td>\n",
       "      <td>19850131</td>\n",
       "      <td>19741219</td>\n",
       "      <td>19670126</td>\n",
       "      <td>19600119</td>\n",
       "      <td>20001212</td>\n",
       "      <td>19971223</td>\n",
       "      <td>19681222</td>\n",
       "      <td>19741221</td>\n",
       "      <td>20110114</td>\n",
       "      <td>19980108</td>\n",
       "      <td>19851223</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>19480103</th>\n",
       "      <td>19491206</td>\n",
       "      <td>19741222</td>\n",
       "      <td>19750110</td>\n",
       "      <td>19851222</td>\n",
       "      <td>19641231</td>\n",
       "      <td>19921215</td>\n",
       "      <td>19741221</td>\n",
       "      <td>19931217</td>\n",
       "      <td>19950124</td>\n",
       "      <td>19491207</td>\n",
       "      <td>19991211</td>\n",
       "      <td>19750109</td>\n",
       "      <td>20031217</td>\n",
       "      <td>19921216</td>\n",
       "      <td>19991212</td>\n",
       "      <td>19741225</td>\n",
       "      <td>20020120</td>\n",
       "      <td>19921214</td>\n",
       "      <td>19600120</td>\n",
       "      <td>19681220</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>19480104</th>\n",
       "      <td>20031217</td>\n",
       "      <td>19891221</td>\n",
       "      <td>19921215</td>\n",
       "      <td>19510114</td>\n",
       "      <td>20020121</td>\n",
       "      <td>19931218</td>\n",
       "      <td>19921216</td>\n",
       "      <td>19741224</td>\n",
       "      <td>19931217</td>\n",
       "      <td>19851222</td>\n",
       "      <td>19550120</td>\n",
       "      <td>19641231</td>\n",
       "      <td>19741223</td>\n",
       "      <td>19491206</td>\n",
       "      <td>19491207</td>\n",
       "      <td>19971209</td>\n",
       "      <td>19681220</td>\n",
       "      <td>19921206</td>\n",
       "      <td>19871218</td>\n",
       "      <td>19551224</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>19480105</th>\n",
       "      <td>19971210</td>\n",
       "      <td>19971209</td>\n",
       "      <td>19971211</td>\n",
       "      <td>19591230</td>\n",
       "      <td>19931217</td>\n",
       "      <td>19591228</td>\n",
       "      <td>19591229</td>\n",
       "      <td>19950129</td>\n",
       "      <td>20020121</td>\n",
       "      <td>19550120</td>\n",
       "      <td>19931218</td>\n",
       "      <td>19861228</td>\n",
       "      <td>19741224</td>\n",
       "      <td>20020122</td>\n",
       "      <td>19510114</td>\n",
       "      <td>19551224</td>\n",
       "      <td>19680115</td>\n",
       "      <td>19861230</td>\n",
       "      <td>20010104</td>\n",
       "      <td>20020120</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>19480106</th>\n",
       "      <td>20020122</td>\n",
       "      <td>19710120</td>\n",
       "      <td>19931217</td>\n",
       "      <td>19591227</td>\n",
       "      <td>19991211</td>\n",
       "      <td>20020121</td>\n",
       "      <td>19991210</td>\n",
       "      <td>19950129</td>\n",
       "      <td>19710126</td>\n",
       "      <td>19781214</td>\n",
       "      <td>20040113</td>\n",
       "      <td>19510112</td>\n",
       "      <td>19591228</td>\n",
       "      <td>20020120</td>\n",
       "      <td>20031227</td>\n",
       "      <td>19811213</td>\n",
       "      <td>19891221</td>\n",
       "      <td>19610131</td>\n",
       "      <td>20030102</td>\n",
       "      <td>19861230</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>19480107</th>\n",
       "      <td>19781214</td>\n",
       "      <td>19591227</td>\n",
       "      <td>19510112</td>\n",
       "      <td>19811214</td>\n",
       "      <td>19991211</td>\n",
       "      <td>19591228</td>\n",
       "      <td>19581213</td>\n",
       "      <td>19571210</td>\n",
       "      <td>20040114</td>\n",
       "      <td>19811213</td>\n",
       "      <td>19830131</td>\n",
       "      <td>19950128</td>\n",
       "      <td>19651210</td>\n",
       "      <td>19991212</td>\n",
       "      <td>20030102</td>\n",
       "      <td>19660102</td>\n",
       "      <td>19991210</td>\n",
       "      <td>19891218</td>\n",
       "      <td>20040113</td>\n",
       "      <td>19991209</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>19480108</th>\n",
       "      <td>20040114</td>\n",
       "      <td>19591228</td>\n",
       "      <td>19781214</td>\n",
       "      <td>19931217</td>\n",
       "      <td>19811213</td>\n",
       "      <td>19510113</td>\n",
       "      <td>19660102</td>\n",
       "      <td>20030102</td>\n",
       "      <td>19740205</td>\n",
       "      <td>19811214</td>\n",
       "      <td>19991211</td>\n",
       "      <td>19681220</td>\n",
       "      <td>19550119</td>\n",
       "      <td>19991210</td>\n",
       "      <td>19950128</td>\n",
       "      <td>19510112</td>\n",
       "      <td>20020122</td>\n",
       "      <td>19660206</td>\n",
       "      <td>19850126</td>\n",
       "      <td>19950129</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>19480109</th>\n",
       "      <td>20021230</td>\n",
       "      <td>20020121</td>\n",
       "      <td>19931217</td>\n",
       "      <td>19681220</td>\n",
       "      <td>19861230</td>\n",
       "      <td>19590114</td>\n",
       "      <td>19660206</td>\n",
       "      <td>19811213</td>\n",
       "      <td>19660207</td>\n",
       "      <td>20021229</td>\n",
       "      <td>19590113</td>\n",
       "      <td>20010104</td>\n",
       "      <td>19551221</td>\n",
       "      <td>19850126</td>\n",
       "      <td>19850127</td>\n",
       "      <td>19850125</td>\n",
       "      <td>19550206</td>\n",
       "      <td>19580207</td>\n",
       "      <td>19740208</td>\n",
       "      <td>20110114</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>19480110</th>\n",
       "      <td>19550128</td>\n",
       "      <td>19710116</td>\n",
       "      <td>19800130</td>\n",
       "      <td>19850126</td>\n",
       "      <td>20030101</td>\n",
       "      <td>20020122</td>\n",
       "      <td>20020121</td>\n",
       "      <td>19590114</td>\n",
       "      <td>19851230</td>\n",
       "      <td>19861231</td>\n",
       "      <td>19550129</td>\n",
       "      <td>20021230</td>\n",
       "      <td>20020125</td>\n",
       "      <td>19831224</td>\n",
       "      <td>19580207</td>\n",
       "      <td>19850125</td>\n",
       "      <td>19950130</td>\n",
       "      <td>19950127</td>\n",
       "      <td>19861230</td>\n",
       "      <td>20030102</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "          date.an1  date.an2  date.an3  date.an4  date.an5  date.an6  \\\n",
       "date                                                                   \n",
       "19480101  19560126  19850131  20110114  19551224  19850130  19980114   \n",
       "19480102  20001211  19681221  19551224  20110112  19560126  19741220   \n",
       "19480103  19491206  19741222  19750110  19851222  19641231  19921215   \n",
       "19480104  20031217  19891221  19921215  19510114  20020121  19931218   \n",
       "19480105  19971210  19971209  19971211  19591230  19931217  19591228   \n",
       "19480106  20020122  19710120  19931217  19591227  19991211  20020121   \n",
       "19480107  19781214  19591227  19510112  19811214  19991211  19591228   \n",
       "19480108  20040114  19591228  19781214  19931217  19811213  19510113   \n",
       "19480109  20021230  20020121  19931217  19681220  19861230  19590114   \n",
       "19480110  19550128  19710116  19800130  19850126  20030101  20020122   \n",
       "\n",
       "          date.an7  date.an8  date.an9  date.an10  date.an11  date.an12  \\\n",
       "date                                                                      \n",
       "19480101  19521221  20001211  19681220   19871224   19870101   19540119   \n",
       "19480102  19600121  19681220  19521221   19850131   19741219   19670126   \n",
       "19480103  19741221  19931217  19950124   19491207   19991211   19750109   \n",
       "19480104  19921216  19741224  19931217   19851222   19550120   19641231   \n",
       "19480105  19591229  19950129  20020121   19550120   19931218   19861228   \n",
       "19480106  19991210  19950129  19710126   19781214   20040113   19510112   \n",
       "19480107  19581213  19571210  20040114   19811213   19830131   19950128   \n",
       "19480108  19660102  20030102  19740205   19811214   19991211   19681220   \n",
       "19480109  19660206  19811213  19660207   20021229   19590113   20010104   \n",
       "19480110  20020121  19590114  19851230   19861231   19550129   20021230   \n",
       "\n",
       "          date.an13  date.an14  date.an15  date.an16  date.an17  date.an18  \\\n",
       "date                                                                         \n",
       "19480101   19610109   19560125   20010108   20110112   19651223   19651213   \n",
       "19480102   19600119   20001212   19971223   19681222   19741221   20110114   \n",
       "19480103   20031217   19921216   19991212   19741225   20020120   19921214   \n",
       "19480104   19741223   19491206   19491207   19971209   19681220   19921206   \n",
       "19480105   19741224   20020122   19510114   19551224   19680115   19861230   \n",
       "19480106   19591228   20020120   20031227   19811213   19891221   19610131   \n",
       "19480107   19651210   19991212   20030102   19660102   19991210   19891218   \n",
       "19480108   19550119   19991210   19950128   19510112   20020122   19660206   \n",
       "19480109   19551221   19850126   19850127   19850125   19550206   19580207   \n",
       "19480110   20020125   19831224   19580207   19850125   19950130   19950127   \n",
       "\n",
       "          date.an19  date.an20  \n",
       "date                            \n",
       "19480101   20110113   20031209  \n",
       "19480102   19980108   19851223  \n",
       "19480103   19600120   19681220  \n",
       "19480104   19871218   19551224  \n",
       "19480105   20010104   20020120  \n",
       "19480106   20030102   19861230  \n",
       "19480107   20040113   19991209  \n",
       "19480108   19850126   19950129  \n",
       "19480109   19740208   20110114  \n",
       "19480110   19861230   20030102  "
      ]
     },
     "execution_count": 5,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "#CREATE ANALOGUE DATE TABLE ONLY\n",
    "#s1.iloc[:3] #selects rows only\n",
    "# df1.iloc[1:5,2:4] #selects rows, cols\n",
    "dfA_raw = dfS.iloc[:, 0:20]\n",
    "dfA_raw.head(10)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "(24831, 20)"
      ]
     },
     "execution_count": 6,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "dfA_raw.shape"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "496620"
      ]
     },
     "execution_count": 7,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "20*24831"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "#STACK ANALOGUE DATES ACROSS COLUMNS INTO ONE COLUMN\n",
    "\n",
    "#first remove index name of dfA_raw\n",
    "dfA_raw.index.name = \"\"\n",
    "\n",
    "dfA_stack = dfA_raw.stack()\n",
    "dfA_stack.index.name"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "19480101  date.an1     19560126\n",
       "          date.an2     19850131\n",
       "          date.an3     20110114\n",
       "          date.an4     19551224\n",
       "          date.an5     19850130\n",
       "          date.an6     19980114\n",
       "          date.an7     19521221\n",
       "          date.an8     20001211\n",
       "          date.an9     19681220\n",
       "          date.an10    19871224\n",
       "          date.an11    19870101\n",
       "          date.an12    19540119\n",
       "          date.an13    19610109\n",
       "          date.an14    19560125\n",
       "          date.an15    20010108\n",
       "          date.an16    20110112\n",
       "          date.an17    19651223\n",
       "          date.an18    19651213\n",
       "          date.an19    20110113\n",
       "          date.an20    20031209\n",
       "19480102  date.an1     20001211\n",
       "          date.an2     19681221\n",
       "          date.an3     19551224\n",
       "          date.an4     20110112\n",
       "          date.an5     19560126\n",
       "          date.an6     19741220\n",
       "          date.an7     19600121\n",
       "          date.an8     19681220\n",
       "          date.an9     19521221\n",
       "          date.an10    19850131\n",
       "                         ...   \n",
       "20151224  date.an11    20111129\n",
       "          date.an12    19981228\n",
       "          date.an13    19991127\n",
       "          date.an14    20061214\n",
       "          date.an15    19481204\n",
       "          date.an16    19531202\n",
       "          date.an17    19731210\n",
       "          date.an18    20020120\n",
       "          date.an19    19731229\n",
       "          date.an20    19741221\n",
       "20151225  date.an1     20080120\n",
       "          date.an2     19990105\n",
       "          date.an3     20061215\n",
       "          date.an4     19970118\n",
       "          date.an5     19771224\n",
       "          date.an6     19571222\n",
       "          date.an7     20111201\n",
       "          date.an8     19921217\n",
       "          date.an9     19531127\n",
       "          date.an10    19760108\n",
       "          date.an11    19890116\n",
       "          date.an12    20140108\n",
       "          date.an13    19941211\n",
       "          date.an14    20061216\n",
       "          date.an15    20061214\n",
       "          date.an16    19990120\n",
       "          date.an17    20080119\n",
       "          date.an18    19821125\n",
       "          date.an19    19890117\n",
       "          date.an20    19590123\n",
       "dtype: int64"
      ]
     },
     "execution_count": 9,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "dfA_stack"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 10,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "#list(dfA_stack)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 11,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "(24831, 1)"
      ]
     },
     "execution_count": 11,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "#CREATE EMPTY DATAFRAME WITH NUM ROWS = NUM COLS OF dfA_raw\n",
    "index = dfA_raw.shape[0] #num cols of dfA\n",
    "columns = 2\n",
    "dfA = pd.DataFrame(np.nan, index=range(0, dfA_raw.shape[0]), columns=['dateAnlg'])\n",
    "\n",
    "#dfA = pd.DataFrame(np.nan, index=range(0,4), columns=['Adate'])\n",
    "\n",
    "dfA.shape"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 12,
   "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>dateAnlg</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>dateRef</th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>19480101</th>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>19480102</th>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>19480103</th>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>19480104</th>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>19480105</th>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>19480106</th>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>19480107</th>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>19480108</th>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>19480109</th>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>19480110</th>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "          dateAnlg\n",
       "dateRef           \n",
       "19480101       NaN\n",
       "19480102       NaN\n",
       "19480103       NaN\n",
       "19480104       NaN\n",
       "19480105       NaN\n",
       "19480106       NaN\n",
       "19480107       NaN\n",
       "19480108       NaN\n",
       "19480109       NaN\n",
       "19480110       NaN"
      ]
     },
     "execution_count": 12,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "#REPLACE INDEX COL OF dfA WITH ORIG date_id\n",
    "dfA = dfA.set_index(dfS['dateRef'])\n",
    "dfA.head(10)\n",
    "#dfA.shape"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 13,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "#REPLICATE INDEX COL 20 TIMES PER ROW\n",
    "\n",
    "#http://stackoverflow.com/questions/26777832/replicating-rows-in-a-pandas-data-frame-by-a-column-value\n",
    "#df2 = df.loc[np.repeat(df.index.values,df.n)]\n",
    "dfA = dfA.loc[np.repeat(dfA.index.values,num_analogues)]\n",
    "\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 14,
   "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>dateAnlg</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>dateRef</th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>19480101</th>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>19480101</th>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>19480101</th>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>19480101</th>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>19480101</th>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>19480101</th>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>19480101</th>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>19480101</th>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>19480101</th>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>19480101</th>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>19480101</th>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>19480101</th>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>19480101</th>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>19480101</th>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>19480101</th>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>19480101</th>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>19480101</th>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>19480101</th>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>19480101</th>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>19480101</th>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>19480102</th>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>19480102</th>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>19480102</th>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>19480102</th>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>19480102</th>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>19480102</th>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>19480102</th>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>19480102</th>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>19480102</th>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>19480102</th>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "          dateAnlg\n",
       "dateRef           \n",
       "19480101       NaN\n",
       "19480101       NaN\n",
       "19480101       NaN\n",
       "19480101       NaN\n",
       "19480101       NaN\n",
       "19480101       NaN\n",
       "19480101       NaN\n",
       "19480101       NaN\n",
       "19480101       NaN\n",
       "19480101       NaN\n",
       "19480101       NaN\n",
       "19480101       NaN\n",
       "19480101       NaN\n",
       "19480101       NaN\n",
       "19480101       NaN\n",
       "19480101       NaN\n",
       "19480101       NaN\n",
       "19480101       NaN\n",
       "19480101       NaN\n",
       "19480101       NaN\n",
       "19480102       NaN\n",
       "19480102       NaN\n",
       "19480102       NaN\n",
       "19480102       NaN\n",
       "19480102       NaN\n",
       "19480102       NaN\n",
       "19480102       NaN\n",
       "19480102       NaN\n",
       "19480102       NaN\n",
       "19480102       NaN"
      ]
     },
     "execution_count": 14,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "dfA.head(30)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 15,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "#REPLACE COL 0 WITH STACKED DATES\n",
    "dfA.iloc[:,0] = list(dfA_stack)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 16,
   "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>dateAnlg</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>dateRef</th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>20151224</th>\n",
       "      <td>20111129</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>20151224</th>\n",
       "      <td>19981228</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>20151224</th>\n",
       "      <td>19991127</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>20151224</th>\n",
       "      <td>20061214</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>20151224</th>\n",
       "      <td>19481204</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>20151224</th>\n",
       "      <td>19531202</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>20151224</th>\n",
       "      <td>19731210</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>20151224</th>\n",
       "      <td>20020120</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>20151224</th>\n",
       "      <td>19731229</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>20151224</th>\n",
       "      <td>19741221</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>20151225</th>\n",
       "      <td>20080120</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>20151225</th>\n",
       "      <td>19990105</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>20151225</th>\n",
       "      <td>20061215</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>20151225</th>\n",
       "      <td>19970118</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>20151225</th>\n",
       "      <td>19771224</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>20151225</th>\n",
       "      <td>19571222</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>20151225</th>\n",
       "      <td>20111201</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>20151225</th>\n",
       "      <td>19921217</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>20151225</th>\n",
       "      <td>19531127</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>20151225</th>\n",
       "      <td>19760108</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>20151225</th>\n",
       "      <td>19890116</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>20151225</th>\n",
       "      <td>20140108</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>20151225</th>\n",
       "      <td>19941211</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>20151225</th>\n",
       "      <td>20061216</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>20151225</th>\n",
       "      <td>20061214</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>20151225</th>\n",
       "      <td>19990120</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>20151225</th>\n",
       "      <td>20080119</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>20151225</th>\n",
       "      <td>19821125</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>20151225</th>\n",
       "      <td>19890117</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>20151225</th>\n",
       "      <td>19590123</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "          dateAnlg\n",
       "dateRef           \n",
       "20151224  20111129\n",
       "20151224  19981228\n",
       "20151224  19991127\n",
       "20151224  20061214\n",
       "20151224  19481204\n",
       "20151224  19531202\n",
       "20151224  19731210\n",
       "20151224  20020120\n",
       "20151224  19731229\n",
       "20151224  19741221\n",
       "20151225  20080120\n",
       "20151225  19990105\n",
       "20151225  20061215\n",
       "20151225  19970118\n",
       "20151225  19771224\n",
       "20151225  19571222\n",
       "20151225  20111201\n",
       "20151225  19921217\n",
       "20151225  19531127\n",
       "20151225  19760108\n",
       "20151225  19890116\n",
       "20151225  20140108\n",
       "20151225  19941211\n",
       "20151225  20061216\n",
       "20151225  20061214\n",
       "20151225  19990120\n",
       "20151225  20080119\n",
       "20151225  19821125\n",
       "20151225  19890117\n",
       "20151225  19590123"
      ]
     },
     "execution_count": 16,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "dfA.tail(30)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 44,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "#SAVE TO TSV FILE\n",
    "\n",
    "#dfA.to_csv('analogues_reformat.tsv', sep='\\t')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 55,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "u'/homel/cnangini/PROJECTS/analogues'"
      ]
     },
     "execution_count": 55,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "pwd"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 62,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "#check 1970-1979\n",
    "df_check =  dfA[ (dfA['dateAnlg'] >= 19700101) & (dfA['dateAnlg'] <= 19791231) ]\n",
    "\n",
    "\n",
    "#((df['column1']=='banana') & (df['colour']=='green'))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 65,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "(76375, 1)"
      ]
     },
     "execution_count": 65,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df_check.shape"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 47,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "(496620, 1)"
      ]
     },
     "execution_count": 47,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "dfA.shape"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 66,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "df_check =  dfA[ (dfA['dateAnlg'] >= 20100101) & (dfA['dateAnlg'] <= 20191231) ]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 67,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "(42013, 1)"
      ]
     },
     "execution_count": 67,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df_check.shape"
   ]
  },
  {
   "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.10"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 0
}