← pandas: reliable tabular analytics Progress & review ↗

Typorithm · medium

Long and wide layouts with unique cells

pythonpandasnumerical-computingindependent-practice

Saved on this device

Full lesson · 4 learning steps Open any exercise for focused practice

Step 1 of 4 · intro card

Long and wide layouts with unique cells

Concept and contract

Reshape measurements while rejecting ambiguous role-metric pairs.

A long table stores one observation per ID and metric; a wide table stores metrics in separate columns. The layout changes, but the underlying identity and measurement meaning should not. A wide cell needs a unique row key and column key. If several long rows target the same cell, pivot cannot choose a value without an additional aggregation policy. Reject ambiguity when no such policy has been specified.

This lesson accepts two metrics, views and applications, and produces one row per role with those two columns. An absent combination remains missing, which differs from an observed zero.

Mechanism and worked example

Create typed id, metric and value columns.

Create typed id, metric and value columns. Reject missing identity fields, unsupported metric names and duplicate ID-metric pairs. Pivot id into the row index and metric into columns, then reindex the two declared output columns so a batch containing only one metric still has a stable schema. Rename the column-axis metadata, reset ID into an ordinary column and sort IDs for deterministic output.

Trace role a with views ten and applications two, plus role b with only views five. The wide table contains a complete a row and a missing applications cell for b. Reordering the long observations must not change those associations.

Complete worked example

python

import pandas as pd

def inspect_example(rows):
    frame = pd.DataFrame(rows, columns=["id", "metric", "value"]).astype({"id": "string", "metric": "string", "value": "Float64"})
    if frame[["id", "metric"]].isna().any().any() or (~frame["metric"].isin(["views", "applications"])).any() or frame.duplicated(["id", "metric"]).any():
        raise ValueError("ambiguous measurement cell")
    metrics = ["views", "applications"]
    wide = frame.pivot(index="id", columns="metric", values="value").reindex(columns=metrics)
    wide = wide.astype("Float64").rename_axis(columns=None).reset_index()
    return wide.sort_values("id").reset_index(drop=True)

Reasoning: Create typed id, metric and value columns. Reject missing identity fields, unsupported metric names and duplicate ID-metric pairs. Pivot id into the row index and metric into columns, then reindex the two declared output columns so a batch containing only one metric still has a stable schema. Rename the column-axis metadata, reset ID into an ordinary column and sort IDs for deterministic output. Trace role a with views ten and applications two, plus role b with only views five. The wide table contains a complete a row and a missing applications cell for b. Reordering the long observations must not change those associations.

Open in local Python playground ↗

Focused decision: context is in the complete example

python

wide = frame.pivot(index="id", columns="metric", values="value")
wide = wide.reindex(columns=["views", "applications"])

Focused decision: Type this short table decision after reading its complete context. The complete imports, table construction, validation, and result checks remain in the worked example. Typing records practice; only the downloadable computer lab executes Python.

Counterexamples and common mistakes

The controlled mistake fills absent cells with zero after pivoting.

The controlled mistake fills absent cells with zero after pivoting. That claims b had an observed applications value of zero when no such observation exists. Another shortcut uses pivot_table with a default mean to hide duplicate pairs. Aggregation may be appropriate under a declared rule, but it is not a substitute for deciding what repeated measurements mean.

Sorting, resetting an index and reindexing columns solve different problems. None of them validates duplicate business cells. Keep the uniqueness check visible so a stable-looking table cannot disguise conflicting observations from two records.

Controlled mistake: inspect the failing table contract

python

import pandas as pd

def inspect_example(rows):
    frame = pd.DataFrame(rows, columns=["id", "metric", "value"]).astype({"id": "string", "metric": "string", "value": "Float64"})
    if frame[["id", "metric"]].isna().any().any() or (~frame["metric"].isin(["views", "applications"])).any() or frame.duplicated(["id", "metric"]).any():
        raise ValueError("ambiguous measurement cell")
    metrics = ["views", "applications"]
    wide = frame.pivot(index="id", columns="metric", values="value").reindex(columns=metrics)
    wide = wide.fillna(0).astype("Float64").rename_axis(columns=None).reset_index()
    return wide.sort_values("id").reset_index(drop=True)

Reasoning: The controlled mistake fills absent cells with zero after pivoting. That claims b had an observed applications value of zero when no such observation exists. Another shortcut uses pivot_table with a default mean to hide duplicate pairs. Aggregation may be appropriate under a declared rule, but it is not a substitute for deciding what repeated measurements mean. Sorting, resetting an index and reindexing columns solve different problems. None of them validates duplicate business cells. Keep the uniqueness check visible so a stable-looking table cannot disguise conflicting observations from two records.

