Where we start
The file is the one you downloaded for the homework: 2024-03-04-15.json.gz, one hour of GH Archive, 279,130 events. Last session it went through a validator line by line. Tonight it goes into a DataFrame, because the questions have changed from "is each event well formed" to "what does the whole hour look like", and those are grouping questions. Every number on this page is printed by the code in the build-along, on this file, and the ones that are sketches say so.
★ What you walk out with tonight. A flattened frame of one real hour, a written bot rule and the 15.5 percent it removes, the shape of the actor distribution (median 1, mean 3.75, max 25,458), a 30-minute sessionizer that yields 76,584 sessions from 74,082 human actors, and the reason created_at alone can never order this stream.
What one hour looks like
5 min liveBefore grouping anything, look at the raw shape: how many rows, which columns, and what mix of event types. GH Archive gives you 8 raw columns and 279,130 rows for this hour, and the mix is lopsided in a way most product streams share. One event type, PushEvent, is 56.2 percent of everything. Six types together cover more than nine tenths. The moment you see that, you know a naive "events per user" number is really "pushes per user" wearing a general name.
LiveFlatten first, then look3 min▶
Each line of the file is one nested JSON object: actor is itself a dict with an id and a login, repo another, payload a third whose keys depend on the type. pandas reads the file in one call, but a column of dicts cannot be grouped. The first move is to pull out the handful of scalar fields you will actually group by: a UTC timestamp, the type, the actor login and id, the repository name. Five flat columns. Everything else stays in the raw frame for when a question needs it.
Then three cheap looks before any metric: the row count, the column list, and value_counts on type. On this hour that already tells you the stream is a push stream with other things mixed in. Your own product's frame will have the same skew somewhere, and it is worth finding before someone builds a chart on "total events".
Self-studyWhere the hidden moments still hide2 min▶
The type mix hides the same trap session 1 met in the validator. CreateEvent is 14.2 percent of the hour, but on the tracking-plan pass only 10,384 of its 39,497 events were repository births; the rest were branches and tags. A bar chart by raw type therefore overstates "new projects" by a factor near four. When a type's share looks surprisingly large, open its payload and count the sub-kinds before believing the bar.
The org column is worth a glance too: it exists only when a repository belongs to an organisation, so it is missing on most rows. pandas will show it as NaN. That is a legitimate "does not apply", not a data-quality fault, and it is the kind of absence a plan should document per property.
The frame that would not group. A common pattern: an analyst loads a product export where every row's properties is a dict, tries groupby("properties"), gets an unhashable-type error, and spends an afternoon on it. The fix is always the same as tonight's step 2: decide which three to six scalar fields the questions need, flatten only those, and leave the nested column alone. A frame is for grouping; the raw JSON is for looking things up.
Who wrote the diary
6 min live279,130 events came from 74,491 actors, and the split is nothing like even. The median actor has 1 event in the hour; the mean is 3.75; the maximum is 25,458. 52.1 percent of actors appear exactly once. The top 1 percent of actors, 744 accounts, produce 38.7 percent of the events. And 409 of the logins end in [bot], which is GitHub's own suffix for apps, and those 409 wrote 15.5 percent of the hour. The busiest login of all does not carry the suffix.
LiveThe bot rule is a decision, so write it down3 min▶
GitHub makes the easy part easy: apps act under logins ending in [bot], so str.endswith("[bot]") finds 409 actors and removes 15.5 percent of the hour in one line. The top five logins show both halves of the problem. Three are declared bots: github-actions[bot] 19,528 events, dependabot[bot] 8,962, renovate[bot] 6,769. One, SolukGece at 3,033, has no suffix and no obvious tell. And the busiest of all, censameesss at 25,458 events in sixty minutes, has no suffix either. Nobody types 25,458 actions in an hour. A login without the suffix can still be automation.
So the rule has two parts, and both belong in the code with a comment: the suffix rule, which is safe and exact, and a behaviour rule, which is a judgment. Whichever threshold you choose for the second part, print the count of actors and the share of events it removes on every run. A bot rule nobody can see is a metric nobody can audit.
Self-studyMedian 1, mean 3.75: which one goes in the deck3 min▶
Both, with labels, and neither alone. The mean of 3.75 is pulled up by a right tail so long that one account has 25,458 events, 25,458 times the median. The median of 1 says what a typical actor did, which is: one thing. The 52.1 percent who appear once are not a quality problem; they are what a public activity stream looks like in any single hour, since most people are not active in any given sixty minutes. The mistake is reporting "3.75 events per active user" as if it described a person. It describes a mixture of people and machines.
The top-1-percent figure is the honest summary of concentration: 744 accounts, 38.7 percent of events. When a leader asks how engaged "our users" are, this is the number that stops the conversation from being about an average that no single user resembles. Session 5 builds the full curve; tonight the one number is enough to change the question.
The monitor that became a power user. A common pattern: an uptime checker hits a product's login page every minute from a service account nobody flagged. In the weekly review it is the most engaged "customer" in the account, its sessions are perfectly regular, and it never churns. The fix is not smarter analytics; it is the sentence in the bot rule that says "service accounts are excluded, here is the list", reviewed when the list changes.
Where one visit ends
4 min liveEvents are points; a visit is a stretch. The standard way to turn one into the other is an inactivity gap: sort each actor's events by time, and wherever the gap to the previous event is longer than a threshold, start a new session. Thirty minutes is the common default, inherited from web analytics, and it is a choice, not a law. On this hour, for the 74,082 human actors, the 30-minute rule yields 76,584 sessions, median 1 event per session, mean 3.08, and 2,502 actors who came back for a second session inside the same sixty minutes.
LiveThe tie a timestamp cannot break2 min▶
GH Archive records created_at to the second, and this hour has so many events that every single distinct timestamp is shared by 2 or more events. Sort by created_at alone and the order within a second is whatever pandas happened to leave it in, which is not stable and not meaningful. For a funnel, "did the push come before the pull request" can flip between two runs on the same data.
The fix is a second sort key that is unique and monotone. GitHub's event id is both, so sort_values(["actor_id", "ts", "id"]) gives a stable order. Your own product's stream needs the same thing: a sequence number, an ingest offset, or an id that increases. If it has none, say so in the definition, because any step-order claim is then partly decided by row order.
Self-studyThirty minutes is inherited, not derived2 min▶
The 30-minute gap comes from web analytics tooling and survived because it is a round number, not because anyone measured it for your product. For a developer pushing commits, a 45-minute think between pushes is one work session; for a mobile app opened for eight seconds, 30 minutes is an eternity. Test the threshold: compute session counts at 10, 30 and 60 minutes and look at how many actors gain a second session. On this hour 2,502 actors have 2 or more sessions under the 30-minute rule; that number is what a different gap would move first.
Then remember the window. One hour of data truncates every session that started before 15:00 or was still going at 16:00, so mean length is biased short and the "2+ sessions" count is biased low. When you sessionize a day or a week, the same thing happens at the edges of the extract, only less visibly.
The funnel that changed overnight. A common pattern: a step-order funnel is rebuilt on the same day's data and the conversion moves by a point. Nothing in the product changed. The events carried second-level timestamps, hundreds shared each second, and the sort had no tie-breaker, so a fraction of "viewed then clicked" pairs became "clicked then viewed" between runs. Adding the event id as a second sort key makes the number stop wandering; it does not make the order true, only stable, and the definition should say which.
One real hour in pandas
★ 22 min · everyone buildspandas and the same GH Archive file. Every number in the comments below is what this code prints on 2024-03-04-15.json.gz. Reading 150 MB of gzipped JSON into a frame takes a minute or two and a few gigabytes of memory; if your laptop objects, read with chunksize and concatenate the five flat columns only.
Get the hour. If you kept the file from session 1, skip this. GH Archive names files by UTC date and hour, no login.
Load and flatten. One read_json call, then five scalar columns pulled out of the nested ones with the .str accessor, which indexes into a column of dicts. Then the three cheap looks.
The bot rule, then events per actor. The suffix rule is one line. The distribution is one groupby. Print the median, the mean and the maximum together, always.
Concentration. The top five logins by name, and the share of events the top 1 percent of actors produce. Two of the top five have no [bot] suffix.
Sessionize the humans. Sort by actor and time, take the gap to the previous event within each actor, and start a new session where the gap is missing (first event) or over 30 minutes. A cumulative sum of the "new" flag numbers the sessions.
The tie check. How many distinct timestamps are shared by more than one event. On this hour: all of them. Add raw["id"] to the frame and to the sort before any step-order claim.
Print the rule with the result. Wherever the bot share or the session count appears in a report, the line above it should say the rule that produced it: "logins ending in [bot]; 30-minute gap; one hour, truncated at both edges". A number with its rule can be challenged; a number alone can only be believed or not.
Before session 3
30 min- Write the second half of the bot rule. Pick a behaviour threshold (events per hour, or regularity of gaps) that would catch censameesss, apply it to the human frame, and print how many actors and what share of events it removes. Write one sentence defending the threshold.
- Move the gap. Re-run step 5 at 10 and 60 minutes. Report the session count and the number of actors with 2+ sessions at each, next to the 30-minute values (76,584 and 2,502).
- Sort without the id, twice. Take one busy actor, sort by
tsonly, run it two ways (once after shuffling the frame) and check whether the event order inside a shared second changes. Then repeat withidas the second key. - Keep the flattened frame. Save
dfto Parquet. Session 3 uses the same accounts to compute three different "active" counts from three definitions.
Three questions
Clear all three and analyst session 2 gets stamped in your passport.
1 · The [bot] suffix rule finds 409 actors and removes 15.5 percent of the hour's events. What does that share tell you about automation in the stream?
A rule by name is exact for the accounts that follow the convention and blind to the rest. Print what each half of the rule removes on every run, so the definition of "human activity" can be audited rather than believed.
2 · Why can created_at alone not order this stream?
A step-order claim such as "pushed before opening the pull request" needs a stable order. The id makes the order stable; whether it is the true order is a separate question the definition should state.
3 · What does the 30-minute gap decide in the sessionizer?
The threshold is inherited from web analytics, not derived from your product. Test 10, 30 and 60 minutes, pick one for a reason, and remember that a bounded extract truncates sessions at both edges.
What this session teaches, and where it came from
Every fact on this page, and its verification tier, is recorded in the course's source map.
Analyst session 2 cheat sheet · pin this
You now have a clean frame and a written bot rule. Session 3 asks the question the frame was built for: what does "active" mean? The same GitHub accounts, over four weeks and sampled hours, give three different monthly active counts under three definitions, 79,555 for any event, 48,946 for pushed code, 8,993 for opened an issue or pull request, and a DAU/MAU ratio that changes with each. The definition is the metric.