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.

12 sections · 3 questions · 2 coding challenges

1 / 12

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.

Before deleting either row, we need to know how an event was identified and recorded. Its time and units help us interpret the measurement. The collection rule tells us whether two matching rows could legitimately describe separate events.

The event IDs represent a small invented sensor export.

Step 1 of 3

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

Record 1101
Record 2102
Record 3102
Record 4103

Step 2 of 3

Count the distinct event identities

There are three distinct IDs. We have not yet decided which conflicting version to keep if repeated IDs disagree.

Distinct IDs

Record 1101
Record 2102
Record 3103

Step 3 of 3

Count the excess copies

One imported row exceeds the distinct event count. This is a duplication audit, not an automatic cleaning decision.

Imported IDs: [101, 102, 102, 103]
Rows: 4
Distinct events: 3
Excess copies = 4 − 3 = 1

The second copy of event 102 adds a row, but it does not describe a new event.

One row needs a clear identity before we count it

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

Did we import another event or another copy?

Repeated values can be real observations, but repeated event IDs can reveal duplicate imports. Change the IDs and count the extra copies without treating every equal measurement as a duplicate.

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

Number of extra copies

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.

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.

5. Two Equal Temperatures Are Not Necessarily One Event

An event identifier says which observation a row claims to describe. Equal temperatures on different events can be perfectly legitimate. Conversely, two rows with one identifier but different temperatures are a conflict, not harmless duplication.

Before dropping anything, group by the identity rule and distinguish exact repeated reports from conflicting reports. A later timestamp may indicate a correction, but only if the data producer defines it that way. Otherwise send the disagreement for review and retain its source.

6. Count Identities Before Choosing Which Report to Keep

Botie can produce a small audit table before cleaning. It should show which identities repeat and how many rows each supplied. This does not yet select a correct value; it tells us which records need the resolution policy.

Retain the raw input and record which rule selected or rejected each row. If an implausible measurement later proves valid, we can revisit the decision without recreating a deleted observation.

Find repeated greenhouse events

The third row repeats the first event identifier; equal values under different identifiers would not count as a duplicate event.

Python

import numpy as np
event_id = np.array(['north-1', 'south-1', 'north-1', 'north-2'])
identities, counts = np.unique(event_id, return_counts=True)
print(identities[counts > 1].tolist())
print(counts[counts > 1].tolist())

Output

['north-1']
[2]

The next step is to compare the two source records and apply the declared resolution rule. Counting duplicates alone cannot decide which measurement is true.