シカゴの犯罪データ

BigQuery を使用して一般公開データセットをクエリする方法

BigQuery は、フルマネージドのデータ ウェアハウスおよび分析プラットフォームです。SQL クエリを使用して分析できる一般公開データセットが用意されています。BigQuery の一般公開データセットにアクセスするには、ウェブ UIコマンドライン ツールを使用します。また、Java.NETPython など、さまざまなクライアント ライブラリを使用して BigQuery REST API を呼び出すという方法でもアクセスできます。

現在、BigQuery の一般公開データセットは US マルチリージョン ロケーションに格納されています。一般公開データセットをクエリで取得する場合は、コマンドラインで --location=US フラグを指定するか、BigQuery ウェブ UI で処理ロケーションとして US を選択するか、または API でジョブリソースjobReference セクションにある location プロパティを指定します。一般公開データセットは米国内に格納されているため、一般公開データのクエリ結果を別のリージョンにあるテーブルに書き込むことはできず、一般公開データセットのテーブルを別のリージョンにあるテーブルと結合することもできません。

BigQuery の一般公開データセットの使用を開始するには、プロジェクトを作成または選択します。毎月、最初の 1 テラバイトのデータの処理については課金されないため、課金を有効にせずに一般公開データセットのクエリを開始できます。無料枠を超える可能性がある場合は、課金を有効にする必要があります。

  1. Google アカウントにログインします。

    Google アカウントをまだお持ちでない場合は、新しいアカウントを登録します。

  2. GCP プロジェクトを選択または作成します。

    [リソースの管理] ページに移動

  3. プロジェクトに対して課金が有効になっていることを確認します。

    課金を有効にする方法について

  4. 新しいプロジェクトでは、BigQuery が自動的に有効になります。既存のプロジェクトで BigQuery を有効にする手順は以下のとおりです。 BigQuery API を有効にする。

    Enable the API

データセットの概要

このデータセットは、2001 年から現在までの間(最近 7 日間を除く)シカゴ市で発生が報告された犯罪事件(各被害者のデータが存在する殺人事件を除く)を反映したものです。このデータは、シカゴ警察の CLEAR(市民法施行分析報告)システムから抽出されたものです。犯罪被害者のプライバシーを保護するため、住所はブロックレベルでのみ表示し、場所が特定されないようにしています。

このデータには、警察に提供された未確認の報告も含まれています。予備犯罪分類は、後で追加の調査に基づいて変更される可能性があり、常に機械的または人的ミスの可能性があります。したがって、シカゴ警察は、情報の正確性、完全性、適時性、正しい順序付けを(明示的にも暗示的にも)保証するものではありません。この情報は、時間経過に基づく比較の目的で使用すべきではありません。

BigQuery コンソールで、このデータを探索してみましょう。

シカゴの犯罪データに移動

サンプルクエリ

BigQuery でこのデータに対して実行できる SQL クエリの例をいくつか示します。

このサンプルでは、BigQuery の標準 SQL サポートを使用します。標準 SQL を使用することを BigQuery に認識させるためには、#standardSQL タグを使用します。#standardSQL 接頭辞の詳細については、クエリ接頭辞の設定をご覧ください。

2015 年から 2016 年にかけて、どのカテゴリの犯罪が前年比最大増加を示したか?

このクエリは、2015 年と 2016 年の逮捕者数とその増加率を要約したものです。このクエリは、2015 年に 100 回以上の逮捕の犯罪分類にデータセットを制限し、上位 10 の増加率を示しています。

逮捕が最も減少しているカテゴリを見るには、DESC キーワードを削除して分類を逆転させてください。

ウェブ UI

ウェブ UI で次のクエリを開きます。

#standardSQL

standardSQL

