Why doesn't a month with no sales show up when I group?

August in Coyoacán

The Coyoacán shop closed for all of August 2025 for a remodel. In ventas_diarias there isn't a single Coyoacán row in August: nobody recorded sales of 0, the rows just aren't there. On top of that, all three shops close on January 1 and December 25.

Management asks how much Coyoacán sells per month, on average, in 2025. Predict what happens with a small case first.

A shop that was open in July and September and closed in August, with 1,000 pesos in sales every day it was open. (The code's comment and labels are in Spanish: the comment says the shop closed in August, and promedio mensual means monthly average.) Write what you think it prints and why.

Sales by month and the average

promedio.py

import pandas as pd

dias = pd.date_range("2025-07-01", "2025-09-30", freq="D")
dias = dias[dias.month != 8]  # la sucursal cerró agosto
ventas = pd.DataFrame({"fecha": dias, "ventas": 1000})

por_mes = ventas.groupby(ventas["fecha"].dt.to_period("M"))["ventas"].sum()
print(por_mes)
print("promedio mensual:", por_mes.mean())

How many months do you think come out, and what's the monthly average?

Why?

A guess is fine: it's kept as you wrote it, so you can compare. Only you and your Maestro see your predictions.

Make your prediction

What the table doesn't tell you

A month with no rows isn't a month with 0. As far as GROUP BY is concerned, it doesn't exist. With Coyoacán in 2025 it happens at full scale: 11 months come out, adding up to 4,017,120 pesos. The average of those 11 months is 365,193 pesos. Divided by all 12 months of the year, it's 334,760.

Which one is right? It depends on the question, and that's your call, not something the missing rows should decide for you:

If the question is how much the shop brought in per month in 2025, August counts as 0 and the average is 334,760.
If the question is how much the shop sells in a month when it's open, August doesn't count and the average is 365,193. But then say so in the report.

Days work the same way: in 2025, Roma and Del Valle have 363 rows, not 365, and Coyoacán has 332. Not sure which average fits your case? Talk it over with your Maestro.

A calendar table

To make the month show up, you need a table with every period, whether there were sales or not: a calendar. Then you attach your data to the calendar and put 0 wherever there's nothing. The chart below shows why it matters: without a calendar, Coyoacán's line connects July straight to September and the August closure disappears.

Try it

The code builds Coyoacán's monthly sales and charts them. Add a calendar with pd.date_range("2025-01-01", "2026-06-01", freq="MS") (the first day of each month) and use reindex with fill_value=0 so August shows up. You're done when the line drops to 0 in August 2025. If reindex leaves everything at 0, ask your Maestro.

Your turn

Add a calendar with every month from January 2025 to June 2026 and fill the ones with no sales with 0, so the line shows the August closure.

Your code (Python)

The chart redraws when you pause typing, or right away with Run. Ctrl+Enter runs it. Tab indents; to leave the editor, press Escape, then Tab.

Ready to run your code.

What the chart shows

Line chart of Coyoacán's monthly sales from January 2025 to June 2026, between about 320,000 and 410,000 pesos. With August 2025 missing from the data, the line goes straight from July to September and the closure doesn't show.

Data: ventas-diarias, 1,598 rows

Enter saves a cell and Escape leaves it as it was. Leave a cell empty for a missing value.

The exercise's data, "ventas-diarias". Your edits run with your code.
Row				Remove
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20

Page 1 of 80

Run it yourself

The calendar is pd.date_range, and attaching it is reindex:

Python

calendario = pd.date_range("2025-01-01", "2025-12-01", freq="MS")
por_mes = por_mes.reindex(calendario, fill_value=0)

Heads-up: resample("MS").sum() on a series with a date index also creates the empty months, but only between the first and the last month that have data. An empty month at the start or the end won't appear.

The calendar comes from generate_series, and attaching it is a LEFT JOIN from the calendar, so every one of its months stays:

SQL

WITH calendario AS (
  SELECT mes::DATE AS mes
  FROM generate_series(DATE '2025-01-01', DATE '2025-12-01', INTERVAL 1 MONTH) AS t(mes)
)
SELECT c.mes, coalesce(p.ventas, 0) AS ventas
FROM calendario c
LEFT JOIN por_mes p ON p.mes = c.mes;

coalesce puts 0 wherever the LEFT JOIN found no sales.

generate_series for dates (DuckDB documentation)

DataFrame.reindex and fill_value (pandas documentation)

Try it in DuckDB

The query already has the 2025 calendar and Coyoacán's monthly sales, each in its own WITH. All that's left is to attach them: change the final SELECT so all 12 months come out, with 0 in August. A good answer has 12 rows, from 2025-01-01 to 2025-12-01, with August at 0.

Your turn

Join the 2025 calendar with Coyoacán's monthly sales so all 12 months come out, with sales of 0 where there were no rows.

Tables: ventas_diarias (fecha, sucursal, ventas)

ventas_diarias · 1,598 rows

Column	Type
fecha El día de la venta.	DATE
sucursal Roma, Coyoacán o Del Valle.	VARCHAR
ventas Ventas del día en pesos.	BIGINT

First rows

fecha	sucursal	ventas
2025-01-02	Roma	12120
2025-01-02	Coyoacán	10650
2025-01-02	Del Valle	8880

Your query (SQL)

Every run starts from fresh copies of the course's tables, so try anything. Tab indents; to leave the editor, press Escape, then Tab.

Ready to run your query.

Run it yourself

Check yourself: Without a calendar, Coyoacán's 2025 monthly average comes out at 365,193 pesos. With August at 0, it comes out at 334,760. Why is the first one higher?

If you later want to compare each month with the one before, a missing month breaks that too: comparing against the previous row isn't the same as comparing against the previous month. The free window functions course covers this on its page about missing months.

What if a month is missing? (Window functions in SQL, in Spanish)