import pandas as pd
import numpy as np
import geopandas as gpd
import carto2gpd as c2g
import esri2gpd as e2g
import matplotlib.pyplot as plt
import seaborn as sns
import folium
import cenpy
#define variables for carto2gpd query
url = 'https://phl.carto.com/api/v2/sql'
shootings = 'shootings'
fiveFY = "date_ >= '2018-07-01' AND date_ < '2023-07-01'"
oneFY = "date_ >= '2022-07-01' AND date_ < '2023-07-01'"
#define variables for esri2gpd query
vars = ['NAME']
acs = cenpy.remote.APIConnection('ACSDT5Y2020')
pa = "42"
phl = "101"
where = f"STATE = {pa} AND COUNTY = {phl}"
# query 1 (FY23)
shootings1 = c2g.get(url, shootings, where=oneFY)
print("Shootings in Fiscal Year 2023:", len(shootings1))
shootings1.head()
Shootings in Fiscal Year 2023: 2024
| geometry | cartodb_id | objectid | year | dc_key | code | date_ | time | race | sex | ... | offender_injured | offender_deceased | location | latino | point_x | point_y | dist | inside | outside | fatal | |
|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|
| 0 | POINT (-75.13956 39.99530) | 498 | 10850362 | 2022 | 20222073062.0 | None | 2022-12-21T00:00:00Z | 00:13:00 | B | F | ... | N | N | 2900 BLOCK N 5TH ST | 0 | -75.139560 | 39.995303 | 25 | 0 | 1 | 0 |
| 1 | GEOMETRYCOLLECTION EMPTY | 1280 | 10864689 | 2023 | 202322021100.0 | 411 | 2023-04-15T00:00:00Z | 12:32:00 | B | M | ... | N | N | 1200 BLOCK CAMBRIDGE COURT | 0 | NaN | NaN | 22 | 0 | 1 | 0 |
| 2 | POINT (-75.16006 39.99164) | 2569 | 10862928 | 2022 | 202222051089.0 | 411 | 2022-09-10T00:00:00Z | 17:58:00 | B | F | ... | N | N | 2500 BLOCK N 17TH ST | 0 | -75.160058 | 39.991636 | 22 | 0 | 1 | 0 |
| 3 | POINT (-75.18148 39.98023) | 2570 | 10862929 | 2022 | 202222051777.0 | 411 | 2022-09-14T00:00:00Z | 14:35:00 | B | M | ... | N | N | 2800 BLOCK W OXFORD ST | 0 | -75.181482 | 39.980232 | 22 | 0 | 1 | 0 |
| 4 | POINT (-75.18148 39.98023) | 2571 | 10862930 | 2022 | 202222051777.0 | 411 | 2022-09-14T00:00:00Z | 14:35:00 | B | M | ... | N | N | 2800 BLOCK W OXFORD ST | 0 | -75.181482 | 39.980232 | 22 | 0 | 1 | 0 |
5 rows × 23 columns
# query 2 (FY18 to FY23)
shootings2 = c2g.get(url, shootings, where=fiveFY)
print("Shootings from Fiscal Year 2018 to Fiscal Year 2023:", len(shootings2))
shootings2.head()
Shootings from Fiscal Year 2018 to Fiscal Year 2023: 10049
| geometry | cartodb_id | objectid | year | dc_key | code | date_ | time | race | sex | ... | offender_injured | offender_deceased | location | latino | point_x | point_y | dist | inside | outside | fatal | |
|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|
| 0 | POINT (-75.13956 39.99530) | 498 | 10850362 | 2022 | 20222073062.0 | None | 2022-12-21T00:00:00Z | 00:13:00 | B | F | ... | N | N | 2900 BLOCK N 5TH ST | 0.0 | -75.139560 | 39.995303 | 25 | 0.0 | 1.0 | 0.0 |
| 1 | GEOMETRYCOLLECTION EMPTY | 1280 | 10864689 | 2023 | 202322021100.0 | 411 | 2023-04-15T00:00:00Z | 12:32:00 | B | M | ... | N | N | 1200 BLOCK CAMBRIDGE COURT | 0.0 | NaN | NaN | 22 | 0.0 | 1.0 | 0.0 |
| 2 | POINT (-75.24478 39.95216) | 2542 | 10862496 | 2022 | 202218002500.0 | 401 | 2022-01-16T00:00:00Z | 23:41:00 | B | M | ... | N | N | 600 BLOCK S 61ST ST | 0.0 | -75.244782 | 39.952160 | 18 | 0.0 | 1.0 | 0.0 |
| 3 | POINT (-75.24659 39.96115) | 2543 | 10862497 | 2022 | 202218002878.0 | 416 | 2022-01-19T00:00:00Z | 09:38:00 | B | M | ... | N | N | 6200 BLOCK CHESTNUT ST | 0.0 | -75.246588 | 39.961147 | 18 | 0.0 | 1.0 | 0.0 |
| 4 | POINT (-75.23975 39.94606) | 2544 | 10862498 | 2022 | 202218003061.0 | 401 | 2022-01-20T00:00:00Z | 06:31:00 | B | M | ... | N | N | 5700 BLOCK BALTIMORE AVE | 0.0 | -75.239754 | 39.946059 | 18 | 0.0 | 1.0 | 0.0 |
5 rows × 23 columns
# define function to use carto2gpd to query shootings data and keep geometry, year, and date_ columns
def shootings_gdf(url, shootings, where):
shootings_gdf = c2g.get(url, shootings, where=where)
shootings_gdf = shootings_gdf[['year', 'date_', 'geometry']]
return shootings_gdf
# query for FY23
shootings1 = shootings_gdf(url, shootings, oneFY)
print("Shootings in Fiscal Year 2023:", len(shootings1))
# query for FY18 to FY23
shootings2 = shootings_gdf(url, shootings, fiveFY)
print("Shootings from Fiscal Year 2018 to Fiscal Year 2023:", len(shootings2))
Shootings in Fiscal Year 2023: 2024 Shootings from Fiscal Year 2018 to Fiscal Year 2023: 10049
acs.set_mapservice('tigerWMS_Census2020')
where = "STATE = '42' AND COUNTY = '101'"
tr = acs.mapservice.layers[6]
tr._baseurl
tracts = e2g.get(tr._baseurl, where=where)
#make unique id column based on index
tracts['id'] = tracts.index + 1
tracts = tracts[['id', 'geometry']]
tracts.head()
| id | geometry | |
|---|---|---|
| 0 | 1 | POLYGON ((-75.02251 40.03083, -75.02194 40.030... |
| 1 | 2 | POLYGON ((-75.03343 40.03947, -75.03337 40.039... |
| 2 | 3 | POLYGON ((-75.04067 40.03683, -75.04086 40.036... |
| 3 | 4 | POLYGON ((-75.05146 40.05075, -75.05150 40.050... |
| 4 | 5 | POLYGON ((-75.06034 40.05132, -75.06065 40.051... |
'''
create a new geodataframe called 'tracts23' that accomplishes the following:
- gets a count of how many shootings happened in each tract in FY23
'''
c:\Users\jtrum\miniconda3\envs\musa-550-fall-2022\lib\site-packages\IPython\core\interactiveshell.py:3338: FutureWarning: The `op` parameter is deprecated and will be removed in a future release. Please use the `predicate` parameter instead. if await self.run_code(code, result, async_=asy):
shootings1.head()
| id | year | date_ | geometry_x | index_right | geometry_y | |
|---|---|---|---|---|---|---|
| 0 | 2.0 | 3 | 3 | 3 | 3 | POLYGON ((-75.03343 40.03947, -75.03337 40.039... |
| 1 | 3.0 | 10 | 10 | 10 | 10 | POLYGON ((-75.04067 40.03683, -75.04086 40.036... |
| 2 | 4.0 | 1 | 1 | 1 | 1 | POLYGON ((-75.05146 40.05075, -75.05150 40.050... |
| 3 | 5.0 | 1 | 1 | 1 | 1 | POLYGON ((-75.06034 40.05132, -75.06065 40.051... |
| 4 | 6.0 | 2 | 2 | 2 | 2 | POLYGON ((-75.08557 40.06269, -75.08573 40.062... |