BigQuery & SQL¶

BigQuery is Google's serverless data warehouse that enables scalable analysis
over petabytes of data. It is a Platform as a Service (PaaS) that supports querying
using SQL. In this part of the midterm you'll be asked to query BigQuery tables
using its python API. Please use the sandbox BigQuery environment and query
only public datasets.

We will utilize two datasets about New-York City:

The first one called new_york_311 is the 311 calls or service requests dataset, which contains resident complaints from 2010 to 2022.

The second dataset called new_york_trees is the "2015 Street Tree Census - Tree Data," which includes information from the 1995, 2005, and 2015 Street Tree Censuses. This dataset catalogs trees by address and provides details such as species, diameter, and condition.

More information on the two datasets can be found here and here.

import pandas as pd
import matplotlib as plt
from google.cloud import bigquery
from sqlite3 import connect

BigQuery client & Initital Query¶

from google.colab import auth
auth.authenticate_user()
print('Authenticated')

Authenticated

client = bigquery.Client(project="part-one-mid-term")

Displaying five columns of the two datasets in order to check the connection.

# the SQL query
initital_query = """
SELECT tree_id, spc_common,spc_latin, health, sidewalk
FROM `bigquery-public-data.new_york_trees.tree_census_2015`
WHERE tree_id IS NOT NULL AND spc_common IS NOT NULL AND health IS NOT NULL
 AND spc_latin IS NOT NULL AND sidewalk IS NOT NULL
LIMIT 10
"""
# Execute it
initital_query_job = client.query(initital_query)
# Convert to a Pandas DataFrame
df = initital_query_job.to_dataframe()
# Display the results
df.head(10)

	tree_id	spc_common	spc_latin	health	sidewalk
0	80549	pagoda dogwood	Cornus alternifolia	Fair	Damage
1	451320	northern red oak	Quercus rubra	Fair	NoDamage
2	80547	Callery pear	Pyrus calleryana	Good	Damage
3	451259	pin oak	Quercus palustris	Good	NoDamage
4	451258	pin oak	Quercus palustris	Good	NoDamage
5	451316	ash	Fraxinus	Good	Damage
6	451321	northern red oak	Quercus rubra	Poor	Damage
7	451315	honeylocust	Gleditsia triacanthos var. inermis	Good	NoDamage
8	451317	honeylocust	Gleditsia triacanthos var. inermis	Good	NoDamage
9	451318	American linden	Tilia americana	Good	NoDamage

The query retrieves meaningful data from the dataset bigquery-public-data.new_york_trees.tree_census_2015 by selecting five columns: tree_id, spc_common, spc_latin, health, and sidewalk. To ensure the data is useful, a WHERE clause filters out rows where any of these columns contain NULL values. This guarantees that all rows in the result have complete information for the selected columns. The LIMIT 10 clause restricts the output to only 10 rows.

The resulting table provides a clear and informative view of the data, showing details of 10 individual trees. Each row represents a unique tree identified by its tree_id, along with its common name (spc_common), scientific name (spc_latin), health status (health), and sidewalk condition (sidewalk) which indicate whether the tree has caused damage to its surroundings or not.

Exploratory Data Analysis¶

# the SQL query
q2a_query = """
SELECT
    COUNT(DISTINCT tree_id) AS total_tree_count,
    COUNT(DISTINCT CASE WHEN health IS NOT NULL AND health != 'Poor' THEN tree_id END) AS healthy_tree_count
FROM bigquery-public-data.new_york_trees.tree_census_2015;
"""
q2a_query_job = client.query(q2a_query)
df_q2a = q2a_query_job.to_dataframe()
df_q2a

	total_tree_count	healthy_tree_count
0	683788	625354

total_tree_count = df_q2a.loc[0, 'total_tree_count']
total_healthy_tree_count = df_q2a.loc[0, 'healthy_tree_count']

We can see that there are 683788 trees in New York in 2015, and 625345 of them were healthy.