SELECT primary_type, description, COUNTIF(year = 2015) AS arrests_2015, COUNTIF(year = 2016) AS arrests_2016, FORMAT('%3.2f', (COUNTIF(year = 2016) - COUNTIF(year = 2015)) / COUNTIF(year = 2015)*100) AS pct_change_2015_to_2016 FROM `bigquery-public-data.chicago_crime.crime` WHERE arrest = TRUE AND year IN (2015, 2016) GROUP BY primary_type, description HAVING COUNTIF(year = 2015) > 100 ORDER BY (COUNTIF(year = 2016) - COUNTIF(year = 2015)) / COUNTIF(year = 2015) DESC

コマンドライン

bq query --use_legacy_sql=false '
SELECT
  primary_type,
  description,
  COUNTIF(year = 2015) AS arrests_2015,
  COUNTIF(year = 2016) AS arrests_2016,
  FORMAT("%3.2f", (COUNTIF(year = 2016) - COUNTIF(year = 2015)) / COUNTIF(year = 2015)*100) AS pct_change_2015_to_2016
FROM
  `bigquery-public-data.chicago_crime.crime`
WHERE
  arrest = TRUE
  AND year IN (2015,
    2016)
GROUP BY
  primary_type,
  description
HAVING
  COUNTIF(year = 2015) > 100
ORDER BY
  (COUNTIF(year = 2016) - COUNTIF(year = 2015)) / COUNTIF(year = 2015) DESC'

サンプルの結果を以下に示します。

+----------------------------------+-------------------------------------------------+--------------+--------------+-------------------------+
|           primary_type           |                   description                   | arrests_2015 | arrests_2016 | pct_change_2015_to_2016 |
+----------------------------------+-------------------------------------------------+--------------+--------------+-------------------------+
| OTHER OFFENSE                    | VEHICLE TITLE/REG OFFENSE                       |          288 |          418 | 45.14                   |
| OTHER OFFENSE                    | FALSE/STOLEN/ALTERED TRP                        |          299 |          418 | 39.80                   |
| NARCOTICS                        | FOUND SUSPECT NARCOTICS                         |          651 |          842 | 29.34                   |
| HOMICIDE                         | FIRST DEGREE MURDER                             |          164 |          201 | 22.56                   |
| ASSAULT                          | AGGRAVATED: HANDGUN                             |          468 |          521 | 11.32                   |
| MOTOR VEHICLE THEFT              | AUTOMOBILE                                      |          748 |          830 | 10.96                   |
| OTHER OFFENSE                    | OTHER VEHICLE OFFENSE                           |          186 |          200 | 7.53                    |
| LIQUOR LAW VIOLATION             | LIQUOR LICENSE VIOLATION                        |          134 |          144 | 7.46                    |
| ROBBERY                          | ARMED: HANDGUN                                  |          227 |          242 | 6.61                    |
| MOTOR VEHICLE THEFT              | THEFT/RECOVERY: AUTOMOBILE                      |          164 |          171 | 4.27                    |
| BATTERY                          | AGGRAVATED DOMESTIC BATTERY: OTHER DANG WEAPON  |          251 |          256 | 1.99                    |
| WEAPONS VIOLATION                | UNLAWFUL POSS OF HANDGUN                        |         2079 |         2075 | -0.19                   |
| THEFT                            | RETAIL THEFT                                    |         4932 |         4851 | -1.64                   |
| CRIMINAL TRESPASS                | TO VEHICLE                                      |          245 |          239 | -2.45                   |
| ROBBERY                          | STRONGARM - NO WEAPON                           |          353 |          338 | -4.25                   |
| THEFT                            | FROM BUILDING                                   |          148 |          141 | -4.73                   |
| BURGLARY                         | FORCIBLE ENTRY                                  |          367 |          348 | -5.18                   |
| ...                                                                                                                                        |
+----------------------------------+-------------------------------------------------+--------------+--------------+-------------------------+

一般に自動車盗難の数が最も多い月はどの月か?

次のクエリは、毎年および毎月の MOTOR VEHICLE THEFT インシデントの数値を要約し、月の合計を 1 位から 12 位までランク付けしたものです。そのうえで、外側の SELECT 句で、最終結果セットを各年の総合ランキングの 1 位に制限しています。このデータによると、過去 10 年間のうち 3 年間において、12 月に自動車盗難件数が最高となりました。

