<jupyter_start><jupyter_text>First, we need to connect to NewsroomDB and download all shootings and homicides.<jupyter_code>import os
import requests

def get_table_data(table_name):
    url = '%stable/json/%s' % (os.environ['NEWSROOMDB_URL'], table_name)
    try:
        r = requests.get(url)
        return r.json()
    except:
        print 'doh'
        return get_table_data(table_name)

homicides = get_table_data('homicides')
shootings = get_table_data('shootings')

print 'Found %d homicides and %d shootings' % (len(homicides), len(shootings))<jupyter_output>doh
doh
doh
Found 1519 homicides and 11463 shootings<jupyter_text>Right now, we're interested in all shootings and homicides for the current month. So filter the lists based on whatever that month is.<jupyter_code>from datetime import date, datetime
today = date.today()

homicides_this_month = {}
for h in homicides:
    try:
        dt = datetime.strptime(h['Occ Date'], '%Y-%m-%d')
    except ValueError:
        continue
    if dt.month == today.month:
        if dt.year not in homicides_this_month:
            homicides_this_month[dt.year] = []
        homicides_this_month[dt.year].append(h)

shootings_this_month = {}
for s in shootings:
    try:
        dt = datetime.strptime(s['Date'], '%Y-%m-%d')
    except ValueError:
        continue
    if dt.month == today.month:
        if dt.year not in shootings_this_month:
            shootings_this_month[dt.year] = []
        shootings_this_month[dt.year].append(s)

for year in sorted(shootings_this_month.keys(), reverse=True):
    try:
        s = len(shootings_this_month[year])
    except:
        s = 0
    try:
        h = len(homicides_this_month[year])
    except:
        h = 0
    print '%d:\t%d shootings\t\t%d homicides' % (year, s, h)<jupyter_output>2016:	257 shootings		47 homicides
2015:	145 shootings		32 homicides
2014:	101 shootings		24 homicides
2013:	190 shootings		44 homicides
2012:	174 shootings		0 homicides
1984:	1 shootings		0 homicides<jupyter_text>Now let's find the days without a shooting.<jupyter_code>from datetime import date, timedelta

test_date = date.today()
one_day = timedelta(days=1)

shooting_days = {}
for shooting in shootings:
    if shooting['Date'] not in shooting_days:
        shooting_days[shooting['Date']] = 0
    shooting_days[shooting['Date']] += 1

while test_date.year > 2013:
    if test_date.strftime('%Y-%m-%d') not in shooting_days:
        print 'No shootings on %s' % test_date
    test_date -= one_day<jupyter_output>No shootings on 2015-02-28
No shootings on 2015-02-24
No shootings on 2015-02-18
No shootings on 2015-02-05
No shootings on 2015-02-02
No shootings on 2015-01-15
No shootings on 2015-01-12
No shootings on 2015-01-07
No shootings on 2014-11-14
No shootings on 2014-05-23
No shootings on 2014-03-26
No shootings on 2014-03-13
No shootings on 2014-02-26
No shootings on 2014-02-17
No shootings on 2014-02-16
No shootings on 2014-02-11
No shootings on 2014-01-23
No shootings on 2014-01-21
No shootings on 2014-01-10
No shootings on 2014-01-06
No shootings on 2014-01-02<jupyter_text>And days without a homicide.<jupyter_code>from datetime import date, timedelta

test_date = date.today()
one_day = timedelta(days=1)

homicide_days = {}
for homicide in homicides:
    if homicide['Occ Date'] not in homicide_days:
        homicide_days[homicide['Occ Date']] = 0
    homicide_days[homicide['Occ Date']] += 1

while test_date.year > 2013:
    if test_date.strftime('%Y-%m-%d') not in homicide_days:
        print 'No homicides on %s' % test_date
    test_date -= one_day<jupyter_output>No homicides on 2016-03-25
