Get startedGet started for free

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

View Course

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())
Edit and Run Code