Open in local Python playground ↗

Independent practice

Change the presentation column order to applications followed by views.

Change the presentation column order to applications followed by views. Preserve unique-cell validation, missing cells, ID ordering and nullable measurement types. This is a small layout edit; it must not change the underlying metric names or aggregate observations.

Predict the output for a role that has only views. Under the changed order, its first metric cell is still missing and its second is the observed views value. Include a completely empty batch in your prediction. A stable empty schema is part of the same representation contract, rather than a special case left for consumers to guess.

Step 1: Present applications before views without filling absent metric observations. Step 2: Retain the working imports, input validation and table schemas. Change the stated policy, run the supplied practice cases, then explain the newly accepted or rejected boundary.

Practice starter: solve before reading the solution

python

import pandas as pd

def inspect_example(rows):
    frame = pd.DataFrame(rows, columns=["id", "metric", "value"]).astype({"id": "string", "metric": "string", "value": "Float64"})
    if frame[["id", "metric"]].isna().any().any() or (~frame["metric"].isin(["views", "applications"])).any() or frame.duplicated(["id", "metric"]).any():
        raise ValueError("ambiguous measurement cell")
    metrics = ["views", "applications"]
    wide = frame.pivot(index="id", columns="metric", values="value").reindex(columns=metrics)
    wide = wide.astype("Float64").rename_axis(columns=None).reset_index()
    return wide.sort_values("id").reset_index(drop=True)

# Independent task: Present applications before views without filling absent metric observations.

Reasoning: Present applications before views without filling absent metric observations.

Open in local Python playground ↗

Hint 1: Find the one field or branch that owns this policy.
Hint 2: Compare the first changed acceptance case with the retained baseline.

Solution, tests and trade-offs

The solution changes the declared metric order used for reindexing and the final output schema.

The solution changes the declared metric order used for reindexing and the final output schema. The lab checks exact values, column order, missing cells and types, including empty input. Duplicate ID-metric pairs remain rejected. A fixture with an observed zero alongside an absent combination ensures those two cases cannot be silently conflated.

The reverse operation, melt, moves declared value columns into metric and value rows. Its missing-value policy should also be explicit: retaining a missing wide cell as a long missing measurement differs from claiming an observed value. Always state which layout changes are reversible under the chosen identity and missingness rules.

Practice solution and acceptance checks

python

import pandas as pd

def inspect_example(rows):
    frame = pd.DataFrame(rows, columns=["id", "metric", "value"]).astype({"id": "string", "metric": "string", "value": "Float64"})
    if frame[["id", "metric"]].isna().any().any() or (~frame["metric"].isin(["views", "applications"])).any() or frame.duplicated(["id", "metric"]).any():
        raise ValueError("ambiguous measurement cell")
    metrics = ["applications", "views"]
    wide = frame.pivot(index="id", columns="metric", values="value").reindex(columns=metrics)
    wide = wide.astype("Float64").rename_axis(columns=None).reset_index()
    return wide.sort_values("id").reset_index(drop=True)

Reasoning: The solution changes the declared metric order used for reindexing and the final output schema. The lab checks exact values, column order, missing cells and types, including empty input. Duplicate ID-metric pairs remain rejected. A fixture with an observed zero alongside an absent combination ensures those two cases cannot be silently conflated. The reverse operation, melt, moves declared value columns into metric and value rows. Its missing-value policy should also be explicit: retaining a missing wide cell as a long missing measurement differs from claiming an observed value. Always state which layout changes are reversible under the chosen identity and missingness rules.

Open in local Python playground ↗

Interview reasoning and next step

Explain why pivot requires unique cells and when pivot_table adds an aggregation decision.

Explain why pivot requires unique cells and when pivot_table adds an aggregation decision. Distinguish an absent metric from a measured zero. Describe what reindex establishes in the output and what information could be lost by an undocumented fill. If asked to melt the result, name the identifier column and the two value columns first.

On the PC, type pivot and explicit column reindexing, then recall the stable metric list. In the app, reason about the missing cell. Next, handle event timestamps as instants with a declared timezone before grouping them into calendar days.

Optional stretch — after the core task works: Add one independently reasoned boundary case and explain its expected value before running it.

Computer practice: https://applydjinn.com/typorithm/pandas-lab-20261106.zip. Install pinned dependencies in an isolated Python environment. Run verify.py, then change the working exercise baseline and run --practice pd-reshape. The real pandas tables, exact schemas, unchanged inputs, CSV parser options and owned in-memory SQL reconciliation use owned fixtures only. No production database, feed, account or paid service is used. This lab executes Python; the PC typing trainer and native reading progress do not execute it.

Reshape measurements while rejecting ambiguous role-metric pairs.