makesequence.ipynb

{
 "cells": [
  {
   "cell_type": "code",
   "execution_count": 1,
   "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>cluster</th>\n",
       "      <th>startdate</th>\n",
       "      <th>type</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>6</td>\n",
       "      <td>2001-01-01</td>\n",
       "      <td>E</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>6</td>\n",
       "      <td>2001-01-01</td>\n",
       "      <td>E</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>6</td>\n",
       "      <td>2001-01-02</td>\n",
       "      <td>E</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>11</td>\n",
       "      <td>2001-01-02</td>\n",
       "      <td>E</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>8</td>\n",
       "      <td>2001-01-08</td>\n",
       "      <td>V</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>11</td>\n",
       "      <td>2001-01-09</td>\n",
       "      <td>E</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>5</td>\n",
       "      <td>2001-01-10</td>\n",
       "      <td>E</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>6</td>\n",
       "      <td>2001-01-11</td>\n",
       "      <td>V</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8</th>\n",
       "      <td>7</td>\n",
       "      <td>2001-01-11</td>\n",
       "      <td>V</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9</th>\n",
       "      <td>5</td>\n",
       "      <td>2001-01-11</td>\n",
       "      <td>E</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>10</th>\n",
       "      <td>14</td>\n",
       "      <td>2001-01-13</td>\n",
       "      <td>E</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>11</th>\n",
       "      <td>2</td>\n",
       "      <td>2001-01-15</td>\n",
       "      <td>E</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>12</th>\n",
       "      <td>10</td>\n",
       "      <td>2001-01-16</td>\n",
       "      <td>E</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>13</th>\n",
       "      <td>10</td>\n",
       "      <td>2001-01-16</td>\n",
       "      <td>E</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>14</th>\n",
       "      <td>11</td>\n",
       "      <td>2001-01-16</td>\n",
       "      <td>V</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>15</th>\n",
       "      <td>14</td>\n",
       "      <td>2001-01-17</td>\n",
       "      <td>V</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>16</th>\n",
       "      <td>0</td>\n",
       "      <td>2001-01-19</td>\n",
       "      <td>E</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>17</th>\n",
       "      <td>11</td>\n",
       "      <td>2001-01-19</td>\n",
       "      <td>E</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>18</th>\n",
       "      <td>3</td>\n",
       "      <td>2001-01-19</td>\n",
       "      <td>E</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>19</th>\n",
       "      <td>13</td>\n",
       "      <td>2001-01-26</td>\n",
       "      <td>E</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>20</th>\n",
       "      <td>6</td>\n",
       "      <td>2001-01-29</td>\n",
       "      <td>E</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>21</th>\n",
       "      <td>5</td>\n",
       "      <td>2001-02-01</td>\n",
       "      <td>E</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>22</th>\n",
       "      <td>5</td>\n",
       "      <td>2001-02-02</td>\n",
       "      <td>V</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>23</th>\n",
       "      <td>9</td>\n",
       "      <td>2001-02-06</td>\n",
       "      <td>V</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>24</th>\n",
       "      <td>10</td>\n",
       "      <td>2001-02-13</td>\n",
       "      <td>E</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>25</th>\n",
       "      <td>14</td>\n",
       "      <td>2001-02-13</td>\n",
       "      <td>E</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>26</th>\n",
       "      <td>11</td>\n",
       "      <td>2001-02-14</td>\n",
       "      <td>E</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>27</th>\n",
       "      <td>11</td>\n",
       "      <td>2001-02-14</td>\n",
       "      <td>E</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>28</th>\n",
       "      <td>6</td>\n",
       "      <td>2001-02-16</td>\n",
       "      <td>E</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>29</th>\n",
       "      <td>5</td>\n",
       "      <td>2001-02-17</td>\n",
       "      <td>E</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>144</th>\n",
       "      <td>14</td>\n",
       "      <td>2001-11-09</td>\n",
       "      <td>E</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>145</th>\n",
       "      <td>0</td>\n",
       "      <td>2001-11-13</td>\n",
       "      <td>E</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>146</th>\n",
       "      <td>7</td>\n",
       "      <td>2001-11-13</td>\n",
       "      <td>E</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>147</th>\n",
       "      <td>7</td>\n",
       "      <td>2001-11-13</td>\n",
       "      <td>E</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>148</th>\n",
       "      <td>13</td>\n",
       "      <td>2001-11-14</td>\n",
       "      <td>E</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>149</th>\n",
       "      <td>9</td>\n",
       "      <td>2001-11-15</td>\n",
       "      <td>E</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>150</th>\n",
       "      <td>7</td>\n",
       "      <td>2001-11-15</td>\n",
       "      <td>V</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>151</th>\n",
       "      <td>6</td>\n",
       "      <td>2001-11-20</td>\n",
       "      <td>E</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>152</th>\n",
       "      <td>11</td>\n",
       "      <td>2001-11-22</td>\n",
       "      <td>E</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>153</th>\n",
       "      <td>11</td>\n",
       "      <td>2001-11-22</td>\n",
       "      <td>E</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>154</th>\n",
       "      <td>13</td>\n",
       "      <td>2001-11-23</td>\n",
       "      <td>E</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>155</th>\n",
       "      <td>3</td>\n",
       "      <td>2001-11-24</td>\n",
       "      <td>V</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>156</th>\n",
       "      <td>14</td>\n",
       "      <td>2001-11-26</td>\n",
       "      <td>V</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>157</th>\n",
       "      <td>11</td>\n",
       "      <td>2001-11-27</td>\n",
       "      <td>E</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>158</th>\n",
       "      <td>0</td>\n",
       "      <td>2001-11-28</td>\n",
       "      <td>E</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>159</th>\n",
       "      <td>9</td>\n",
       "      <td>2001-12-01</td>\n",
       "      <td>V</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>160</th>\n",
       "      <td>7</td>\n",
       "      <td>2001-12-02</td>\n",
       "      <td>E</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>161</th>\n",
       "      <td>11</td>\n",
       "      <td>2001-12-02</td>\n",
       "      <td>E</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>162</th>\n",
       "      <td>11</td>\n",
       "      <td>2001-12-03</td>\n",
       "      <td>E</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>163</th>\n",
       "      <td>14</td>\n",
       "      <td>2001-12-04</td>\n",
       "      <td>V</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>164</th>\n",
       "      <td>8</td>\n",
       "      <td>2001-12-08</td>\n",
       "      <td>E</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>165</th>\n",
       "      <td>6</td>\n",
       "      <td>2001-12-09</td>\n",
       "      <td>E</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>166</th>\n",
       "      <td>7</td>\n",
       "      <td>2001-12-10</td>\n",
       "      <td>V</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>167</th>\n",
       "      <td>11</td>\n",
       "      <td>2001-12-12</td>\n",
       "      <td>E</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>168</th>\n",
       "      <td>6</td>\n",
       "      <td>2001-12-12</td>\n",
       "      <td>E</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>169</th>\n",
       "      <td>8</td>\n",
       "      <td>2001-12-18</td>\n",
       "      <td>E</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>170</th>\n",
       "      <td>11</td>\n",
       "      <td>2001-12-22</td>\n",
       "      <td>E</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>171</th>\n",
       "      <td>11</td>\n",
       "      <td>2001-12-23</td>\n",
       "      <td>E</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>172</th>\n",
       "      <td>11</td>\n",
       "      <td>2001-12-27</td>\n",
       "      <td>E</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>173</th>\n",
       "      <td>14</td>\n",
       "      <td>2001-12-28</td>\n",
       "      <td>E</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>174 rows × 3 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "    cluster   startdate type\n",
       "0         6  2001-01-01    E\n",
       "1         6  2001-01-01    E\n",
       "2         6  2001-01-02    E\n",
       "3        11  2001-01-02    E\n",
       "4         8  2001-01-08    V\n",
       "5        11  2001-01-09    E\n",
       "6         5  2001-01-10    E\n",
       "7         6  2001-01-11    V\n",
       "8         7  2001-01-11    V\n",
       "9         5  2001-01-11    E\n",
       "10       14  2001-01-13    E\n",
       "11        2  2001-01-15    E\n",
       "12       10  2001-01-16    E\n",
       "13       10  2001-01-16    E\n",
       "14       11  2001-01-16    V\n",
       "15       14  2001-01-17    V\n",
       "16        0  2001-01-19    E\n",
       "17       11  2001-01-19    E\n",
       "18        3  2001-01-19    E\n",
       "19       13  2001-01-26    E\n",
       "20        6  2001-01-29    E\n",
       "21        5  2001-02-01    E\n",
       "22        5  2001-02-02    V\n",
       "23        9  2001-02-06    V\n",
       "24       10  2001-02-13    E\n",
       "25       14  2001-02-13    E\n",
       "26       11  2001-02-14    E\n",
       "27       11  2001-02-14    E\n",
       "28        6  2001-02-16    E\n",
       "29        5  2001-02-17    E\n",
       "..      ...         ...  ...\n",
       "144      14  2001-11-09    E\n",
       "145       0  2001-11-13    E\n",
       "146       7  2001-11-13    E\n",
       "147       7  2001-11-13    E\n",
       "148      13  2001-11-14    E\n",
       "149       9  2001-11-15    E\n",
       "150       7  2001-11-15    V\n",
       "151       6  2001-11-20    E\n",
       "152      11  2001-11-22    E\n",
       "153      11  2001-11-22    E\n",
       "154      13  2001-11-23    E\n",
       "155       3  2001-11-24    V\n",
       "156      14  2001-11-26    V\n",
       "157      11  2001-11-27    E\n",
       "158       0  2001-11-28    E\n",
       "159       9  2001-12-01    V\n",
       "160       7  2001-12-02    E\n",
       "161      11  2001-12-02    E\n",
       "162      11  2001-12-03    E\n",
       "163      14  2001-12-04    V\n",
       "164       8  2001-12-08    E\n",
       "165       6  2001-12-09    E\n",
       "166       7  2001-12-10    V\n",
       "167      11  2001-12-12    E\n",
       "168       6  2001-12-12    E\n",
       "169       8  2001-12-18    E\n",
       "170      11  2001-12-22    E\n",
       "171      11  2001-12-23    E\n",
       "172      11  2001-12-27    E\n",
       "173      14  2001-12-28    E\n",
       "\n",
       "[174 rows x 3 columns]"
      ]
     },
     "execution_count": 1,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "import numpy as np\n",
    "import pandas as pd\n",
    "from datetime import datetime\n",
    "import ephem\n",
    "\n",
    "def get_phase_on_day(date):\n",
    "    d = ephem.Date(date)\n",
    "    pnm = ephem.previous_new_moon(date)\n",
    "    lunation = int(d - pnm)\n",
    "    return lunation\n",
    "\n",
    "df1 = pd.read_csv('basket.csv', dtype = object)\n",
    "df1"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "df1['startdate'] = pd.to_datetime(df1.startdate)\n",
    "df1['startdate'] = df1['startdate'].dt.date\n",
    "df1['phase'] = ' '\n",
    "type(df1['startdate'][0])\n",
    "for i in range(0, len(df1)):\n",
    "    df1['phase'][i] = get_phase_on_day(df1['startdate'][i])"
   ]
  },
  {
   "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>cluster</th>\n",
       "      <th>startdate</th>\n",
       "      <th>type</th>\n",
       "      <th>phase</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>6</td>\n",
       "      <td>2001-01-01</td>\n",
       "      <td>E</td>\n",
       "      <td>6</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>6</td>\n",
       "      <td>2001-01-01</td>\n",
       "      <td>E</td>\n",
       "      <td>6</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>6</td>\n",
       "      <td>2001-01-02</td>\n",
       "      <td>E</td>\n",
       "      <td>7</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>11</td>\n",
       "      <td>2001-01-02</td>\n",
       "      <td>E</td>\n",
       "      <td>7</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>8</td>\n",
       "      <td>2001-01-08</td>\n",
       "      <td>V</td>\n",
       "      <td>13</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>11</td>\n",
       "      <td>2001-01-09</td>\n",
       "      <td>E</td>\n",
       "      <td>14</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>5</td>\n",
       "      <td>2001-01-10</td>\n",
       "      <td>E</td>\n",
       "      <td>15</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>6</td>\n",
       "      <td>2001-01-11</td>\n",
       "      <td>V</td>\n",
       "      <td>16</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8</th>\n",
       "      <td>7</td>\n",
       "      <td>2001-01-11</td>\n",
       "      <td>V</td>\n",
       "      <td>16</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9</th>\n",
       "      <td>5</td>\n",
       "      <td>2001-01-11</td>\n",
       "      <td>E</td>\n",
       "      <td>16</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>10</th>\n",
       "      <td>14</td>\n",
       "      <td>2001-01-13</td>\n",
       "      <td>E</td>\n",
       "      <td>18</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>11</th>\n",
       "      <td>2</td>\n",
       "      <td>2001-01-15</td>\n",
       "      <td>E</td>\n",
       "      <td>20</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>12</th>\n",
       "      <td>10</td>\n",
       "      <td>2001-01-16</td>\n",
       "      <td>E</td>\n",
       "      <td>21</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>13</th>\n",
       "      <td>10</td>\n",
       "      <td>2001-01-16</td>\n",
       "      <td>E</td>\n",
       "      <td>21</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>14</th>\n",
       "      <td>11</td>\n",
       "      <td>2001-01-16</td>\n",
       "      <td>V</td>\n",
       "      <td>21</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>15</th>\n",
       "      <td>14</td>\n",
       "      <td>2001-01-17</td>\n",
       "      <td>V</td>\n",
       "      <td>22</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>16</th>\n",
       "      <td>0</td>\n",
       "      <td>2001-01-19</td>\n",
       "      <td>E</td>\n",
       "      <td>24</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>17</th>\n",
       "      <td>11</td>\n",
       "      <td>2001-01-19</td>\n",
       "      <td>E</td>\n",
       "      <td>24</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>18</th>\n",
       "      <td>3</td>\n",
       "      <td>2001-01-19</td>\n",
       "      <td>E</td>\n",
       "      <td>24</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>19</th>\n",
       "      <td>13</td>\n",
       "      <td>2001-01-26</td>\n",
       "      <td>E</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>20</th>\n",
       "      <td>6</td>\n",
       "      <td>2001-01-29</td>\n",
       "      <td>E</td>\n",
       "      <td>4</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>21</th>\n",
       "      <td>5</td>\n",
       "      <td>2001-02-01</td>\n",
       "      <td>E</td>\n",
       "      <td>7</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>22</th>\n",
       "      <td>5</td>\n",
       "      <td>2001-02-02</td>\n",
       "      <td>V</td>\n",
       "      <td>8</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>23</th>\n",
       "      <td>9</td>\n",
       "      <td>2001-02-06</td>\n",
       "      <td>V</td>\n",
       "      <td>12</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>24</th>\n",
       "      <td>10</td>\n",
       "      <td>2001-02-13</td>\n",
       "      <td>E</td>\n",
       "      <td>19</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>25</th>\n",
       "      <td>14</td>\n",
       "      <td>2001-02-13</td>\n",
       "      <td>E</td>\n",
       "      <td>19</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>26</th>\n",
       "      <td>11</td>\n",
       "      <td>2001-02-14</td>\n",
       "      <td>E</td>\n",
       "      <td>20</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>27</th>\n",
       "      <td>11</td>\n",
       "      <td>2001-02-14</td>\n",
       "      <td>E</td>\n",
       "      <td>20</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>28</th>\n",
       "      <td>6</td>\n",
       "      <td>2001-02-16</td>\n",
       "      <td>E</td>\n",
       "      <td>22</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>29</th>\n",
       "      <td>5</td>\n",
       "      <td>2001-02-17</td>\n",
       "      <td>E</td>\n",
       "      <td>23</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>144</th>\n",
       "      <td>14</td>\n",
       "      <td>2001-11-09</td>\n",
       "      <td>E</td>\n",
       "      <td>23</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>145</th>\n",
       "      <td>0</td>\n",
       "      <td>2001-11-13</td>\n",
       "      <td>E</td>\n",
       "      <td>27</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>146</th>\n",
       "      <td>7</td>\n",
       "      <td>2001-11-13</td>\n",
       "      <td>E</td>\n",
       "      <td>27</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>147</th>\n",
       "      <td>7</td>\n",
       "      <td>2001-11-13</td>\n",
       "      <td>E</td>\n",
       "      <td>27</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>148</th>\n",
       "      <td>13</td>\n",
       "      <td>2001-11-14</td>\n",
       "      <td>E</td>\n",
       "      <td>28</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>149</th>\n",
       "      <td>9</td>\n",
       "      <td>2001-11-15</td>\n",
       "      <td>E</td>\n",
       "      <td>29</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>150</th>\n",
       "      <td>7</td>\n",
       "      <td>2001-11-15</td>\n",
       "      <td>V</td>\n",
       "      <td>29</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>151</th>\n",
       "      <td>6</td>\n",
       "      <td>2001-11-20</td>\n",
       "      <td>E</td>\n",
       "      <td>4</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>152</th>\n",
       "      <td>11</td>\n",
       "      <td>2001-11-22</td>\n",
       "      <td>E</td>\n",
       "      <td>6</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>153</th>\n",
       "      <td>11</td>\n",
       "      <td>2001-11-22</td>\n",
       "      <td>E</td>\n",
       "      <td>6</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>154</th>\n",
       "      <td>13</td>\n",
       "      <td>2001-11-23</td>\n",
       "      <td>E</td>\n",
       "      <td>7</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>155</th>\n",
       "      <td>3</td>\n",
       "      <td>2001-11-24</td>\n",
       "      <td>V</td>\n",
       "      <td>8</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>156</th>\n",
       "      <td>14</td>\n",
       "      <td>2001-11-26</td>\n",
       "      <td>V</td>\n",
       "      <td>10</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>157</th>\n",
       "      <td>11</td>\n",
       "      <td>2001-11-27</td>\n",
       "      <td>E</td>\n",
       "      <td>11</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>158</th>\n",
       "      <td>0</td>\n",
       "      <td>2001-11-28</td>\n",
       "      <td>E</td>\n",
       "      <td>12</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>159</th>\n",
       "      <td>9</td>\n",
       "      <td>2001-12-01</td>\n",
       "      <td>V</td>\n",
       "      <td>15</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>160</th>\n",
       "      <td>7</td>\n",
       "      <td>2001-12-02</td>\n",
       "      <td>E</td>\n",
       "      <td>16</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>161</th>\n",
       "      <td>11</td>\n",
       "      <td>2001-12-02</td>\n",
       "      <td>E</td>\n",
       "      <td>16</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>162</th>\n",
       "      <td>11</td>\n",
       "      <td>2001-12-03</td>\n",
       "      <td>E</td>\n",
       "      <td>17</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>163</th>\n",
       "      <td>14</td>\n",
       "      <td>2001-12-04</td>\n",
       "      <td>V</td>\n",
       "      <td>18</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>164</th>\n",
       "      <td>8</td>\n",
       "      <td>2001-12-08</td>\n",
       "      <td>E</td>\n",
       "      <td>22</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>165</th>\n",
       "      <td>6</td>\n",
       "      <td>2001-12-09</td>\n",
       "      <td>E</td>\n",
       "      <td>23</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>166</th>\n",
       "      <td>7</td>\n",
       "      <td>2001-12-10</td>\n",
       "      <td>V</td>\n",
       "      <td>24</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>167</th>\n",
       "      <td>11</td>\n",
       "      <td>2001-12-12</td>\n",
       "      <td>E</td>\n",
       "      <td>26</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>168</th>\n",
       "      <td>6</td>\n",
       "      <td>2001-12-12</td>\n",
       "      <td>E</td>\n",
       "      <td>26</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>169</th>\n",
       "      <td>8</td>\n",
       "      <td>2001-12-18</td>\n",
       "      <td>E</td>\n",
       "      <td>3</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>170</th>\n",
       "      <td>11</td>\n",
       "      <td>2001-12-22</td>\n",
       "      <td>E</td>\n",
       "      <td>7</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>171</th>\n",
       "      <td>11</td>\n",
       "      <td>2001-12-23</td>\n",
       "      <td>E</td>\n",
       "      <td>8</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>172</th>\n",
       "      <td>11</td>\n",
       "      <td>2001-12-27</td>\n",
       "      <td>E</td>\n",
       "      <td>12</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>173</th>\n",
       "      <td>14</td>\n",
       "      <td>2001-12-28</td>\n",
       "      <td>E</td>\n",
       "      <td>13</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>174 rows × 4 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "    cluster   startdate type phase\n",
       "0         6  2001-01-01    E     6\n",
       "1         6  2001-01-01    E     6\n",
       "2         6  2001-01-02    E     7\n",
       "3        11  2001-01-02    E     7\n",
       "4         8  2001-01-08    V    13\n",
       "5        11  2001-01-09    E    14\n",
       "6         5  2001-01-10    E    15\n",
       "7         6  2001-01-11    V    16\n",
       "8         7  2001-01-11    V    16\n",
       "9         5  2001-01-11    E    16\n",
       "10       14  2001-01-13    E    18\n",
       "11        2  2001-01-15    E    20\n",
       "12       10  2001-01-16    E    21\n",
       "13       10  2001-01-16    E    21\n",
       "14       11  2001-01-16    V    21\n",
       "15       14  2001-01-17    V    22\n",
       "16        0  2001-01-19    E    24\n",
       "17       11  2001-01-19    E    24\n",
       "18        3  2001-01-19    E    24\n",
       "19       13  2001-01-26    E     1\n",
       "20        6  2001-01-29    E     4\n",
       "21        5  2001-02-01    E     7\n",
       "22        5  2001-02-02    V     8\n",
       "23        9  2001-02-06    V    12\n",
       "24       10  2001-02-13    E    19\n",
       "25       14  2001-02-13    E    19\n",
       "26       11  2001-02-14    E    20\n",
       "27       11  2001-02-14    E    20\n",
       "28        6  2001-02-16    E    22\n",
       "29        5  2001-02-17    E    23\n",
       "..      ...         ...  ...   ...\n",
       "144      14  2001-11-09    E    23\n",
       "145       0  2001-11-13    E    27\n",
       "146       7  2001-11-13    E    27\n",
       "147       7  2001-11-13    E    27\n",
       "148      13  2001-11-14    E    28\n",
       "149       9  2001-11-15    E    29\n",
       "150       7  2001-11-15    V    29\n",
       "151       6  2001-11-20    E     4\n",
       "152      11  2001-11-22    E     6\n",
       "153      11  2001-11-22    E     6\n",
       "154      13  2001-11-23    E     7\n",
       "155       3  2001-11-24    V     8\n",
       "156      14  2001-11-26    V    10\n",
       "157      11  2001-11-27    E    11\n",
       "158       0  2001-11-28    E    12\n",
       "159       9  2001-12-01    V    15\n",
       "160       7  2001-12-02    E    16\n",
       "161      11  2001-12-02    E    16\n",
       "162      11  2001-12-03    E    17\n",
       "163      14  2001-12-04    V    18\n",
       "164       8  2001-12-08    E    22\n",
       "165       6  2001-12-09    E    23\n",
       "166       7  2001-12-10    V    24\n",
       "167      11  2001-12-12    E    26\n",
       "168       6  2001-12-12    E    26\n",
       "169       8  2001-12-18    E     3\n",
       "170      11  2001-12-22    E     7\n",
       "171      11  2001-12-23    E     8\n",
       "172      11  2001-12-27    E    12\n",
       "173      14  2001-12-28    E    13\n",
       "\n",
       "[174 rows x 4 columns]"
      ]
     },
     "execution_count": 3,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df1"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "df1['sequence']=df1['type'] + df1['cluster'].map(str)"
   ]
  },
  {
   "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>cluster</th>\n",
       "      <th>startdate</th>\n",
       "      <th>type</th>\n",
       "      <th>phase</th>\n",
       "      <th>sequence</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>6</td>\n",
       "      <td>2001-01-01</td>\n",
       "      <td>E</td>\n",
       "      <td>6</td>\n",
       "      <td>E6</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>6</td>\n",
       "      <td>2001-01-01</td>\n",
       "      <td>E</td>\n",
       "      <td>6</td>\n",
       "      <td>E6</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>6</td>\n",
       "      <td>2001-01-02</td>\n",
       "      <td>E</td>\n",
       "      <td>7</td>\n",
       "      <td>E6</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>11</td>\n",
       "      <td>2001-01-02</td>\n",
       "      <td>E</td>\n",
       "      <td>7</td>\n",
       "      <td>E11</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>8</td>\n",
       "      <td>2001-01-08</td>\n",
       "      <td>V</td>\n",
       "      <td>13</td>\n",
       "      <td>V8</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>11</td>\n",
       "      <td>2001-01-09</td>\n",
       "      <td>E</td>\n",
       "      <td>14</td>\n",
       "      <td>E11</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>5</td>\n",
       "      <td>2001-01-10</td>\n",
       "      <td>E</td>\n",
       "      <td>15</td>\n",
       "      <td>E5</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>6</td>\n",
       "      <td>2001-01-11</td>\n",
       "      <td>V</td>\n",
       "      <td>16</td>\n",
       "      <td>V6</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8</th>\n",
       "      <td>7</td>\n",
       "      <td>2001-01-11</td>\n",
       "      <td>V</td>\n",
       "      <td>16</td>\n",
       "      <td>V7</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9</th>\n",
       "      <td>5</td>\n",
       "      <td>2001-01-11</td>\n",
       "      <td>E</td>\n",
       "      <td>16</td>\n",
       "      <td>E5</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>10</th>\n",
       "      <td>14</td>\n",
       "      <td>2001-01-13</td>\n",
       "      <td>E</td>\n",
       "      <td>18</td>\n",
       "      <td>E14</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>11</th>\n",
       "      <td>2</td>\n",
       "      <td>2001-01-15</td>\n",
       "      <td>E</td>\n",
       "      <td>20</td>\n",
       "      <td>E2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>12</th>\n",
       "      <td>10</td>\n",
       "      <td>2001-01-16</td>\n",
       "      <td>E</td>\n",
       "      <td>21</td>\n",
       "      <td>E10</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>13</th>\n",
       "      <td>10</td>\n",
       "      <td>2001-01-16</td>\n",
       "      <td>E</td>\n",
       "      <td>21</td>\n",
       "      <td>E10</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>14</th>\n",
       "      <td>11</td>\n",
       "      <td>2001-01-16</td>\n",
       "      <td>V</td>\n",
       "      <td>21</td>\n",
       "      <td>V11</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>15</th>\n",
       "      <td>14</td>\n",
       "      <td>2001-01-17</td>\n",
       "      <td>V</td>\n",
       "      <td>22</td>\n",
       "      <td>V14</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>16</th>\n",
       "      <td>0</td>\n",
       "      <td>2001-01-19</td>\n",
       "      <td>E</td>\n",
       "      <td>24</td>\n",
       "      <td>E0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>17</th>\n",
       "      <td>11</td>\n",
       "      <td>2001-01-19</td>\n",
       "      <td>E</td>\n",
       "      <td>24</td>\n",
       "      <td>E11</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>18</th>\n",
       "      <td>3</td>\n",
       "      <td>2001-01-19</td>\n",
       "      <td>E</td>\n",
       "      <td>24</td>\n",
       "      <td>E3</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>19</th>\n",
       "      <td>13</td>\n",
       "      <td>2001-01-26</td>\n",
       "      <td>E</td>\n",
       "      <td>1</td>\n",
       "      <td>E13</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>20</th>\n",
       "      <td>6</td>\n",
       "      <td>2001-01-29</td>\n",
       "      <td>E</td>\n",
       "      <td>4</td>\n",
       "      <td>E6</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>21</th>\n",
       "      <td>5</td>\n",
       "      <td>2001-02-01</td>\n",
       "      <td>E</td>\n",
       "      <td>7</td>\n",
       "      <td>E5</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>22</th>\n",
       "      <td>5</td>\n",
       "      <td>2001-02-02</td>\n",
       "      <td>V</td>\n",
       "      <td>8</td>\n",
       "      <td>V5</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>23</th>\n",
       "      <td>9</td>\n",
       "      <td>2001-02-06</td>\n",
       "      <td>V</td>\n",
       "      <td>12</td>\n",
       "      <td>V9</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>24</th>\n",
       "      <td>10</td>\n",
       "      <td>2001-02-13</td>\n",
       "      <td>E</td>\n",
       "      <td>19</td>\n",
       "      <td>E10</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>25</th>\n",
       "      <td>14</td>\n",
       "      <td>2001-02-13</td>\n",
       "      <td>E</td>\n",
       "      <td>19</td>\n",
       "      <td>E14</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>26</th>\n",
       "      <td>11</td>\n",
       "      <td>2001-02-14</td>\n",
       "      <td>E</td>\n",
       "      <td>20</td>\n",
       "      <td>E11</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>27</th>\n",
       "      <td>11</td>\n",
       "      <td>2001-02-14</td>\n",
       "      <td>E</td>\n",
       "      <td>20</td>\n",
       "      <td>E11</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>28</th>\n",
       "      <td>6</td>\n",
       "      <td>2001-02-16</td>\n",
       "      <td>E</td>\n",
       "      <td>22</td>\n",
       "      <td>E6</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>29</th>\n",
       "      <td>5</td>\n",
       "      <td>2001-02-17</td>\n",
       "      <td>E</td>\n",
       "      <td>23</td>\n",
       "      <td>E5</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>144</th>\n",
       "      <td>14</td>\n",
       "      <td>2001-11-09</td>\n",
       "      <td>E</td>\n",
       "      <td>23</td>\n",
       "      <td>E14</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>145</th>\n",
       "      <td>0</td>\n",
       "      <td>2001-11-13</td>\n",
       "      <td>E</td>\n",
       "      <td>27</td>\n",
       "      <td>E0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>146</th>\n",
       "      <td>7</td>\n",
       "      <td>2001-11-13</td>\n",
       "      <td>E</td>\n",
       "      <td>27</td>\n",
       "      <td>E7</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>147</th>\n",
       "      <td>7</td>\n",
       "      <td>2001-11-13</td>\n",
       "      <td>E</td>\n",
       "      <td>27</td>\n",
       "      <td>E7</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>148</th>\n",
       "      <td>13</td>\n",
       "      <td>2001-11-14</td>\n",
       "      <td>E</td>\n",
       "      <td>28</td>\n",
       "      <td>E13</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>149</th>\n",
       "      <td>9</td>\n",
       "      <td>2001-11-15</td>\n",
       "      <td>E</td>\n",
       "      <td>29</td>\n",
       "      <td>E9</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>150</th>\n",
       "      <td>7</td>\n",
       "      <td>2001-11-15</td>\n",
       "      <td>V</td>\n",
       "      <td>29</td>\n",
       "      <td>V7</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>151</th>\n",
       "      <td>6</td>\n",
       "      <td>2001-11-20</td>\n",
       "      <td>E</td>\n",
       "      <td>4</td>\n",
       "      <td>E6</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>152</th>\n",
       "      <td>11</td>\n",
       "      <td>2001-11-22</td>\n",
       "      <td>E</td>\n",
       "      <td>6</td>\n",
       "      <td>E11</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>153</th>\n",
       "      <td>11</td>\n",
       "      <td>2001-11-22</td>\n",
       "      <td>E</td>\n",
       "      <td>6</td>\n",
       "      <td>E11</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>154</th>\n",
       "      <td>13</td>\n",
       "      <td>2001-11-23</td>\n",
       "      <td>E</td>\n",
       "      <td>7</td>\n",
       "      <td>E13</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>155</th>\n",
       "      <td>3</td>\n",
       "      <td>2001-11-24</td>\n",
       "      <td>V</td>\n",
       "      <td>8</td>\n",
       "      <td>V3</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>156</th>\n",
       "      <td>14</td>\n",
       "      <td>2001-11-26</td>\n",
       "      <td>V</td>\n",
       "      <td>10</td>\n",
       "      <td>V14</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>157</th>\n",
       "      <td>11</td>\n",
       "      <td>2001-11-27</td>\n",
       "      <td>E</td>\n",
       "      <td>11</td>\n",
       "      <td>E11</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>158</th>\n",
       "      <td>0</td>\n",
       "      <td>2001-11-28</td>\n",
       "      <td>E</td>\n",
       "      <td>12</td>\n",
       "      <td>E0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>159</th>\n",
       "      <td>9</td>\n",
       "      <td>2001-12-01</td>\n",
       "      <td>V</td>\n",
       "      <td>15</td>\n",
       "      <td>V9</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>160</th>\n",
       "      <td>7</td>\n",
       "      <td>2001-12-02</td>\n",
       "      <td>E</td>\n",
       "      <td>16</td>\n",
       "      <td>E7</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>161</th>\n",
       "      <td>11</td>\n",
       "      <td>2001-12-02</td>\n",
       "      <td>E</td>\n",
       "      <td>16</td>\n",
       "      <td>E11</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>162</th>\n",
       "      <td>11</td>\n",
       "      <td>2001-12-03</td>\n",
       "      <td>E</td>\n",
       "      <td>17</td>\n",
       "      <td>E11</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>163</th>\n",
       "      <td>14</td>\n",
       "      <td>2001-12-04</td>\n",
       "      <td>V</td>\n",
       "      <td>18</td>\n",
       "      <td>V14</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>164</th>\n",
       "      <td>8</td>\n",
       "      <td>2001-12-08</td>\n",
       "      <td>E</td>\n",
       "      <td>22</td>\n",
       "      <td>E8</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>165</th>\n",
       "      <td>6</td>\n",
       "      <td>2001-12-09</td>\n",
       "      <td>E</td>\n",
       "      <td>23</td>\n",
       "      <td>E6</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>166</th>\n",
       "      <td>7</td>\n",
       "      <td>2001-12-10</td>\n",
       "      <td>V</td>\n",
       "      <td>24</td>\n",
       "      <td>V7</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>167</th>\n",
       "      <td>11</td>\n",
       "      <td>2001-12-12</td>\n",
       "      <td>E</td>\n",
       "      <td>26</td>\n",
       "      <td>E11</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>168</th>\n",
       "      <td>6</td>\n",
       "      <td>2001-12-12</td>\n",
       "      <td>E</td>\n",
       "      <td>26</td>\n",
       "      <td>E6</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>169</th>\n",
       "      <td>8</td>\n",
       "      <td>2001-12-18</td>\n",
       "      <td>E</td>\n",
       "      <td>3</td>\n",
       "      <td>E8</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>170</th>\n",
       "      <td>11</td>\n",
       "      <td>2001-12-22</td>\n",
       "      <td>E</td>\n",
       "      <td>7</td>\n",
       "      <td>E11</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>171</th>\n",
       "      <td>11</td>\n",
       "      <td>2001-12-23</td>\n",
       "      <td>E</td>\n",
       "      <td>8</td>\n",
       "      <td>E11</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>172</th>\n",
       "      <td>11</td>\n",
       "      <td>2001-12-27</td>\n",
       "      <td>E</td>\n",
       "      <td>12</td>\n",
       "      <td>E11</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>173</th>\n",
       "      <td>14</td>\n",
       "      <td>2001-12-28</td>\n",
       "      <td>E</td>\n",
       "      <td>13</td>\n",
       "      <td>E14</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>174 rows × 5 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "    cluster   startdate type phase sequence\n",
       "0         6  2001-01-01    E     6       E6\n",
       "1         6  2001-01-01    E     6       E6\n",
       "2         6  2001-01-02    E     7       E6\n",
       "3        11  2001-01-02    E     7      E11\n",
       "4         8  2001-01-08    V    13       V8\n",
       "5        11  2001-01-09    E    14      E11\n",
       "6         5  2001-01-10    E    15       E5\n",
       "7         6  2001-01-11    V    16       V6\n",
       "8         7  2001-01-11    V    16       V7\n",
       "9         5  2001-01-11    E    16       E5\n",
       "10       14  2001-01-13    E    18      E14\n",
       "11        2  2001-01-15    E    20       E2\n",
       "12       10  2001-01-16    E    21      E10\n",
       "13       10  2001-01-16    E    21      E10\n",
       "14       11  2001-01-16    V    21      V11\n",
       "15       14  2001-01-17    V    22      V14\n",
       "16        0  2001-01-19    E    24       E0\n",
       "17       11  2001-01-19    E    24      E11\n",
       "18        3  2001-01-19    E    24       E3\n",
       "19       13  2001-01-26    E     1      E13\n",
       "20        6  2001-01-29    E     4       E6\n",
       "21        5  2001-02-01    E     7       E5\n",
       "22        5  2001-02-02    V     8       V5\n",
       "23        9  2001-02-06    V    12       V9\n",
       "24       10  2001-02-13    E    19      E10\n",
       "25       14  2001-02-13    E    19      E14\n",
       "26       11  2001-02-14    E    20      E11\n",
       "27       11  2001-02-14    E    20      E11\n",
       "28        6  2001-02-16    E    22       E6\n",
       "29        5  2001-02-17    E    23       E5\n",
       "..      ...         ...  ...   ...      ...\n",
       "144      14  2001-11-09    E    23      E14\n",
       "145       0  2001-11-13    E    27       E0\n",
       "146       7  2001-11-13    E    27       E7\n",
       "147       7  2001-11-13    E    27       E7\n",
       "148      13  2001-11-14    E    28      E13\n",
       "149       9  2001-11-15    E    29       E9\n",
       "150       7  2001-11-15    V    29       V7\n",
       "151       6  2001-11-20    E     4       E6\n",
       "152      11  2001-11-22    E     6      E11\n",
       "153      11  2001-11-22    E     6      E11\n",
       "154      13  2001-11-23    E     7      E13\n",
       "155       3  2001-11-24    V     8       V3\n",
       "156      14  2001-11-26    V    10      V14\n",
       "157      11  2001-11-27    E    11      E11\n",
       "158       0  2001-11-28    E    12       E0\n",
       "159       9  2001-12-01    V    15       V9\n",
       "160       7  2001-12-02    E    16       E7\n",
       "161      11  2001-12-02    E    16      E11\n",
       "162      11  2001-12-03    E    17      E11\n",
       "163      14  2001-12-04    V    18      V14\n",
       "164       8  2001-12-08    E    22       E8\n",
       "165       6  2001-12-09    E    23       E6\n",
       "166       7  2001-12-10    V    24       V7\n",
       "167      11  2001-12-12    E    26      E11\n",
       "168       6  2001-12-12    E    26       E6\n",
       "169       8  2001-12-18    E     3       E8\n",
       "170      11  2001-12-22    E     7      E11\n",
       "171      11  2001-12-23    E     8      E11\n",
       "172      11  2001-12-27    E    12      E11\n",
       "173      14  2001-12-28    E    13      E14\n",
       "\n",
       "[174 rows x 5 columns]"
      ]
     },
     "execution_count": 5,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df1"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "with open(\"test.txt\", \"a\") as myfile:\n",
    "        myfile.write(df1['sequence'][0])\n",
    "for x in range(1, len(df1['sequence'])):\n",
    "    #if date and sequence are different from the previous one\n",
    "    if ((df1['startdate'][x] != df1['startdate'][x - 1]) and (df1['sequence'][x] != df1['sequence'][x - 1])):\n",
    "        with open(\"test.txt\", \"a\") as myfile:\n",
    "            myfile.write(' ' + str(df1['phase'][x-1]) +' -1 ' + df1['sequence'][x] )\n",
    "    #if date is the same but sequence is different\n",
    "    elif df1['sequence'][x] != df1['sequence'][x - 1]:\n",
    "        with open(\"test.txt\", \"a\") as myfile:\n",
    "            myfile.write(' ' + df1['sequence'][x])\n",
    "with open(\"test.txt\", \"a\") as myfile:\n",
    "        myfile.write(' ' + str(df1['phase'][len(df1['phase']) - 1]) + ' -1 -2\\n')"
   ]
  },
  {
   "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.11"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 0
}