All articles

KajoData / python en

Python charts: from GroupBy to a readable time axis

I work from six sales rows to an aggregate and a chart, explaining the difference between a total, a mean, a missing observation and zero.

Kajo RudzińskiPublished Updated 12 min read

A workshop groups golden cubes into stacks that become bars and a stepped line.

A chart can look excellent while answering the wrong question. Plot a mean instead of a total, or replace an absent day with zero, and the story changes. I therefore start with the table behind the chart. Only after defining what each point or bar represents do I choose the colours and layout.

This guide works through a small sales example using pandas and Matplotlib. The data is synthetic and contains six rows, so the expected aggregates can be followed by hand. I provide code for the reader to use and explain the results from the definitions of the operations. The scope follows the pandas 3.1 and Matplotlib 3.11 documentation available on 22 November 2026. A different sales export may have a different grain, which must be established before copying the calculation.

Define the grain of the source table

Each row represents one sales line, and sales contains the amount for that line in one common unit. The category field divides products into A and B. Dates are calendar days without a time or time zone. These are assumptions for the exercise. In a real export, establish whether the amount belongs to a line or an entire order and whether it is gross or net.

date category sales
2026-01-01 A 100
2026-01-01 B 50
2026-01-02 A 60
2026-01-02 A 40
2026-01-02 B 20
2026-01-04 A 30

Category A contributes 100 + 60 + 40 + 30, giving 230. Category B contributes 50 + 20, giving 70. The overall amount is 300. This small calculation provides a reference for the transformations that follow. If a grouping produces a different total without a deliberate filter, I return to the data preparation. A better-looking chart cannot explain missing amounts.

import pandas as pd
import matplotlib.pyplot as plt
import matplotlib.dates as mdates

df = pd.DataFrame({
    "date": ["2026-01-01", "2026-01-01", "2026-01-02",
             "2026-01-02", "2026-01-02", "2026-01-04"],
    "category": ["A", "B", "A", "A", "B", "A"],
    "sales": [100, 50, 60, 40, 20, 30],
})
df["date"] = pd.to_datetime(df["date"], format="%Y-%m-%d")

Prepare dates before grouping

To_datetime converts the input into a date type. I supply the format because the input structure is known. The default errors="raise" stops the operation when parsing fails. An invalid date therefore cannot silently disappear from the later summary while the report continues to look complete.

The alternative errors="coerce" turns invalid input into NaT. That can help identify problematic rows, but it requires a subsequent decision about them. It does not repair the source. If rows with missing dates are later omitted from grouping, the final chart can show a smaller amount even though the original sales column appeared to contain all its values.

Our dates have no time component. If the source contains timestamps, grouping by the full datetime creates groups for exact moments. Two orders on the same day can then become separate points. First define the reporting day and its time zone, then reduce timestamps to days. This is a measurement decision, separate from formatting the labels on the horizontal axis.

Start with a Series of category totals

GroupBy splits the data into groups for an aggregation. I select the numeric column explicitly so that the operation has a clear scope. The following expression creates a Series whose index contains categories and whose values contain sales totals. Sorting by amount determines the order later presented to the reader.

by_category = (
    df.groupby("category", dropna=False)["sales"]
      .sum(min_count=1)
      .sort_values(ascending=False)
)

The expected result is A: 230 and B: 70. I use dropna=False so that a missing category in future input is retained as a grouping key rather than omitted by default. There are no missing categories in this sample. The parameter nevertheless records how unclassified rows should be treated: their amounts may still belong in the overall report.

For GroupBy sum, min_count=1 requires at least one valid value to produce a sum. A group with no known amount should not automatically appear to be a confirmed zero. This is separate from a missing grouping key. One decision concerns the category; the other concerns the measure being aggregated.

Keeping those decisions explicit also makes a notebook easier to review. Someone can inspect the grouping field, missing-key policy and numeric column in one place. If the report later changes from category totals to a different breakdown, the aggregation remains a visible part of the work rather than an incidental detail buried in plotting code.

Use bars to compare categories

With two categories, I choose a simple bar chart. The pandas visualization guide describes convenient plot methods backed by Matplotlib by default. I create a figure and axis explicitly, then pass the axis to pandas. That makes it clear which chart is being formatted when several figures exist in the same notebook.

fig, ax = plt.subplots(figsize=(7, 4), layout="constrained")
by_category.plot.bar(ax=ax, color="#001561", rot=0)
ax.set(title="Sales by category", xlabel="Category", ylabel="Sales units")
ax.set_ylim(bottom=0)
ax.grid(axis="y", alpha=0.2)
ax.set_axisbelow(True)
plt.show()

