SQL with Big Query and Python

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 Statistics
borough: A town or district which is an administrative unit
major_category: Major crime category
minor_category: Minor crime category
value: Summary of the number of crimes for the month
year: Year of the record
month: 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	year	month
0	E01032740	City of London	Violence Against the Person	Harassment	2016	11
1	E01000005	City of London	Violence Against the Person	Harassment	2011	3
2	E01032739	City of London	Violence Against the Person	Harassment	2016	12
3	E01000005	City of London	Violence Against the Person	Harassment	2011	11
4	E01000005	City of London	Violence Against the Person	Harassment	2014	3
5	E01000002	City of London	Violence Against the Person	Harassment	2011	5
6	E01000005	City of London	Violence Against the Person	Harassment	2011	8
7	E01000005	City of London	Violence Against the Person	Harassment	2013	3
8	E01032740	City of London	Violence Against the Person	Harassment	2009	11
9	E01032739	City of London	Violence Against the Person	Harassment	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

SQL with Big Query and Python

Write A Comment Cancel Reply

Safer Gambling Analytics Part 2 – Session Aggregation

Safer Gambling Analytics Part 1 – Declined Deposits

Kaggle Marketing Analysis – Power BI Dashboard

Starbucks Data Science Blog

SQL with Big Query and Python

Step 1: Installing the BigQuery API Client Library

Step 2: Importing Libraries

Step 3: Importing from SQL Database

Column descriptions

Step 4: Questions

Question 1: What is the change in the number of crime incidents from 2011 to 2016?

Question 2: What were the top 3 crimes per borough in 2016?

References

Related Posts

Write A Comment Cancel Reply