Joining, filtering, and aggregating
In this exercise, you'll use what you've learned to assemble a dataset to investigate how the number of heating complaints to New York City's 311 line varies with temperature.
In addition to the hpd311calls table, data.db has a weather table with daily high and low temperature readings for NYC. We want to get each day's count of heat/hot water calls with temperatures joined in. This can be done in one query, which we'll build in parts.
In part one, we'll get just the data we want from hpd311calls. Then, in part two, we'll modify the query to join in weather data.
pandas has been imported as pd, and the database engine has been created as engine.
This exercise is part of the course
Streamlined Data Ingestion with pandas
Hands-on interactive exercise
Have a go at this exercise by completing this sample code.
# Query to get heat/hot water call counts by created_date
query = """
SELECT hpd311calls.____,
____
FROM hpd311calls
____ hpd311calls.____ = ____
____ hpd311calls.____;
"""
# Query database and save results as df
df = ____
# View first 5 records
print(df.head())