What Does One Row Represent?

Two identical-looking rows might be a duplicated import or two genuine observations. We need to establish a record's identity before cleaning a dataset or trusting its counts.

10 sections · 3 questions · 2 coding challenges

1 / 10

The arrow keys move between sections too.

Why a Dataset Needs Its Own Instructions

BOTIE

A dataset can outlive the people who remember how it was collected. When collection details disappear, later users may mistake a convenient table for a complete description of a population.

Gebru and colleagues introduced datasheets for datasets in 2018, with a later journal publication, to document matters such as motivation, composition, collection, and recommended uses. Our duplicate-log exercise handles one small part of that broader problem: recording what an observation means before deciding how to clean it.

Sources for this history

Did We Observe It Twice, or Import It Twice?

Botie joins two greenhouse log files and finds the same event ID twice. If both entries describe the same measurement, counting both gives that event extra influence. If two different events happen to have the same temperature, removing one would discard real information.

The distinction comes from what a row represents. We need an event identity, a time, a unit, and a collection rule before deciding which repetitions are errors.

Two exports can overlap

The same event identifier appears in both files. It names one event even though the import contains two rows.

Imported IDs

An invented example for this lesson.

1 of 3

What we can now calculate

The two branches count all records and distinct identities. Subtracting them measures extra copies rather than genuine repeated values.

Explore the full example

Choose an example, then change a value. Follow the calculation below to see which result changes and why. These are small teaching examples; the construction later asks you to choose the operations yourself.

BOTIE

Botie combined sensor exports that may overlap. Each stable ID names one event, so extra copies should be counted before analysis.

Choose an example

Imported sensor event IDs

Result from these values

Follow the operations

1. Imported sensor event IDs
1. 0101
2. 1102
3. 2102
4. 3103
2. Count items
```
4
```
3. Keep distinct items
1. 0101
2. 1102
3. 2103
4. Count items
```
3
```
5. Subtract
```
1
```
6. Result
```
1
```

This audit assumes IDs identify events reliably. It does not decide which conflicting record should be kept. Editing this example does not change the workshop tests.

1. Define the Unit of Observation

A row might represent one greenhouse, one thermometer reading, or one daily summary. These are different units of observation. If we combine them without checking, the row count stops having a clear meaning.

For this example, one row represents one sensor event, and each event is assigned a stable ID. Repeated IDs therefore deserve inspection. Repeated temperature values alone do not.

A primary key is a field, or combination of fields, intended to identify one record. It is useful only when the collection process actually respects that meaning.

2. Audit Repeated Identities

Count all imported rows, then count distinct event IDs. Their difference reports excess copies. An ID appearing three times contributes two excess copies, not three.

Imported IDs: [101, 102, 102, 103]
Total rows = 4
Distinct event IDs = 3
Excess copies = 4 - 3 = 1

This count tells us how much duplication to investigate. It does not tell us whether two versions contain identical measurements or which one is authoritative.

3. Check Values Against Their Meaning

After identity, check units, timestamp conventions, required fields, and plausible ranges. A measurement in Fahrenheit can look like an unusually warm Celsius reading while remaining a perfectly valid number.

Keep a reason for every exclusion or correction. A surprising value may be a faulty sensor, but it may also be the event we are trying to detect. Deleting surprises because they complicate the model can remove the signal.

Quality checks should separate impossible records, inconsistent records, and unusual but possible observations. They do not all deserve the same response.

4. Preserve the Evidence Behind a Cleaned Table

Retain the original records and document the transformation into the modelling table. If duplicate IDs disagree, resolve the conflict using the source system's rules rather than whichever row happens to arrive first.

The workshop only measures excess copies. A real cleaning workflow also needs a conflict policy and an audit trail. Later validation must keep copies or related observations together so they cannot appear on both sides of an evaluation split.

In the coding exercise, distinct-ID counting is enough because the requested result is an audit count, not a cleaned temperature estimate.