social_infrastructure.ipynb

{"nbformat":4,"nbformat_minor":0,"metadata":{"colab":{"name":"social_infrastructure.ipynb","provenance":[],"collapsed_sections":[]},"kernelspec":{"display_name":"Python 3","name":"python3"},"language_info":{"name":"python"}},"cells":[{"cell_type":"markdown","metadata":{"id":"Ee6h9x5O_LXL"},"source":["This notebook fetches and formats data from a number of sources: \n","\n","* Farm and garden data prepared in _urban_agriculture.ipynb_\n","* 2018 NYC PLUTO building classifications from NYC DCP\n","* Income, education, and other demographic variables from ACS via Data2Go\n","* Violent crime data from NYPD\n","* Voter participation rates from the Campaign Finance Board\n","* Census response rates from Census.gov\n","* Reported mental and physical health from CDC PLACES\n","* Census tract shapefiles and populations centroids from Census.gov\n","\n","aggregating point measures to the census tract level and joining all for modeling in _non_spatial_regression.ipynb_\n"]},{"cell_type":"markdown","metadata":{"id":"rSMT5ar8BEHh"},"source":["Link to Drive and path to data"]},{"cell_type":"code","metadata":{"colab":{"base_uri":"https://localhost:8080/"},"id":"F82HogvU6rci","executionInfo":{"status":"ok","timestamp":1637532112870,"user_tz":300,"elapsed":26722,"user":{"displayName":"Nico Ampuero","photoUrl":"https://lh3.googleusercontent.com/a/default-user=s64","userId":"12880282598849218460"}},"outputId":"ed734339-809e-46c4-f5ae-559d33d6f90b"},"source":["from google.colab import drive\n","drive.mount('/content/drive')"],"execution_count":1,"outputs":[{"output_type":"stream","name":"stdout","text":["Mounted at /content/drive\n"]}]},{"cell_type":"code","metadata":{"colab":{"base_uri":"https://localhost:8080/"},"id":"Cj822XQb66sC","executionInfo":{"status":"ok","timestamp":1637532113451,"user_tz":300,"elapsed":596,"user":{"displayName":"Nico Ampuero","photoUrl":"https://lh3.googleusercontent.com/a/default-user=s64","userId":"12880282598849218460"}},"outputId":"c913b871-2379-4fa1-d0e7-0ddb9813260b"},"source":["cd '/content/drive/My Drive/Summer/MAP NYC x CUSP/data/social_infrastructure'"],"execution_count":2,"outputs":[{"output_type":"stream","name":"stdout","text":["/content/drive/My Drive/Summer/MAP NYC x CUSP/data/social_infrastructure\n"]}]},{"cell_type":"markdown","metadata":{"id":"qV-exh6NBKnB"},"source":["Install and import required packages"]},{"cell_type":"code","metadata":{"colab":{"base_uri":"https://localhost:8080/"},"id":"tnZAk5Bx6-6A","executionInfo":{"status":"ok","timestamp":1637532120612,"user_tz":300,"elapsed":7170,"user":{"displayName":"Nico Ampuero","photoUrl":"https://lh3.googleusercontent.com/a/default-user=s64","userId":"12880282598849218460"}},"outputId":"23bea46e-767c-47d0-e885-746bc715c491"},"source":["!pip install geopandas rtree"],"execution_count":3,"outputs":[{"output_type":"stream","name":"stdout","text":["Collecting geopandas\n","  Downloading geopandas-0.10.2-py2.py3-none-any.whl (1.0 MB)\n","\u001b[K     |████████████████████████████████| 1.0 MB 29.4 MB/s \n","\u001b[?25hCollecting rtree\n","  Downloading Rtree-0.9.7-cp37-cp37m-manylinux2010_x86_64.whl (994 kB)\n","\u001b[K     |████████████████████████████████| 994 kB 48.3 MB/s \n","\u001b[?25hRequirement already satisfied: shapely>=1.6 in /usr/local/lib/python3.7/dist-packages (from geopandas) (1.8.0)\n","Requirement already satisfied: pandas>=0.25.0 in /usr/local/lib/python3.7/dist-packages (from geopandas) (1.1.5)\n","Collecting fiona>=1.8\n","  Downloading Fiona-1.8.20-cp37-cp37m-manylinux1_x86_64.whl (15.4 MB)\n","\u001b[K     |████████████████████████████████| 15.4 MB 39 kB/s \n","\u001b[?25hCollecting pyproj>=2.2.0\n","  Downloading pyproj-3.2.1-cp37-cp37m-manylinux2010_x86_64.whl (6.3 MB)\n","\u001b[K     |████████████████████████████████| 6.3 MB 56.7 MB/s \n","\u001b[?25hCollecting cligj>=0.5\n","  Downloading cligj-0.7.2-py3-none-any.whl (7.1 kB)\n","Requirement already satisfied: six>=1.7 in /usr/local/lib/python3.7/dist-packages (from fiona>=1.8->geopandas) (1.15.0)\n","Requirement already satisfied: click>=4.0 in /usr/local/lib/python3.7/dist-packages (from fiona>=1.8->geopandas) (7.1.2)\n","Collecting munch\n","  Downloading munch-2.5.0-py2.py3-none-any.whl (10 kB)\n","Requirement already satisfied: certifi in /usr/local/lib/python3.7/dist-packages (from fiona>=1.8->geopandas) (2021.10.8)\n","Requirement already satisfied: attrs>=17 in /usr/local/lib/python3.7/dist-packages (from fiona>=1.8->geopandas) (21.2.0)\n","Collecting click-plugins>=1.0\n","  Downloading click_plugins-1.1.1-py2.py3-none-any.whl (7.5 kB)\n","Requirement already satisfied: setuptools in /usr/local/lib/python3.7/dist-packages (from fiona>=1.8->geopandas) (57.4.0)\n","Requirement already satisfied: python-dateutil>=2.7.3 in /usr/local/lib/python3.7/dist-packages (from pandas>=0.25.0->geopandas) (2.8.2)\n","Requirement already satisfied: pytz>=2017.2 in /usr/local/lib/python3.7/dist-packages (from pandas>=0.25.0->geopandas) (2018.9)\n","Requirement already satisfied: numpy>=1.15.4 in /usr/local/lib/python3.7/dist-packages (from pandas>=0.25.0->geopandas) (1.19.5)\n","Installing collected packages: munch, cligj, click-plugins, pyproj, fiona, rtree, geopandas\n","Successfully installed click-plugins-1.1.1 cligj-0.7.2 fiona-1.8.20 geopandas-0.10.2 munch-2.5.0 pyproj-3.2.1 rtree-0.9.7\n"]}]},{"cell_type":"code","metadata":{"id":"rhNvhN837A_h","executionInfo":{"status":"ok","timestamp":1637538106669,"user_tz":300,"elapsed":197,"user":{"displayName":"Nico Ampuero","photoUrl":"https://lh3.googleusercontent.com/a/default-user=s64","userId":"12880282598849218460"}}},"source":["import requests\n","import numpy as np\n","import pandas as pd\n","import geopandas as gpd\n","\n","import requests\n","from urllib.request import urlretrieve\n","from zipfile import ZipFile\n","from os import remove as remove_file\n","from shutil import rmtree as remove_dir"],"execution_count":42,"outputs":[]},{"cell_type":"markdown","metadata":{"id":"T9A60v5CBQMz"},"source":["Define API handler for Open Data sources"]},{"cell_type":"code","metadata":{"id":"l-gBW9HkkUNl","executionInfo":{"status":"ok","timestamp":1637532120858,"user_tz":300,"elapsed":10,"user":{"displayName":"Nico Ampuero","photoUrl":"https://lh3.googleusercontent.com/a/default-user=s64","userId":"12880282598849218460"}}},"source":["def get_socrata(resource, query='', dates=[], limit=1000, sample=False, domain='data.cityofnewyork.us', progress=False):\n","    headers = {'X-App-Token': 'b0nu5h6f5RSJRNPajPuuCf7wo'}\n","    result = []\n","    offset = 0\n","    while offset >= 0:\n","        url = f'https://{domain}/resource/{resource}.json?$limit={limit}&$offset={offset}&$order=:id'\n","        if query != '': #can't use requests params bc of possible $ char\n","            url += f'&{query}'\n","        if (progress):\n","            print(url)\n","        json = requests.get(url, headers=headers).json()\n","        if 'error' in json:\n","            print(json['message'])\n","            return\n","        else:\n","            df = pd.DataFrame(json)\n","            if len(df):\n","                if len(dates):\n","                    df[dates] = df[dates].apply(pd.to_datetime, errors='coerce')\n","                result.append(df)\n","                offset += limit\n","            else:\n","                offset = -1\n","        if sample:\n","          break\n","\n","    result = pd.concat(result).reset_index() if len(result) else pd.DataFrame()\n","    print(f'Retreived {len(result)} records')\n","    return result"],"execution_count":5,"outputs":[]},{"cell_type":"markdown","metadata":{"id":"cbH-Uv0FBXwB"},"source":["Load the _urban_ag.csv_ output from _urban_ag_formatter.ipynb_"]},{"cell_type":"code","metadata":{"colab":{"base_uri":"https://localhost:8080/","height":206},"id":"4R82EzEG7C-v","executionInfo":{"status":"ok","timestamp":1637532121859,"user_tz":300,"elapsed":819,"user":{"displayName":"Nico Ampuero","photoUrl":"https://lh3.googleusercontent.com/a/default-user=s64","userId":"12880282598849218460"}},"outputId":"97df2f9f-1736-4607-96a1-565a48296977"},"source":["usecols = ['name', 'address', 'type', 'grow', 'borocode', 'block', 'lot', 'lat', 'lon', 'lotarea']\n","urban_ag = pd.read_csv('../urban_agriculture/urban_ag.csv', usecols=usecols)\n","urban_ag.columns = urban_ag.columns.str.lower()\n","urban_ag.head()"],"execution_count":6,"outputs":[{"output_type":"execute_result","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>name</th>\n","      <th>address</th>\n","      <th>type</th>\n","      <th>grow</th>\n","      <th>lat</th>\n","      <th>lon</th>\n","      <th>lotarea</th>\n","      <th>borocode</th>\n","      <th>block</th>\n","      <th>lot</th>\n","    </tr>\n","  </thead>\n","  <tbody>\n","    <tr>\n","      <th>0</th>\n","      <td>Fishbridge Park Garden</td>\n","      <td>Pearl Street, New York, NY 10038, USA</td>\n","      <td>Community Garden</td>\n","      <td>Outdoor</td>\n","      <td>40.709068</td>\n","      <td>-74.001378</td>\n","      <td>4034.0</td>\n","      <td>1.0</td>\n","      <td>106.0</td>\n","      <td>22.0</td>\n","    </tr>\n","    <tr>\n","      <th>1</th>\n","      <td>Lower East Side People Care</td>\n","      <td>25 Rutgers Street, New York, NY 10002, USA</td>\n","      <td>Community Garden</td>\n","      <td>Outdoor</td>\n","      <td>40.713006</td>\n","      <td>-73.989824</td>\n","      <td>2135.0</td>\n","      <td>1.0</td>\n","      <td>271.0</td>\n","      <td>63.0</td>\n","    </tr>\n","    <tr>\n","      <th>2</th>\n","      <td>Clinton Community Garden (LES)</td>\n","      <td>Stanton Street, New York, NY 10002, USA</td>\n","      <td>Community Garden</td>\n","      <td>Outdoor</td>\n","      <td>40.720010</td>\n","      <td>-73.984459</td>\n","      <td>2533.0</td>\n","      <td>1.0</td>\n","      <td>344.0</td>\n","      <td>148.0</td>\n","    </tr>\n","    <tr>\n","      <th>3</th>\n","      <td>Siempre Verde Garden</td>\n","      <td>Stanton Street, New York, NY 10002, USA</td>\n","      <td>Community Garden</td>\n","      <td>Outdoor</td>\n","      <td>40.719930</td>\n","      <td>-73.984019</td>\n","      <td>1197.0</td>\n","      <td>1.0</td>\n","      <td>344.0</td>\n","      <td>153.0</td>\n","    </tr>\n","    <tr>\n","      <th>4</th>\n","      <td>Siempre Verde Garden</td>\n","      <td>Attorney Street, New York, NY 10002, USA</td>\n","      <td>Community Garden</td>\n","      <td>Outdoor</td>\n","      <td>40.719744</td>\n","      <td>-73.984008</td>\n","      <td>1405.0</td>\n","      <td>1.0</td>\n","      <td>344.0</td>\n","      <td>158.0</td>\n","    </tr>\n","  </tbody>\n","</table>\n","</div>"],"text/plain":["                             name  ...    lot\n","0          Fishbridge Park Garden  ...   22.0\n","1     Lower East Side People Care  ...   63.0\n","2  Clinton Community Garden (LES)  ...  148.0\n","3            Siempre Verde Garden  ...  153.0\n","4            Siempre Verde Garden  ...  158.0\n","\n","[5 rows x 10 columns]"]},"metadata":{},"execution_count":6}]},{"cell_type":"markdown","metadata":{"id":"ZJdE_oNeE-XZ"},"source":["Aggregate by name, borough and block (many gardens are in adjacent lots and should be counted as a single garden)"]},{"cell_type":"code","metadata":{"id":"TVvtE3k-ZSgI","executionInfo":{"status":"ok","timestamp":1637532121861,"user_tz":300,"elapsed":6,"user":{"displayName":"Nico Ampuero","photoUrl":"https://lh3.googleusercontent.com/a/default-user=s64","userId":"12880282598849218460"}}},"source":["x = urban_ag['type'] == 'NYCHA Garden'\n","urban_ag.loc[x, 'name'] = urban_ag.loc[x, 'name'].str.replace(' Garden [0-9]+$', '') # aggregate NYCHA gardens by development"],"execution_count":7,"outputs":[]},{"cell_type":"code","metadata":{"colab":{"base_uri":"https://localhost:8080/"},"id":"CtMh92SY7E5u","executionInfo":{"status":"ok","timestamp":1637532124041,"user_tz":300,"elapsed":268,"user":{"displayName":"Nico Ampuero","photoUrl":"https://lh3.googleusercontent.com/a/default-user=s64","userId":"12880282598849218460"}},"outputId":"5b046a56-9bc4-4609-cc9b-7dfdd9c816e6"},"source":["urban_ag['borocode'].fillna(0, inplace=True)\n","urban_ag['block'].fillna(0, inplace=True)\n","urban_ag['grow'].fillna('', inplace=True)\n","\n","ag_unique = (\n","    urban_ag\n","    .groupby(['name', 'type', 'grow', 'borocode', 'block'])\n","    .agg({'lat': 'mean', 'lon': 'mean', 'address': 'first', 'lotarea': 'sum'}, min_count=1)\n","    .reset_index()\n",")\n","ag_unique['type'].value_counts()"],"execution_count":8,"outputs":[{"output_type":"execute_result","data":{"text/plain":["School Garden                 676\n","Community Garden              583\n","NYCHA Garden                  276\n","Potential Community Garden     74\n","Non-commercial Farm            29\n","Commercial Farm                22\n","Name: type, dtype: int64"]},"metadata":{},"execution_count":8}]},{"cell_type":"markdown","metadata":{"id":"o_1pnqS9FQcm"},"source":["Define some dictionary maps and variable for subsequent formatting"]},{"cell_type":"code","metadata":{"id":"K0vaKnne7HJU","executionInfo":{"status":"ok","timestamp":1637532125744,"user_tz":300,"elapsed":318,"user":{"displayName":"Nico Ampuero","photoUrl":"https://lh3.googleusercontent.com/a/default-user=s64","userId":"12880282598849218460"}}},"source":["boro_counties = {'1': '061', '2': '005', '3': '047', '4': '081', '5': '085'}\n","\n","counties_str = list(boro_counties.values())\n","counties = [int(x) for x in counties_str]\n","\n","type_codes = {'Commercial Farm': 'FC',\n","              'Non-commercial Farm': 'FN',\n","              'Community Garden': 'GC',\n","              'NYCHA Garden': 'GN',\n","              'School Garden': 'GS',\n","              'Potential Community Garden': 'GP'}\n","types = list(type_codes.keys())"],"execution_count":9,"outputs":[]},{"cell_type":"code","metadata":{"colab":{"base_uri":"https://localhost:8080/"},"id":"jXIi7Izk7Kq7","executionInfo":{"status":"ok","timestamp":1637532127259,"user_tz":300,"elapsed":211,"user":{"displayName":"Nico Ampuero","photoUrl":"https://lh3.googleusercontent.com/a/default-user=s64","userId":"12880282598849218460"}},"outputId":"a858e1f8-a29a-4aad-f58b-69e5728c228d"},"source":["ag_unique.groupby('type')['lotarea'].quantile(.75)"],"execution_count":10,"outputs":[{"output_type":"execute_result","data":{"text/plain":["type\n","Commercial Farm               123399.75\n","Community Garden               10543.50\n","NYCHA Garden                    4595.25\n","Non-commercial Farm           145000.00\n","Potential Community Garden      7798.75\n","School Garden                 108400.25\n","Name: lotarea, dtype: float64"]},"metadata":{},"execution_count":10}]},{"cell_type":"markdown","metadata":{"id":"8NdwdTiTFbxQ"},"source":["Cap the lot size of certain farms of known size and others based on distribution of sizes for each type (tax lots can be significantly larger than the farm/garden itself)"]},{"cell_type":"code","metadata":{"id":"uY8WUDhr7Nzu","executionInfo":{"status":"ok","timestamp":1637532128910,"user_tz":300,"elapsed":172,"user":{"displayName":"Nico Ampuero","photoUrl":"https://lh3.googleusercontent.com/a/default-user=s64","userId":"12880282598849218460"}}},"source":["ag_unique['area_upper'] = (\n","    np.where(ag_unique['name'] == 'Queens County Farm', 200000, \n","             np.where(ag_unique['name'] == 'Brooklyn Grange Rooftop Farms', 135000,\n","                      np.where(ag_unique['name'] == 'GrowNYC Teaching Garden at Governors Island', 4000, #listed as garden, not farm\n","                               np.where(ag_unique['type'].str.contains('Farm'), 40000, # ~1 acre\n","                                        np.where(ag_unique['type'].str.contains('Community'), 10000, #~75th percentile\n","                                                 2000))))) # NYCHA 75th is probably the most realistic for schools too\n"," )\n","ag_unique['area'] = ag_unique['lotarea'].clip(upper=ag_unique['area_upper'])\n","ag_unique['typecode'] = ag_unique['type'].map(type_codes)"],"execution_count":11,"outputs":[]},{"cell_type":"markdown","metadata":{"id":"1wf_Zd3pF2CZ"},"source":["Make GeoDataFrames for spatial joins"]},{"cell_type":"code","metadata":{"id":"0Pyjh0xvLSvS","executionInfo":{"status":"ok","timestamp":1637532130571,"user_tz":300,"elapsed":172,"user":{"displayName":"Nico Ampuero","photoUrl":"https://lh3.googleusercontent.com/a/default-user=s64","userId":"12880282598849218460"}}},"source":["ll_crs = 'EPSG:4326'\n","nyc_crs = 'EPSG:2263' # DCP equal area proj\n","\n","def gdf_ll(df, lon='longitude', lat='latitude', crs=ll_crs):\n","  df = df.dropna(subset=[lon, lat])\n","  return gpd.GeoDataFrame(df, geometry=gpd.points_from_xy(df[lon], df[lat]), crs=crs).to_crs(nyc_crs) \n","\n","ag_geo = gdf_ll(ag_unique, 'lon', 'lat')"],"execution_count":12,"outputs":[]},{"cell_type":"markdown","metadata":{"id":"a6MVy6YvGBHE"},"source":["Load PLUTO data for building classifications and make sure relevant lots are distinct from urban agriculture dataset"]},{"cell_type":"code","metadata":{"colab":{"base_uri":"https://localhost:8080/","height":206},"id":"6VqtgdAf350L","executionInfo":{"status":"ok","timestamp":1637538127205,"user_tz":300,"elapsed":16012,"user":{"displayName":"Nico Ampuero","photoUrl":"https://lh3.googleusercontent.com/a/default-user=s64","userId":"12880282598849218460"}},"outputId":"3df18d66-f425-442d-c151-49f64def51ef"},"source":["ver = '18v2_1'\n","url = f'https://www1.nyc.gov/assets/planning/download/zip/data-maps/open-data/nyc_pluto_{ver}_csv.zip'\n","\n","tmp = 'tmp/'\n","zip = 'tmp.zip'\n","urlretrieve(url, zip)\n","with ZipFile(zip, 'r') as zip_ref:\n","    zip_ref.extractall(tmp)\n","\n","usecols = ['borocode', 'block', 'lot', 'bldgclass', 'xcoord', 'ycoord', 'lotarea']\n","pluto = pd.read_csv(f'{tmp}pluto_{ver}.csv', usecols=usecols)\n","\n","remove_dir(tmp)\n","remove_file(zip)\n","\n","pluto.head()"],"execution_count":43,"outputs":[{"output_type":"execute_result","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>block</th>\n","      <th>lot</th>\n","      <th>bldgclass</th>\n","      <th>lotarea</th>\n","      <th>borocode</th>\n","      <th>xcoord</th>\n","      <th>ycoord</th>\n","    </tr>\n","  </thead>\n","  <tbody>\n","    <tr>\n","      <th>0</th>\n","      <td>5641.0</td>\n","      <td>670.0</td>\n","      <td>T2</td>\n","      <td>53758.0</td>\n","      <td>2.0</td>\n","      <td>1044510.0</td>\n","      <td>246734.0</td>\n","    </tr>\n","    <tr>\n","      <th>1</th>\n","      <td>3935.0</td>\n","      <td>3.0</td>\n","      <td>A1</td>\n","      <td>10870.0</td>\n","      <td>4.0</td>\n","      <td>1025679.0</td>\n","      <td>227096.0</td>\n","    </tr>\n","    <tr>\n","      <th>2</th>\n","      <td>4205.0</td>\n","      <td>41.0</td>\n","      <td>V0</td>\n","      <td>2369.0</td>\n","      <td>2.0</td>\n","      <td>1026345.0</td>\n","      <td>249922.0</td>\n","    </tr>\n","    <tr>\n","      <th>3</th>\n","      <td>2402.0</td>\n","      <td>14.0</td>\n","      <td>G7</td>\n","      <td>3750.0</td>\n","      <td>2.0</td>\n","      <td>1007336.0</td>\n","      <td>238351.0</td>\n","    </tr>\n","    <tr>\n","      <th>4</th>\n","      <td>63.0</td>\n","      <td>83.0</td>\n","      <td>A1</td>\n","      <td>3465.0</td>\n","      <td>5.0</td>\n","      <td>958845.0</td>\n","      <td>172482.0</td>\n","    </tr>\n","  </tbody>\n","</table>\n","</div>"],"text/plain":["    block    lot bldgclass  lotarea  borocode     xcoord    ycoord\n","0  5641.0  670.0        T2  53758.0       2.0  1044510.0  246734.0\n","1  3935.0    3.0        A1  10870.0       4.0  1025679.0  227096.0\n","2  4205.0   41.0        V0   2369.0       2.0  1026345.0  249922.0\n","3  2402.0   14.0        G7   3750.0       2.0  1007336.0  238351.0\n","4    63.0   83.0        A1   3465.0       5.0   958845.0  172482.0"]},"metadata":{},"execution_count":43}]},{"cell_type":"code","metadata":{"id":"rpjjr59iJd5v","executionInfo":{"status":"ok","timestamp":1637538152149,"user_tz":300,"elapsed":17233,"user":{"displayName":"Nico Ampuero","photoUrl":"https://lh3.googleusercontent.com/a/default-user=s64","userId":"12880282598849218460"}}},"source":["pluto_social = pluto[pluto['bldgclass'].str.contains('^M|^P[5,7-8]|^Q[0-4,7]|^W1|^V[0-6]') == True].copy() #public spaces only; exclude gardens, use our count\n","\n","social_geo = gdf_ll(pluto_social, 'xcoord', 'ycoord', crs=nyc_crs)\n","social_geo['ll'] = social_geo['geometry'].to_crs(ll_crs)\n","social_geo['lon'] = social_geo['ll'].apply(lambda p: p.x)\n","social_geo['lat'] = social_geo['ll'].apply(lambda p: p.y)\n","\n","# remove buildings already in ag dataset based on buffer area (BBL is unreliable)\n","social_geo['geometry'] = social_geo['geometry'].buffer(50) # in ft\n","social_geo = social_geo.overlay(ag_geo, how='difference')"],"execution_count":44,"outputs":[]},{"cell_type":"markdown","metadata":{"id":"9BSxreS6GdUf"},"source":["Define social infrastructure categories"]},{"cell_type":"code","metadata":{"colab":{"base_uri":"https://localhost:8080/"},"id":"VAoSRl3aHEG7","executionInfo":{"status":"ok","timestamp":1637532153451,"user_tz":300,"elapsed":31,"user":{"displayName":"Nico Ampuero","photoUrl":"https://lh3.googleusercontent.com/a/default-user=s64","userId":"12880282598849218460"}},"outputId":"428549c7-1cb2-4aac-98e5-d30691938ca6"},"source":["social_unique = (\n","    social_geo\n","    .groupby(['borocode', 'block', 'bldgclass'])\n","    .agg({'lat': 'mean', 'lon': 'mean', 'lotarea': 'sum'}, min_count=1)\n","    .reset_index()\n",")\n","social_unique['bldgcat'] = 'pluto_' + social_unique['bldgclass'].str[0:1].map({'M':'church', 'P':'cultural', 'Q':'outdoor', 'W':'school', 'V':'vacant'})\n","social_unique['bldgcat'].value_counts()"],"execution_count":15,"outputs":[{"output_type":"execute_result","data":{"text/plain":["pluto_vacant      11829\n","pluto_church       4664\n","pluto_outdoor      1800\n","pluto_school       1126\n","pluto_cultural      542\n","Name: bldgcat, dtype: int64"]},"metadata":{},"execution_count":15}]},{"cell_type":"markdown","metadata":{"id":"E8eWSr6-GskD"},"source":["Load penal law codes in order to classify reported crimes as violent"]},{"cell_type":"code","metadata":{"id":"u7AryToCA3Y5","executionInfo":{"status":"ok","timestamp":1637532154299,"user_tz":300,"elapsed":868,"user":{"displayName":"Nico Ampuero","photoUrl":"https://lh3.googleusercontent.com/a/default-user=s64","userId":"12880282598849218460"}}},"source":["pd_code = pd.read_excel('penal_codes.xlsx')\n","violent_codes = \",\".join(pd_code['Violent'].dropna().astype(str))"],"execution_count":16,"outputs":[]},{"cell_type":"markdown","metadata":{"id":"QH6FryCRG1nm"},"source":["Fetch NYPD crime complaints. This takes a while, so cache in Drive"]},{"cell_type":"code","metadata":{"id":"bYWrExaH1eDj","executionInfo":{"status":"ok","timestamp":1637532156922,"user_tz":300,"elapsed":2644,"user":{"displayName":"Nico Ampuero","photoUrl":"https://lh3.googleusercontent.com/a/default-user=s64","userId":"12880282598849218460"}}},"source":["# query = f\"$where=date_extract_y(cmplnt_fr_dt) in (2016, 2017, 2018) and law_cat_cd = 'FELONY' and ky_cd IN ({violent_codes})\"\n","# crime = get_socrata('qgea-i56i', query, dates=['cmplnt_fr_dt'])\n","# crime['year'] = crime['cmplnt_fr_dt'].dt.year\n","# crime = crime[['year', 'latitude', 'longitude']]\n","\n","# crime.to_csv('violent_crime.csv', index=False)\n","\n","crime = pd.read_csv('violent_crime.csv')"],"execution_count":17,"outputs":[]},{"cell_type":"code","metadata":{"id":"NvEcVp1L7ZdT","executionInfo":{"status":"ok","timestamp":1637538969648,"user_tz":300,"elapsed":1145,"user":{"displayName":"Nico Ampuero","photoUrl":"https://lh3.googleusercontent.com/a/default-user=s64","userId":"12880282598849218460"}}},"source":["crime = crime[['year', 'latitude', 'longitude']]\n","\n","crime.to_csv('violent_crime2.csv', index=False)"],"execution_count":46,"outputs":[]},{"cell_type":"markdown","metadata":{"id":"zJpGsugNHAjh"},"source":["Fetch CDC PLACES health data"]},{"cell_type":"code","metadata":{"colab":{"base_uri":"https://localhost:8080/"},"id":"1OPh1mPjIv01","executionInfo":{"status":"ok","timestamp":1637532159180,"user_tz":300,"elapsed":2269,"user":{"displayName":"Nico Ampuero","photoUrl":"https://lh3.googleusercontent.com/a/default-user=s64","userId":"12880282598849218460"}},"outputId":"10344961-341b-4ffe-b28b-bda9fec36505"},"source":["c = \"','\".join('36' + pd.Series(counties_str))\n","places = get_socrata(resource='yjkw-uj5s', domain='chronicdata.cdc.gov', query=f\"$where=countyfips IN ('{c}')\")\n","places = places[['tractfips', 'phlth_crudeprev', 'mhlth_crudeprev']]\n","\n","cols = ['cdc_physical', 'cdc_mental']\n","places.columns = ['geoid', *cols]\n","places[cols] = places[cols].astype(float)"],"execution_count":18,"outputs":[{"output_type":"stream","name":"stdout","text":["Retreived 2117 records\n"]}]},{"cell_type":"markdown","metadata":{"id":"AMKJIIrTHdBw"},"source":["Fetch voter participation data and aggregate 2017 (mayoral election) active voter counts by census tract. This also takes a while, so cache"]},{"cell_type":"code","metadata":{"id":"hlgabG7QPal8","executionInfo":{"status":"ok","timestamp":1637532167555,"user_tz":300,"elapsed":462,"user":{"displayName":"Nico Ampuero","photoUrl":"https://lh3.googleusercontent.com/a/default-user=s64","userId":"12880282598849218460"}}},"source":["# vote = get_socrata('psx2-aqx3', query='$where=GE17 == 1')\n","# vote.to_csv('vote.csv', index=False)\n","\n","# vote_ct = vote.groupby(['borocode_17', 'ct2010_17'])[['random_id']].count().reset_index()\n","# vote_ct['county'] = vote_ct['borocode_17'].map(boro_counties)\n","# vote_ct['geoid'] = '36' + vote_ct['county'].str.zfill(3) + vote_ct['ct2010_17'].str.zfill(6)\n","# vote_ct.rename(columns={'random_id': 'cfb_votes'}, inplace=True)\n","\n","# vote_ct.to_csv('vote_ct.csv', index=False)\n","\n","vote_ct = pd.read_csv('vote_ct.csv')"],"execution_count":20,"outputs":[]},{"cell_type":"markdown","metadata":{"id":"MvgTCuItzzjY"},"source":["Fetch census response rates"]},{"cell_type":"code","metadata":{"id":"ScXt5iQSzzKK","executionInfo":{"status":"ok","timestamp":1637532187879,"user_tz":300,"elapsed":776,"user":{"displayName":"Nico Ampuero","photoUrl":"https://lh3.googleusercontent.com/a/default-user=s64","userId":"12880282598849218460"}}},"source":["response = pd.read_json('https://api.census.gov/data/2010/dec/responserate?get=NAME,GEO_ID,FSRR2010&for=tract:*&in=state:36')\n","response.columns = response.iloc[0]\n","response = response[1:]\n","response = response[response['county'].isin(counties_str)]\n","response['geoid'] = response['GEO_ID'].apply(lambda x : x.split('US')[1])\n","response['census_response'] = response['FSRR2010'].astype(float)"],"execution_count":22,"outputs":[]},{"cell_type":"markdown","metadata":{"id":"sNulrhz1Hse4"},"source":["Load EPA walkabililty scores"]},{"cell_type":"code","metadata":{"id":"3ihUQUw7to-z","executionInfo":{"status":"ok","timestamp":1637532193515,"user_tz":300,"elapsed":623,"user":{"displayName":"Nico Ampuero","photoUrl":"https://lh3.googleusercontent.com/a/default-user=s64","userId":"12880282598849218460"}}},"source":["walk = pd.read_csv('walkability_nyc.csv', usecols=['GEOID10', 'NatWalkInd'])\n","walk.columns = ['geoid', 'epa_walk']\n","walk['geoid'] = walk['geoid'].astype(str).str[:-1]\n","walk = walk.groupby('geoid').mean().reset_index() # agg from block group level"],"execution_count":23,"outputs":[]},{"cell_type":"markdown","metadata":{"id":"D4pamLPqHw00"},"source":["Load Data2Go (ACS) socioeconomic data"]},{"cell_type":"code","metadata":{"id":"Lbs2r6ae3qiR","executionInfo":{"status":"ok","timestamp":1637532195710,"user_tz":300,"elapsed":760,"user":{"displayName":"Nico Ampuero","photoUrl":"https://lh3.googleusercontent.com/a/default-user=s64","userId":"12880282598849218460"}}},"source":["columns = {'GEO_ID': 'geoid',\n","           'GEO_LABEL': 'geolabel',\n","           'total_population_tract': 'population',\n","           'median_household_income_tract': 'acs_income',\n","           'labor_force_participation_tract': 'acs_participation',\n","           'income_inequality_tract': 'acs_gini',\n","           'high_cost_h_tract': 'acs_housing',\n","           'health_insurance_uninsured_tract': 'acs_uninsured',\n","           'at_least_bachelors_tract': 'acs_bachelors',\n","           'at_least_hs_tract': 'acs_hs',\n","           'white_pop_tract': 'acs_white',\n","           'foreign_tract': 'acs_foreign',\n","           'lonely_tract': 'acs_alone',\n","           'familychild_tract': 'acs_child',\n","           'rental_vac_tract': 'acs_vacancy',\n","           'commute_60_mins_plus_tract': 'acs_commute60'}\n","\n","d2g = pd.read_csv('d2g_5_tract.csv', usecols=list(columns.keys()))\n","d2g.rename(columns=columns, inplace=True)\n","\n","d2g = d2g[list(columns.values())]\n","d2g = d2g.dropna()\n","d2g['geoid'] = d2g['geoid'].astype(str)"],"execution_count":24,"outputs":[]},{"cell_type":"markdown","metadata":{"id":"W932VCSTIBpB"},"source":["Load census tract boundaries and join to D2G"]},{"cell_type":"code","metadata":{"id":"Q0HFlBU_DDzr","executionInfo":{"status":"ok","timestamp":1637532204770,"user_tz":300,"elapsed":2402,"user":{"displayName":"Nico Ampuero","photoUrl":"https://lh3.googleusercontent.com/a/default-user=s64","userId":"12880282598849218460"}}},"source":["tract = gpd.read_file('ny_2018_tract').to_crs('EPSG:2263')\n","tract.columns = tract.columns.str.lower()\n","tract['area'] = tract.geometry.area\n","\n","census = d2g.merge(response[['geoid','census_response']]).merge(tract[['geoid', 'area']], on='geoid')\n","census['density'] = census['population'] / census['area'] # per sqft"],"execution_count":25,"outputs":[]},{"cell_type":"markdown","metadata":{"id":"8VGo3oMnICva"},"source":["Load 2010 census tract population centroids (2020 not yet available)"]},{"cell_type":"code","metadata":{"id":"8N-hWejEzJmm","executionInfo":{"status":"ok","timestamp":1637532207714,"user_tz":300,"elapsed":362,"user":{"displayName":"Nico Ampuero","photoUrl":"https://lh3.googleusercontent.com/a/default-user=s64","userId":"12880282598849218460"}}},"source":["pop_tract = pd.read_csv('https://www2.census.gov/geo/docs/reference/cenpop2010/tract/CenPop2010_Mean_TR36.txt')\n","pop_tract.columns = pop_tract.columns.str.lower()\n","pop_nyc = pop_tract[pop_tract['countyfp'].isin(counties)].copy()"],"execution_count":26,"outputs":[]},{"cell_type":"markdown","metadata":{"id":"vRy_K1fcIMCo"},"source":["Format population, crime, urban agriculture and social infrastructure data (all lat-lon based) as GeoDFs and aggregate to census tract level (based on population centroid)"]},{"cell_type":"code","metadata":{"id":"t6Me0cdxp9Xq","executionInfo":{"status":"ok","timestamp":1637532212049,"user_tz":300,"elapsed":507,"user":{"displayName":"Nico Ampuero","photoUrl":"https://lh3.googleusercontent.com/a/default-user=s64","userId":"12880282598849218460"}}},"source":["pop_geo = gdf_ll(pop_nyc)\n","pop_geo['geoid'] = pop_geo['statefp'].astype(str).str.zfill(2) + pop_geo['countyfp'].astype(str).str.zfill(3) + pop_geo['tractce'].astype(str).str.zfill(6)\n","pop_geo['geometry'] = pop_geo['geometry'].buffer(5280 / 2) # in ft"],"execution_count":27,"outputs":[]},{"cell_type":"code","metadata":{"id":"gm9WBJCL8soM"},"source":["crime_geo = gdf_ll(crime)\n","pop_crime = gpd.sjoin(pop_geo, crime_geo)\n","crime_total = pd.pivot_table(pop_crime, index='geoid', columns='year', values='index_right', aggfunc='count').fillna(0)\n","crime_total['nypd_violent'] = crime_total.mean(axis=1)\n","crime_total = crime_total.reset_index()"],"execution_count":null,"outputs":[]},{"cell_type":"code","metadata":{"id":"XggBYTUwveoj","executionInfo":{"status":"ok","timestamp":1637532250844,"user_tz":300,"elapsed":519,"user":{"displayName":"Nico Ampuero","photoUrl":"https://lh3.googleusercontent.com/a/default-user=s64","userId":"12880282598849218460"}}},"source":["pop_ag = gpd.sjoin(pop_geo, ag_geo)\n","ag_total = pd.pivot_table(pop_ag, index='geoid', columns='typecode', values='lotarea', aggfunc='count').fillna(0) # can try sum(lotarea) too\n","ag_total['nc_agriculture'] = ag_total[['FN', 'GC', 'GN']].sum(axis=1)\n","ag_total = ag_total.reset_index()"],"execution_count":29,"outputs":[]},{"cell_type":"code","metadata":{"id":"HDMo1kLZu4c7","executionInfo":{"status":"ok","timestamp":1637532255496,"user_tz":300,"elapsed":4688,"user":{"displayName":"Nico Ampuero","photoUrl":"https://lh3.googleusercontent.com/a/default-user=s64","userId":"12880282598849218460"}}},"source":["social_geo = gdf_ll(social_unique, 'lon', 'lat')\n","pop_social = gpd.sjoin(pop_geo, social_geo)\n","bldg_total = pd.pivot_table(pop_social, index='geoid', columns='bldgcat', values='lotarea', aggfunc='count').fillna(0)\n","bldg_total = bldg_total.reset_index()"],"execution_count":30,"outputs":[]},{"cell_type":"markdown","metadata":{"id":"p0waL5MPJkVZ"},"source":["Join all the datasets, now at the census tract level, and ouput dataframe for modeling"]},{"cell_type":"code","metadata":{"colab":{"base_uri":"https://localhost:8080/","height":320},"id":"Cjv43sidV9Ir","executionInfo":{"status":"ok","timestamp":1637532275399,"user_tz":300,"elapsed":589,"user":{"displayName":"Nico Ampuero","photoUrl":"https://lh3.googleusercontent.com/a/default-user=s64","userId":"12880282598849218460"}},"outputId":"4e4ff972-a1c9-4405-e4a3-329dc4926b25"},"source":["social = (\n","    places\n","    .merge(census, on='geoid')\n","    .merge(walk, on='geoid')\n","    .merge(vote_ct[['geoid', 'cfb_votes']], on='geoid', how='left')\n","    .merge(crime_total[['geoid', 'nypd_violent']], on='geoid', how='left')\n","    .merge(ag_total[['geoid', 'nc_agriculture']], on='geoid', how='left')\n","    .merge(bldg_total, on='geoid', how='left')\n",").fillna(0)\n","\n","social.to_csv('social.csv', index=False)\n","\n","social.describe()"],"execution_count":33,"outputs":[{"output_type":"execute_result","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>cdc_physical</th>\n","      <th>cdc_mental</th>\n","      <th>population</th>\n","      <th>acs_income</th>\n","      <th>acs_participation</th>\n","      <th>acs_gini</th>\n","      <th>acs_housing</th>\n","      <th>acs_uninsured</th>\n","      <th>acs_bachelors</th>\n","      <th>acs_hs</th>\n","      <th>acs_white</th>\n","      <th>acs_foreign</th>\n","      <th>acs_alone</th>\n","      <th>acs_child</th>\n","      <th>acs_vacancy</th>\n","      <th>acs_commute60</th>\n","      <th>census_response</th>\n","      <th>area</th>\n","      <th>density</th>\n","      <th>epa_walk</th>\n","      <th>cfb_votes</th>\n","      <th>nypd_violent</th>\n","      <th>nc_agriculture</th>\n","      <th>pluto_church</th>\n","      <th>pluto_cultural</th>\n","      <th>pluto_outdoor</th>\n","      <th>pluto_school</th>\n","      <th>pluto_vacant</th>\n","    </tr>\n","  </thead>\n","  <tbody>\n","    <tr>\n","      <th>count</th>\n","      <td>1977.000000</td>\n","      <td>1977.000000</td>\n","      <td>1977.000000</td>\n","      <td>1977.000000</td>\n","      <td>1977.000000</td>\n","      <td>1977.000000</td>\n","      <td>1977.000000</td>\n","      <td>1977.000000</td>\n","      <td>1977.000000</td>\n","      <td>1977.000000</td>\n","      <td>1977.000000</td>\n","      <td>1977.000000</td>\n","      <td>1977.000000</td>\n","      <td>1977.000000</td>\n","      <td>1977.000000</td>\n","      <td>1977.000000</td>\n","      <td>1977.000000</td>\n","      <td>1.977000e+03</td>\n","      <td>1977.000000</td>\n","      <td>1977.000000</td>\n","      <td>1977.000000</td>\n","      <td>1977.000000</td>\n","      <td>1977.000000</td>\n","      <td>1977.000000</td>\n","      <td>1977.000000</td>\n","      <td>1977.000000</td>\n","      <td>1977.000000</td>\n","      <td>1977.000000</td>\n","    </tr>\n","    <tr>\n","      <th>mean</th>\n","      <td>12.092109</td>\n","      <td>12.923672</td>\n","      <td>3850.776935</td>\n","      <td>67233.815377</td>\n","      <td>63.152045</td>\n","      <td>0.462404</td>\n","      <td>54.904654</td>\n","      <td>8.308773</td>\n","      <td>35.164131</td>\n","      <td>81.566303</td>\n","      <td>32.356795</td>\n","      <td>37.618811</td>\n","      <td>28.542001</td>\n","      <td>31.640799</td>\n","      <td>8.702193</td>\n","      <td>28.789690</td>\n","      <td>62.037785</td>\n","      <td>3.590928e+06</td>\n","      <td>0.001810</td>\n","      <td>13.869044</td>\n","      <td>521.835104</td>\n","      <td>370.082954</td>\n","      <td>4.154780</td>\n","      <td>20.789074</td>\n","      <td>2.256955</td>\n","      <td>5.547294</td>\n","      <td>4.779970</td>\n","      <td>36.029337</td>\n","    </tr>\n","    <tr>\n","      <th>std</th>\n","      <td>3.436537</td>\n","      <td>2.855676</td>\n","      <td>1921.031115</td>\n","      <td>33169.628607</td>\n","      <td>8.918321</td>\n","      <td>0.065392</td>\n","      <td>13.325002</td>\n","      <td>5.154003</td>\n","      <td>20.461771</td>\n","      <td>11.265062</td>\n","      <td>29.320701</td>\n","      <td>14.832653</td>\n","      <td>12.159253</td>\n","      <td>11.260798</td>\n","      <td>6.131210</td>\n","      <td>12.553966</td>\n","      <td>8.659691</td>\n","      <td>6.073919e+06</td>\n","      <td>0.001246</td>\n","      <td>1.911985</td>\n","      <td>364.839669</td>\n","      <td>349.885244</td>\n","      <td>7.365344</td>\n","      <td>14.082231</td>\n","      <td>1.953336</td>\n","      <td>5.191175</td>\n","      <td>3.357969</td>\n","      <td>18.987055</td>\n","    </tr>\n","    <tr>\n","      <th>min</th>\n","      <td>4.000000</td>\n","      <td>6.200000</td>\n","      <td>60.000000</td>\n","      <td>9939.000000</td>\n","      <td>8.550186</td>\n","      <td>0.253500</td>\n","      <td>0.000000</td>\n","      <td>0.000000</td>\n","      <td>2.957907</td>\n","      <td>37.684053</td>\n","      <td>0.000000</td>\n","      <td>5.715141</td>\n","      <td>0.000000</td>\n","      <td>0.000000</td>\n","      <td>0.000000</td>\n","      <td>0.000000</td>\n","      <td>21.400000</td>\n","      <td>4.161252e+05</td>\n","      <td>0.000002</td>\n","      <td>6.666667</td>\n","      <td>1.000000</td>\n","      <td>0.666667</td>\n","      <td>0.000000</td>\n","      <td>0.000000</td>\n","      <td>0.000000</td>\n","      <td>0.000000</td>\n","      <td>0.000000</td>\n","      <td>0.000000</td>\n","    </tr>\n","    <tr>\n","      <th>25%</th>\n","      <td>10.000000</td>\n","      <td>11.000000</td>\n","      <td>2399.000000</td>\n","      <td>44514.000000</td>\n","      <td>57.975460</td>\n","      <td>0.416900</td>\n","      <td>46.322581</td>\n","      <td>4.753381</td>\n","      <td>20.212102</td>\n","      <td>74.031621</td>\n","      <td>4.365672</td>\n","      <td>25.912409</td>\n","      <td>19.457014</td>\n","      <td>23.646209</td>\n","      <td>4.905660</td>\n","      <td>19.978225</td>\n","      <td>55.900000</td>\n","      <td>1.676125e+06</td>\n","      <td>0.000909</td>\n","      <td>12.583333</td>\n","      <td>276.000000</td>\n","      <td>133.666667</td>\n","      <td>0.000000</td>\n","      <td>10.000000</td>\n","      <td>1.000000</td>\n","      <td>2.000000</td>\n","      <td>2.000000</td>\n","      <td>23.000000</td>\n","    </tr>\n","    <tr>\n","      <th>50%</th>\n","      <td>11.700000</td>\n","      <td>12.500000</td>\n","      <td>3537.000000</td>\n","      <td>63125.000000</td>\n","      <td>63.313180</td>\n","      <td>0.457100</td>\n","      <td>55.041628</td>\n","      <td>7.470651</td>\n","      <td>30.003887</td>\n","      <td>83.259494</td>\n","      <td>23.529412</td>\n","      <td>37.314488</td>\n","      <td>27.246377</td>\n","      <td>32.363636</td>\n","      <td>7.566204</td>\n","      <td>30.282486</td>\n","      <td>62.300000</td>\n","      <td>1.961303e+06</td>\n","      <td>0.001555</td>\n","      <td>14.000000</td>\n","      <td>416.000000</td>\n","      <td>239.333333</td>\n","      <td>1.000000</td>\n","      <td>18.000000</td>\n","      <td>2.000000</td>\n","      <td>4.000000</td>\n","      <td>4.000000</td>\n","      <td>33.000000</td>\n","    </tr>\n","    <tr>\n","      <th>75%</th>\n","      <td>13.800000</td>\n","      <td>14.600000</td>\n","      <td>4829.000000</td>\n","      <td>83092.000000</td>\n","      <td>68.164062</td>\n","      <td>0.499300</td>\n","      <td>63.692946</td>\n","      <td>10.945008</td>\n","      <td>45.159485</td>\n","      <td>90.224033</td>\n","      <td>58.414840</td>\n","      <td>47.813411</td>\n","      <td>35.540070</td>\n","      <td>39.609994</td>\n","      <td>11.027397</td>\n","      <td>38.220481</td>\n","      <td>68.700000</td>\n","      <td>2.997727e+06</td>\n","      <td>0.002393</td>\n","      <td>15.166667</td>\n","      <td>659.000000</td>\n","      <td>523.333333</td>\n","      <td>5.000000</td>\n","      <td>29.000000</td>\n","      <td>3.000000</td>\n","      <td>8.000000</td>\n","      <td>6.000000</td>\n","      <td>46.000000</td>\n","    </tr>\n","    <tr>\n","      <th>max</th>\n","      <td>29.900000</td>\n","      <td>24.000000</td>\n","      <td>15456.000000</td>\n","      <td>250001.000000</td>\n","      <td>97.163121</td>\n","      <td>0.750600</td>\n","      <td>100.000000</td>\n","      <td>45.087001</td>\n","      <td>96.140082</td>\n","      <td>100.000000</td>\n","      <td>99.590723</td>\n","      <td>82.991803</td>\n","      <td>95.353535</td>\n","      <td>84.244792</td>\n","      <td>83.934426</td>\n","      <td>70.534459</td>\n","      <td>100.000000</td>\n","      <td>1.170070e+08</td>\n","      <td>0.009349</td>\n","      <td>19.666667</td>\n","      <td>2444.000000</td>\n","      <td>2677.333333</td>\n","      <td>51.000000</td>\n","      <td>99.000000</td>\n","      <td>13.000000</td>\n","      <td>40.000000</td>\n","      <td>19.000000</td>\n","      <td>124.000000</td>\n","    </tr>\n","  </tbody>\n","</table>\n","</div>"],"text/plain":["       cdc_physical   cdc_mental  ...  pluto_school  pluto_vacant\n","count   1977.000000  1977.000000  ...   1977.000000   1977.000000\n","mean      12.092109    12.923672  ...      4.779970     36.029337\n","std        3.436537     2.855676  ...      3.357969     18.987055\n","min        4.000000     6.200000  ...      0.000000      0.000000\n","25%       10.000000    11.000000  ...      2.000000     23.000000\n","50%       11.700000    12.500000  ...      4.000000     33.000000\n","75%       13.800000    14.600000  ...      6.000000     46.000000\n","max       29.900000    24.000000  ...     19.000000    124.000000\n","\n","[8 rows x 28 columns]"]},"metadata":{},"execution_count":33}]}]}