Big Query requires a special package bigquery
that allows us to connect to the database via an API using our credentials. We will be able to see things about any database we select and run queries against it using SQL to extract information. This is the most popular way of working with BigQuery datasets in Python, where we can subsequently able to do further analysis on it.
This page shows how to get started with the Cloud Client Libraries for the BigQuery API. Read more about the client libraries for Cloud APIs, including the older Google APIs Client Libraries, in Client Libraries Explained.
Step 1: Installing the BigQuery API Client Library
First, we need to create a Google BigQuery account, then we need to install the library via Anaconda Prompt.
pip install --upgrade google-cloud-bigquery
Next, we must create a service account. Once we have done that and downloaded our API key we need to point Windows PowerShell to our authorisation credentials.$env:GOOGLE_APPLICATION_CREDENTIALS=”C:\Users\Ags91\Jupyter Notebooks\Data Analyst Portfolio\API Keys\plenary-edition-309221-3adba393ed83.json”
The same command can also be executed in the Command Prompt as:set GOOGLE_APPLICATION_CREDENTIALS=”C:\Users\Ags91\Jupyter Notebooks\Data Analyst Portfolio\API Keys\plenary-edition-309221-3adba393ed83.json”
We are also going to install a library called Big Query Helper, this helps us simplify common read-only tasks.
pip install -e git+https://github.com/SohierDane/BigQuery_Helper#egg=bq_helper
Step 2: Importing Libraries
These are the libraries we require to work with SQL in python.
# Import libraries import pandas as pd import numpy as np import matplotlib.pyplot as plt import seaborn as sns import os import bq_helper as BigQueryHelper from google.cloud import bigquery # This will set the limit of rows to 500 pd.options.display.max_rows = 500
# Setting the backup variable for API key in case the PowerShell does not work os.environ["GOOGLE_APPLICATION_CREDENTIALS"]=r"C:\Users\Ags91\Jupyter Notebooks\Data Analyst Portfolio\API Keys\plenary-edition-309221-3adba393ed83.json" # Create a "Client" object client = bigquery.Client() # load magic command that lets us run queries with minimal code %load_ext google.cloud.bigquery
Step 3: Importing from SQL Database
The data we are going to query using SQL is hosted in Google BigQuery. London is the capital and most populous city of England and the United Kingdom. Standing on the River Thames in the southeast of the island of Great Britain, London has been a major settlement for two millennia. Source: https://en.wikipedia.org/wiki/London.
This data counts the number of crimes at two different geographic levels of London (LSOA and borough) by year, according to crime type. Includes data from 2008 to the present.
Below is a list of the crime types covered (*not available at LSOA level):
Major Category: Minor Category
- Burglary: Burglary in a Dwelling (data to March 2017)
- Burglary: Burglary in Other Buildings (data to March 2017)
- Burglary: Burglary – Residential (as of April 2017 Burglary in a Dwelling was changed to this subcategory to take account of certain offences being reclassified from what was the ‘Other buildings’ category (now Business and Community) into this new category eg. burglaries of shed and outbuildings). The old and new subcategories are therefore not comparable.
- Burglary: Burglary – Business and Community (as of April 2017 Burglary in Other Buildings was changed to this subcategory to take account of certain offences being reclassified to what is now the ‘Burglary Residential’ category. The old and new subcategories are therefore not comparable.
- Criminal Damage: Criminal Damage To Dwelling
- Criminal Damage: Criminal Damage To Motor Vehicle
- Criminal Damage: Criminal Damage To Other Building
- Criminal Damage: Other Criminal Damage
- Drugs: Drug Trafficking
- Drugs: Other Drugs
- Drugs: Possession Of Drugs
- Fraud or Forgery: Counted per Victim*
- Fraud or Forgery: Other Fraud & Forgery
- Other Notifiable Offences: Going Equipped
- Other Notifiable Offences: Other Notifiable
- Robbery: Business Property
- Robbery: Personal Property
- Sexual Offences: Other Sexual
- Sexual Offences: Rape
- Theft and Handling: Handling Stolen Goods
- Theft and Handling: Motor Vehicle Interference & Tampering
- Theft and Handling: Other Theft
- Theft and Handling: Other Theft Person
- Theft and Handling: Theft From Motor Vehicle
- Theft and Handling: Theft From Shops
- Theft and Handling: Theft/Taking of Pedal Cycle
- Violence Against the Person: Assault with Injury
- Violence Against the Person: Common Assault
- Violence Against the Person: Grievous Bodily Harm
- Violence Against the Person: Harassment
- Violence Against the Person: Murder
- Violence Against the Person: Offensive Weapon
- Violence Against the Person: Other violence
- Violence Against the Person: Wounding/GBH
Column descriptions
lsoa_code:
Lower Layer Super Output Area code according to the Office for National Statisticsborough:
A town or district which is an administrative unitmajor_category:
Major crime categoryminor_category:
Minor crime categoryvalue:
Summary of the number of crimes for the monthyear:
Year of the recordmonth:
The month of the record
%%bigquery SELECT * FROM bigquery-public-data.london_crime.crime_by_lsoa LIMIT 10
Query complete after 0.00s: 100%|███████████████████████████████████████████████████| 1/1 [00:00<00:00, 1008.97query/s] Downloading: 100%|███████████████████████████████████████████████████████████████████| 10/10 [00:00<00:00, 10.91rows/s]
lsoa_code | borough | major_category | minor_category | value | year | month | |
---|---|---|---|---|---|---|---|
0 | E01032740 | City of London | Violence Against the Person | Harassment | 0 | 2016 | 11 |
1 | E01000005 | City of London | Violence Against the Person | Harassment | 0 | 2011 | 3 |
2 | E01032739 | City of London | Violence Against the Person | Harassment | 0 | 2016 | 12 |
3 | E01000005 | City of London | Violence Against the Person | Harassment | 0 | 2011 | 11 |
4 | E01000005 | City of London | Violence Against the Person | Harassment | 0 | 2014 | 3 |
5 | E01000002 | City of London | Violence Against the Person | Harassment | 0 | 2011 | 5 |
6 | E01000005 | City of London | Violence Against the Person | Harassment | 0 | 2011 | 8 |
7 | E01000005 | City of London | Violence Against the Person | Harassment | 0 | 2013 | 3 |
8 | E01032740 | City of London | Violence Against the Person | Harassment | 0 | 2009 | 11 |
9 | E01032739 | City of London | Violence Against the Person | Harassment | 0 | 2008 | 1 |
Step 4: Questions
Question 1: What is the change in the number of crime incidents from 2011 to 2016?
%%bigquery SELECT SUM(value) AS total_crimes, year FROM bigquery-public-data.london_crime.crime_by_lsoa WHERE year between 2011 and 2016 GROUP BY year ORDER BY year
Query complete after 0.00s: 100%|███████████████████████████████████████████████████| 1/1 [00:00<00:00, 2014.56query/s] Downloading: 100%|█████████████████████████████████████████████████████████████████████| 6/6 [00:00<00:00, 6.50rows/s]
total_crimes | year | |
---|---|---|
0 | 724915 | 2011 |
1 | 737329 | 2012 |
2 | 686407 | 2013 |
3 | 680183 | 2014 |
4 | 711624 | 2015 |
5 | 736121 | 2016 |
Question 2: What were the top 3 crimes per borough in 2016?
To answer this question, first, we need to find all the boroughs included in our data.
%%bigquery SELECT DISTINCT borough FROM bigquery-public-data.london_crime.crime_by_lsoa
Query complete after 0.00s: 100%|███████████████████████████████████████████████████| 2/2 [00:00<00:00, 1344.54query/s] Downloading: 100%|███████████████████████████████████████████████████████████████████| 33/33 [00:01<00:00, 32.94rows/s]
borough | |
---|---|
0 | City of London |
1 | Brent |
2 | Barnet |
3 | Bexley |
4 | Camden |
5 | Ealing |
6 | Harrow |
7 | Merton |
8 | Newham |
9 | Sutton |
10 | Bromley |
11 | Croydon |
12 | Enfield |
13 | Hackney |
14 | Lambeth |
15 | Haringey |
16 | Havering |
17 | Hounslow |
18 | Lewisham |
19 | Greenwich |
20 | Islington |
21 | Redbridge |
22 | Southwark |
23 | Hillingdon |
24 | Wandsworth |
25 | Westminster |
26 | Tower Hamlets |
27 | Waltham Forest |
28 | Barking and Dagenham |
29 | Kingston upon Thames |
30 | Richmond upon Thames |
31 | Hammersmith and Fulham |
32 | Kensington and Chelsea |
top_crimes_borough_query = """ SELECT borough, major_category, SUM(value) AS total_crimes FROM bigquery-public-data.london_crime.crime_by_lsoa WHERE year = 2016 GROUP BY borough, major_category ORDER BY borough, total_crimes DESC; """
%%bigquery SELECT borough, major_category, SUM(value) AS total_crimes FROM bigquery-public-data.london_crime.crime_by_lsoa WHERE year = 2016 GROUP BY borough, major_category ORDER BY borough, total_crimes DESC;
Query complete after 0.00s: 100%|███████████████████████████████████████████████████| 3/3 [00:00<00:00, 2005.24query/s] Downloading: 100%|████████████████████████████████████████████████████████████████| 295/295 [00:00<00:00, 345.68rows/s]
borough | major_category | total_crimes | |
---|---|---|---|
0 | Barking and Dagenham | Violence Against the Person | 6067 |
1 | Barking and Dagenham | Theft and Handling | 5607 |
2 | Barking and Dagenham | Criminal Damage | 1949 |
3 | Barking and Dagenham | Burglary | 1287 |
4 | Barking and Dagenham | Drugs | 919 |
5 | Barking and Dagenham | Robbery | 534 |
6 | Barking and Dagenham | Other Notifiable Offences | 378 |
7 | Barking and Dagenham | Sexual Offences | 0 |
8 | Barking and Dagenham | Fraud or Forgery | 0 |
9 | Barnet | Theft and Handling | 9731 |
10 | Barnet | Violence Against the Person | 7499 |
11 | Barnet | Burglary | 3402 |
12 | Barnet | Criminal Damage | 2183 |
13 | Barnet | Drugs | 906 |
14 | Barnet | Other Notifiable Offences | 499 |
15 | Barnet | Robbery | 464 |
16 | Barnet | Sexual Offences | 0 |
17 | Barnet | Fraud or Forgery | 0 |
18 | Bexley | Violence Against the Person | 4503 |
19 | Bexley | Theft and Handling | 4392 |
20 | Bexley | Criminal Damage | 1673 |
21 | Bexley | Burglary | 1123 |
22 | Bexley | Drugs | 646 |
23 | Bexley | Other Notifiable Offences | 294 |
24 | Bexley | Robbery | 209 |
25 | Bexley | Sexual Offences | 0 |
26 | Bexley | Fraud or Forgery | 0 |
27 | Brent | Violence Against the Person | 9205 |
28 | Brent | Theft and Handling | 9026 |
29 | Brent | Burglary | 2631 |
30 | Brent | Criminal Damage | 2280 |
31 | Brent | Drugs | 2096 |
32 | Brent | Robbery | 919 |
33 | Brent | Other Notifiable Offences | 536 |
34 | Brent | Sexual Offences | 0 |
35 | Brent | Fraud or Forgery | 0 |
36 | Bromley | Theft and Handling | 7584 |
37 | Bromley | Violence Against the Person | 6650 |
38 | Bromley | Burglary | 2214 |
39 | Bromley | Criminal Damage | 2202 |
40 | Bromley | Drugs | 728 |
41 | Bromley | Other Notifiable Offences | 417 |
42 | Bromley | Robbery | 369 |
43 | Bromley | Sexual Offences | 0 |
44 | Bromley | Fraud or Forgery | 0 |
45 | Camden | Theft and Handling | 14088 |
46 | Camden | Violence Against the Person | 7626 |
47 | Camden | Burglary | 2652 |
48 | Camden | Criminal Damage | 1935 |
49 | Camden | Drugs | 1493 |
50 | Camden | Robbery | 899 |
51 | Camden | Other Notifiable Offences | 490 |
52 | Camden | Sexual Offences | 0 |
53 | Camden | Fraud or Forgery | 0 |
54 | City of London | Theft and Handling | 129 |
55 | City of London | Violence Against the Person | 25 |
56 | City of London | Drugs | 10 |
57 | City of London | Other Notifiable Offences | 6 |
58 | City of London | Robbery | 4 |
59 | City of London | Criminal Damage | 2 |
60 | City of London | Burglary | 2 |
61 | Croydon | Violence Against the Person | 10302 |
62 | Croydon | Theft and Handling | 9229 |
63 | Croydon | Criminal Damage | 3219 |
64 | Croydon | Burglary | 2738 |
65 | Croydon | Drugs | 1367 |
66 | Croydon | Robbery | 1139 |
67 | Croydon | Other Notifiable Offences | 718 |
68 | Croydon | Sexual Offences | 0 |
69 | Croydon | Fraud or Forgery | 0 |
70 | Ealing | Theft and Handling | 10040 |
71 | Ealing | Violence Against the Person | 9396 |
72 | Ealing | Criminal Damage | 2562 |
73 | Ealing | Burglary | 2492 |
74 | Ealing | Drugs | 1355 |
75 | Ealing | Robbery | 669 |
76 | Ealing | Other Notifiable Offences | 613 |
77 | Ealing | Sexual Offences | 0 |
78 | Ealing | Fraud or Forgery | 0 |
79 | Enfield | Theft and Handling | 8037 |
80 | Enfield | Violence Against the Person | 7409 |
81 | Enfield | Burglary | 2541 |
82 | Enfield | Criminal Damage | 2136 |
83 | Enfield | Drugs | 1063 |
84 | Enfield | Robbery | 807 |
85 | Enfield | Other Notifiable Offences | 492 |
86 | Enfield | Sexual Offences | 0 |
87 | Enfield | Fraud or Forgery | 0 |
88 | Greenwich | Violence Against the Person | 8590 |
89 | Greenwich | Theft and Handling | 8010 |
90 | Greenwich | Criminal Damage | 2476 |
91 | Greenwich | Burglary | 1780 |
92 | Greenwich | Drugs | 867 |
93 | Greenwich | Other Notifiable Offences | 521 |
94 | Greenwich | Robbery | 486 |
95 | Greenwich | Sexual Offences | 0 |
96 | Greenwich | Fraud or Forgery | 0 |
97 | Hackney | Theft and Handling | 11851 |
98 | Hackney | Violence Against the Person | 8832 |
99 | Hackney | Burglary | 2719 |
100 | Hackney | Criminal Damage | 1981 |
101 | Hackney | Drugs | 1353 |
102 | Hackney | Robbery | 1030 |
103 | Hackney | Other Notifiable Offences | 499 |
104 | Hackney | Sexual Offences | 0 |
105 | Hackney | Fraud or Forgery | 0 |
106 | Hammersmith and Fulham | Theft and Handling | 8925 |
107 | Hammersmith and Fulham | Violence Against the Person | 6118 |
108 | Hammersmith and Fulham | Burglary | 1531 |
109 | Hammersmith and Fulham | Criminal Damage | 1408 |
110 | Hammersmith and Fulham | Drugs | 1321 |
111 | Hammersmith and Fulham | Other Notifiable Offences | 474 |
112 | Hammersmith and Fulham | Robbery | 397 |
113 | Hammersmith and Fulham | Sexual Offences | 0 |
114 | Hammersmith and Fulham | Fraud or Forgery | 0 |
115 | Haringey | Theft and Handling | 10554 |
116 | Haringey | Violence Against the Person | 8574 |
117 | Haringey | Burglary | 2474 |
118 | Haringey | Criminal Damage | 2233 |
119 | Haringey | Drugs | 1540 |
120 | Haringey | Robbery | 1248 |
121 | Haringey | Other Notifiable Offences | 551 |
122 | Haringey | Sexual Offences | 0 |
123 | Haringey | Fraud or Forgery | 0 |
124 | Harrow | Theft and Handling | 4537 |
125 | Harrow | Violence Against the Person | 4293 |
126 | Harrow | Burglary | 1994 |
127 | Harrow | Criminal Damage | 1212 |
128 | Harrow | Drugs | 473 |
129 | Harrow | Robbery | 377 |
130 | Harrow | Other Notifiable Offences | 267 |
131 | Harrow | Sexual Offences | 0 |
132 | Harrow | Fraud or Forgery | 0 |
133 | Havering | Violence Against the Person | 5936 |
134 | Havering | Theft and Handling | 5919 |
135 | Havering | Burglary | 1826 |
136 | Havering | Criminal Damage | 1804 |
137 | Havering | Drugs | 718 |
138 | Havering | Other Notifiable Offences | 389 |
139 | Havering | Robbery | 311 |
140 | Havering | Sexual Offences | 0 |
141 | Havering | Fraud or Forgery | 0 |
142 | Hillingdon | Theft and Handling | 9045 |
143 | Hillingdon | Violence Against the Person | 7830 |
144 | Hillingdon | Criminal Damage | 2470 |
145 | Hillingdon | Burglary | 2229 |
146 | Hillingdon | Other Notifiable Offences | 1305 |
147 | Hillingdon | Drugs | 1042 |
148 | Hillingdon | Robbery | 420 |
149 | Hillingdon | Sexual Offences | 0 |
150 | Hillingdon | Fraud or Forgery | 0 |
151 | Hounslow | Theft and Handling | 8682 |
152 | Hounslow | Violence Against the Person | 7405 |
153 | Hounslow | Criminal Damage | 2213 |
154 | Hounslow | Burglary | 1808 |
155 | Hounslow | Drugs | 1086 |
156 | Hounslow | Other Notifiable Offences | 665 |
157 | Hounslow | Robbery | 392 |
158 | Hounslow | Sexual Offences | 0 |
159 | Hounslow | Fraud or Forgery | 0 |
160 | Islington | Theft and Handling | 12077 |
161 | Islington | Violence Against the Person | 7840 |
162 | Islington | Burglary | 2051 |
163 | Islington | Criminal Damage | 1903 |
164 | Islington | Drugs | 1777 |
165 | Islington | Robbery | 936 |
166 | Islington | Other Notifiable Offences | 508 |
167 | Islington | Sexual Offences | 0 |
168 | Islington | Fraud or Forgery | 0 |
169 | Kensington and Chelsea | Theft and Handling | 9855 |
170 | Kensington and Chelsea | Violence Against the Person | 4696 |
171 | Kensington and Chelsea | Drugs | 1680 |
172 | Kensington and Chelsea | Burglary | 1449 |
173 | Kensington and Chelsea | Criminal Damage | 1051 |
174 | Kensington and Chelsea | Robbery | 523 |
175 | Kensington and Chelsea | Other Notifiable Offences | 326 |
176 | Kensington and Chelsea | Sexual Offences | 0 |
177 | Kensington and Chelsea | Fraud or Forgery | 0 |
178 | Kingston upon Thames | Theft and Handling | 3803 |
179 | Kingston upon Thames | Violence Against the Person | 3194 |
180 | Kingston upon Thames | Criminal Damage | 1054 |
181 | Kingston upon Thames | Burglary | 879 |
182 | Kingston upon Thames | Drugs | 743 |
183 | Kingston upon Thames | Other Notifiable Offences | 189 |
184 | Kingston upon Thames | Robbery | 121 |
185 | Kingston upon Thames | Sexual Offences | 0 |
186 | Kingston upon Thames | Fraud or Forgery | 0 |
187 | Lambeth | Theft and Handling | 13155 |
188 | Lambeth | Violence Against the Person | 10496 |
189 | Lambeth | Burglary | 3087 |
190 | Lambeth | Criminal Damage | 2764 |
191 | Lambeth | Drugs | 2738 |
192 | Lambeth | Robbery | 1196 |
193 | Lambeth | Other Notifiable Offences | 635 |
194 | Lambeth | Sexual Offences | 0 |
195 | Lambeth | Fraud or Forgery | 0 |
196 | Lewisham | Violence Against the Person | 8809 |
197 | Lewisham | Theft and Handling | 7702 |
198 | Lewisham | Criminal Damage | 2351 |
199 | Lewisham | Burglary | 2071 |
200 | Lewisham | Drugs | 1617 |
201 | Lewisham | Robbery | 869 |
202 | Lewisham | Other Notifiable Offences | 485 |
203 | Lewisham | Sexual Offences | 0 |
204 | Lewisham | Fraud or Forgery | 0 |
205 | Merton | Theft and Handling | 4894 |
206 | Merton | Violence Against the Person | 4026 |
207 | Merton | Burglary | 1419 |
208 | Merton | Criminal Damage | 1418 |
209 | Merton | Drugs | 466 |
210 | Merton | Robbery | 283 |
211 | Merton | Other Notifiable Offences | 249 |
212 | Merton | Sexual Offences | 0 |
213 | Merton | Fraud or Forgery | 0 |
214 | Newham | Theft and Handling | 11964 |
215 | Newham | Violence Against the Person | 9646 |
216 | Newham | Criminal Damage | 2496 |
217 | Newham | Burglary | 2115 |
218 | Newham | Drugs | 1684 |
219 | Newham | Robbery | 1472 |
220 | Newham | Other Notifiable Offences | 713 |
221 | Newham | Sexual Offences | 0 |
222 | Newham | Fraud or Forgery | 0 |
223 | Redbridge | Theft and Handling | 7447 |
224 | Redbridge | Violence Against the Person | 6411 |
225 | Redbridge | Burglary | 1997 |
226 | Redbridge | Criminal Damage | 1650 |
227 | Redbridge | Drugs | 1017 |
228 | Redbridge | Robbery | 599 |
229 | Redbridge | Other Notifiable Offences | 381 |
230 | Redbridge | Sexual Offences | 0 |
231 | Redbridge | Fraud or Forgery | 0 |
232 | Richmond upon Thames | Theft and Handling | 4769 |
233 | Richmond upon Thames | Violence Against the Person | 3155 |
234 | Richmond upon Thames | Burglary | 1359 |
235 | Richmond upon Thames | Criminal Damage | 1148 |
236 | Richmond upon Thames | Drugs | 320 |
237 | Richmond upon Thames | Other Notifiable Offences | 217 |
238 | Richmond upon Thames | Robbery | 106 |
239 | Richmond upon Thames | Sexual Offences | 0 |
240 | Richmond upon Thames | Fraud or Forgery | 0 |
241 | Southwark | Theft and Handling | 12946 |
242 | Southwark | Violence Against the Person | 9474 |
243 | Southwark | Burglary | 2946 |
244 | Southwark | Criminal Damage | 2621 |
245 | Southwark | Drugs | 1838 |
246 | Southwark | Robbery | 1317 |
247 | Southwark | Other Notifiable Offences | 494 |
248 | Southwark | Sexual Offences | 0 |
249 | Southwark | Fraud or Forgery | 0 |
250 | Sutton | Violence Against the Person | 3714 |
251 | Sutton | Theft and Handling | 3516 |
252 | Sutton | Criminal Damage | 1316 |
253 | Sutton | Burglary | 1233 |
254 | Sutton | Drugs | 461 |
255 | Sutton | Other Notifiable Offences | 253 |
256 | Sutton | Robbery | 165 |
257 | Sutton | Sexual Offences | 0 |
258 | Sutton | Fraud or Forgery | 0 |
259 | Tower Hamlets | Theft and Handling | 10953 |
260 | Tower Hamlets | Violence Against the Person | 9608 |
261 | Tower Hamlets | Burglary | 2794 |
262 | Tower Hamlets | Criminal Damage | 2357 |
263 | Tower Hamlets | Drugs | 1629 |
264 | Tower Hamlets | Robbery | 1234 |
265 | Tower Hamlets | Other Notifiable Offences | 678 |
266 | Tower Hamlets | Sexual Offences | 0 |
267 | Tower Hamlets | Fraud or Forgery | 0 |
268 | Waltham Forest | Violence Against the Person | 7409 |
269 | Waltham Forest | Theft and Handling | 7357 |
270 | Waltham Forest | Criminal Damage | 1989 |
271 | Waltham Forest | Burglary | 1873 |
272 | Waltham Forest | Drugs | 1042 |
273 | Waltham Forest | Robbery | 602 |
274 | Waltham Forest | Other Notifiable Offences | 444 |
275 | Waltham Forest | Sexual Offences | 0 |
276 | Waltham Forest | Fraud or Forgery | 0 |
277 | Wandsworth | Theft and Handling | 10789 |
278 | Wandsworth | Violence Against the Person | 6809 |
279 | Wandsworth | Burglary | 2351 |
280 | Wandsworth | Criminal Damage | 1836 |
281 | Wandsworth | Drugs | 870 |
282 | Wandsworth | Robbery | 609 |
283 | Wandsworth | Other Notifiable Offences | 415 |
284 | Wandsworth | Sexual Offences | 0 |
285 | Wandsworth | Fraud or Forgery | 0 |
286 | Westminster | Theft and Handling | 27520 |
287 | Westminster | Violence Against the Person | 10834 |
288 | Westminster | Burglary | 3218 |
289 | Westminster | Criminal Damage | 2179 |
290 | Westminster | Drugs | 2049 |
291 | Westminster | Robbery | 1822 |
292 | Westminster | Other Notifiable Offences | 708 |
293 | Westminster | Sexual Offences | 0 |
294 | Westminster | Fraud or Forgery | 0 |
%%bigquery SELECT borough, major_category, rank_per_borough, no_of_incidents FROM ( SELECT borough, major_category, RANK() OVER(PARTITION BY borough ORDER BY SUM(value) DESC) AS rank_per_borough, SUM(value) AS no_of_incidents FROM `bigquery-public-data.london_crime.crime_by_lsoa` GROUP BY borough, major_category ) WHERE rank_per_borough <= 3 ORDER BY borough, rank_per_borough
Query complete after 0.00s: 100%|███████████████████████████████████████████████████| 4/4 [00:00<00:00, 2689.52query/s] Downloading: 100%|██████████████████████████████████████████████████████████████████| 99/99 [00:00<00:00, 116.45rows/s]
borough | major_category | rank_per_borough | no_of_incidents | |
---|---|---|---|---|
0 | Barking and Dagenham | Theft and Handling | 1 | 50999 |
1 | Barking and Dagenham | Violence Against the Person | 2 | 43091 |
2 | Barking and Dagenham | Criminal Damage | 3 | 18888 |
3 | Barnet | Theft and Handling | 1 | 87285 |
4 | Barnet | Violence Against the Person | 2 | 46565 |
5 | Barnet | Burglary | 3 | 36981 |
6 | Bexley | Theft and Handling | 1 | 40071 |
7 | Bexley | Violence Against the Person | 2 | 30037 |
8 | Bexley | Criminal Damage | 3 | 17244 |
9 | Brent | Theft and Handling | 1 | 72523 |
10 | Brent | Violence Against the Person | 2 | 63178 |
11 | Brent | Burglary | 3 | 28923 |
12 | Bromley | Theft and Handling | 1 | 69742 |
13 | Bromley | Violence Against the Person | 2 | 46759 |
14 | Bromley | Burglary | 3 | 27135 |
15 | Camden | Theft and Handling | 1 | 140596 |
16 | Camden | Violence Against the Person | 2 | 53012 |
17 | Camden | Burglary | 3 | 27939 |
18 | City of London | Theft and Handling | 1 | 561 |
19 | City of London | Violence Against the Person | 2 | 114 |
20 | City of London | Drugs | 3 | 33 |
21 | Croydon | Theft and Handling | 1 | 91437 |
22 | Croydon | Violence Against the Person | 2 | 67791 |
23 | Croydon | Burglary | 3 | 33376 |
24 | Ealing | Theft and Handling | 1 | 93834 |
25 | Ealing | Violence Against the Person | 2 | 68492 |
26 | Ealing | Burglary | 3 | 30831 |
27 | Enfield | Theft and Handling | 1 | 70371 |
28 | Enfield | Violence Against the Person | 2 | 45036 |
29 | Enfield | Burglary | 3 | 30213 |
30 | Greenwich | Theft and Handling | 1 | 64923 |
31 | Greenwich | Violence Against the Person | 2 | 52897 |
32 | Greenwich | Criminal Damage | 3 | 22755 |
33 | Hackney | Theft and Handling | 1 | 91118 |
34 | Hackney | Violence Against the Person | 2 | 56584 |
35 | Hackney | Burglary | 3 | 21450 |
36 | Hammersmith and Fulham | Theft and Handling | 1 | 86381 |
37 | Hammersmith and Fulham | Violence Against the Person | 2 | 43014 |
38 | Hammersmith and Fulham | Burglary | 3 | 17010 |
39 | Haringey | Theft and Handling | 1 | 83979 |
40 | Haringey | Violence Against the Person | 2 | 50943 |
41 | Haringey | Burglary | 3 | 28213 |
42 | Harrow | Theft and Handling | 1 | 40800 |
43 | Harrow | Violence Against the Person | 2 | 30213 |
44 | Harrow | Burglary | 3 | 19630 |
45 | Havering | Theft and Handling | 1 | 52609 |
46 | Havering | Violence Against the Person | 2 | 33968 |
47 | Havering | Burglary | 3 | 21302 |
48 | Hillingdon | Theft and Handling | 1 | 80028 |
49 | Hillingdon | Violence Against the Person | 2 | 55264 |
50 | Hillingdon | Burglary | 3 | 26056 |
51 | Hounslow | Theft and Handling | 1 | 70180 |
52 | Hounslow | Violence Against the Person | 2 | 51404 |
53 | Hounslow | Criminal Damage | 3 | 21407 |
54 | Islington | Theft and Handling | 1 | 107661 |
55 | Islington | Violence Against the Person | 2 | 52975 |
56 | Islington | Burglary | 3 | 22207 |
57 | Kensington and Chelsea | Theft and Handling | 1 | 95963 |
58 | Kensington and Chelsea | Violence Against the Person | 2 | 29570 |
59 | Kensington and Chelsea | Burglary | 3 | 14980 |
60 | Kingston upon Thames | Theft and Handling | 1 | 38226 |
61 | Kingston upon Thames | Violence Against the Person | 2 | 21540 |
62 | Kingston upon Thames | Criminal Damage | 3 | 10610 |
63 | Lambeth | Theft and Handling | 1 | 114899 |
64 | Lambeth | Violence Against the Person | 2 | 72726 |
65 | Lambeth | Burglary | 3 | 30199 |
66 | Lewisham | Theft and Handling | 1 | 70382 |
67 | Lewisham | Violence Against the Person | 2 | 63652 |
68 | Lewisham | Burglary | 3 | 24871 |
69 | Merton | Theft and Handling | 1 | 44128 |
70 | Merton | Violence Against the Person | 2 | 28322 |
71 | Merton | Burglary | 3 | 16485 |
72 | Newham | Theft and Handling | 1 | 106146 |
73 | Newham | Violence Against the Person | 2 | 66221 |
74 | Newham | Burglary | 3 | 25356 |
75 | Redbridge | Theft and Handling | 1 | 71496 |
76 | Redbridge | Violence Against the Person | 2 | 41430 |
77 | Redbridge | Burglary | 3 | 26735 |
78 | Richmond upon Thames | Theft and Handling | 1 | 40858 |
79 | Richmond upon Thames | Violence Against the Person | 2 | 20314 |
80 | Richmond upon Thames | Burglary | 3 | 16097 |
81 | Southwark | Theft and Handling | 1 | 109432 |
82 | Southwark | Violence Against the Person | 2 | 68356 |
83 | Southwark | Burglary | 3 | 27980 |
84 | Sutton | Theft and Handling | 1 | 39533 |
85 | Sutton | Violence Against the Person | 2 | 25409 |
86 | Sutton | Criminal Damage | 3 | 14474 |
87 | Tower Hamlets | Theft and Handling | 1 | 87620 |
88 | Tower Hamlets | Violence Against the Person | 2 | 59993 |
89 | Tower Hamlets | Drugs | 3 | 23408 |
90 | Waltham Forest | Theft and Handling | 1 | 77940 |
91 | Waltham Forest | Violence Against the Person | 2 | 51898 |
92 | Waltham Forest | Burglary | 3 | 25565 |
93 | Wandsworth | Theft and Handling | 1 | 92523 |
94 | Wandsworth | Violence Against the Person | 2 | 45865 |
95 | Wandsworth | Burglary | 3 | 25533 |
96 | Westminster | Theft and Handling | 1 | 277617 |
97 | Westminster | Violence Against the Person | 2 | 71448 |
98 | Westminster | Drugs | 3 | 34031 |
london = bq_helper.BigQueryHelper(active_project="bigquery-public-data", dataset_name="london_crime")
top_crimes_borough = london.query_to_pandas_safe(top_crimes_borough_query)
var = top_crimes_borough.groupby(['borough','major_category']).sum() df = var.groupby('borough').apply(lambda x: x.nlargest(3,'total_crimes')).reset_index(level=0, drop=True)
total_crimes | ||
---|---|---|
borough | major_category | |
Barking and Dagenham | Violence Against the Person | 6067 |
Theft and Handling | 5607 | |
Criminal Damage | 1949 | |
Barnet | Theft and Handling | 9731 |
Violence Against the Person | 7499 | |
Burglary | 3402 | |
Bexley | Violence Against the Person | 4503 |
Theft and Handling | 4392 | |
Criminal Damage | 1673 | |
Brent | Violence Against the Person | 9205 |
Theft and Handling | 9026 | |
Burglary | 2631 | |
Bromley | Theft and Handling | 7584 |
Violence Against the Person | 6650 | |
Burglary | 2214 | |
Camden | Theft and Handling | 14088 |
Violence Against the Person | 7626 | |
Burglary | 2652 | |
City of London | Theft and Handling | 129 |
Violence Against the Person | 25 | |
Drugs | 10 | |
Croydon | Violence Against the Person | 10302 |
Theft and Handling | 9229 | |
Criminal Damage | 3219 | |
Ealing | Theft and Handling | 10040 |
Violence Against the Person | 9396 | |
Criminal Damage | 2562 | |
Enfield | Theft and Handling | 8037 |
Violence Against the Person | 7409 | |
Burglary | 2541 | |
Greenwich | Violence Against the Person | 8590 |
Theft and Handling | 8010 | |
Criminal Damage | 2476 | |
Hackney | Theft and Handling | 11851 |
Violence Against the Person | 8832 | |
Burglary | 2719 | |
Hammersmith and Fulham | Theft and Handling | 8925 |
Violence Against the Person | 6118 | |
Burglary | 1531 | |
Haringey | Theft and Handling | 10554 |
Violence Against the Person | 8574 | |
Burglary | 2474 | |
Harrow | Theft and Handling | 4537 |
Violence Against the Person | 4293 | |
Burglary | 1994 | |
Havering | Violence Against the Person | 5936 |
Theft and Handling | 5919 | |
Burglary | 1826 | |
Hillingdon | Theft and Handling | 9045 |
Violence Against the Person | 7830 | |
Criminal Damage | 2470 | |
Hounslow | Theft and Handling | 8682 |
Violence Against the Person | 7405 | |
Criminal Damage | 2213 | |
Islington | Theft and Handling | 12077 |
Violence Against the Person | 7840 | |
Burglary | 2051 | |
Kensington and Chelsea | Theft and Handling | 9855 |
Violence Against the Person | 4696 | |
Drugs | 1680 | |
Kingston upon Thames | Theft and Handling | 3803 |
Violence Against the Person | 3194 | |
Criminal Damage | 1054 | |
Lambeth | Theft and Handling | 13155 |
Violence Against the Person | 10496 | |
Burglary | 3087 | |
Lewisham | Violence Against the Person | 8809 |
Theft and Handling | 7702 | |
Criminal Damage | 2351 | |
Merton | Theft and Handling | 4894 |
Violence Against the Person | 4026 | |
Burglary | 1419 | |
Newham | Theft and Handling | 11964 |
Violence Against the Person | 9646 | |
Criminal Damage | 2496 | |
Redbridge | Theft and Handling | 7447 |
Violence Against the Person | 6411 | |
Burglary | 1997 | |
Richmond upon Thames | Theft and Handling | 4769 |
Violence Against the Person | 3155 | |
Burglary | 1359 | |
Southwark | Theft and Handling | 12946 |
Violence Against the Person | 9474 | |
Burglary | 2946 | |
Sutton | Violence Against the Person | 3714 |
Theft and Handling | 3516 | |
Criminal Damage | 1316 | |
Tower Hamlets | Theft and Handling | 10953 |
Violence Against the Person | 9608 | |
Burglary | 2794 | |
Waltham Forest | Violence Against the Person | 7409 |
Theft and Handling | 7357 | |
Criminal Damage | 1989 | |
Wandsworth | Theft and Handling | 10789 |
Violence Against the Person | 6809 | |
Burglary | 2351 | |
Westminster | Theft and Handling | 27520 |
Violence Against the Person | 10834 | |
Burglary | 3218 |
df.plot(kind='barh', figsize=(10,100))

In [101]:
top_crimes_borough.groupby(['borough','major_category']).sum().plot(kind='barh', figsize=(10,100))

%%bigquery SELECT borough, major_category, SUM(value) AS total_crimes FROM bigquery-public-data.london_crime.crime_by_lsoa WHERE borough = 'Barking and Dagenham' GROUP BY borough, major_category ORDER BY borough, total_crimes DESC LIMIT 3
Query complete after 0.00s: 100%|███████████████████████████████████████████████████| 1/1 [00:00<00:00, 1008.00query/s] Downloading: 100%|█████████████████████████████████████████████████████████████████████| 3/3 [00:00<00:00, 3.06rows/s]
borough | major_category | total_crimes | |
---|---|---|---|
0 | Barking and Dagenham | Theft and Handling | 50999 |
1 | Barking and Dagenham | Violence Against the Person | 43091 |
2 | Barking and Dagenham | Criminal Damage | 18888 |
%%bigquery SELECT bigquery-public-data.london_crime.crime_by_lsoa.* FROM bigquery-public-data.london_crime.crime_by_lsoa INNER JOIN ( SELECT borough, GROUP_CONCAT(year ORDER BY total_crimes DESC) grouped_year FROM yourtable GROUP BY id) group_max ON yourtable.id = group_max.id AND FIND_IN_SET(year, grouped_year) BETWEEN 1 AND 5 ORDER BY yourtable.id, yourtable.year DESC;
In [8]:
%%bigquery SELECT SUM(value) AS total_crimes, major_category, year FROM bigquery-public-data.london_crime.crime_by_lsoa WHERE year between 2011 and 2016 GROUP BY year ORDER BY year
Executing query with job ID: 7eac20b8-460d-432d-8cec-b6c422ea8f7a Query executing: 0.44s
References
https://cloud.google.com/docs/authentication/getting-started
https://cloud.google.com/bigquery/docs/reference/libraries#windows
https://stackoverflow.com/questions/37400974/unicode-error-unicodeescape-codec-cant-decode-bytes-in-position-2-3-trunca
https://stackoverflow.com/questions/45501082/set-google-application-credentials-in-python-project-to-use-google-api