Making Quick Charts From a CSV With Python and DuckDB

The fastest way to understand a column is to look at it, and for a CSV of a million rows the question is how to look without freezing a laptop or drawing a black rectangle of points. The answer is the same each time: do the heavy work in SQL, where aggregating millions of rows takes a second, and hand a small table to the plotting code. This article shows two ways to chart, a text bar chart straight from DuckDB for a look in the terminal, and a matplotlib figure for something you will share, on a 2 million row orders file, with rules for keeping the chart honest. It is a move in more analysis moves for CSV data, and the library side is covered in more depth in reading and plotting CSV data with pandas.

Aggregate before you plot

A scatter of two million points is a smear, and a plotting library may take a minute to draw it. The data you want to see is a pattern, and a pattern lives in an aggregate: revenue by month, orders by status, a histogram of amounts in buckets. DuckDB computes those from the CSV in a fraction of a second, and the result is a few rows you can plot or print:

SELECT strftime(date_trunc('month', created_at), '%Y-%m') AS month, sum(amount) AS revenue
FROM read_csv('orders-2m.csv') GROUP BY 1 ORDER BY 1;

Twelve rows from two million. The rule of thumb: if a chart has more than a few hundred marks, aggregate more. For a distribution, a histogram with 20 to 50 buckets shows the shape; for a time series, a point per day or per week; for categories, a bar for each of the top ten and one bar for "other".

Charts in the terminal

DuckDB has a bar() function that returns a string of block characters proportional to a value, so a query becomes a chart. The arguments are the value, the value that maps to an empty bar, the value that maps to a full bar and the width in characters. A histogram of order amounts, in buckets of 50:

SELECT (floor(amount / 50) * 50)::INTEGER AS bucket, count(*) AS orders,
       bar(count(*), 0, 720000, 30) AS chart
FROM read_csv('orders-2m.csv') WHERE amount < 350 GROUP BY 1 ORDER BY 1;

bucket  orders  chart
0       709777  █████████████████████████████▌
50      713972  █████████████████████████████▋
100     352461  ██████████████▋
150     142930  █████▉
200     52626   ██▏
250     18822   ▊
300     6332    ▎

The shape is clear: two big buckets and a tail that halves with each step, which is a right-skewed distribution. It took one query and no plotting code. Choose the min and max arguments from the data, because a bar scaled to a maximum smaller than the values clips at full width and hides differences, as my first attempt with 600,000 did: the two biggest buckets both filled the bar. The same trick for revenue per day, which matters when months have different lengths:

month    thousands_per_day  chart
2025-01  439.0              ████████████████████████████▋
2025-02  441.3              ████████████████████████████▊
2025-03  439.0              ████████████████████████████▋
2025-04  437.9              ████████████████████████████▌

Per day, the four bars are equal, and February, which had 28 days and 9.2 percent less total revenue than January, is not low at all. A text chart of monthly totals would have shown a short February bar and invited the wrong question.

A figure with matplotlib

For something you will put in a document, matplotlib is the standard. The pattern is the same: the query returns a small DataFrame, and the plot draws it. This script makes a two-panel figure, revenue by month and orders by status:

import duckdb
import matplotlib
matplotlib.use("Agg")
import matplotlib.pyplot as plt

F = "orders-2m.csv"
df = duckdb.sql(f"""
    SELECT date_trunc('month', created_at)::DATE AS month, sum(amount) / 1e6 AS revenue_m
    FROM read_csv('{F}') GROUP BY 1 ORDER BY 1""").df()
status = duckdb.sql(f"SELECT status, count(*) AS n FROM read_csv('{F}') GROUP BY 1 ORDER BY n DESC").df()

fig, axes = plt.subplots(1, 2, figsize=(10, 3.5))
axes[0].bar(df["month"].astype(str).str[5:7], df["revenue_m"])
axes[0].set_title("Revenue by month, millions")
axes[0].set_ylim(0)
axes[1].barh(status["status"], status["n"])
axes[1].set_title("Orders by status")
fig.tight_layout()
fig.savefig("orders.png", dpi=100)

matplotlib.use("Agg") selects a non-interactive backend, which is what a script on a server wants, since it draws to a file without needing a display. duckdb.sql(...).df() returns a pandas DataFrame. The monthly labels are sliced from the date string to show only the month number. set_ylim(0) pins the bottom of the axis at zero. Run in the test container, it printed no errors and wrote a file of about 24 KB.

For a notebook, drop the Agg line and call plt.show(). If your team uses another library, Plotly and Altair also take a DataFrame, and Polars has plotting through them, but the discipline about aggregation is the same (Polars for large CSV processing).

Keeping a chart honest

A chart is a claim, and a few habits keep it true. Start bar charts at zero. A bar's length is the number, and a bar that starts at 12 million exaggerates a difference of 1 percent into a visual drop of half. Line charts may use a truncated axis, since position and not length carries the value, and a truncated axis should be labeled. Compare like with like: charting monthly totals when months differ in length puts the calendar in the picture, as above, so chart a rate per day. Label the measure and the unit on the chart itself: "Revenue per day, thousands, paid and shipped orders" says more than "Revenue". Show the count behind a point when the base varies, in a second axis or a note, since a spike over six orders is not a spike.

Do not use a chart where a number is better. A single figure is a number, and a table of three comparisons is a table. And avoid dual axes, pie charts with more than four slices and 3D anything: each makes it harder to read the values. Use color sparingly, and make sure it still works for people who cannot tell red from green, by also varying position or labeling directly. For categories with long names, a horizontal bar chart is far easier to read than rotated labels.

Reusing the work

Keep the query next to the plot, in the same script, and save the aggregated table as a small CSV or Parquet beside the figure, so that the chart can be rebuilt without the 2 million row file and anyone can check the numbers behind it. A figure whose source is a screenshot cannot be reproduced. If a chart will be republished each month, parameterize the file name and the period, and make the script fail if the data has no rows for the latest month, since an empty plot is a silent error.

When a quick chart is not enough

A quick chart answers "what does this look like". It is not a dashboard, and not an explanation. If many people will look at the same view every day, a BI tool with access control, refresh and annotations is the right home. If the question is why a number moved, the chart is the start and a breakdown by the likely drivers is the next step (comparing two time periods in a CSV). And if the data is sensitive, a chart in a document is a copy of it: aggregate enough that no individual can be identified, as in stripping personal data from a CSV before sharing it. The bar() function is documented in DuckDB's utility functions, and matplotlib's reference is at matplotlib.org.