Tree_manipulation.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "* Importing the required modeuls and reading the raw data"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 1,
   "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>tree_id</th>\n",
       "      <th>block_id</th>\n",
       "      <th>created_at</th>\n",
       "      <th>tree_dbh</th>\n",
       "      <th>stump_diam</th>\n",
       "      <th>curb_loc</th>\n",
       "      <th>status</th>\n",
       "      <th>health</th>\n",
       "      <th>spc_latin</th>\n",
       "      <th>spc_common</th>\n",
       "      <th>...</th>\n",
       "      <th>boro_ct</th>\n",
       "      <th>state</th>\n",
       "      <th>latitude</th>\n",
       "      <th>longitude</th>\n",
       "      <th>x_sp</th>\n",
       "      <th>y_sp</th>\n",
       "      <th>council district</th>\n",
       "      <th>census tract</th>\n",
       "      <th>bin</th>\n",
       "      <th>bbl</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>496956</th>\n",
       "      <td>707763</td>\n",
       "      <td>205047</td>\n",
       "      <td>09/11/2016</td>\n",
       "      <td>4</td>\n",
       "      <td>0</td>\n",
       "      <td>OnCurb</td>\n",
       "      <td>Alive</td>\n",
       "      <td>Good</td>\n",
       "      <td>Prunus</td>\n",
       "      <td>cherry</td>\n",
       "      <td>...</td>\n",
       "      <td>3040000</td>\n",
       "      <td>New York</td>\n",
       "      <td>40.597287</td>\n",
       "      <td>-73.974087</td>\n",
       "      <td>9.914462e+05</td>\n",
       "      <td>156885.2263</td>\n",
       "      <td>47.0</td>\n",
       "      <td>400.0</td>\n",
       "      <td>3191376.0</td>\n",
       "      <td>3.071020e+09</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>616245</th>\n",
       "      <td>122377</td>\n",
       "      <td>337506</td>\n",
       "      <td>08/06/2015</td>\n",
       "      <td>8</td>\n",
       "      <td>0</td>\n",
       "      <td>OnCurb</td>\n",
       "      <td>Alive</td>\n",
       "      <td>Good</td>\n",
       "      <td>Gleditsia triacanthos var. inermis</td>\n",
       "      <td>honeylocust</td>\n",
       "      <td>...</td>\n",
       "      <td>4130100</td>\n",
       "      <td>New York</td>\n",
       "      <td>40.731956</td>\n",
       "      <td>-73.740624</td>\n",
       "      <td>1.056135e+06</td>\n",
       "      <td>206054.4895</td>\n",
       "      <td>23.0</td>\n",
       "      <td>1301.0</td>\n",
       "      <td>4563146.0</td>\n",
       "      <td>4.079430e+09</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>11489</th>\n",
       "      <td>207484</td>\n",
       "      <td>319786</td>\n",
       "      <td>09/06/2015</td>\n",
       "      <td>3</td>\n",
       "      <td>0</td>\n",
       "      <td>OnCurb</td>\n",
       "      <td>Alive</td>\n",
       "      <td>Good</td>\n",
       "      <td>Quercus palustris</td>\n",
       "      <td>pin oak</td>\n",
       "      <td>...</td>\n",
       "      <td>4129104</td>\n",
       "      <td>New York</td>\n",
       "      <td>40.741180</td>\n",
       "      <td>-73.755033</td>\n",
       "      <td>1.052133e+06</td>\n",
       "      <td>209403.3289</td>\n",
       "      <td>23.0</td>\n",
       "      <td>129104.0</td>\n",
       "      <td>4444484.0</td>\n",
       "      <td>4.077420e+09</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>143733</th>\n",
       "      <td>337298</td>\n",
       "      <td>510003</td>\n",
       "      <td>10/16/2015</td>\n",
       "      <td>8</td>\n",
       "      <td>0</td>\n",
       "      <td>OnCurb</td>\n",
       "      <td>Alive</td>\n",
       "      <td>Good</td>\n",
       "      <td>Gleditsia triacanthos var. inermis</td>\n",
       "      <td>honeylocust</td>\n",
       "      <td>...</td>\n",
       "      <td>2009600</td>\n",
       "      <td>New York</td>\n",
       "      <td>40.837095</td>\n",
       "      <td>-73.846122</td>\n",
       "      <td>1.026830e+06</td>\n",
       "      <td>244290.9268</td>\n",
       "      <td>18.0</td>\n",
       "      <td>96.0</td>\n",
       "      <td>2103339.0</td>\n",
       "      <td>2.038460e+09</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>216365</th>\n",
       "      <td>406859</td>\n",
       "      <td>206142</td>\n",
       "      <td>10/30/2015</td>\n",
       "      <td>15</td>\n",
       "      <td>0</td>\n",
       "      <td>OnCurb</td>\n",
       "      <td>Alive</td>\n",
       "      <td>Good</td>\n",
       "      <td>Quercus palustris</td>\n",
       "      <td>pin oak</td>\n",
       "      <td>...</td>\n",
       "      <td>3026600</td>\n",
       "      <td>New York</td>\n",
       "      <td>40.612839</td>\n",
       "      <td>-73.989786</td>\n",
       "      <td>9.870858e+05</td>\n",
       "      <td>162550.4443</td>\n",
       "      <td>47.0</td>\n",
       "      <td>266.0</td>\n",
       "      <td>3158005.0</td>\n",
       "      <td>3.061840e+09</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>5 rows × 45 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "        tree_id  block_id  created_at  tree_dbh  stump_diam curb_loc status  \\\n",
       "496956   707763    205047  09/11/2016         4           0   OnCurb  Alive   \n",
       "616245   122377    337506  08/06/2015         8           0   OnCurb  Alive   \n",
       "11489    207484    319786  09/06/2015         3           0   OnCurb  Alive   \n",
       "143733   337298    510003  10/16/2015         8           0   OnCurb  Alive   \n",
       "216365   406859    206142  10/30/2015        15           0   OnCurb  Alive   \n",
       "\n",
       "       health                           spc_latin   spc_common      ...       \\\n",
       "496956   Good                              Prunus       cherry      ...        \n",
       "616245   Good  Gleditsia triacanthos var. inermis  honeylocust      ...        \n",
       "11489    Good                   Quercus palustris      pin oak      ...        \n",
       "143733   Good  Gleditsia triacanthos var. inermis  honeylocust      ...        \n",
       "216365   Good                   Quercus palustris      pin oak      ...        \n",
       "\n",
       "        boro_ct     state   latitude  longitude          x_sp         y_sp  \\\n",
       "496956  3040000  New York  40.597287 -73.974087  9.914462e+05  156885.2263   \n",
       "616245  4130100  New York  40.731956 -73.740624  1.056135e+06  206054.4895   \n",
       "11489   4129104  New York  40.741180 -73.755033  1.052133e+06  209403.3289   \n",
       "143733  2009600  New York  40.837095 -73.846122  1.026830e+06  244290.9268   \n",
       "216365  3026600  New York  40.612839 -73.989786  9.870858e+05  162550.4443   \n",
       "\n",
       "       council district census tract        bin           bbl  \n",
       "496956             47.0        400.0  3191376.0  3.071020e+09  \n",
       "616245             23.0       1301.0  4563146.0  4.079430e+09  \n",
       "11489              23.0     129104.0  4444484.0  4.077420e+09  \n",
       "143733             18.0         96.0  2103339.0  2.038460e+09  \n",
       "216365             47.0        266.0  3158005.0  3.061840e+09  \n",
       "\n",
       "[5 rows x 45 columns]"
      ]
     },
     "execution_count": 1,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "import json\n",
    "import requests\n",
    "from urllib.request import urlopen\n",
    "import time\n",
    "import pandas as pd\n",
    "import numpy as np\n",
    "import glob\n",
    "url = \"https://data.cityofnewyork.us/api/views/uvpi-gqnh/rows.csv?accessType=DOWNLOAD\"\n",
    "original = pd.read_csv(url)\n",
    "original.sample(5)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "* Filtering out \"Dead\" trees according to the status column in the data"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "metadata": {},
   "outputs": [],
   "source": [
    "notDead = original[original.status != 'Dead']"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "* Selecting the appropriate columns and saving the results in a new file"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "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>tree_id</th>\n",
       "      <th>latitude</th>\n",
       "      <th>longitude</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>435739</th>\n",
       "      <td>623942</td>\n",
       "      <td>40.675932</td>\n",
       "      <td>-73.812018</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>430780</th>\n",
       "      <td>655717</td>\n",
       "      <td>40.759579</td>\n",
       "      <td>-73.900920</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>200384</th>\n",
       "      <td>359925</td>\n",
       "      <td>40.537187</td>\n",
       "      <td>-74.204952</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>168389</th>\n",
       "      <td>369704</td>\n",
       "      <td>40.540392</td>\n",
       "      <td>-74.199399</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>521810</th>\n",
       "      <td>138</td>\n",
       "      <td>40.816356</td>\n",
       "      <td>-73.890581</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "        tree_id   latitude  longitude\n",
       "435739   623942  40.675932 -73.812018\n",
       "430780   655717  40.759579 -73.900920\n",
       "200384   359925  40.537187 -74.204952\n",
       "168389   369704  40.540392 -74.199399\n",
       "521810      138  40.816356 -73.890581"
      ]
     },
     "execution_count": 3,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "SelectedColumns = ['tree_id', 'latitude', 'longitude']\n",
    "final = notDead.loc[:, SelectedColumns]\n",
    "final.to_csv('Tree_LatLong.csv')\n",
    "final.sample(5)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "* So far we have managed to have the exact location of each tree, but to generate the final dataset we must convert latitude and longitude to census tract. Function below is defined to take two arguments, latitude and longitude, as the input and return the FIPS code."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "metadata": {},
   "outputs": [],
   "source": [
    "def geo_to_fips(latitude, longitude):\n",
    "    url = \"https://geo.fcc.gov/api/census/block/find?latitude=\" + str(latitude) + \"&longitude=\"+ str(longitude) + \"&format=json\"\n",
    "    obj = json.load(urlopen(url))\n",
    "    return  obj['Block']['FIPS']"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "* Note that the raw data is too big to use the API for all the data at once. It is suggested to break the data into smaller subsets and convert latitude and longitude to census tract separately. \n",
    "* Eventually the converted subsets need to be merged to create the final dataset.\n",
    "* Once previous steps are done, you should have a dataset containing census tract in one column and tree id in the other. You must aggregate the data on the census tract  to count the number of trees in each census tract. The code below is the example of what you should be using.\n",
    "* Finally you must remove any unnecessary columns from the dataset and only keep two columns; census tract column and count column"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "Census_Tract_Data = pd.DataFrame()\n",
    "Tree_Count = Census_Tract_Data.groupby(by=\"Census_Tract\").count()"
   ]
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3",
   "language": "python",
   "name": "python3"
  },
  "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.6.4"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 2
}