No homicides on 2016-03-24
No homicides on 2016-03-23
No homicides on 2016-03-22
No homicides on 2016-03-21
No homicides on 2016-03-20
No homicides on 2016-03-19
No homicides on 2016-03-17
No homicides on 2016-03-16
No homicides on 2016-03-06
No homicides on 2016-03-05
No homicides on 2016-03-03
No homicides on 2016-02-29
No homicides on 2016-02-28
No homicides on 2016-02-25
No homicides on 2016-02-23
No homicides on 2016-02-15
No homicides on 2016-02-12
No homicides on 2016-02-10
No homicides on 2016-02-09
No homicides on 2016-02-06
No homicides on 2016-01-22
No homicides on 2016-01-19
No homicides on 2016-01-15
No homicides on 2016-01-12
No homicides on 2016-01-10
No homicides on 2016-01-03
No homicides on 2015-12-31
No homicides on 2015-12-30
No homicides on 2015-12-28
No homicides on 2015-12-21
No homicides on 2015-12-19
No homicides on 2015-12-18
No homicides on 2015-12-15
No homicides on 2015-12-14
No homicides on 2015-12-09
No homicides on 2015-12-07
N[...]<jupyter_text>Let's get the latitudes and longitudes of every murder in 2015 (<NAME> asked).<jupyter_code>coordinates = []
for homicide in homicides:
    if not homicide['Occ Date'].startswith('2015-'):
        continue
    # Since the format of this field is (x, y) (or y, x? I always confuse the two) we need to extract just x and y
    try:
        coordinates.append(
            (homicide['Geocode Override'][1:-1].split(',')[0], homicide['Geocode Override'][1:-1].split(',')[1]))
    except:
        # Not valid/expected lat/long format
        continue
print len(coordinates)
for coordinate in coordinates:
    print '%s,%s' % (coordinate[0].strip(), coordinate[1].strip())<jupyter_output>41.87549740076065,-87.71006070077419
41.99511721730232,-87.68506787717342
41.75300158560276,-87.60407656431198
41.715926080942154,-87.6460363715887
41.784969329833984,-87.6525650024414
41.75092315673828,-87.66917419433594
41.87679022550583,-87.74513997137547
41.877763867378235,-87.73800395429134
41.88046216964722,-87.74548664689064
41.90096664428711,-87.72660064697266
41.765151992440224,-87.64943540096283
41.951365023851395,-87.76714414358139
41.790470123291016,-87.6502685546875
41.858261,-87.653007
41.729496002197266,-87.566162109375
41.87741115689278,-87.75500379502773
41.872432708740234,-87.71296691894531
41.681470423936844,-87.62647099792957
41.74011993408203,-87.66717529296875
41.8740838766098,-87.76458531618118
41.883392333984375,-87.7058334350586
41.79697036743164,-87.65287780761719
41.76420249044895,-87.56497532129288
41.76323318481445,-87.64591979980469
41.76974105834961,-87.6594467163086
41.8737678527832,-87.7481918334961
41.75372314453125,-87.61934661865234
41.909419298172,-[...]
{'fraction_non_alphanumeric': 0.10533841754051478, 'fraction_numerical': 0.22513504925325706, 'mean_word_length': 4.003974562798092, 'pattern_counts': {'":': 0, '<': 17, '<?xml version=': 0, '>': 19, 'https://': 0, 'lorem ipsum': 0, 'www.': 0, 'xml': 0}, 'pii_count': 0, 'substrings_counts': 0, 'word_list_counts': {'cursed_substrings.json': 1, 'profanity_word_list.json': 0, 'sexual_word_list.json': 0, 'zh_pornsignals.json': 0}}
{'chain_length': '5', 'dir': 'jupyter-structured-clean-dedup', 'id': '325738', 'license': 'MIT', 'n_tokens_mistral': 3301, 'n_tokens_neox': 2296, 'n_words': 595, 'path': 'Monthly homicides and shootings report.ipynb', 'repo_name': 'newsapps/public-notebooks', 'size': '6302'}