# Fill query here inside triple quotes and display results:
q2b_query = """
WITH tree_stats AS (
    SELECT
        spc_latin,
        COUNT(*) AS total_trees,
        SUM(CASE WHEN health = 'Good' THEN 1 ELSE 0 END) AS healthy_trees
    FROM bigquery-public-data.new_york_trees.tree_census_2015
    WHERE spc_latin IS NOT NULL AND health IS NOT NULL
    GROUP BY spc_latin
)
SELECT
    spc_latin,
    total_trees,
    healthy_trees,
    ROUND(healthy_trees * 100.0 / total_trees, 2) AS percent_healthy
FROM tree_stats
ORDER BY total_trees DESC
LIMIT 10;
"""

q2b_query_job = client.query(q2b_query)
df_q2b = q2b_query_job.to_dataframe()
df_q2b

	spc_latin	total_trees	healthy_trees	percent_healthy
0	Platanus x acerifolia	87014	73311	84.25
1	Gleditsia triacanthos var. inermis	64263	54501	84.81
2	Pyrus calleryana	58931	48073	81.58
3	Quercus palustris	53185	45556	85.66
4	Acer platanoides	34189	21248	62.15
5	Tilia cordata	29742	23582	79.29
6	Prunus	29279	24526	83.77
7	Zelkova serrata	29258	25285	86.42
8	Ginkgo biloba	21024	17126	81.46
9	Styphnolobium japonicum	19338	15832	81.87

These are the ten most common tree species in New York. With the number of trees for each species, the number of healthy trees and the percentage of healthy trees within each species. We excluded None species.

# Your answer code with queries here:
q2c_query = """
WITH zip_tree_stats AS (
    SELECT
        zipcode,
        COUNT(*) AS total_trees,
        SUM(CASE WHEN health = 'Good' THEN 1 ELSE 0 END) AS good_trees,
        SUM(CASE WHEN health = 'Fair' THEN 1 ELSE 0 END) AS fair_trees,
        SUM(CASE WHEN health = 'Poor' THEN 1 ELSE 0 END) AS poor_trees
    FROM bigquery-public-data.new_york_trees.tree_census_2015
    WHERE zipcode IS NOT NULL AND health IS NOT NULL
    GROUP BY zipcode
)
SELECT
    zipcode,
    poor_trees,
    fair_trees,
    good_trees,
    total_trees,
    ROUND(good_trees * 100.0 / total_trees, 2) AS percent_good,
    ROUND(fair_trees * 100.0 / total_trees, 2) AS percent_fair,
    ROUND(poor_trees * 100.0 / total_trees, 2) AS percent_poor
FROM zip_tree_stats
ORDER BY total_trees DESC;
"""

q2c_query_job = client.query(q2c_query)
df_q2c = q2c_query_job.to_dataframe()
df_q2c.head()

	zipcode	poor_trees	fair_trees	good_trees	total_trees	percent_good	percent_fair	percent_poor
0	10312	1071	3594	16691	21356	78.16	16.83	5.01
1	10314	662	2340	13328	16330	81.62	14.33	4.05
2	10306	369	1801	10446	12616	82.80	14.28	2.92
3	10309	593	1623	9889	12105	81.69	13.41	4.90
4	11234	341	1270	9227	10838	85.14	11.72	3.15

The table shows the results for the five ZIP codes with the highest number of total trees.

# Plot histograms for the health status percentages
plt.pyplot.figure(figsize=(6, 4))
# Histograms
plt.pyplot.hist(df_q2c['good_trees'], bins=10, alpha=0.7, label='Good', color='green', edgecolor='black')
plt.pyplot.hist(df_q2c['fair_trees'], bins=10, alpha=0.7, label='Fair', color='orange', edgecolor='black')
plt.pyplot.hist(df_q2c['poor_trees'], bins=10, alpha=0.7, label='Poor', color='red', edgecolor='black')
plt.pyplot.title('Distribution of Tree Health Percentages Across ZIP Codes', fontsize=16)
plt.pyplot.xlabel('Number of trees', fontsize=14)
plt.pyplot.ylabel('Frequency', fontsize=14)
plt.pyplot.legend(title='Health Status', fontsize=12)
plt.pyplot.grid(axis='y', linestyle='--', alpha=0.7)
plt.pyplot.tight_layout()
plt.pyplot.show()