ウェブ UI

ウェブ UI で次のクエリを開きます。

#standardSQL
SELECT
  year,
  month,
  incidents
FROM (
  SELECT
    year,
    EXTRACT(MONTH
    FROM
      date) AS month,
    COUNT(1) AS incidents,
    RANK() OVER (PARTITION BY year ORDER BY COUNT(1) DESC) AS ranking
  FROM
    `bigquery-public-data.chicago_crime.crime`
  WHERE
    primary_type = 'MOTOR VEHICLE THEFT'
    AND year <= 2016
  GROUP BY
    year,
    month )
WHERE
  ranking = 1
ORDER BY
  year DESC

コマンドライン

bq query --use_legacy_sql=false '
SELECT
  year,
  month,
  incidents
FROM (
  SELECT
    year,
    EXTRACT(MONTH
    FROM
      date) AS month,
    COUNT(1) AS incidents,
    RANK() OVER (PARTITION BY year ORDER BY COUNT(1) DESC) AS ranking
  FROM
    `bigquery-public-data.chicago_crime.crime`
  WHERE
    primary_type = 'MOTOR VEHICLE THEFT'
    AND year <= 2016
  GROUP BY
    year,
    month )
WHERE
  ranking = 1
ORDER BY
  year DESC'

サンプルの結果を以下に示します。

+--------+--------+-----------+
| year   | month  | incidents |
+--------+--------+-----------+
| 2016   | 12     | 1112      |
| 2015   | 8      | 966       |
| 2014   | 10     | 922       |
| 2013   | 1      | 1470      |
| 2012   | 6      | 1469      |
| 2011   | 1      | 1862      |
| 2010   | 12     | 1880      |
| 2009   | 12     | 1539      |
| 2008   | 7      | 2015      |
| 2007   | 10     | 1709      |
| ...                         |
+--------+--------+-----------+

暴力犯罪(脅迫または暴行)の発生率に気温はどう影響するか?

このクエリでは、シカゴの犯罪データと NOAA Global Surface Summary of the Day Weather データが結合されており、極端に温度が低い気象または高い気象が暴力犯罪に及ぼす影響を特定できます。年ごとに、寒い日から暑い日へと各日を順に並べ、その年の最も寒い日 10% と最も暑い日 10% の犯罪発生率を中央の 80% と比較します。

ウェブ UI

ウェブ UI で次のクエリを開きます。

#standardSQL
SELECT
  year,
  coldest_days,
  incidents_coldest,
  ROUND(incidents_coldest / coldest_days, 2) AS rate_coldest,
  middle_days,
  incidents_middle,
  ROUND(incidents_middle / middle_days, 2) AS rate_middle,
  hottest_days,
  incidents_hottest,
  ROUND(incidents_hottest / hottest_days, 2) AS rate_hottest
FROM (
  SELECT
    w.year AS year,
    COUNTIF(rank <= 0.10) AS coldest_days,
    SUM(IF(rank <= 0.10, c.incidents, 0)) AS incidents_coldest,
    COUNTIF(rank > 0.10
      AND rank <= 90) AS middle_days,
    SUM(IF(rank > 0.10
        AND rank <= 90, c.incidents, 0)) AS incidents_middle,
    COUNTIF(rank > 0.90
      AND rank <= 90) AS hottest_days,
    SUM(IF(rank > 0.90, c.incidents, 0)) AS incidents_hottest
  FROM (
    SELECT
      TIMESTAMP_TRUNC(TIMESTAMP(CONCAT(year, '-', mo, '-', da)),
        DAY) AS date,
      year,
      PERCENT_RANK() OVER (PARTITION BY year ORDER BY AVG(temp)) AS rank,
      AVG(temp) AS temperature
    FROM
      `bigquery-public-data.noaa_gsod.gsod*` AS w
    JOIN
      `bigquery-public-data.noaa_gsod.stations` AS s
    ON
      w.stn = s.usaf
      AND s.name = "CHICAGO O'HARE INTERNATIONAL"
    WHERE
      w._TABLE_SUFFIX BETWEEN '2014'
      AND '2016'
    GROUP BY
      date,
      year ) AS w
  LEFT JOIN (
    SELECT
      TIMESTAMP_TRUNC(date,
        DAY) AS date,
      year,
      COUNT(1) AS incidents
    FROM
      `bigquery-public-data.chicago_crime.crime`
    WHERE
      primary_type IN ('BATTERY',
        'ASSAULT')
      AND year BETWEEN 2014
      AND 2016
    GROUP BY
      date,
      year ) AS c
  ON
    w.date = c.date
  GROUP BY
    year)
