主人 and 小狗

The data is loaded into SQLite as one timestamp column plus author, word_count, sentence_count — no message text, and no separate date/hour columns. SQL pulls those out itself with strftime(). query() runs SQL and returns a pandas DataFrame. Building value as a list of small dicts - not just one number - is what lets this page lay them out side by side.

Docs: SQLite aggregate functions · SQLite date/time functions

One query, one chart. strftime('%Y-%m-%d', timestamp) collapses each timestamp down to just its date, so GROUP BY groups by day. pd.to_datetime() turns that column from plain text into a real date type - without it, Plotly may treat every unique day as its own category and try to label all of them; as a proper date type, it draws a continuous date axis that thins its own tick labels no matter how many days there are. fig is a Plotly figure — this page reads it and draws it.

Docs: Plotly bar charts · pandas.to_datetime

strftime('%w', timestamp) gives day of week as 0 (Sunday) through 6 (Saturday). A CASE expression turns that number into a readable label, right in the query - and ORDER BY on the number keeps Sunday through Saturday in the right order, since the names alone would sort alphabetically.

Docs: SQLite CASE expression

Same idea, a different piece of the timestamp: strftime('%H', timestamp) gives the hour as a zero-padded string ("06", "14", ...), so it's cast to an integer for sorting and coloring.

Docs: Plotly color scales

Message length is heavily right-skewed - lots of short messages, a thinning tail of longer ones - so equal-width bins leave most of them crammed into the first bucket or two. np.logspace builds bin edges that grow wider as the values grow, derived straight from this dataset's own min and max rather than a fixed guess. go.Bar's width parameter draws each bar at its own bin's actual width, so the growing bin sizes are visible, not just implied - though the narrowest true widths get bumped up to MIN_WIDTH so they don't shrink to an invisible sliver. The y-axis is logarithmic too, since a handful of very common short lengths would otherwise flatten every longer bar into the axis.

Docs: numpy.logspace · numpy.histogram

One query, two aggregates at once: COUNT(*) and AVG(word_count) both grouped by the same hour. Plotting one against the other asks a real question - do the busiest hours also carry the longest messages, or the shortest? hour_label turns the raw 0-23 hour into something readable ("2 PM"), and hover_name puts it front and center on hover - without it, a point is just an unlabeled dot with no way to tell which hour it came from.

Docs: Plotly scatter plots · hover text & formatting

Same query as before, but arranged differently: hour and messages take the x and y axes this time, and avg_words joins as a third variable instead of an axis. size="avg_words" tells Plotly Express to scale each point's marker by that column - no manual math required yet.

Docs: Plotly bubble charts

One row per day comes back from SQL, same as before; week = (date - grid_start) // 7 turns each date into a continuous week index - this is the exact same technique the next chart builds a whole grid out of, just used here to group days into weeks with groupby("week").sum(). Every point sits on the same flat line - only its size (again from bubble_sizes()) shows how busy that week was, which makes the busy and quiet stretches across the whole timeline easy to spot at a glance. The fixed color is one member of the same palette random_color() draws from later, rather than an unrelated one-off.

Docs: pandas.DataFrame.groupby

One aggregated point per day, not per message - this is what makes 13 months fit without a month-by-month grid. GROUP BY date gets each day's count and average length. week = (date - grid_start) // 7 gives a continuous column that never wraps at month boundaries; weekday gives the row, so Mondays stay stacked above Mondays the whole way through. Each dot gets a random hue from a small curated palette, nudged slightly so no two days match exactly; dot size is message count, and opacity is average length. Hovering a dot shows its actual date instead of the underlying week/weekday numbers.

Docs: pandas.Timedelta

Same query, same random-palette colors and size encoding as the week grid above. The difference is in the geometry: instead of one discrete ring per month, radius grows smoothly with elapsed days - total_days / DAYS_PER_TURN - so there's never a jump back to the start of a new loop, just a continuous line winding outward. DAYS_PER_TURN is deliberately a multiple of 7: day 28 lands on the same weekday and the same angle as day 0, since 28 is a whole number of weeks - so weekly rhythms show up as repeating shapes rather than being scattered randomly around the circle. Each spoke nudges forward or backward depending on which lap it falls in (total_days // DAYS_PER_TURN), alternating every time it comes back around. Opacity still fades toward the center and brightens toward the edge, pulling the eye toward "now." Same date tooltip as before, too.

Docs: Plotly polar charts · Scatterpolar reference

A rainbow arc rather than a full circle: week 0 sits at 9 o'clock, the most recent week sits at 3 o'clock, and everything in between spreads evenly across the top half - the bottom stays empty on purpose. There's no wraparound seam here, since the data starts at one end and ends at the other rather than meeting itself. The center hole is bigger and the 7 rings sit closer together than a full circle needs, since the arc has room to spare. Opacity is a little more muted on the left (oldest) and fully vivid on the right (most recent) - a subtle shift rather than a dramatic one, so even the oldest messages stay clearly visible.

Docs: Plotly polar layout reference