Подход, основанный на данных.
Тайлер Долл, Джефф Грин, Гази Моршед
Изначально эта статья была отправлена на мой курс по базам данных в колледже. Нам это показалось интересным, и мы потратили на это много времени, поэтому я хотел поделиться этим здесь. Он был отредактирован для этого формата.
Введение
Город Чикаго часто называют «Городом ветров» или «Вторым городом». Он часто известен своей едой, в том числе пиццей с глубоким блюдом и хот-догом Чикаго. К сожалению, Чикаго также известен высоким уровнем преступности, и часто совершаемые преступления носят насильственный характер. Из-за доступности данных о различных преступлениях в городе Чикаго мы решили изучить преступление в Чикаго, чтобы определить, какую информацию мы могли бы извлечь из анализа набора данных. Нам было любопытно узнать, какие преступления в городе наиболее часты, меняются ли эти особо совершенные преступления из года в год и какова частота арестов по разным преступлениям. Хотя этот набор данных может не содержать самой обнадеживающей информации, мы подумали, что было бы интересно проанализировать и попытаться определить, действительно ли Чикаго настолько жесток, как его изображают СМИ.
Данные
Выбранные нами данные представляли собой набор данных, состоящий из информации о преступности в городе Чикаго за последние 5 лет. Мы подумали, что это будет интригующий набор данных, потому что мы сможем обнаружить убедительные тенденции в данных, помимо наблюдения того, что именно происходит с преступностью в городе Чикаго. Данные включали, но не ограничивались датой преступления, блоком, на котором было совершено преступление, территорией сообщества, Единым кодексом отчетности Иллинойса (IUCR), Единым кодексом отчетности ФБР и ударами офицера, сообщившего о преступлении. преступление. Набор данных был найден на Kaggle, и этот набор данных считается лицензированным в соответствии с лицензией Creative Commons License CC0 1.0, являющейся общественным достоянием, что означает, что мы можем использовать данные без какого-либо дополнительного одобрения. Всего у нас четыре таблицы, но одна из наших таблиц была промежуточной таблицей, используемой для хранения ненормализованных данных. После просмотра данных и начала их нормализации мы получили 3 основные таблицы: данные о преступности в Чикаго, коды IUCR в Чикаго, места общего пользования (районы) Чикаго и автоматически сгенерированную таблицу последовательности для chicago_crime_data. Информация и атрибуты в таблице данных о преступности в Чикаго по большей части не требуют пояснений. Однако атрибуты таблицы IUCR могут потребовать небольшого пояснения. Идентификатор в этой таблице соответствует коду IUCR, используемому в таблице данных о преступности в Чикаго. Этот идентификатор прикрепляется к первичному и вторичному описанию преступления. Например, существуют разные идентификаторы, основанные на комбинации первичного и вторичного описания. Приведенный ниже пример касается убийств. Хотя все три этих правонарушения относятся к одному и тому же основному типу, у них разный вторичный тип и идентификатор.
id | primary_desc | secondary_desc — — — + — — — — — — — -+ — — — — — — — — — — — — — 0110 | HOMICIDE | FIRST DEGREE MURDER 0141 | HOMICIDE | INVOLUNTARY MANSLAUGHTER 0142 | HOMICIDE | RECKLESS HOMICIDE
Для простоты объяснения атрибутов и типов данных, используемых для каждой из наших таблиц, мы включили две схемы для наших таблиц ниже, которые также содержат первичный и внешний ключи:
Table “chicago_crime_data” Column | Type — — — — — — — — — — — + — — — — — — — — — — — — — — — id | integer date | timestamp without time zone block | text iucr | text location_description | text arrest | boolean domestic | boolean community_area | integer fbi_code | text latitude | double precision longitude | double precision Indexes: “chicago_crime_data_pkey” PRIMARY KEY, btree (id) Foreign-key constraints: “chicago_crime_data_community_area_fkey” FOREIGN KEY (community_area) REFERENCES chicago_common_area(id) “chicago_crime_data_iucr_fkey” FOREIGN KEY (iucr) REFERENCES chicago_iucr_code(id) Table “chicago_iucr_code” Column | Type — — — — — — — — + — — — id | text primary_desc | text secondary_desc | text Indexes: “chicago_iucr_code_pkey” PRIMARY KEY, btree (id) Referenced by: TABLE “chicago_crime_data” CONSTRAINT “chicago_crime_data_iucr_fkey” FOREIGN KEY (iucr) REFERENCES chicago_iucr_code(id)
Результаты
Большинство запросов, которые мы выполняли в базе данных, касались количества конкретных преступлений и мест их совершения. Хотя мы хотели выполнить множество запросов к данным, в целом мы хотели сосредоточиться на количестве преступлений и использовать эту информацию, чтобы определить, какие преступления происходят чаще всего. Мы также посмотрели, какие совершенные преступления подвергаются наибольшему количеству арестов, а также их местонахождение по всему Чикаго.
Здесь мы хотели узнать общее количество преступлений, независимо от их тяжести и подробностей. Как видно из графика, воровство было самым совершенным преступлением в Чикаго за последние пару лет, за ним последовали побои.
Из рисунков 2 и 3 видно, что воровство было самым совершенным преступлением в Чикаго за последние два года. В дополнение к этим визуализациям мы также рассмотрели графики за предыдущие годы, начиная с 2014 года. Мы можем использовать эти графики, чтобы сделать вывод, что на самом деле кражи были наиболее распространенным преступлением и не видели резких скачков за последние пару лет.
Мы также хотели посмотреть на распределение этих преступлений в окрестностях Чикаго. Эти данные были бы полезны для новичка в Чикаго, который пытается найти район с меньшим уровнем преступности, чтобы жить и в то же время держаться подальше от района, наиболее подверженного преступности. Также будет полезно охарактеризовать типы преступлений, от которых страдает каждый квартал. В приведенных ниже запросах мы нашли наименее и наиболее подверженные преступлениям области, а также изучили распределение преступности.
Кроме того, мы также можем запросить и показать распределение преступности в конкретном районе по запросу пользователя.
По этим данным мы можем точно подсчитать, как часто людей арестовывают за конкретные преступления. Например, мы можем видеть по различным типам обвинений в нападении более 80% людей, которым предъявлено обвинение в хранении при отягчающих обстоятельствах: арестованы руки без травм / мин. Это интересно по сравнению с чем-то вроде нападения с применением огнестрельного оружия при отягчающих обстоятельствах, когда уровень арестов составляет всего около 20%.
Мы также можем изучить конкретные места совершения преступлений. На рисунке 7 показаны все нападения в известных местах с 1 января 2017 года. Однако на графике не проводится различий между разными типами и серьезностью нападений. Тем не менее, можно с уверенностью сказать, что у Чикаго есть проблема со штурмом.
Используя данные о местоположении, мы можем определить районы с высокой концентрацией убийств по всему Чикаго. Здесь убийство включает в себя убийство первой и второй степени и непредумышленное убийство.
Достаточно интересная диаграмма - это преступления, связанные с проституцией по всему Чикаго. Здесь мы видим, что связанные преступления, по-видимому, представлены в виде четких линий, которые, скорее всего, представляют собой разные улицы по всему городу.
Технические проблемы
После получения данных единственной серьезной проблемой, с которой мы столкнулись, была загрузка набора данных из Kaggle в базу данных. Набор данных хранился как набор данных BigQuery в Kaggle, что потребовало от нас использования ядра Kaggle для загрузки ограниченного набора данных из базы данных. Мы решили загрузить данные за последние 5 лет, так как это дало нам большой набор данных для работы. После того, как мы успешно получили данные, мы столкнулись с проблемами при вставке данных в промежуточную таблицу. Мы считаем, что большинство проблем здесь связано с чистым размером набора данных, который содержит более 1,3 миллиона строк. При попытке принять такой большой объем данных мы столкнулись с ошибками памяти на компьютерах с Windows, которые мы использовали, и не определили способ исправить это. Однако, когда данные были загружены на Mac, мы не столкнулись с этими проблемами. Чтобы решить проблемы с ошибками памяти, мы использовали Mac для загрузки набора данных в базу данных, а затем предоставили доступ к таблицам другим членам команды с помощью методов, которые мы нашли на Piazza. Мы также столкнулись с проблемами с запросами к полям меток времени, когда пытались сосредоточиться только на месяце и игнорировать остальные. К счастью, мы смогли использовать для этого команду EXTRACT.
Заключение
Проанализировав графики и другие визуализации, мы можем сделать вывод, что Чикаго - довольно опасный город и на самом деле столь же жестокий, как его изображают СМИ. Количество нападений само по себе поражает, не говоря уже об уровне убийств. Использование этих данных для проекта сделало задание интересным, хотя это был не самый удачный набор данных. Мы считаем, что использование этого набора данных будет напоминать данные, с которыми нам, возможно, придется иметь дело при возможной занятости в отрасли, поэтому это задание дало нам опыт, который будет полезен для будущей занятости.
