Pandas resample: make absent days visible

Resample onto an explicit calendar, then use source-coverage evidence to decide whether an empty day means zero or unknown. A missing row in an extract is not proof that no business activity occurred.

Also distinguish an observed zero-value order from an order whose amount is missing. Those cases can look identical after indiscriminate fillna(0), but they support different conclusions.

Define the reporting window and coverage

The pandas quality lab covers January 1–6. January 4 has incomplete source coverage. January 3 contains a known zero-value Paid order. January 6 contains a Paid order with an unknown amount.

The separate coverage.csv is synthetic evidence supplied for the exercise. In a real pipeline, completeness needs a trustworthy source contract, watermark or reconciliation process.

Keep counts, values and coverage together

import pandas as pd
from build_and_verify import ROOT, load_orders

orders = load_orders()
paid = orders.loc[orders['status'].eq('Paid')].set_index('order_date')
coverage = pd.read_csv(ROOT / 'coverage.csv', parse_dates=['date']).set_index('date')
daily = pd.DataFrame({
    'observed_orders': paid['amount_paise'].resample('D').size(),
    'observed_amounts': paid['amount_paise'].resample('D').count(),
    'known_subtotal_paise': paid['amount_paise'].resample('D').sum(min_count=1),
}).reindex(coverage.index)
daily = daily.join(coverage)
daily[['observed_orders', 'observed_amounts']] = daily[['observed_orders', 'observed_amounts']].fillna(0)
complete = daily['source_complete'].eq(1)
all_amounts_known = daily['observed_orders'].eq(daily['observed_amounts'])
daily['reportable_total_paise'] = daily['known_subtotal_paise'].where(complete & all_amounts_known)
daily.loc[complete & daily['observed_orders'].eq(0), 'reportable_total_paise'] = 0
assert daily.loc['2026-01-03', 'reportable_total_paise'] == 0
assert pd.isna(daily.loc['2026-01-04', 'reportable_total_paise'])
assert pd.isna(daily.loc['2026-01-06', 'reportable_total_paise'])
assert daily.loc['2026-01-04', 'observed_orders'] == 0
assert daily.loc['2026-01-06', 'observed_orders'] == 1
assert int(daily['known_subtotal_paise'].sum()) == 57000
print(daily.to_string())

The count columns describe records observed in the extract. On an incomplete day, zero observed records still does not establish zero actual orders. The reportable-total column remains missing until both source coverage and amount completeness support a total.

The resample reference describes time-based grouping. This example uses daily bins over date-only order values and was executed with pandas 3.0.2.

Explain each day's status

January 1 has 10,000 paise, January 2 has 25,000, January 3 has an observed zero and January 5 has 22,000. January 4 is unresolved because the source is incomplete. January 6 is unresolved because its order amount is missing.

The known subtotal across observed amounts is 57,000 paise. A chart may display those known amounts, but it should not label that subtotal as the complete six-day business total.

Use an explicit calendar for the whole period

Resampling observed events typically spans the observed index range. If the reporting period extends before the first event or after the last, reindex to the intended calendar. Otherwise, leading or trailing absent days may never appear.

This example uses the coverage table as that calendar. Validate that it contains exactly one record per date and covers the promised window before relying on it in a larger workflow.

Do not interpolate transactions by default

Forward filling a daily sales amount copies the previous day's result; linear interpolation invents intermediate values. Those techniques can be appropriate for some measured signals under explicit assumptions, but they are not automatic repairs for missing transaction extracts.

For a line chart, preserve gaps or show an explicit incomplete marker. A smooth line can visually imply continuity that the data does not establish.

Test the distinction deliberately

Exercise: mark January 4 complete in a copied coverage table while keeping its observed order count at zero. Under that hypothetical evidence, its reportable total should become zero. January 6 should remain unknown until its missing amount is resolved. Explain why changing source completeness cannot repair an unknown measure.

NeuraPath's Data Analytics with Generative AI course connects pandas time series with reporting completeness. A dependable daily chart distinguishes quiet business days from missing evidence.

Continue learning

This article is part of the Pandas wrangling and data checks sequence. Use the neighbouring tasks when you need the prerequisite or the next application.

• Review the prerequisite or neighbouring task in Read a large CSV in chunks with consistent aggregates.
• Continue with Calculate customer cohorts in pandas and reconcile with SQL.