M6. Final Joins  (SQL).ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# MERGE ALL CAPTURED DATA"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 1,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "from sqlalchemy import create_engine\n",
    "import pandas as pd"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "cnx = create_engine('postgresql://username:password@ip:port/database')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "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>id</th>\n",
       "      <th>index</th>\n",
       "      <th>assemdist</th>\n",
       "      <th>atomicid</th>\n",
       "      <th>borough</th>\n",
       "      <th>censusbloc</th>\n",
       "      <th>censustrac</th>\n",
       "      <th>commdist</th>\n",
       "      <th>electdist</th>\n",
       "      <th>schooldist</th>\n",
       "      <th>...</th>\n",
       "      <th>station2_index</th>\n",
       "      <th>station3_distance</th>\n",
       "      <th>station3_index</th>\n",
       "      <th>stationid</th>\n",
       "      <th>year</th>\n",
       "      <th>total_months</th>\n",
       "      <th>as_start_count</th>\n",
       "      <th>as_end_count</th>\n",
       "      <th>as_start_avg</th>\n",
       "      <th>as_end_avg</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>62</td>\n",
       "      <td>5024401430</td>\n",
       "      <td>5</td>\n",
       "      <td>4023</td>\n",
       "      <td>24400</td>\n",
       "      <td>503</td>\n",
       "      <td>2</td>\n",
       "      <td>31</td>\n",
       "      <td>...</td>\n",
       "      <td>75</td>\n",
       "      <td>0.284858</td>\n",
       "      <td>541</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>2</td>\n",
       "      <td>1</td>\n",
       "      <td>62</td>\n",
       "      <td>5024401439</td>\n",
       "      <td>5</td>\n",
       "      <td>4030</td>\n",
       "      <td>24400</td>\n",
       "      <td>503</td>\n",
       "      <td>4</td>\n",
       "      <td>31</td>\n",
       "      <td>...</td>\n",
       "      <td>75</td>\n",
       "      <td>0.279811</td>\n",
       "      <td>541</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>3</td>\n",
       "      <td>2</td>\n",
       "      <td>62</td>\n",
       "      <td>5024402223</td>\n",
       "      <td>5</td>\n",
       "      <td>2002</td>\n",
       "      <td>24400</td>\n",
       "      <td>503</td>\n",
       "      <td>5</td>\n",
       "      <td>31</td>\n",
       "      <td>...</td>\n",
       "      <td>75</td>\n",
       "      <td>0.272801</td>\n",
       "      <td>541</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>4</td>\n",
       "      <td>3</td>\n",
       "      <td>62</td>\n",
       "      <td>5024401441</td>\n",
       "      <td>5</td>\n",
       "      <td>4031</td>\n",
       "      <td>24400</td>\n",
       "      <td>503</td>\n",
       "      <td>4</td>\n",
       "      <td>31</td>\n",
       "      <td>...</td>\n",
       "      <td>75</td>\n",
       "      <td>0.278737</td>\n",
       "      <td>541</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>5</td>\n",
       "      <td>4</td>\n",
       "      <td>62</td>\n",
       "      <td>5024401440</td>\n",
       "      <td>5</td>\n",
       "      <td>4015</td>\n",
       "      <td>24400</td>\n",
       "      <td>503</td>\n",
       "      <td>2</td>\n",
       "      <td>31</td>\n",
       "      <td>...</td>\n",
       "      <td>75</td>\n",
       "      <td>0.289309</td>\n",
       "      <td>541</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>5 rows × 26 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "   id  index  assemdist    atomicid  borough  censusbloc  censustrac  \\\n",
       "0   1      0         62  5024401430        5        4023       24400   \n",
       "1   2      1         62  5024401439        5        4030       24400   \n",
       "2   3      2         62  5024402223        5        2002       24400   \n",
       "3   4      3         62  5024401441        5        4031       24400   \n",
       "4   5      4         62  5024401440        5        4015       24400   \n",
       "\n",
       "   commdist  electdist  schooldist    ...      station2_index  \\\n",
       "0       503          2          31    ...                  75   \n",
       "1       503          4          31    ...                  75   \n",
       "2       503          5          31    ...                  75   \n",
       "3       503          4          31    ...                  75   \n",
       "4       503          2          31    ...                  75   \n",
       "\n",
       "   station3_distance  station3_index  stationid  year  total_months  \\\n",
       "0           0.284858             541       None  None          None   \n",
       "1           0.279811             541       None  None          None   \n",
       "2           0.272801             541       None  None          None   \n",
       "3           0.278737             541       None  None          None   \n",
       "4           0.289309             541       None  None          None   \n",
       "\n",
       "   as_start_count  as_end_count  as_start_avg as_end_avg  \n",
       "0            None          None          None       None  \n",
       "1            None          None          None       None  \n",
       "2            None          None          None       None  \n",
       "3            None          None          None       None  \n",
       "4            None          None          None       None  \n",
       "\n",
       "[5 rows x 26 columns]"
      ]
     },
     "execution_count": 4,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# BRING IN ALL BLOCK AND CLOSEST STATIONS ID\n",
    "pd.read_sql_query('''SELECT * FROM karl3 limit 5;''',cnx)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "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>id</th>\n",
       "      <th>stationid1</th>\n",
       "      <th>year1</th>\n",
       "      <th>total_months1</th>\n",
       "      <th>as_start_count1</th>\n",
       "      <th>as_end_count1</th>\n",
       "      <th>as_start_avg1</th>\n",
       "      <th>as_end_avg1</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>0</td>\n",
       "      <td>489</td>\n",
       "      <td>2013</td>\n",
       "      <td>41</td>\n",
       "      <td>96274</td>\n",
       "      <td>96591</td>\n",
       "      <td>2348.146341</td>\n",
       "      <td>2355.878049</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>1</td>\n",
       "      <td>3317</td>\n",
       "      <td>2016</td>\n",
       "      <td>4</td>\n",
       "      <td>1839</td>\n",
       "      <td>1838</td>\n",
       "      <td>459.750000</td>\n",
       "      <td>459.500000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>2</td>\n",
       "      <td>3306</td>\n",
       "      <td>2016</td>\n",
       "      <td>4</td>\n",
       "      <td>2870</td>\n",
       "      <td>2705</td>\n",
       "      <td>717.500000</td>\n",
       "      <td>676.250000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>3</td>\n",
       "      <td>458</td>\n",
       "      <td>2013</td>\n",
       "      <td>41</td>\n",
       "      <td>145438</td>\n",
       "      <td>147743</td>\n",
       "      <td>3547.268293</td>\n",
       "      <td>3603.487805</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>4</td>\n",
       "      <td>478</td>\n",
       "      <td>2013</td>\n",
       "      <td>41</td>\n",
       "      <td>121505</td>\n",
       "      <td>125955</td>\n",
       "      <td>2963.536585</td>\n",
       "      <td>3072.073171</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   id  stationid1  year1  total_months1  as_start_count1  as_end_count1  \\\n",
       "0   0         489   2013             41            96274          96591   \n",
       "1   1        3317   2016              4             1839           1838   \n",
       "2   2        3306   2016              4             2870           2705   \n",
       "3   3         458   2013             41           145438         147743   \n",
       "4   4         478   2013             41           121505         125955   \n",
       "\n",
       "   as_start_avg1  as_end_avg1  \n",
       "0    2348.146341  2355.878049  \n",
       "1     459.750000   459.500000  \n",
       "2     717.500000   676.250000  \n",
       "3    3547.268293  3603.487805  \n",
       "4    2963.536585  3072.073171  "
      ]
     },
     "execution_count": 6,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# INFO ON CLOSEST STATIONS 1, 2, & 3\n",
    "pd.read_sql_query('''SELECT * FROM firtclosest limit 5;''',cnx)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "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>id</th>\n",
       "      <th>stationid2</th>\n",
       "      <th>year2</th>\n",
       "      <th>total_months2</th>\n",
       "      <th>as_start_count2</th>\n",
       "      <th>as_end_count2</th>\n",
       "      <th>as_start_avg2</th>\n",
       "      <th>as_end_avg2</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>0</td>\n",
       "      <td>489</td>\n",
       "      <td>2013</td>\n",
       "      <td>41</td>\n",
       "      <td>96274</td>\n",
       "      <td>96591</td>\n",
       "      <td>2348.146341</td>\n",
       "      <td>2355.878049</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>1</td>\n",
       "      <td>3317</td>\n",
       "      <td>2016</td>\n",
       "      <td>4</td>\n",
       "      <td>1839</td>\n",
       "      <td>1838</td>\n",
       "      <td>459.750000</td>\n",
       "      <td>459.500000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>2</td>\n",
       "      <td>3306</td>\n",
       "      <td>2016</td>\n",
       "      <td>4</td>\n",
       "      <td>2870</td>\n",
       "      <td>2705</td>\n",
       "      <td>717.500000</td>\n",
       "      <td>676.250000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>3</td>\n",
       "      <td>458</td>\n",
       "      <td>2013</td>\n",
       "      <td>41</td>\n",
       "      <td>145438</td>\n",
       "      <td>147743</td>\n",
       "      <td>3547.268293</td>\n",
       "      <td>3603.487805</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>4</td>\n",
       "      <td>478</td>\n",
       "      <td>2013</td>\n",
       "      <td>41</td>\n",
       "      <td>121505</td>\n",
       "      <td>125955</td>\n",
       "      <td>2963.536585</td>\n",
       "      <td>3072.073171</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   id  stationid2  year2  total_months2  as_start_count2  as_end_count2  \\\n",
       "0   0         489   2013             41            96274          96591   \n",
       "1   1        3317   2016              4             1839           1838   \n",
       "2   2        3306   2016              4             2870           2705   \n",
       "3   3         458   2013             41           145438         147743   \n",
       "4   4         478   2013             41           121505         125955   \n",
       "\n",
       "   as_start_avg2  as_end_avg2  \n",
       "0    2348.146341  2355.878049  \n",
       "1     459.750000   459.500000  \n",
       "2     717.500000   676.250000  \n",
       "3    3547.268293  3603.487805  \n",
       "4    2963.536585  3072.073171  "
      ]
     },
     "execution_count": 7,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "pd.read_sql_query('''SELECT * FROM secondclosest limit 5;''',cnx)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "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>id</th>\n",
       "      <th>stationid3</th>\n",
       "      <th>year3</th>\n",
       "      <th>total_months3</th>\n",
       "      <th>as_start_count3</th>\n",
       "      <th>as_end_count3</th>\n",
       "      <th>as_start_avg3</th>\n",
       "      <th>as_end_avg3</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>0</td>\n",
       "      <td>489</td>\n",
       "      <td>2013</td>\n",
       "      <td>41</td>\n",
       "      <td>96274</td>\n",
       "      <td>96591</td>\n",
       "      <td>2348.146341</td>\n",
       "      <td>2355.878049</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>1</td>\n",
       "      <td>3317</td>\n",
       "      <td>2016</td>\n",
       "      <td>4</td>\n",
       "      <td>1839</td>\n",
       "      <td>1838</td>\n",
       "      <td>459.750000</td>\n",
       "      <td>459.500000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>2</td>\n",
       "      <td>3306</td>\n",
       "      <td>2016</td>\n",
       "      <td>4</td>\n",
       "      <td>2870</td>\n",
       "      <td>2705</td>\n",
       "      <td>717.500000</td>\n",
       "      <td>676.250000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>3</td>\n",
       "      <td>458</td>\n",
       "      <td>2013</td>\n",
       "      <td>41</td>\n",
       "      <td>145438</td>\n",
       "      <td>147743</td>\n",
       "      <td>3547.268293</td>\n",
       "      <td>3603.487805</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>4</td>\n",
       "      <td>478</td>\n",
       "      <td>2013</td>\n",
       "      <td>41</td>\n",
       "      <td>121505</td>\n",
       "      <td>125955</td>\n",
       "      <td>2963.536585</td>\n",
       "      <td>3072.073171</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   id  stationid3  year3  total_months3  as_start_count3  as_end_count3  \\\n",
       "0   0         489   2013             41            96274          96591   \n",
       "1   1        3317   2016              4             1839           1838   \n",
       "2   2        3306   2016              4             2870           2705   \n",
       "3   3         458   2013             41           145438         147743   \n",
       "4   4         478   2013             41           121505         125955   \n",
       "\n",
       "   as_start_avg3  as_end_avg3  \n",
       "0    2348.146341  2355.878049  \n",
       "1     459.750000   459.500000  \n",
       "2     717.500000   676.250000  \n",
       "3    3547.268293  3603.487805  \n",
       "4    2963.536585  3072.073171  "
      ]
     },
     "execution_count": 8,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "pd.read_sql_query('''SELECT * FROM thirdclosest limit 5;''',cnx)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 15,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "# JOINS\n",
    "final = pd.read_sql_query('''SELECT * FROM karl3 as k \n",
    "                    JOIN firtclosest AS f ON k.station1_index = f.id \n",
    "                    JOIN secondclosest AS s ON k.station2_index = s.id\n",
    "                    JOIN thirdclosest AS t ON k.station3_index = t.id''',cnx)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 22,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "(65849, 50)"
      ]
     },
     "execution_count": 22,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "final.shape"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 16,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "Index([u'id', u'index', u'assemdist', u'atomicid', u'borough', u'censusbloc',\n",
       "       u'censustrac', u'commdist', u'electdist', u'schooldist', u'shape_area',\n",
       "       u'shape_leng', u'water_flag', u'station1_distance', u'station1_index',\n",
       "       u'station2_distance', u'station2_index', u'station3_distance',\n",
       "       u'station3_index', u'stationid', u'year', u'total_months',\n",
       "       u'as_start_count', u'as_end_count', u'as_start_avg', u'as_end_avg',\n",
       "       u'id', u'stationid1', u'year1', u'total_months1', u'as_start_count1',\n",
       "       u'as_end_count1', u'as_start_avg1', u'as_end_avg1', u'id',\n",
       "       u'stationid2', u'year2', u'total_months2', u'as_start_count2',\n",
       "       u'as_end_count2', u'as_start_avg2', u'as_end_avg2', u'id',\n",
       "       u'stationid3', u'year3', u'total_months3', u'as_start_count3',\n",
       "       u'as_end_count3', u'as_start_avg3', u'as_end_avg3'],\n",
       "      dtype='object')"
      ]
     },
     "execution_count": 16,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "final.columns"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 17,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "id                   65849\n",
       "index                65849\n",
       "assemdist            65849\n",
       "atomicid             65849\n",
       "borough              65849\n",
       "censusbloc           65849\n",
       "censustrac           65849\n",
       "commdist             65849\n",
       "electdist            65849\n",
       "schooldist           65849\n",
       "shape_area           65849\n",
       "shape_leng           65849\n",
       "water_flag           65849\n",
       "station1_distance    65849\n",
       "station1_index       65849\n",
       "station2_distance    65849\n",
       "station2_index       65849\n",
       "station3_distance    65849\n",
       "station3_index       65849\n",
       "stationid              698\n",
       "year                   698\n",
       "total_months           698\n",
       "as_start_count         698\n",
       "as_end_count           698\n",
       "as_start_avg           698\n",
       "as_end_avg             698\n",
       "id                   65849\n",
       "stationid1           65849\n",
       "year1                65849\n",
       "total_months1        65849\n",
       "as_start_count1      65849\n",
       "as_end_count1        65849\n",
       "as_start_avg1        65849\n",
       "as_end_avg1          65849\n",
       "id                   65849\n",
       "stationid2           65849\n",
       "year2                65849\n",
       "total_months2        65849\n",
       "as_start_count2      65849\n",
       "as_end_count2        65849\n",
       "as_start_avg2        65849\n",
       "as_end_avg2          65849\n",
       "id                   65849\n",
       "stationid3           65849\n",
       "year3                65849\n",
       "total_months3        65849\n",
       "as_start_count3      65849\n",
       "as_end_count3        65849\n",
       "as_start_avg3        65849\n",
       "as_end_avg3          65849\n",
       "dtype: int64"
      ]
     },
     "execution_count": 17,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "final.count()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 20,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "import csv\n",
    "final.to_csv('Second_try.csv')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 25,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "# ADDED ZONES\n",
    "finalwzones = pd.read_sql_query('''SELECT * FROM karl3 as k \n",
    "                    JOIN firtclosest AS f ON k.station1_index = f.id \n",
    "                    JOIN secondclosest AS s ON k.station2_index = s.id\n",
    "                    JOIN thirdclosest AS t ON k.station3_index = t.id\n",
    "                    JOIN zones AS z ON k.atomicid = z.atomicid''',cnx)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 26,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "(65849, 53)"
      ]
     },
     "execution_count": 26,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "finalwzones.shape"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 27,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "finalwzones.to_csv('Third_try.csv')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 30,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "# ADJUSTED STATIONS\n",
    "adjustedfinalwzones = pd.read_sql_query('''SELECT * FROM karl3fix as k \n",
    "                    JOIN firtclosest AS f ON k.station1_index = f.id \n",
    "                    JOIN secondclosest AS s ON k.station2_index = s.id\n",
    "                    JOIN thirdclosest AS t ON k.station3_index = t.id\n",
    "                    JOIN zones AS z ON k.atomicid = z.atomicid''',cnx)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 32,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "(65849, 53)"
      ]
     },
     "execution_count": 32,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "adjustedfinalwzones.shape"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 31,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "adjustedfinalwzones.to_csv('Fourth_try.csv')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 34,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "# ADDED ZIPCODE INFO\n",
    "adjustedfinalwzoneswzips = pd.read_sql_query('''SELECT * FROM karl3fix as k \n",
    "                    JOIN firtclosest AS f ON k.station1_index = f.id \n",
    "                    JOIN secondclosest AS s ON k.station2_index = s.id\n",
    "                    JOIN thirdclosest AS t ON k.station3_index = t.id\n",
    "                    JOIN zones AS z ON k.atomicid = z.atomicid\n",
    "                    JOIN zipsloc AS zip ON k.atomicid = zip.atomicid''',cnx)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 35,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "(65849, 62)"
      ]
     },
     "execution_count": 35,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "adjustedfinalwzoneswzips.shape"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 36,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "adjustedfinalwzoneswzips.to_csv('Fifth_try.csv')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "# ADDED MTA INFO\n",
    "blocsstationszoneszipsmta = pd.read_sql_query('''SELECT * FROM karl3fix as k \n",
    "                    JOIN firtclosest AS f ON k.station1_index = f.id \n",
    "                    JOIN secondclosest AS s ON k.station2_index = s.id\n",
    "                    JOIN thirdclosest AS t ON k.station3_index = t.id\n",
    "                    JOIN zones AS z ON k.atomicid = z.atomicid\n",
    "                    JOIN zipsloc AS zip ON k.atomicid = zip.atomicid\n",
    "                    LEFT JOIN mta2 AS m ON k.atomicid = m.atomicid''',cnx)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 10,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "(66135, 66)"
      ]
     },
     "execution_count": 10,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "blocsstationszoneszipsmta.shape"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 11,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "blocsstationszoneszipsmta.to_csv('Sixth_try.csv')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 15,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "# MORE COMPLETE JOIN\n",
    "blocs_station_stations_zones_zips_mta_mtas = pd.read_sql_query('''SELECT * FROM karl3fix as k \n",
    "                    JOIN firtclosest AS f ON k.station1_index = f.id \n",
    "                    JOIN secondclosest AS s ON k.station2_index = s.id\n",
    "                    JOIN thirdclosest AS t ON k.station3_index = t.id\n",
    "                    JOIN zones AS z ON k.atomicid = z.atomicid\n",
    "                    JOIN zipsloc AS zip ON k.atomicid = zip.atomicid\n",
    "                    JOIN mtadistances AS d ON k.atomicid = d.atomicid \n",
    "                    LEFT JOIN mta2 AS m ON k.atomicid = m.atomicid''',cnx)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 16,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "(66135, 71)"
      ]
     },
     "execution_count": 16,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "blocs_station_stations_zones_zips_mta_mtas.shape"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 17,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "blocs_station_stations_zones_zips_mta_mtas.to_csv(\"Seventh_try.csv\")"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## CITIBIKE TRIP DATA"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 1,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "# TRIP DATA\n",
    "monthly_trips = pd.read_sql_query('''SELECT * FROM trips; ''', cnx)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "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>id</th>\n",
       "      <th>tripduration</th>\n",
       "      <th>starttime</th>\n",
       "      <th>stoptime</th>\n",
       "      <th>startstationid</th>\n",
       "      <th>startstationname</th>\n",
       "      <th>startstationlatitude</th>\n",
       "      <th>startstationlongitude</th>\n",
       "      <th>endstationid</th>\n",
       "      <th>endstationname</th>\n",
       "      <th>endstationlatitude</th>\n",
       "      <th>endstationlongitude</th>\n",
       "      <th>bikeid</th>\n",
       "      <th>usertype</th>\n",
       "      <th>birthyear</th>\n",
       "      <th>gender</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>3551565</td>\n",
       "      <td>518</td>\n",
       "      <td>2013-10-20 13:23:19</td>\n",
       "      <td>2013-10-20 13:31:57</td>\n",
       "      <td>294</td>\n",
       "      <td>Washington Square E</td>\n",
       "      <td>40.730494</td>\n",
       "      <td>-73.995721</td>\n",
       "      <td>168</td>\n",
       "      <td>W 18 St &amp; 6 Ave</td>\n",
       "      <td>40.739713</td>\n",
       "      <td>-73.994564</td>\n",
       "      <td>15987</td>\n",
       "      <td>Subscriber</td>\n",
       "      <td>1979</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>3551566</td>\n",
       "      <td>907</td>\n",
       "      <td>2013-10-20 13:23:19</td>\n",
       "      <td>2013-10-20 13:38:26</td>\n",
       "      <td>525</td>\n",
       "      <td>W 34 St &amp; 11 Ave</td>\n",
       "      <td>40.755942</td>\n",
       "      <td>-74.002116</td>\n",
       "      <td>358</td>\n",
       "      <td>Christopher St &amp; Greenwich St</td>\n",
       "      <td>40.732916</td>\n",
       "      <td>-74.007114</td>\n",
       "      <td>15704</td>\n",
       "      <td>Subscriber</td>\n",
       "      <td>1969</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>3551567</td>\n",
       "      <td>465</td>\n",
       "      <td>2013-10-20 13:23:20</td>\n",
       "      <td>2013-10-20 13:31:05</td>\n",
       "      <td>483</td>\n",
       "      <td>E 12 St &amp; 3 Ave</td>\n",
       "      <td>40.732233</td>\n",
       "      <td>-73.988900</td>\n",
       "      <td>383</td>\n",
       "      <td>Greenwich Ave &amp; Charles St</td>\n",
       "      <td>40.735238</td>\n",
       "      <td>-74.000271</td>\n",
       "      <td>18708</td>\n",
       "      <td>Subscriber</td>\n",
       "      <td>1986</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>3551568</td>\n",
       "      <td>395</td>\n",
       "      <td>2013-10-20 13:23:20</td>\n",
       "      <td>2013-10-20 13:29:55</td>\n",
       "      <td>339</td>\n",
       "      <td>Avenue D &amp; E 12 St</td>\n",
       "      <td>40.725806</td>\n",
       "      <td>-73.974225</td>\n",
       "      <td>339</td>\n",
       "      <td>Avenue D &amp; E 12 St</td>\n",
       "      <td>40.725806</td>\n",
       "      <td>-73.974225</td>\n",
       "      <td>17005</td>\n",
       "      <td>Subscriber</td>\n",
       "      <td>1983</td>\n",
       "      <td>2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>3551569</td>\n",
       "      <td>1472</td>\n",
       "      <td>2013-10-20 13:23:23</td>\n",
       "      <td>2013-10-20 13:47:55</td>\n",
       "      <td>304</td>\n",
       "      <td>Broadway &amp; Battery Pl</td>\n",
       "      <td>40.704633</td>\n",
       "      <td>-74.013617</td>\n",
       "      <td>212</td>\n",
       "      <td>W 16 St &amp; The High Line</td>\n",
       "      <td>40.743349</td>\n",
       "      <td>-74.006818</td>\n",
       "      <td>19283</td>\n",
       "      <td>Subscriber</td>\n",
       "      <td>1977</td>\n",
       "      <td>2</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "        id  tripduration            starttime            stoptime  \\\n",
       "0  3551565           518  2013-10-20 13:23:19 2013-10-20 13:31:57   \n",
       "1  3551566           907  2013-10-20 13:23:19 2013-10-20 13:38:26   \n",
       "2  3551567           465  2013-10-20 13:23:20 2013-10-20 13:31:05   \n",
       "3  3551568           395  2013-10-20 13:23:20 2013-10-20 13:29:55   \n",
       "4  3551569          1472  2013-10-20 13:23:23 2013-10-20 13:47:55   \n",
       "\n",
       "   startstationid       startstationname  startstationlatitude  \\\n",
       "0             294    Washington Square E             40.730494   \n",
       "1             525       W 34 St & 11 Ave             40.755942   \n",
       "2             483        E 12 St & 3 Ave             40.732233   \n",
       "3             339     Avenue D & E 12 St             40.725806   \n",
       "4             304  Broadway & Battery Pl             40.704633   \n",
       "\n",
       "   startstationlongitude  endstationid                 endstationname  \\\n",
       "0             -73.995721           168                W 18 St & 6 Ave   \n",
       "1             -74.002116           358  Christopher St & Greenwich St   \n",
       "2             -73.988900           383     Greenwich Ave & Charles St   \n",
       "3             -73.974225           339             Avenue D & E 12 St   \n",
       "4             -74.013617           212        W 16 St & The High Line   \n",
       "\n",
       "   endstationlatitude  endstationlongitude  bikeid    usertype birthyear  \\\n",
       "0           40.739713           -73.994564   15987  Subscriber      1979   \n",
       "1           40.732916           -74.007114   15704  Subscriber      1969   \n",
       "2           40.735238           -74.000271   18708  Subscriber      1986   \n",
       "3           40.725806           -73.974225   17005  Subscriber      1983   \n",
       "4           40.743349           -74.006818   19283  Subscriber      1977   \n",
       "\n",
       "   gender  \n",
       "0       1  \n",
       "1       1  \n",
       "2       1  \n",
       "3       2  \n",
       "4       2  "
      ]
     },
     "execution_count": 4,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "monthly_trips.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "import datetime\n",
    "import pandas as pd\n",
    "monthly_trips['datetime'] = pd.to_datetime(monthly_trips['starttime'], format = '%Y/%m/%d %H:%M:%S')\n",
    "monthly_trips['year'] = monthly_trips.datetime.dt.year\n",
    "monthly_trips['month'] = monthly_trips.datetime.dt.month"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 20,
   "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>id</th>\n",
       "      <th>tripduration</th>\n",
       "      <th>starttime</th>\n",
       "      <th>stoptime</th>\n",
       "      <th>startstationid</th>\n",
       "      <th>startstationname</th>\n",
       "      <th>startstationlatitude</th>\n",
       "      <th>startstationlongitude</th>\n",
       "      <th>endstationid</th>\n",
       "      <th>endstationname</th>\n",
       "      <th>endstationlatitude</th>\n",
       "      <th>endstationlongitude</th>\n",
       "      <th>bikeid</th>\n",
       "      <th>usertype</th>\n",
       "      <th>birthyear</th>\n",
       "      <th>gender</th>\n",
       "      <th>datetime</th>\n",
       "      <th>year</th>\n",
       "      <th>month</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>3551565</td>\n",
       "      <td>518</td>\n",
       "      <td>2013-10-20 13:23:19</td>\n",
       "      <td>2013-10-20 13:31:57</td>\n",
       "      <td>294</td>\n",
       "      <td>Washington Square E</td>\n",
       "      <td>40.730494</td>\n",
       "      <td>-73.995721</td>\n",
       "      <td>168</td>\n",
       "      <td>W 18 St &amp; 6 Ave</td>\n",
       "      <td>40.739713</td>\n",
       "      <td>-73.994564</td>\n",
       "      <td>15987</td>\n",
       "      <td>Subscriber</td>\n",
       "      <td>1979</td>\n",
       "      <td>1</td>\n",
       "      <td>2013-10-20 13:23:19</td>\n",
       "      <td>2013</td>\n",
       "      <td>10</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>3551566</td>\n",
       "      <td>907</td>\n",
       "      <td>2013-10-20 13:23:19</td>\n",
       "      <td>2013-10-20 13:38:26</td>\n",
       "      <td>525</td>\n",
       "      <td>W 34 St &amp; 11 Ave</td>\n",
       "      <td>40.755942</td>\n",
       "      <td>-74.002116</td>\n",
       "      <td>358</td>\n",
       "      <td>Christopher St &amp; Greenwich St</td>\n",
       "      <td>40.732916</td>\n",
       "      <td>-74.007114</td>\n",
       "      <td>15704</td>\n",
       "      <td>Subscriber</td>\n",
       "      <td>1969</td>\n",
       "      <td>1</td>\n",
       "      <td>2013-10-20 13:23:19</td>\n",
       "      <td>2013</td>\n",
       "      <td>10</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>3551567</td>\n",
       "      <td>465</td>\n",
       "      <td>2013-10-20 13:23:20</td>\n",
       "      <td>2013-10-20 13:31:05</td>\n",
       "      <td>483</td>\n",
       "      <td>E 12 St &amp; 3 Ave</td>\n",
       "      <td>40.732233</td>\n",
       "      <td>-73.988900</td>\n",
       "      <td>383</td>\n",
       "      <td>Greenwich Ave &amp; Charles St</td>\n",
       "      <td>40.735238</td>\n",
       "      <td>-74.000271</td>\n",
       "      <td>18708</td>\n",
       "      <td>Subscriber</td>\n",
       "      <td>1986</td>\n",
       "      <td>1</td>\n",
       "      <td>2013-10-20 13:23:20</td>\n",
       "      <td>2013</td>\n",
       "      <td>10</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>3551568</td>\n",
       "      <td>395</td>\n",
       "      <td>2013-10-20 13:23:20</td>\n",
       "      <td>2013-10-20 13:29:55</td>\n",
       "      <td>339</td>\n",
       "      <td>Avenue D &amp; E 12 St</td>\n",
       "      <td>40.725806</td>\n",
       "      <td>-73.974225</td>\n",
       "      <td>339</td>\n",
       "      <td>Avenue D &amp; E 12 St</td>\n",
       "      <td>40.725806</td>\n",
       "      <td>-73.974225</td>\n",
       "      <td>17005</td>\n",
       "      <td>Subscriber</td>\n",
       "      <td>1983</td>\n",
       "      <td>2</td>\n",
       "      <td>2013-10-20 13:23:20</td>\n",
       "      <td>2013</td>\n",
       "      <td>10</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>3551569</td>\n",
       "      <td>1472</td>\n",
       "      <td>2013-10-20 13:23:23</td>\n",
       "      <td>2013-10-20 13:47:55</td>\n",
       "      <td>304</td>\n",
       "      <td>Broadway &amp; Battery Pl</td>\n",
       "      <td>40.704633</td>\n",
       "      <td>-74.013617</td>\n",
       "      <td>212</td>\n",
       "      <td>W 16 St &amp; The High Line</td>\n",
       "      <td>40.743349</td>\n",
       "      <td>-74.006818</td>\n",
       "      <td>19283</td>\n",
       "      <td>Subscriber</td>\n",
       "      <td>1977</td>\n",
       "      <td>2</td>\n",
       "      <td>2013-10-20 13:23:23</td>\n",
       "      <td>2013</td>\n",
       "      <td>10</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "        id  tripduration            starttime            stoptime  \\\n",
       "0  3551565           518  2013-10-20 13:23:19 2013-10-20 13:31:57   \n",
       "1  3551566           907  2013-10-20 13:23:19 2013-10-20 13:38:26   \n",
       "2  3551567           465  2013-10-20 13:23:20 2013-10-20 13:31:05   \n",
       "3  3551568           395  2013-10-20 13:23:20 2013-10-20 13:29:55   \n",
       "4  3551569          1472  2013-10-20 13:23:23 2013-10-20 13:47:55   \n",
       "\n",
       "   startstationid       startstationname  startstationlatitude  \\\n",
       "0             294    Washington Square E             40.730494   \n",
       "1             525       W 34 St & 11 Ave             40.755942   \n",
       "2             483        E 12 St & 3 Ave             40.732233   \n",
       "3             339     Avenue D & E 12 St             40.725806   \n",
       "4             304  Broadway & Battery Pl             40.704633   \n",
       "\n",
       "   startstationlongitude  endstationid                 endstationname  \\\n",
       "0             -73.995721           168                W 18 St & 6 Ave   \n",
       "1             -74.002116           358  Christopher St & Greenwich St   \n",
       "2             -73.988900           383     Greenwich Ave & Charles St   \n",
       "3             -73.974225           339             Avenue D & E 12 St   \n",
       "4             -74.013617           212        W 16 St & The High Line   \n",
       "\n",
       "   endstationlatitude  endstationlongitude  bikeid    usertype birthyear  \\\n",
       "0           40.739713           -73.994564   15987  Subscriber      1979   \n",
       "1           40.732916           -74.007114   15704  Subscriber      1969   \n",
       "2           40.735238           -74.000271   18708  Subscriber      1986   \n",
       "3           40.725806           -73.974225   17005  Subscriber      1983   \n",
       "4           40.743349           -74.006818   19283  Subscriber      1977   \n",
       "\n",
       "   gender            datetime  year  month  \n",
       "0       1 2013-10-20 13:23:19  2013     10  \n",
       "1       1 2013-10-20 13:23:19  2013     10  \n",
       "2       1 2013-10-20 13:23:20  2013     10  \n",
       "3       2 2013-10-20 13:23:20  2013     10  \n",
       "4       2 2013-10-20 13:23:23  2013     10  "
      ]
     },
     "execution_count": 20,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "monthly_trips.tail()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": []
  }
 ],
 "metadata": {
  "anaconda-cloud": {},
  "kernelspec": {
   "display_name": "Python [conda root]",
   "language": "python",
   "name": "conda-root-py"
  },
  "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.12"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 1
}