No description has been provided for this image

The histogram shows the distribution of the number of trees by health status (Good, Fair, and Poor) across various ZIP codes. Most ZIP codes have a relatively small number of trees, with the majority of these trees being in "Good" health, as indicated by the green bars. Trees in "Fair" and "Poor" health, represented by orange and red bars respectively, occur less frequently across ZIP codes. The distribution shows that only a few ZIP codes having a very large number of trees.

Changes Over Time¶

q3a1_query = """
CREATE OR REPLACE TABLE `part-one-mid-term.dataset_part1midterm.tree_counts_1995` AS
SELECT
  UPPER(spc_latin) AS species,
  COUNT(*) AS tree_count_1995
FROM `bigquery-public-data.new_york_trees.tree_census_1995`
WHERE spc_latin IS NOT NULL
GROUP BY UPPER(spc_latin);
"""
client.query(q3a1_query).result()

q3a2_query = """
CREATE OR REPLACE TABLE `part-one-mid-term.dataset_part1midterm.tree_counts_2015` AS
SELECT
  UPPER(spc_latin) AS species,
  COUNT(*) AS tree_count_2015
FROM `bigquery-public-data.new_york_trees.tree_census_2015`
WHERE spc_latin IS NOT NULL
GROUP BY UPPER(spc_latin);
"""

client.query(q3a2_query).result()

q3a_query = """
-- Join the two tables and calculate the change
SELECT
  t95.species AS species_from_1995,
  t15.species AS species_from_2015,
  t95.tree_count_1995,
  t15.tree_count_2015,
  (t15.tree_count_2015 - t95.tree_count_1995) AS change_in_tree_count
FROM `part-one-mid-term.dataset_part1midterm.tree_counts_1995` t95
CROSS JOIN `part-one-mid-term.dataset_part1midterm.tree_counts_2015` t15
WHERE EDIT_DISTANCE(t95.species, t15.species) <= 2
ORDER BY ABS(t15.tree_count_2015 - t95.tree_count_1995) DESC
LIMIT 5;
"""
q3a_query_job = client.query(q3a_query)
df_q3a = q3a_query_job.to_dataframe()
df_q3a.head()

	species_from_1995	species_from_2015	tree_count_1995	tree_count_2015	change_in_tree_count
0	ACER PLATANOIDES	ACER PLATANOIDES	109325	34189	-75136
1	PYRUS CALLERYANA	PYRUS CALLERYANA	31295	58931	27636
2	ZELKOVA SERRATA	ZELKOVA SERRATA	5740	29258	23518
3	ACER SACCHARINUM	ACER SACCHARUM	22347	2844	-19503
4	QUERCUS PALUSTRIS	QUERCUS PALUSTRIS	36553	53185	16632

This table shows the number of the 5 most species with the changes in number from 1995 till 2015.

q3b1_query = """
CREATE OR REPLACE TABLE `part-one-mid-term.dataset_part1midterm.tree_counts_1995` AS
SELECT
  UPPER(c95.spc_latin) AS species_1995,
  UPPER(ts.species_scientific_name) AS species_tree_species,
  IFNULL(ts.fall_color, 'Unknown') AS fall_color,
  COUNT(*) AS tree_count_1995
FROM `bigquery-public-data.new_york_trees.tree_census_1995` c95
CROSS JOIN `bigquery-public-data.new_york_trees.tree_species` ts
WHERE EDIT_DISTANCE(UPPER(c95.spc_latin), UPPER(ts.species_scientific_name)) <= 2
GROUP BY species_1995, species_tree_species, fall_color;
"""