ORDER BY
  year DESC

コマンドライン

bq query --use_legacy_sql=false '
SELECT
  year,
  coldest_days,
  incidents_coldest,
  ROUND(incidents_coldest / coldest_days, 2) AS rate_coldest,
  middle_days,
  incidents_middle,
  ROUND(incidents_middle / middle_days, 2) AS rate_middle,
  hottest_days,
  incidents_hottest,
  ROUND(incidents_hottest / hottest_days, 2) AS rate_hottest
FROM (
  SELECT
    w.year AS year,
    COUNTIF(rank <= 0.10) AS coldest_days,
    SUM(IF(rank <= 0.10, c.incidents, 0)) AS incidents_coldest,
    COUNTIF(rank > 0.10
      AND rank <= 90) AS middle_days,
    SUM(IF(rank > 0.10
        AND rank <= 90, c.incidents, 0)) AS incidents_middle,
    COUNTIF(rank > 0.90
      AND rank <= 90) AS hottest_days,
    SUM(IF(rank > 0.90, c.incidents, 0)) AS incidents_hottest
  FROM (
    SELECT
      TIMESTAMP_TRUNC(TIMESTAMP(CONCAT(year, '-', mo, '-', da)),
        DAY) AS date,
      year,
      PERCENT_RANK() OVER (PARTITION BY year ORDER BY AVG(temp)) AS rank,
      AVG(temp) AS temperature
    FROM
      `bigquery-public-data.noaa_gsod.gsod*` AS w
    JOIN
      `bigquery-public-data.noaa_gsod.stations` AS s
    ON
      w.stn = s.usaf
      AND s.name = "CHICAGO O'HARE INTERNATIONAL"
    WHERE
      w._TABLE_SUFFIX BETWEEN '2014'
      AND '2016'
    GROUP BY
      date,
      year ) AS w
  LEFT JOIN (
    SELECT
      TIMESTAMP_TRUNC(date,
        DAY) AS date,
      year,
      COUNT(1) AS incidents
    FROM
      `bigquery-public-data.chicago_crime.crime`
    WHERE
      primary_type IN ('BATTERY',
        'ASSAULT')
      AND year BETWEEN 2014
      AND 2016
    GROUP BY
      date,
      year ) AS c
  ON
    w.date = c.date
  GROUP BY
    year)
ORDER BY
  year DESC'

サンプルの結果を以下に示します。

+-------+---------------+--------------------+---------------+--------------+-------------------+--------------+---------------+--------------------+---------------+
| year  | coldest_days  | incidents_coldest  | rate_coldest  | middle_days  | incidents_middle  | rate_middle  | hottest_days  | incidents_hottest  | rate_hottest  |
+-------+---------------+--------------------+---------------+--------------+-------------------+--------------+---------------+--------------------+---------------+
| 2016  | 37            | 5415               | 146.35        | 329          | 63575             | 193.24       | 37            | 7569               | 204.57        |
| 2015  | 37            | 5232               | 141.41        | 328          | 60716             | 185.11       | 37            | 7690               | 207.84        |
| 2014  | 37            | 5120               | 138.38        | 328          | 61222             | 186.65       | 36            | 7701               | 213.92        |
+-------+---------------+--------------------+---------------+--------------+-------------------+--------------+---------------+--------------------+---------------+

