EDA_SourceSink.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Mobility Patterns in US Cities\n",
    "\n",
    "### Exploratory Data Analysis: Temporal Source-Sink Mapping"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 1,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Populating the interactive namespace from numpy and matplotlib\n"
     ]
    }
   ],
   "source": [
    "import pandas as pd\n",
    "import geopandas as gpd\n",
    "import os\n",
    "import matplotlib.pyplot as plt\n",
    "\n",
    "%pylab inline"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "metadata": {},
   "outputs": [],
   "source": [
    "#set working directory\n",
    "os.chdir('/projects/mpi/shared/Data')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "metadata": {},
   "outputs": [],
   "source": [
    "#read in geodata for mapping\n",
    "geo = gpd.read_file('all_cts.shp')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "metadata": {},
   "outputs": [],
   "source": [
    "#import processed data files at the census tract level\n",
    "ny = pd.read_csv('ny_ct.csv')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "metadata": {
    "scrolled": true
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>origin_ct</th>\n",
       "      <th>dest_ct</th>\n",
       "      <th>time_period</th>\n",
       "      <th>origin_st</th>\n",
       "      <th>dest_st</th>\n",
       "      <th>od_counts</th>\n",
       "      <th>num_dates</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>100</td>\n",
       "      <td>100</td>\n",
       "      <td>10:00 - 10:30</td>\n",
       "      <td>34</td>\n",
       "      <td>34</td>\n",
       "      <td>17</td>\n",
       "      <td>10</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>100</td>\n",
       "      <td>100</td>\n",
       "      <td>6:00 - 6:30</td>\n",
       "      <td>34</td>\n",
       "      <td>34</td>\n",
       "      <td>3</td>\n",
       "      <td>3</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>100</td>\n",
       "      <td>100</td>\n",
       "      <td>6:30 - 7:00</td>\n",
       "      <td>34</td>\n",
       "      <td>34</td>\n",
       "      <td>19</td>\n",
       "      <td>13</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>100</td>\n",
       "      <td>100</td>\n",
       "      <td>6:30 - 7:00</td>\n",
       "      <td>34</td>\n",
       "      <td>36</td>\n",
       "      <td>2</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>100</td>\n",
       "      <td>100</td>\n",
       "      <td>7:00 - 7:30</td>\n",
       "      <td>34</td>\n",
       "      <td>34</td>\n",
       "      <td>38</td>\n",
       "      <td>23</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>100</td>\n",
       "      <td>100</td>\n",
       "      <td>7:30 - 8:00</td>\n",
       "      <td>34</td>\n",
       "      <td>34</td>\n",
       "      <td>5</td>\n",
       "      <td>5</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>100</td>\n",
       "      <td>100</td>\n",
       "      <td>8:00 - 8:30</td>\n",
       "      <td>34</td>\n",
       "      <td>34</td>\n",
       "      <td>17</td>\n",
       "      <td>10</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>100</td>\n",
       "      <td>100</td>\n",
       "      <td>8:30 - 9:00</td>\n",
       "      <td>34</td>\n",
       "      <td>34</td>\n",
       "      <td>35</td>\n",
       "      <td>18</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8</th>\n",
       "      <td>100</td>\n",
       "      <td>100</td>\n",
       "      <td>9:00 - 9:30</td>\n",
       "      <td>34</td>\n",
       "      <td>34</td>\n",
       "      <td>37</td>\n",
       "      <td>17</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9</th>\n",
       "      <td>100</td>\n",
       "      <td>100</td>\n",
       "      <td>9:30 - 10:00</td>\n",
       "      <td>34</td>\n",
       "      <td>34</td>\n",
       "      <td>31</td>\n",
       "      <td>17</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>10</th>\n",
       "      <td>100</td>\n",
       "      <td>200</td>\n",
       "      <td>10:00 - 10:30</td>\n",
       "      <td>34</td>\n",
       "      <td>36</td>\n",
       "      <td>5</td>\n",
       "      <td>4</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>11</th>\n",
       "      <td>100</td>\n",
       "      <td>200</td>\n",
       "      <td>10:00 - 10:30</td>\n",
       "      <td>36</td>\n",
       "      <td>36</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>12</th>\n",
       "      <td>100</td>\n",
       "      <td>200</td>\n",
       "      <td>6:00 - 6:30</td>\n",
       "      <td>34</td>\n",
       "      <td>34</td>\n",
       "      <td>2</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>13</th>\n",
       "      <td>100</td>\n",
       "      <td>200</td>\n",
       "      <td>6:00 - 6:30</td>\n",
       "      <td>34</td>\n",
       "      <td>36</td>\n",
       "      <td>2</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>14</th>\n",
       "      <td>100</td>\n",
       "      <td>200</td>\n",
       "      <td>6:30 - 7:00</td>\n",
       "      <td>34</td>\n",
       "      <td>34</td>\n",
       "      <td>20</td>\n",
       "      <td>11</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>15</th>\n",
       "      <td>100</td>\n",
       "      <td>200</td>\n",
       "      <td>6:30 - 7:00</td>\n",
       "      <td>34</td>\n",
       "      <td>36</td>\n",
       "      <td>2</td>\n",
       "      <td>2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>16</th>\n",
       "      <td>100</td>\n",
       "      <td>200</td>\n",
       "      <td>6:30 - 7:00</td>\n",
       "      <td>36</td>\n",
       "      <td>36</td>\n",
       "      <td>3</td>\n",
       "      <td>2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>17</th>\n",
       "      <td>100</td>\n",
       "      <td>200</td>\n",
       "      <td>7:00 - 7:30</td>\n",
       "      <td>36</td>\n",
       "      <td>36</td>\n",
       "      <td>8</td>\n",
       "      <td>6</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>18</th>\n",
       "      <td>100</td>\n",
       "      <td>200</td>\n",
       "      <td>7:30 - 8:00</td>\n",
       "      <td>34</td>\n",
       "      <td>34</td>\n",
       "      <td>2</td>\n",
       "      <td>2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>19</th>\n",
       "      <td>100</td>\n",
       "      <td>200</td>\n",
       "      <td>7:30 - 8:00</td>\n",
       "      <td>36</td>\n",
       "      <td>36</td>\n",
       "      <td>2</td>\n",
       "      <td>2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>20</th>\n",
       "      <td>100</td>\n",
       "      <td>200</td>\n",
       "      <td>8:00 - 8:30</td>\n",
       "      <td>34</td>\n",
       "      <td>34</td>\n",
       "      <td>2</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>21</th>\n",
       "      <td>100</td>\n",
       "      <td>200</td>\n",
       "      <td>8:00 - 8:30</td>\n",
       "      <td>34</td>\n",
       "      <td>36</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>22</th>\n",
       "      <td>100</td>\n",
       "      <td>200</td>\n",
       "      <td>8:00 - 8:30</td>\n",
       "      <td>36</td>\n",
       "      <td>36</td>\n",
       "      <td>9</td>\n",
       "      <td>4</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>23</th>\n",
       "      <td>100</td>\n",
       "      <td>200</td>\n",
       "      <td>8:30 - 9:00</td>\n",
       "      <td>34</td>\n",
       "      <td>36</td>\n",
       "      <td>3</td>\n",
       "      <td>2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>24</th>\n",
       "      <td>100</td>\n",
       "      <td>200</td>\n",
       "      <td>8:30 - 9:00</td>\n",
       "      <td>36</td>\n",
       "      <td>36</td>\n",
       "      <td>4</td>\n",
       "      <td>3</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>25</th>\n",
       "      <td>100</td>\n",
       "      <td>200</td>\n",
       "      <td>9:00 - 9:30</td>\n",
       "      <td>34</td>\n",
       "      <td>34</td>\n",
       "      <td>3</td>\n",
       "      <td>2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>26</th>\n",
       "      <td>100</td>\n",
       "      <td>200</td>\n",
       "      <td>9:00 - 9:30</td>\n",
       "      <td>34</td>\n",
       "      <td>36</td>\n",
       "      <td>2</td>\n",
       "      <td>2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>27</th>\n",
       "      <td>100</td>\n",
       "      <td>200</td>\n",
       "      <td>9:00 - 9:30</td>\n",
       "      <td>36</td>\n",
       "      <td>36</td>\n",
       "      <td>5</td>\n",
       "      <td>3</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>28</th>\n",
       "      <td>100</td>\n",
       "      <td>200</td>\n",
       "      <td>9:30 - 10:00</td>\n",
       "      <td>34</td>\n",
       "      <td>34</td>\n",
       "      <td>6</td>\n",
       "      <td>4</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>29</th>\n",
       "      <td>100</td>\n",
       "      <td>200</td>\n",
       "      <td>9:30 - 10:00</td>\n",
       "      <td>34</td>\n",
       "      <td>36</td>\n",
       "      <td>2</td>\n",
       "      <td>2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>70</th>\n",
       "      <td>100</td>\n",
       "      <td>400</td>\n",
       "      <td>9:00 - 9:30</td>\n",
       "      <td>36</td>\n",
       "      <td>36</td>\n",
       "      <td>6</td>\n",
       "      <td>4</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>71</th>\n",
       "      <td>100</td>\n",
       "      <td>400</td>\n",
       "      <td>9:30 - 10:00</td>\n",
       "      <td>34</td>\n",
       "      <td>34</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>72</th>\n",
       "      <td>100</td>\n",
       "      <td>400</td>\n",
       "      <td>9:30 - 10:00</td>\n",
       "      <td>34</td>\n",
       "      <td>36</td>\n",
       "      <td>11</td>\n",
       "      <td>6</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>73</th>\n",
       "      <td>100</td>\n",
       "      <td>400</td>\n",
       "      <td>9:30 - 10:00</td>\n",
       "      <td>36</td>\n",
       "      <td>36</td>\n",
       "      <td>4</td>\n",
       "      <td>2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>74</th>\n",
       "      <td>100</td>\n",
       "      <td>402</td>\n",
       "      <td>8:00 - 8:30</td>\n",
       "      <td>36</td>\n",
       "      <td>36</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>75</th>\n",
       "      <td>100</td>\n",
       "      <td>600</td>\n",
       "      <td>10:00 - 10:30</td>\n",
       "      <td>34</td>\n",
       "      <td>34</td>\n",
       "      <td>45</td>\n",
       "      <td>25</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>76</th>\n",
       "      <td>100</td>\n",
       "      <td>600</td>\n",
       "      <td>10:00 - 10:30</td>\n",
       "      <td>36</td>\n",
       "      <td>36</td>\n",
       "      <td>7</td>\n",
       "      <td>4</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>77</th>\n",
       "      <td>100</td>\n",
       "      <td>600</td>\n",
       "      <td>6:00 - 6:30</td>\n",
       "      <td>34</td>\n",
       "      <td>34</td>\n",
       "      <td>38</td>\n",
       "      <td>21</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>78</th>\n",
       "      <td>100</td>\n",
       "      <td>600</td>\n",
       "      <td>6:00 - 6:30</td>\n",
       "      <td>36</td>\n",
       "      <td>36</td>\n",
       "      <td>2</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>79</th>\n",
       "      <td>100</td>\n",
       "      <td>600</td>\n",
       "      <td>6:30 - 7:00</td>\n",
       "      <td>34</td>\n",
       "      <td>34</td>\n",
       "      <td>2</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>80</th>\n",
       "      <td>100</td>\n",
       "      <td>600</td>\n",
       "      <td>6:30 - 7:00</td>\n",
       "      <td>34</td>\n",
       "      <td>36</td>\n",
       "      <td>2</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>81</th>\n",
       "      <td>100</td>\n",
       "      <td>600</td>\n",
       "      <td>7:00 - 7:30</td>\n",
       "      <td>34</td>\n",
       "      <td>34</td>\n",
       "      <td>43</td>\n",
       "      <td>23</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>82</th>\n",
       "      <td>100</td>\n",
       "      <td>600</td>\n",
       "      <td>7:00 - 7:30</td>\n",
       "      <td>36</td>\n",
       "      <td>36</td>\n",
       "      <td>2</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>83</th>\n",
       "      <td>100</td>\n",
       "      <td>600</td>\n",
       "      <td>7:30 - 8:00</td>\n",
       "      <td>34</td>\n",
       "      <td>34</td>\n",
       "      <td>43</td>\n",
       "      <td>21</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>84</th>\n",
       "      <td>100</td>\n",
       "      <td>600</td>\n",
       "      <td>7:30 - 8:00</td>\n",
       "      <td>36</td>\n",
       "      <td>36</td>\n",
       "      <td>2</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>85</th>\n",
       "      <td>100</td>\n",
       "      <td>600</td>\n",
       "      <td>8:00 - 8:30</td>\n",
       "      <td>34</td>\n",
       "      <td>34</td>\n",
       "      <td>14</td>\n",
       "      <td>9</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>86</th>\n",
       "      <td>100</td>\n",
       "      <td>600</td>\n",
       "      <td>8:00 - 8:30</td>\n",
       "      <td>36</td>\n",
       "      <td>36</td>\n",
       "      <td>3</td>\n",
       "      <td>2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>87</th>\n",
       "      <td>100</td>\n",
       "      <td>600</td>\n",
       "      <td>8:30 - 9:00</td>\n",
       "      <td>34</td>\n",
       "      <td>34</td>\n",
       "      <td>4</td>\n",
       "      <td>3</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>88</th>\n",
       "      <td>100</td>\n",
       "      <td>600</td>\n",
       "      <td>8:30 - 9:00</td>\n",
       "      <td>36</td>\n",
       "      <td>36</td>\n",
       "      <td>4</td>\n",
       "      <td>3</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>89</th>\n",
       "      <td>100</td>\n",
       "      <td>600</td>\n",
       "      <td>9:00 - 9:30</td>\n",
       "      <td>34</td>\n",
       "      <td>34</td>\n",
       "      <td>4</td>\n",
       "      <td>3</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>90</th>\n",
       "      <td>100</td>\n",
       "      <td>600</td>\n",
       "      <td>9:00 - 9:30</td>\n",
       "      <td>34</td>\n",
       "      <td>36</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>91</th>\n",
       "      <td>100</td>\n",
       "      <td>600</td>\n",
       "      <td>9:00 - 9:30</td>\n",
       "      <td>36</td>\n",
       "      <td>36</td>\n",
       "      <td>5</td>\n",
       "      <td>3</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>92</th>\n",
       "      <td>100</td>\n",
       "      <td>600</td>\n",
       "      <td>9:30 - 10:00</td>\n",
       "      <td>34</td>\n",
       "      <td>34</td>\n",
       "      <td>16</td>\n",
       "      <td>11</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>93</th>\n",
       "      <td>100</td>\n",
       "      <td>600</td>\n",
       "      <td>9:30 - 10:00</td>\n",
       "      <td>36</td>\n",
       "      <td>36</td>\n",
       "      <td>6</td>\n",
       "      <td>3</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>94</th>\n",
       "      <td>100</td>\n",
       "      <td>700</td>\n",
       "      <td>10:00 - 10:30</td>\n",
       "      <td>36</td>\n",
       "      <td>36</td>\n",
       "      <td>2</td>\n",
       "      <td>2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>95</th>\n",
       "      <td>100</td>\n",
       "      <td>700</td>\n",
       "      <td>6:00 - 6:30</td>\n",
       "      <td>36</td>\n",
       "      <td>36</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>96</th>\n",
       "      <td>100</td>\n",
       "      <td>700</td>\n",
       "      <td>6:30 - 7:00</td>\n",
       "      <td>34</td>\n",
       "      <td>36</td>\n",
       "      <td>2</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>97</th>\n",
       "      <td>100</td>\n",
       "      <td>700</td>\n",
       "      <td>6:30 - 7:00</td>\n",
       "      <td>36</td>\n",
       "      <td>36</td>\n",
       "      <td>2</td>\n",
       "      <td>2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>98</th>\n",
       "      <td>100</td>\n",
       "      <td>700</td>\n",
       "      <td>7:00 - 7:30</td>\n",
       "      <td>36</td>\n",
       "      <td>36</td>\n",
       "      <td>2</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>99</th>\n",
       "      <td>100</td>\n",
       "      <td>700</td>\n",
       "      <td>8:00 - 8:30</td>\n",
       "      <td>36</td>\n",
       "      <td>36</td>\n",
       "      <td>2</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>100 rows × 7 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "    origin_ct  dest_ct    time_period  origin_st  dest_st  od_counts  \\\n",
       "0         100      100  10:00 - 10:30         34       34         17   \n",
       "1         100      100    6:00 - 6:30         34       34          3   \n",
       "2         100      100    6:30 - 7:00         34       34         19   \n",
       "3         100      100    6:30 - 7:00         34       36          2   \n",
       "4         100      100    7:00 - 7:30         34       34         38   \n",
       "5         100      100    7:30 - 8:00         34       34          5   \n",
       "6         100      100    8:00 - 8:30         34       34         17   \n",
       "7         100      100    8:30 - 9:00         34       34         35   \n",
       "8         100      100    9:00 - 9:30         34       34         37   \n",
       "9         100      100   9:30 - 10:00         34       34         31   \n",
       "10        100      200  10:00 - 10:30         34       36          5   \n",
       "11        100      200  10:00 - 10:30         36       36          1   \n",
       "12        100      200    6:00 - 6:30         34       34          2   \n",
       "13        100      200    6:00 - 6:30         34       36          2   \n",
       "14        100      200    6:30 - 7:00         34       34         20   \n",
       "15        100      200    6:30 - 7:00         34       36          2   \n",
       "16        100      200    6:30 - 7:00         36       36          3   \n",
       "17        100      200    7:00 - 7:30         36       36          8   \n",
       "18        100      200    7:30 - 8:00         34       34          2   \n",
       "19        100      200    7:30 - 8:00         36       36          2   \n",
       "20        100      200    8:00 - 8:30         34       34          2   \n",
       "21        100      200    8:00 - 8:30         34       36          1   \n",
       "22        100      200    8:00 - 8:30         36       36          9   \n",
       "23        100      200    8:30 - 9:00         34       36          3   \n",
       "24        100      200    8:30 - 9:00         36       36          4   \n",
       "25        100      200    9:00 - 9:30         34       34          3   \n",
       "26        100      200    9:00 - 9:30         34       36          2   \n",
       "27        100      200    9:00 - 9:30         36       36          5   \n",
       "28        100      200   9:30 - 10:00         34       34          6   \n",
       "29        100      200   9:30 - 10:00         34       36          2   \n",
       "..        ...      ...            ...        ...      ...        ...   \n",
       "70        100      400    9:00 - 9:30         36       36          6   \n",
       "71        100      400   9:30 - 10:00         34       34          1   \n",
       "72        100      400   9:30 - 10:00         34       36         11   \n",
       "73        100      400   9:30 - 10:00         36       36          4   \n",
       "74        100      402    8:00 - 8:30         36       36          1   \n",
       "75        100      600  10:00 - 10:30         34       34         45   \n",
       "76        100      600  10:00 - 10:30         36       36          7   \n",
       "77        100      600    6:00 - 6:30         34       34         38   \n",
       "78        100      600    6:00 - 6:30         36       36          2   \n",
       "79        100      600    6:30 - 7:00         34       34          2   \n",
       "80        100      600    6:30 - 7:00         34       36          2   \n",
       "81        100      600    7:00 - 7:30         34       34         43   \n",
       "82        100      600    7:00 - 7:30         36       36          2   \n",
       "83        100      600    7:30 - 8:00         34       34         43   \n",
       "84        100      600    7:30 - 8:00         36       36          2   \n",
       "85        100      600    8:00 - 8:30         34       34         14   \n",
       "86        100      600    8:00 - 8:30         36       36          3   \n",
       "87        100      600    8:30 - 9:00         34       34          4   \n",
       "88        100      600    8:30 - 9:00         36       36          4   \n",
       "89        100      600    9:00 - 9:30         34       34          4   \n",
       "90        100      600    9:00 - 9:30         34       36          1   \n",
       "91        100      600    9:00 - 9:30         36       36          5   \n",
       "92        100      600   9:30 - 10:00         34       34         16   \n",
       "93        100      600   9:30 - 10:00         36       36          6   \n",
       "94        100      700  10:00 - 10:30         36       36          2   \n",
       "95        100      700    6:00 - 6:30         36       36          1   \n",
       "96        100      700    6:30 - 7:00         34       36          2   \n",
       "97        100      700    6:30 - 7:00         36       36          2   \n",
       "98        100      700    7:00 - 7:30         36       36          2   \n",
       "99        100      700    8:00 - 8:30         36       36          2   \n",
       "\n",
       "    num_dates  \n",
       "0          10  \n",
       "1           3  \n",
       "2          13  \n",
       "3           1  \n",
       "4          23  \n",
       "5           5  \n",
       "6          10  \n",
       "7          18  \n",
       "8          17  \n",
       "9          17  \n",
       "10          4  \n",
       "11          1  \n",
       "12          1  \n",
       "13          1  \n",
       "14         11  \n",
       "15          2  \n",
       "16          2  \n",
       "17          6  \n",
       "18          2  \n",
       "19          2  \n",
       "20          1  \n",
       "21          1  \n",
       "22          4  \n",
       "23          2  \n",
       "24          3  \n",
       "25          2  \n",
       "26          2  \n",
       "27          3  \n",
       "28          4  \n",
       "29          2  \n",
       "..        ...  \n",
       "70          4  \n",
       "71          1  \n",
       "72          6  \n",
       "73          2  \n",
       "74          1  \n",
       "75         25  \n",
       "76          4  \n",
       "77         21  \n",
       "78          1  \n",
       "79          1  \n",
       "80          1  \n",
       "81         23  \n",
       "82          1  \n",
       "83         21  \n",
       "84          1  \n",
       "85          9  \n",
       "86          2  \n",
       "87          3  \n",
       "88          3  \n",
       "89          3  \n",
       "90          1  \n",
       "91          3  \n",
       "92         11  \n",
       "93          3  \n",
       "94          2  \n",
       "95          1  \n",
       "96          1  \n",
       "97          2  \n",
       "98          1  \n",
       "99          1  \n",
       "\n",
       "[100 rows x 7 columns]"
      ]
     },
     "execution_count": 5,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "ny.head(100)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>STATEFP</th>\n",
       "      <th>COUNTYFP</th>\n",
       "      <th>TRACTCE</th>\n",
       "      <th>AFFGEOID</th>\n",
       "      <th>GEOID</th>\n",
       "      <th>NAME</th>\n",
       "      <th>LSAD</th>\n",
       "      <th>ALAND</th>\n",
       "      <th>AWATER</th>\n",
       "      <th>geometry</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>06</td>\n",
       "      <td>001</td>\n",
       "      <td>400600</td>\n",
       "      <td>1400000US06001400600</td>\n",
       "      <td>06001400600</td>\n",
       "      <td>4006</td>\n",
       "      <td>CT</td>\n",
       "      <td>297856</td>\n",
       "      <td>0</td>\n",
       "      <td>POLYGON ((-122.26807 37.844136, -122.26514 37....</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>06</td>\n",
       "      <td>001</td>\n",
       "      <td>400900</td>\n",
       "      <td>1400000US06001400900</td>\n",
       "      <td>06001400900</td>\n",
       "      <td>4009</td>\n",
       "      <td>CT</td>\n",
       "      <td>420877</td>\n",
       "      <td>0</td>\n",
       "      <td>POLYGON ((-122.285576 37.839778, -122.283186 3...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>06</td>\n",
       "      <td>001</td>\n",
       "      <td>401400</td>\n",
       "      <td>1400000US06001401400</td>\n",
       "      <td>06001401400</td>\n",
       "      <td>4014</td>\n",
       "      <td>CT</td>\n",
       "      <td>758204</td>\n",
       "      <td>0</td>\n",
       "      <td>POLYGON ((-122.278611 37.826878, -122.268563 3...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>06</td>\n",
       "      <td>001</td>\n",
       "      <td>403000</td>\n",
       "      <td>1400000US06001403000</td>\n",
       "      <td>06001403000</td>\n",
       "      <td>4030</td>\n",
       "      <td>CT</td>\n",
       "      <td>352394</td>\n",
       "      <td>0</td>\n",
       "      <td>POLYGON ((-122.274757 37.79883299999999, -122....</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>06</td>\n",
       "      <td>001</td>\n",
       "      <td>405902</td>\n",
       "      <td>1400000US06001405902</td>\n",
       "      <td>06001405902</td>\n",
       "      <td>4059.02</td>\n",
       "      <td>CT</td>\n",
       "      <td>487280</td>\n",
       "      <td>0</td>\n",
       "      <td>POLYGON ((-122.247175 37.789913, -122.243512 3...</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "  STATEFP COUNTYFP TRACTCE              AFFGEOID        GEOID     NAME LSAD  \\\n",
       "0      06      001  400600  1400000US06001400600  06001400600     4006   CT   \n",
       "1      06      001  400900  1400000US06001400900  06001400900     4009   CT   \n",
       "2      06      001  401400  1400000US06001401400  06001401400     4014   CT   \n",
       "3      06      001  403000  1400000US06001403000  06001403000     4030   CT   \n",
       "4      06      001  405902  1400000US06001405902  06001405902  4059.02   CT   \n",
       "\n",
       "    ALAND  AWATER                                           geometry  \n",
       "0  297856       0  POLYGON ((-122.26807 37.844136, -122.26514 37....  \n",
       "1  420877       0  POLYGON ((-122.285576 37.839778, -122.283186 3...  \n",
       "2  758204       0  POLYGON ((-122.278611 37.826878, -122.268563 3...  \n",
       "3  352394       0  POLYGON ((-122.274757 37.79883299999999, -122....  \n",
       "4  487280       0  POLYGON ((-122.247175 37.789913, -122.243512 3...  "
      ]
     },
     "execution_count": 6,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "geo.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "metadata": {},
   "outputs": [],
   "source": [
    "ct_files = ['ny_ct.csv','chic_ct.csv','sf_ct.csv','la_ct.csv']\n",
    "\n",
    "for ct_file in ct_files:  \n",
    "    df_ct = pd.read_csv(ct_file)   \n",
    "    df_ct_loss = df_ct.loc[df_ct['origin_ct']!=df_ct['dest_ct'],['origin_ct','time_period','od_counts']].groupby(['origin_ct','time_period']).sum()\n",
    "    df_ct_loss.reset_index(inplace=True)\n",
    "    df_ct_loss.rename(columns={'od_counts':'loss','origin_ct':'ct_id'},inplace=True)\n",
    "    df_ct_gain = df_ct.loc[df_ct['origin_ct']!=df_ct['dest_ct'],['dest_ct','time_period','od_counts']].groupby(['dest_ct','time_period']).sum()\n",
    "    df_ct_gain.reset_index(inplace=True)\n",
    "    df_ct_gain.rename(columns={'od_counts':'gain','dest_ct':'ct_id'},inplace=True)\n",
    "    df_ct_stay = df_ct.loc[df_ct['origin_ct']==df_ct['dest_ct'],['dest_ct','time_period','od_counts']].groupby(['dest_ct','time_period']).sum()\n",
    "    df_ct_stay.reset_index(inplace=True)\n",
    "    df_ct_stay.rename(columns={'od_counts':'stay','dest_ct':'ct_id'},inplace=True)\n",
    "    df_ct_agg = pd.merge(df_ct_gain,df_ct_loss,on=['ct_id','time_period'])\n",
    "    df_ct_agg = pd.merge(df_ct_agg,df_ct_stay,on=['ct_id','time_period'])\n",
    "    df_ct_agg = df_ct_agg.loc[:,['ct_id','time_period','gain','loss','stay']]\n",
    "    df_ct_agg.fillna(0,inplace=True)\n",
    "    city = ct_file.split('.')[0]\n",
    "    city += '_agg.csv'\n",
    "    df_ct_agg.to_csv(city)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 17,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>Unnamed: 0</th>\n",
       "      <th>ct_id</th>\n",
       "      <th>time_period</th>\n",
       "      <th>gain</th>\n",
       "      <th>loss</th>\n",
       "      <th>stay</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>0</td>\n",
       "      <td>100</td>\n",
       "      <td>10:00 - 10:30</td>\n",
       "      <td>10156</td>\n",
       "      <td>7738</td>\n",
       "      <td>17</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>1</td>\n",
       "      <td>100</td>\n",
       "      <td>6:00 - 6:30</td>\n",
       "      <td>7440</td>\n",
       "      <td>5966</td>\n",
       "      <td>3</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>2</td>\n",
       "      <td>100</td>\n",
       "      <td>6:30 - 7:00</td>\n",
       "      <td>9893</td>\n",
       "      <td>6987</td>\n",
       "      <td>21</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>3</td>\n",
       "      <td>100</td>\n",
       "      <td>7:00 - 7:30</td>\n",
       "      <td>11074</td>\n",
       "      <td>8252</td>\n",
       "      <td>38</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>4</td>\n",
       "      <td>100</td>\n",
       "      <td>7:30 - 8:00</td>\n",
       "      <td>11382</td>\n",
       "      <td>8814</td>\n",
       "      <td>5</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   Unnamed: 0  ct_id    time_period   gain  loss  stay\n",
       "0           0    100  10:00 - 10:30  10156  7738    17\n",
       "1           1    100    6:00 - 6:30   7440  5966     3\n",
       "2           2    100    6:30 - 7:00   9893  6987    21\n",
       "3           3    100    7:00 - 7:30  11074  8252    38\n",
       "4           4    100    7:30 - 8:00  11382  8814     5"
      ]
     },
     "execution_count": 17,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "pd.read_csv('ny_ct_agg.csv').head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "ADS",
   "language": "python",
   "name": "ads"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 3
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython3",
   "version": "3.5.0"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 2
}