q3b2_query = """
CREATE OR REPLACE TABLE `part-one-mid-term.dataset_part1midterm.tree_counts_2015` AS
SELECT
  UPPER(c15.spc_latin) AS species_2015,
  UPPER(ts.species_scientific_name) AS species_tree_species,
  IFNULL(ts.fall_color, 'Unknown') AS fall_color,
  COUNT(*) AS tree_count_2015
FROM `bigquery-public-data.new_york_trees.tree_census_2015` c15
CROSS JOIN `bigquery-public-data.new_york_trees.tree_species` ts
WHERE EDIT_DISTANCE(UPPER(c15.spc_latin), UPPER(ts.species_scientific_name)) <= 2
GROUP BY species_2015, species_tree_species, fall_color
;
"""

q3b3_query = """
CREATE OR REPLACE TABLE `part-one-mid-term.dataset_part1midterm.combined_tree_counts` AS
SELECT
  t95.species_1995 As species_1995,
  t15.species_2015 As species_2015,
  t95.fall_color AS fall_color_1995,
  t15.fall_color AS fall_color_2015,
  t95.tree_count_1995 AS Number_of_trees_1995,
  t15.tree_count_2015 AS Number_of_trees_2015
FROM `part-one-mid-term.dataset_part1midterm.tree_counts_1995` t95
CROSS JOIN `part-one-mid-term.dataset_part1midterm.tree_counts_2015` t15
WHERE EDIT_DISTANCE(t95.species_1995, t15.species_2015) <= 2 AND t95.fall_color = t15.fall_color;
"""

q3b4_query = """
CREATE OR REPLACE TABLE `part-one-mid-term.dataset_part1midterm.total_tree_counts` AS
SELECT
  (SELECT SUM(tree_count_1995) FROM `part-one-mid-term.dataset_part1midterm.tree_counts_1995`) AS total_1995,
  (SELECT SUM(tree_count_2015) FROM `part-one-mid-term.dataset_part1midterm.tree_counts_2015`) AS total_2015;
"""

q3b5_query = """
SELECT
  COALESCE(com_tree.fall_color_1995, com_tree.fall_color_2015) AS fall_color,
  ROUND(SAFE_DIVIDE(SUM(IFNULL(com_tree.Number_of_trees_1995, 0)), total_tree_counts.total_1995) * 100, 2) AS percent_1995,
  ROUND(SAFE_DIVIDE(SUM(IFNULL(com_tree.Number_of_trees_2015, 0)), total_tree_counts.total_2015) * 100, 2) AS percent_2015,
  ROUND(
    ROUND(SAFE_DIVIDE(SUM(IFNULL(com_tree.Number_of_trees_2015, 0)), total_tree_counts.total_2015) * 100, 2)
    - ROUND(SAFE_DIVIDE(SUM(IFNULL(com_tree.Number_of_trees_1995, 0)), total_tree_counts.total_1995) * 100, 2),2)
     AS percent_difference
FROM `part-one-mid-term.dataset_part1midterm.combined_tree_counts` com_tree
CROSS JOIN `part-one-mid-term.dataset_part1midterm.total_tree_counts` total_tree_counts
GROUP BY fall_color, total_tree_counts.total_1995, total_tree_counts.total_2015
ORDER BY ABS(percent_difference) DESC;
"""

q3b1_query_job = client.query(q3b1_query).result()
q3b2_query_job = client.query(q3b2_query).result()
#df_q3b2 = q3b2_query_job.to_dataframe()
#df_q3b2.head()
q3b3_query_job = client.query(q3b3_query).result()
q3b4_query_job = client.query(q3b4_query).result()
q3b5_query_job = client.query(q3b5_query).result()
df_q3b5 = q3b5_query_job.to_dataframe()
df_q3b5.head()

	fall_color	percent_1995	percent_2015	percent_difference
0	Yellow	61.85	45.23	-16.62
1	Red/Bronze	2.16	5.72	3.56
2	Red	7.10	3.83	-3.27
3	Maroon	28.12	24.92	-3.20
4	Orange/Brown	0.01	0.84	0.83

This table show the 5 highest absolute value of the difference between percent of trees in 1995, the percent of trees in 2015 in fall foliage color.