The A bar represents 230 and B represents 70. I start the value axis at zero so that bar lengths remain meaningful. The grid is light and only helps read heights. A label such as “sales units” also needs context: does it mean an amount, a quantity of products or an arbitrary exercise value? Here it denotes the common amount unit used throughout the synthetic table.

Colour should not replace a label. When categories measure the same thing, a single colour is usually sufficient. With long category names I would consider horizontal bars. The reader should not need to tilt their head merely because the first plotting method placed the labels vertically. Orientation should follow the content of the labels and the comparison being made.

Totals and means answer different questions

On 1 January, the total is 150 across two lines, so the mean line amount is 75. On 2 January, the total is 120 across three lines, giving a mean of 40. On 4 January, there is one line for 30. A chart of totals compares the accumulated amount, while a chart of means compares the average value of a source row within each day.

I would not call that an average order value. A source row represents a line, not an order, and there is no order identifier with which to assemble baskets. This is a common trap: the calculation is technically valid, but its title promises a different measure. The chart label needs to follow the grain of the data and the definition of the aggregation.

daily = (
    df.groupby("date")["sales"]
      .sum(min_count=1)
      .sort_index()
)
daily_mean = df.groupby("date")["sales"].mean().sort_index()

Means also raise a weighting question. Averaging daily means gives every day equal weight even when the number of lines differs. The mean of all six lines is 300 divided by 6, which is 50. Reconstructing that figure from daily summaries requires daily totals and counts. The daily means alone do not provide a safe way to combine groups of different sizes.

A line needs a chronological axis

A line joins observations in the order supplied. I sort the index chronologically and retain its date type. I avoid converting it to strings merely to shorten labels. Matplotlib provides date locators and formatters, allowing the presentation to change without losing the meaning of elapsed time between observations.

fig, ax = plt.subplots(figsize=(8, 4), layout="constrained")
ax.plot(daily.index, daily.values, marker="o", color="#001561")
locator = mdates.AutoDateLocator()
ax.xaxis.set_major_locator(locator)
ax.xaxis.set_major_formatter(mdates.ConciseDateFormatter(locator))
ax.set(title="Observed daily sales", xlabel="Date", ylabel="Sales units")
ax.grid(axis="y", alpha=0.2)
plt.show()

For this date-axis example I use ax.plot directly to keep the relationship between data, axis and formatter explicit. Markers identify observed days, while the line helps follow their sequence. Our sample has no 3 January observation. The segment from 2 to 4 January is not another measured value in the middle. Without an explanation, a reader might attribute more information to it than the data contains.

An absent day does not establish zero

The missing 3 January could mean no sales, a failed export or an incomplete input file. These six rows cannot settle the question. I therefore describe the chart as observed daily values. If a continuous calendar is needed with an explicit gap, I can extend the index to all days while retaining a missing amount for the unobserved day.

calendar = pd.date_range("2026-01-01", "2026-01-04", freq="D")
daily_with_gaps = daily.reindex(calendar)

Reindexing introduces 3 January with a missing value. Filling that value with zero becomes justified only after confirming that the source fully covers the day and that no rows really means no sales. This also affects daily averages: adding a zero day changes the denominator. The conclusion can change even though none of the existing source rows has been edited.

A business-day report needs an additional decision about weekends and holidays. A full daily calendar is one possible reference set. An operational report may need a non-working day to appear as zero, a gap or a day outside the measure altogether. I agree that interpretation with the process owner instead of delegating it to a plotting library’s defaults.

Two shelves distinguish a missing calendar tile from an explicitly empty position.

Group by two fields, then unstack

Next I want to compare categories over time. Grouping by date and category produces a Series with a MultiIndex, where each date-category pair identifies one total. Unstack moves an index level into columns. It reshapes a result that has already been aggregated; it does not calculate the sales again.

by_day_category = (
    df.groupby(["date", "category"], observed=True)["sales"]
      .sum(min_count=1)
)
wide = by_day_category.unstack("category").sort_index()

For 1 January, the table contains A: 100 and B: 50. For 2 January, it contains A: 100 and B: 20. For 4 January, it contains A: 30 with no B combination. The two A lines on the second day have already been summed. They have not disappeared merely because the final table has only one cell for that date and category.

