Working with pandas dataframes

Install the analytical libraries before you start

Parts 1–6 needed only Python itself and code bundled with the exercises. Part 7 uses four established libraries. The TMC server already has them, but your own computer probably does not — and the TMC extension runs the tests on your machine, so you need them locally.

Install them once, in the VS Code terminal:

python3 -m pip install pandas seaborn scikit-learn spacy
python3 -m spacy download en_core_web_sm

On Windows, write python instead of python3. The second command fetches the small English language model used in the last exercise. It is a separate download because a model is data rather than code.

Then check that it worked:

import pandas, seaborn, sklearn, spacy
print(pandas.__version__, seaborn.__version__, sklearn.__version__, spacy.__version__)

If a command fails — a common one is pip refusing with an "externally managed environment" message — or the import still fails afterwards, paste the exact error to your coding agent and ask it to find out which Python installation VS Code is using and install the packages into that one. A computer often has several Python installations, and matching them up is genuinely fiddly. It is a setup problem rather than a programming one, so it is a reasonable thing to delegate. Ask the agent which command finally worked and why, and do not let it change exercise code to work around a missing library.

The server currently runs pandas 2.1, seaborn 0.13, scikit-learn 1.3 and spaCy 3.7. A newer local version is normally fine; if a result differs from what an exercise describes, compare versions before assuming your own code is wrong.

Parts 5 and 6 implemented table traversal, grouping and file ingestion directly. From this point onward we will normally use existing analytical libraries for those established operations. Our own code will describe the question, check the expected data shape, connect library results and name the values used in later output.

Orient yourself in a dataframe

pandas.read_csv reads a CSV file into a DataFrame: a table whose rows share named columns.

import pandas as pd

records = pd.read_csv("collections.csv")
print(records.head())
print(records.columns)
print(records.dtypes)

Before transforming unfamiliar data, inspect:

several raw rows with head();
columns, including spelling and capitalization;
dtypes, especially fields which should be numeric or dates;
missing values with isna().sum().

This is validation by observation. It does not prove that every row is correct, but it often catches a mistaken delimiter, header or type before that mistake reaches a chart.

Reminder: functions and methods

A function receives the object it works on as an argument. For example, len(records) passes records to the function len.

A method is a function associated with a particular kind of object. The object before the dot is its implicit main input. Methods attached to data objects therefore usually inspect, process or transform that data. In records.head(), the method head operates on the dataframe records. In records.sort_values("year"), sort_values operates on the same dataframe, while "year" is an additional argument controlling the operation.

You can mentally read these as operations such as “take records and return its first rows” and “take records and return it ordered by year.” Most pandas methods used here return a new data object rather than modifying the original one.

There is one visually similar but distinct use of the dot in pd.read_csv("collections.csv"): pd is the pandas module, and the dot selects the function named read_csv from that module. The pandas documentation therefore lists read_csv as a function and DataFrame.head and DataFrame.sort_values as dataframe methods.

Read a pandas pipeline from left to right

Pandas operations are often written as a method chain. Each method receives the object returned by the preceding method and returns the value used by the next method:

finnish_titles = (
    records[records["language"] == "Finnish"]
    .sort_values("year")
    .head(10)
)

Read this from top to bottom:

select rows whose language is Finnish;
sort the selected dataframe by year; and
keep its first ten rows.

The surrounding parentheses allow the expression to continue across lines. The same flow can be written with named intermediate values:

finnish_records = records[records["language"] == "Finnish"]
ordered_records = finnish_records.sort_values("year")
finnish_titles = ordered_records.head(10)

Named intermediates are useful while learning or debugging because each stage can be inspected separately. A short chain is useful when the succession of operations remains clear. Most dataframe methods return a new object; do not assume an operation changed the original dataframe unless its documentation says so.

Pandas also provides the method pipe for inserting one of your own functions into such a chain. The current dataframe is passed as the first argument:

def select_language(dataframe, language):
    return dataframe[dataframe["language"] == language]

finnish_titles = (
    records
    .pipe(select_language, "Finnish")
    .sort_values("year")
    .head(10)
)

Here .pipe(select_language, "Finnish") is equivalent to select_language(records, "Finnish"). pipe is most helpful when a project-specific transformation has a clear name and returns an object suitable for the next library operation. It does not remove the need to understand the input and output of each stage.

Select, group and sort

Suppose the dataframe contains collection, language and documents columns. A boolean condition can select rows:

finnish = records[records["language"] == "Finnish"]

Column selection states which fields cross into the next stage:

selected = finnish[["collection", "documents"]]