Broken Windows Theory¶

The Broken Windows Theory suggests that poor condition of city areas may affect crime levels. In this section, we’ll look for a correlation between the proportion of dead trees (relative to total trees) and the number of NYPD service requests in different zip codes.

q4a1_query = """
SELECT
  complaint_type,
  COUNT(*) AS request_count
FROM `bigquery-public-data.new_york_311.311_service_requests`
WHERE agency = 'NYPD' -- Filter only for NYPD service requests
GROUP BY complaint_type
ORDER BY request_count DESC;
"""
q4a1_query_job = client.query(q4a1_query).result()
df_q4a1 = q4a1_query_job.to_dataframe()
df_q4a1.head()

	complaint_type	request_count
0	Noise - Residential	2540450
1	Illegal Parking	1376331
2	Blocked Driveway	1165274
3	Noise - Street/Sidewalk	854517
4	Noise - Commercial	440092

This table show the top 5 reasons for complains, the first one is noise complaints which may indicate disturbances or disorder, but it is not direct indicators of criminal activity. Second one is Illegal Parking which relates to traffic violations, but not crimes. The third one is Blocked Driveway which also does not indecate about crimes. The forth and fifth are Noise - Street/Sidewalk and Noise - Commercial and they are also not a strong indicators about crime. So we can use them as an indirect measure of crime trends.

# Define the query
q4a2_query = """
-- CREATE OR REPLACE TABLE `part-one-mid-term.dataset_part1midterm.dead_tree_proportion` AS
SELECT
  zipcode,
  COUNT(*) AS total_trees,
  SUM(CASE WHEN status IN ('Standing Dead', 'Stump') THEN 1 ELSE 0 END) AS dead_trees,
  ROUND(SUM(CASE WHEN status IN ('Standing Dead', 'Stump') THEN 1 ELSE 0 END) / COUNT(*) * 100, 2) AS dead_tree_proportion
FROM `bigquery-public-data.new_york_trees.tree_census_2015`
WHERE zipcode IS NOT NULL
GROUP BY zipcode
HAVING COUNT(*) >= 100
"""
q4a3_query = """
-- CREATE OR REPLACE TABLE `part-one-mid-term.dataset_part1midterm.nypd_requests_normalized` AS
SELECT
  SAFE_CAST(incident_zip AS INT64) AS zipcode,
  COUNT(CASE WHEN complaint_type NOT LIKE 'Noise%' THEN 1 END) AS non_noise_requests,
  COUNT(*) AS total_requests,
  ROUND(
     SAFE_DIVIDE(COUNT(CASE WHEN complaint_type NOT LIKE 'Noise%' THEN 1 END),
     COUNT(*)) * 100, 2) AS normalized_non_noise_percentage
FROM `bigquery-public-data.new_york_311.311_service_requests`
WHERE   agency = 'NYPD' AND SAFE_CAST(incident_zip AS INT64) IS NOT NULL
GROUP BY zipcode;
"""
q4a2_query_job = client.query(q4a2_query)
q4a3_query_job = client.query(q4a3_query)

# Convert the result to a Pandas DataFrame
result_df_q4a2 = q4a2_query_job.to_dataframe()
result_df_q4a3 = q4a3_query_job.to_dataframe()

# Merge
combined_df = result_df_q4a2.merge(result_df_q4a3, on='zipcode')
combined_df = combined_df[['zipcode', 'dead_tree_proportion', 'non_noise_requests']]
plt.pyplot.figure(figsize=(6, 4))
plt.pyplot.scatter(combined_df['dead_tree_proportion'], combined_df['non_noise_requests'], alpha=0.7)
plt.pyplot.title('Proportion of dead trees vs. NYPD Non-Noise Requests')
plt.pyplot.xlabel('Proportion of dead trees %')
plt.pyplot.ylabel('Number of Non-Noise NYPD Requests')
plt.pyplot.grid(True)
plt.pyplot.show()