The observed=True parameter matters for categorical grouping keys. Stating it explicitly preserves the intention across pandas versions; the documentation records a default change in version 3.0. The categories here are ordinary strings, but types can change in a larger project. I prefer an important grouping decision to be visible at the point where the calculation happens.

A wide table does not prove completeness

The missing B combination on 4 January differs from the entirely absent 3 January. Unstack can create an empty cell for an unobserved combination of existing keys. It does not invent a whole date that is absent from the index. A fill parameter therefore does not provide a complete calendar. That needs a separate reference set of dates and deliberate reindexing.

I avoid immediately using fill_value=0 when the meaning of absence is unknown. It can be appropriate for a complete transaction register and misleading for a partial export. The report instructions should explain empty cells consistently. A reader should not have to guess whether the same blank means zero in one section and a data failure in another.

A wide table can be drawn as grouped bars with wide.plot.bar(). Its date labels then act as categories rather than a continuous time scale. This can help compare a handful of days, but the distance between groups does not represent elapsed calendar time. For a longer series I prefer a genuine time axis and explicit rules about gaps.

A maximum describes the largest line

The previous version of this guide also showed a maximum by day and category. That aggregation is useful when the question concerns the largest individual line. For A on 2 January the maximum is 60, while the total is 100. A general title such as “category sales” would conceal the difference and could lead to an incorrect comparison.

Before choosing an aggregation, I write one sentence defining a point. “Total value of lines on a day” and “largest individual line on a day” describe different charts even with identical source data. Finding the largest order would require first grouping lines by order. Replacing sum with max cannot create a measure whose underlying entity is missing from the source.

Prepare the chart for another reader

I check the title, unit, period and measure definition. The recipient should know whether the chart contains all records or a filtered subset. When two charts compare similar values, I use consistent scales or clearly explain why they differ. A line chart can use a narrowed range to show variation, provided the axis and the choice remain clear.

I remove a legend if it merely repeats the only axis label. With many categories, I reduce the series or create separate views rather than relying on increasingly similar colours. I also inspect the chart at its actual delivery size. A label that looks comfortable in a wide notebook can become unreadable on a presentation slide or phone.

Alongside the export, I retain the aggregate table and the definition that produced it. That makes it possible to trace a bar back to an amount. I make no claim that one chart type handles a fixed percentage of everyday analytical problems. The value of this workflow lies in an explainable calculation and a clear message, with formatting supporting both.

A filter changes the comparison scope

Selecting category A before grouping leaves a total of 230 rather than 300. Any shares calculated from that filtered result describe the selected subset. They do not automatically describe A’s share of the original sales. Choose the denominator from the population that the report is intended to represent, and keep the selection visible beside the aggregate table.

Dates need the same care. Comparing a partly loaded day with complete days can create an apparent decline. Record the refresh time when it affects interpretation. This exercise assumes simple calendar days, but a real report needs a definition of source completeness as well as an aggregation rule.

I would not infer a trend or seasonality from these three points. They show different observed totals on particular days. A recurring pattern requires a longer comparable period and knowledge of the process. A line connecting the points does not supply that evidence. Keeping a short record of filters, missing-value policy and reporting period makes the chart’s meaning easier to reconstruct later.

Exercise: change the question, keep the data

Using the six rows, describe three results: the category total, the mean line amount on a day and the largest A line on 2 January. Write the expected values before working on the chart. Then remove the category from one row and decide whether its amount should remain in the overall summary. Do not assign an arbitrary category merely to make the final table look tidy.

Separately, state what evidence would justify adding zero for 3 January. Prepare two captions: one for observed days and one for a complete calendar with a confirmed zero. This exercise shows that charting begins with data meaning. For organizing the next steps, see custom Python functions. For an interactive report, continue to the guide to a Tableau sales dashboard.

Key takeaways

Are pandas and Matplotlib separate plotting approaches?

The default pandas plotting backend uses Matplotlib. You can pass an Axes object to plot and then format that same object with Matplotlib methods.

Does a day missing after groupby mean zero sales?

No. It can mean no observations or missing data. I add zero only after confirming that the source is complete for that day.

Does unstack require converting dates to text?

No. Unstack reshapes a table. I retain the date type and format the axis labels separately.

AI was used in an automated way while working on this article. If you want to learn how to do it too, check out this project.

Kajo Rudziński

Newsletter KajoData

Let’s keep in touch.

I write about data analysis, learning and changing careers. My newsletter and the sign-up form are in Polish. If that works for you, I’d be glad to see you there.