Counting in groups

In previous exercises, you pulled data from tables, then summarized the resulting dataframes in pandas to create graphs. By using COUNT and GROUP BY in a SQL query, we can pull those summary figures from the database directly.

The hpd311calls table has a column, complaint_type, that categorizes call records by issue, such as heating or plumbing. In order to graph call volumes by issue, you'll write a SQL query that COUNTs records by complaint type.

pandas has been imported as pd, and the database engine for data.db has been created as engine.

Este ejercicio forma parte del curso

Streamlined Data Ingestion with pandas

Ver curso

Instrucciones del ejercicio

Create a SQL query that gets the complaint_type column and counts of all records from hpd311calls, grouped by complaint_type.
Create a dataframe with read_sql() of call counts by issue, calls_by_issue.
Run the last section of code to graph the number of calls for each housing issue.

Ejercicio interactivo práctico

Prueba este ejercicio completando el código de muestra.

# Create query to get call counts by complaint_type
query = """
____ ____, 
     ____(*)
  FROM hpd311calls
  ____ ____;
"""

# Create dataframe of call counts by issue
calls_by_issue = pd.read_sql(____, ____)

# Graph the number of calls for each housing issue
calls_by_issue.plot.barh(x="complaint_type")
plt.show()

Editar y ejecutar código