x = combined_df['dead_tree_proportion'].tolist()
y = combined_df['non_noise_requests'].tolist()
mean_x = sum(x) / len(x)
mean_y = sum(y) / len(y)

# Pearson
numerator = sum((xi - mean_x) * (yi - mean_y) for xi, yi in zip(x, y))
denominator_x = sum((xi - mean_x) ** 2 for xi in x)
denominator_y = sum((yi - mean_y) ** 2 for yi in y)
denominator = (denominator_x * denominator_y) ** 0.5
if denominator != 0:
    correlation = round(numerator / denominator,3)
else:
    correlation = 0

print(f"Pearson Correlation: {correlation}")

Pearson Correlation: 0.101

The low correlation of 0.101 suggests that the proportion of dead trees and the number of non-noise NYPD service requests are mostly independent.

q5a1_query = """
SELECT
  agency,
  COUNT(*) AS tree_related_complaints
FROM  `bigquery-public-data.new_york_311.311_service_requests`
WHERE  complaint_type LIKE '%Tree%'
GROUP BY  agency
ORDER BY  tree_related_complaints DESC;
"""
q5a1_query_job = client.query(q5a1_query).result()
df_q5a1 = q5a1_query_job.to_dataframe()
df_q5a1.head()

	agency	tree_related_complaints
0	DPR	899528
1	DSNY	1986
2	NYPD	2
3	ACS	1
4	DOHMH	1

This table shows the top 5 agencies with the most commplains which related to trees.

q5b1_query = """
SELECT
  complaint_type,
  COUNT(*) AS complaint_count
FROM `bigquery-public-data.new_york_311.311_service_requests`
WHERE complaint_type LIKE '%Tree%' AND agency = 'DPR'
GROUP BY complaint_type
ORDER BY complaint_count DESC
LIMIT 3;
"""
q5b1_query_job = client.query(q5b1_query).result()
df_q5b1 = q5b1_query_job.to_dataframe()
df_q5b1.head()

	complaint_type	complaint_count
0	Damaged Tree	387695
1	New Tree Request	183445
2	Overgrown Tree/Branches	178582

This table show the top 3 complaint types which related to trees from the database.

q5b1_query = """
SELECT  EXTRACT(YEAR FROM created_date) AS year, complaint_type, COUNT(*) AS complaint_count
FROM `bigquery-public-data.new_york_311.311_service_requests`
WHERE complaint_type LIKE '%Tree%' AND agency = 'DPR' AND (complaint_type ='Damaged Tree' OR complaint_type = 'New Tree Request' OR complaint_type ='Overgrown Tree/Branches')
Group by year, complaint_type
ORDER BY year, complaint_count DESC
"""
q5b1_query_job = client.query(q5b1_query).result()
df_q5b1 = q5b1_query_job.to_dataframe()
df_q5b1.head()

	year	complaint_type	complaint_count
0	2010	Damaged Tree	39178
1	2010	Overgrown Tree/Branches	14968
2	2010	New Tree Request	9859
3	2011	Damaged Tree	32104
4	2011	Overgrown Tree/Branches	13482

pivot_df = df_q5b1.pivot(index="year", columns="complaint_type", values="complaint_count")
pivot_df.plot(kind="bar", stacked=False, figsize=(6, 5), width=0.8)
plt.pyplot.title("Tree-Related Complaints by Type and Year (DPR)", fontsize=14)
plt.pyplot.xlabel("Year", fontsize=12)
plt.pyplot.ylabel("Number of Complaints", fontsize=12)
plt.pyplot.legend(title="Complaint Type", fontsize=10)
plt.pyplot.xticks(rotation=0)
plt.pyplot.tight_layout()
plt.pyplot.show()

This chart displays the number of tree-related complaints received by the DPR agency. The top three complaint categories include damaged trees, new tree requests, and overgrown trees or branches. It provides a comparison of these three complaint types across the years from 2010 to 2021.

BigQuery & SQL¶

BigQuery client & Initital Query¶

Exploratory Data Analysis¶

Changes Over Time¶

Broken Windows Theory¶

Tree-Related Complaints¶