Use the library's grouping operation after you understand the result you want:

summary = (
    records.groupby("language", as_index=False)
    .agg(documents=("documents", "sum"))
    .sort_values("documents", ascending=False)
)

Inspect summary.columns, its row count and one hand-calculated group. We test our choice of grouping field and aggregation, rather than retesting pandas' implementation of addition.

Prepare plain results for presentation

A table displayed in a notebook, a text report and a static plot can reuse the same verified result. Keep the analytical result separate from the code which displays it:

table_rows = summary.to_dict(orient="records")

metrics = {
    "documents": int(records["documents"].sum()),
    "collections": int(records["collection"].nunique()),
}

chart_series = []
for row in table_rows:
    chart_series.append({"label": row["language"], "value": row["documents"]})

result_data = {
    "metrics": metrics,
    "table_rows": table_rows,
    "chart_series": chart_series,
}

The calls to int turn pandas or NumPy scalar values into ordinary Python values. to_dict(orient="records") turns each dataframe row into a named dictionary. These conversions make the output easy to inspect, test and pass to another component.

This example gives its three results descriptive names:

{
    "metrics": {"documents": 120, "collections": 3},
    "table_rows": [{"language": "Finnish", "documents": 75}],
    "chart_series": [{"label": "Finnish", "value": 75}],
}

A project may need different results and names. What matters is that each value has a clear role and can be inspected before presentation:

metrics contains headline values;
table_rows contains named records for detailed inspection;
chart_series contains tidy observations for plotting.

Present verified chart data with seaborn

Seaborn already knows how to turn tidy observations into statistical graphics:

import pandas as pd

def plot_chart(chart_series: list):
    import seaborn as sns

    chart_data = pd.DataFrame(chart_series)
    return sns.barplot(data=chart_data, x="label", y="value")

The seaborn import is inside the plotting function because only presentation needs it. Loading the module and using its pandas transformations therefore do not also require the plotting library. The analytical checks should compare chart_series with hand-calculated records. A lightweight presentation check can then verify that plot_chart returns a Matplotlib axes object, uses the promised axis labels and can save a non-empty image. Comparing image pixels would make the test depend on irrelevant rendering details.

Before plotting, you can also print metrics, table_rows and chart_series. This gives direct evidence about the values passed to the presentation library and helps distinguish a transformation problem from a plotting problem.

When reviewing agent-written analysis, trace one value through five stages:

Ingest: which file and dataframe column supplied it?
Validate: where did you inspect its type, missingness or example value?
Transform: which selection or derived column changed it?
Summarize: which grouping or calculation produced the result?
Present: which metrics, table_rows or chart_series field exposes it?

The following exercises repeat this trace with dates, JSON, text enrichment, similarity results and joined submission data.

Return to modification evidence at a larger scale

The opening example contained ordinary lists and three function calls. A library-backed program can have the same structural problem even when every individual dataframe operation is correct.

Suppose the existing flow is:

records = load_records("records.csv")
prepared = prepare_records(records)

metrics = summarize_metrics(prepared)
table_rows = make_table(prepared)
chart_series = make_chart_series(prepared)

The requirement is: “Add a minimum-year filter which affects every displayed result.” The agent reports that all three outputs now use the filter and supplies this change:

 records = load_records("records.csv")
 prepared = prepare_records(records)

 metrics = summarize_metrics(prepared)
+active = filter_minimum_year(prepared, minimum_year)
-table_rows = make_table(prepared)
-chart_series = make_chart_series(prepared)
+table_rows = make_table(active)
+chart_series = make_chart_series(active)

The new library-backed filter may return exactly the intended rows. The remaining defect is about order and consumers: summarize_metrics runs before active exists, so the metric still summarizes prepared. A check covering only the returned table and chart cannot establish the metric requirement.

Use the five stages to inspect this change:

load_records and prepare_records establish the ingested and transformed dataframe.
filter_minimum_year creates the active transformed dataframe for this request.
All summaries and presentation records which the requirement names should consume that active dataframe.
The changed checks should inspect each affected plain result, not merely confirm that the interface renders.

The smallest consistent structure establishes active before any consumer:

records = load_records("records.csv")
prepared = prepare_records(records)
active = filter_minimum_year(prepared, minimum_year)

metrics = summarize_metrics(active)
table_rows = make_table(active)
chart_series = make_chart_series(active)

The agent can explain or audit this flow. Your added capability is being able to compare that explanation with the actual function arguments, order and returned shapes it cites.

You have reached the end of this section! Continue to the next section:

3. Times and dates

You can check your current points from the blue blob in the bottom-right corner of the page.