3 年間のサンプリング期間における各日の数値を表示するようにクエリを変更し、これを視覚的に確認します。このクエリを使用して、Google データスタジオでレポートを作成できます。

Google データスタジオと BigQuery の使用に関するチュートリアルについては、Google データスタジオを使用して BigQuery データを可視化するをご覧ください。

変更されたクエリは次のようになります。

ウェブ UI

#standardSQL
SELECT
  w.date AS date,
  temperature,
  incidents
FROM (
  SELECT
    TIMESTAMP_TRUNC(TIMESTAMP(CONCAT(year, '-', mo, '-', da)),
      DAY) AS date,
    year,
    PERCENT_RANK() OVER (PARTITION BY year ORDER BY AVG(temp)) AS rank,
    AVG(temp) AS temperature
  FROM
    `bigquery-public-data.noaa_gsod.gsod*` AS w
  JOIN
    `bigquery-public-data.noaa_gsod.stations` AS s
  ON
    w.stn = s.usaf
    AND s.name = "CHICAGO O'HARE INTERNATIONAL"
  WHERE
    w._TABLE_SUFFIX BETWEEN '2014'
    AND '2016'
  GROUP BY
    date,
    year ) AS w
LEFT JOIN (
  SELECT
    TIMESTAMP_TRUNC(date,
      DAY) AS date,
    year,
    COUNT(1) AS incidents
  FROM
    `bigquery-public-data.chicago_crime.crime`
  WHERE
    primary_type IN ('BATTERY',
      'ASSAULT')
    AND year BETWEEN 2014
    AND 2016
  GROUP BY
    date,
    year ) AS c
ON
  w.date = c.date
ORDER BY
  Date

コマンドライン

bq query --use_legacy_sql=false '
SELECT
  w.date AS date,
  temperature,
  incidents
FROM (
  SELECT
    TIMESTAMP_TRUNC(TIMESTAMP(CONCAT(year, '-', mo, '-', da)),
      DAY) AS date,
    year,
    PERCENT_RANK() OVER (PARTITION BY year ORDER BY AVG(temp)) AS rank,
    AVG(temp) AS temperature
  FROM
    `bigquery-public-data.noaa_gsod.gsod*` AS w
  JOIN
    `bigquery-public-data.noaa_gsod.stations` AS s
  ON
    w.stn = s.usaf
    AND s.name = "CHICAGO O'HARE INTERNATIONAL"
  WHERE
    w._TABLE_SUFFIX BETWEEN '2014'
    AND '2016'
  GROUP BY
    date,
    year ) AS w
LEFT JOIN (
  SELECT
    TIMESTAMP_TRUNC(date,
      DAY) AS date,
    year,
    COUNT(1) AS incidents
  FROM
    `bigquery-public-data.chicago_crime.crime`
  WHERE
    primary_type IN ('BATTERY',
      'ASSAULT')
    AND year BETWEEN 2014
    AND 2016
  GROUP BY
    date,
    year ) AS c
ON
  w.date = c.date
ORDER BY
  Date'

温度別犯罪

傾向はプロットから明らかです。1 月 1 日(4 分割した 25 ℃未満の領域における 3 つの外れ値)以外では、一般に気温が低いと暴力犯罪率が低くなっています。

データについて

データセット ソース: シカゴ市

カテゴリ: シカゴ、公安

使用: このデータセットは、データセット ソース(https://data.cityofchicago.org)によって規定されている条件の下ですべてのユーザーに公開されています。Google はこのデータセットを「現状のまま」提供し、明示または黙示を問わず、いかなる保証も行いません。Google は、このデータセットの使用によって直接または間接の損害が発生したとしても一切責任を負いません。

BigQuery で表示: シカゴの犯罪データに移動

このページは役立ちましたか?評価をお願いいたします。

フィードバックを送信...

ご不明な点がありましたら、Google のサポートページをご覧ください。