Search⌘ K
AI Features
Home
Courses
Grokking the Data Science Interview

GroupBy, Merge, and Dates

Explore how to use pandas GroupBy and merge functions to create accurate daily active user and revenue tables. Understand the importance of validating join keys, handling duplicates, and parsing dates correctly to avoid inflated or missing metrics. This lesson equips you with strategies to maintain data integrity before computing rolling averages and final metrics.

We'll cover the following...

A preliminary pipeline reports daily revenue per active user and a falling 7-day average, but the reported denominator contains duplicated user-days, and one order date failed to parse. The arithmetic runs without an exception. The metric is still wrong because each transformation changed what one row represented.

For the prompt, events contains event_date, user_id, and event_name; orders contains order_id, user_id, order_date, and revenue. We need three stated intermediate tables:

  • An active-user table at user-day grain, with one row per active user_id and calendar date.

  • A revenue table at day grain, with one row per valid order_date.

  • A daily metric table at day grain, where daily revenue is divided by daily active users.

The final rolling-metric table retains one row per day. Avoid directly merging raw event and order records before calculating the metric. Multiple matching records can duplicate order revenue or inflate activity counts.

As covered in Joins Deep Dive, a successful merge does not guarantee that the resulting data preserves the intended metric. Check the uniqueness of the join keys and the expected relationship between records before relying on the merged results.

Control the grouped table’s grain

Before a merge can be safe, groupby must produce the exact rows the metric needs. In an events table, grouping by event_date and user_id can produce one active user-day. Counting raw event rows instead would measure activity volume, not active users.

as_index=False keeps grouping keys such as event_date and user_id as ordinary columns. That form often makes the next merge easier to inspect. Named aggregations also make the output contract visible. A table with columns event_date, active_users, and event_count is easier to review than a Series with an implicit index.

Missing grouping keys require a policy. By default, groupby excludes rows whose grouping key is missing. If an event has user_id equal to NaN, that event does not contribute to an active-user group unless we request dropna=False. Including the missing-key group can expose a data-quality problem, but it should not automatically enter an active-user denominator. A missing identifier is not a person we can count once.

The following output contrasts the grouped tables when missing user_id values are excluded and when they remain visible as a separate group.

Files
main.py
README.md
requirements.txt
Python 3.14.0
import pandas as pd
events = pd.DataFrame(
    {
        "event_date": pd.to_datetime(
            ["2025-01-01", "2025-01-01", "2025-01-02", "2025-01-02"]
        ),
        # Leave the missing key as NaN (None becomes NaN in a float column) so dropna behavior is visible.
        "user_id": [101, None, 101, 102],
        "event_name": ["view", "view", "purchase", "view"],
    }
)
for dropna in [True, False]:
    for as_index in [True, False]:
        result = events.groupby(
            ["event_date", "user_id"], dropna=dropna, as_index=as_index
        ).agg(
            event_count=("event_name", "size"), unique_events=("event_name", "nunique")
        )
        print(f"dropna={dropna}, as_index={as_index}")
        print(result)
        print("index:", result.index)
        print("columns:", list(result.columns))
        print("rows:", len(result))
        print()

In the main.py file:

  • Lines 3–12: user_id includes a Python None, which pandas stores as NaN in the column; that makes the effect of groupby(..., dropna=...) ...