PHASE 4Beginner ~26 min· topic 4 of 7

Topic 4.4

Cleaning Real Data

In one line

Real exports have blanks, "n/a", numbers with commas, impossible dates and outliers. Look first, then fix only what is certain, flag what is missing, drop what cannot be placed, never invent values, and keep a log of every change.

Think of it like this

A librarian gets a returned box of books. Some are damaged, one belongs to another library, one is a copy of a book already on the shelf. She does not rewrite the pages or guess a missing title. She repairs what is clearly fixable, marks what is uncertain, sets aside what does not belong, and writes each decision in a notebook. Cleaning data is that notebook discipline.

Words you'll meet

New words in this topic, in plain English. Come back here whenever one feels fuzzy.

cleaning: Repairing, marking or removing bad rows so charts can trust the data, while recording what was done.
null: JavaScript's value for "no value here". In this course it means missing, which is different from 0.
outlier: A value far from the others. It may be an error (1,240 minutes) or real (the August outage day): look before you remove it.
duplicate: The same record twice, such as 21 March in the file two times in a row.
sensor code: A special number a device writes instead of a measurement, like -1 for no reading.
log: A list of every change made, with the old value, the new value and the reason.

Step by step

01List the problems with line numbers

The first example checks every cell against a rule for its column and prints the problem with its file line. No data is changed yet. This is the step you do for any new file: first learn what is wrong.

02Decide an action per problem

A small table of decisions keeps the team consistent.

the cleaning policy for daily.csvwhole filetext

blank / n/a / "-"        -> flag    (null, kept as missing)
"2,016"                  -> fix     (thousands separator)
13/03/2025               -> fix     (first number above 12: day-first)
05/03/2025               -> drop    (ambiguous: could be 5 March or 3 May)
2025-03-32               -> drop    (not a day)
weekday "wed"            -> fix     (taken from the date)
rain_mm -1               -> flag    (sensor code, not rain)
avg_minutes > 180        -> flag    (a ride average above 3 hours is not real)
same row twice           -> drop    (keep the first)
same day, other numbers  -> drop    (keep the first, log it)

03Check what is still missing

After cleaning, compare the dates you have with the dates you expect: 25 March has no row, and 4 March has a row whose rides is null.

04Run the cleaner and read its log

The second example is the cleaner that goes into src/data/clean.js in the build topic. On the March file it keeps 30 of 32 rows and writes 9 notes. Read them against the table above: 3 fixed (the comma number, the date, the weekday), 4 flagged (the blank rides, the n/a temperature, the rain code, the 1,240 minutes), 2 dropped (the duplicate and the impossible date).

It then compares the totals. Trusting +d.rides gives 59,415 rides. After cleaning: 56,930. The naive total counted the duplicate day and the impossible-date row, and missed the "2,016" because +"2,016" is NaN.

Try it yourself

1
Make the ambiguity rule stricter or looser
In the second example's parseDate, change if (day <= 12) return null; to if (day <= 31) return null;. The 13/03/2025 row is now dropped instead of fixed: 29 rows kept, still 9 notes. A stricter rule loses a day that was certainly 13 March, so the 12 limit is the right one.
2
Lower the outlier limit
Change value > 180 to value > 24. Five real days are flagged too (24.8, 24.7, 25, 24.4 and 25.3 minutes) besides the 1,240, and the log grows to 14 notes. A limit that is too tight throws away real data, so choose it from what a ride can really be.
3
Run it on the clean file
Change daily-dirty.csv to daily.csv in the second example. Output: all 365 rows kept and a log of 0 notes. A cleaner that changes clean data would be a bug.

Code & diagrams

Look first: list every problem in the March file New tab

Reading with d3.csv and no autoType keeps every cell as text, so nothing is hidden. The line number is the position in the file (line 1 is the header).

Preview

Press Run to see the page.

Console

Nothing logged yet.

Expected console output

32 rows read, 9 problems found:
line  5: rides is blank
line  9: temp_c is 'n/a', not a number
line 12: rides is '2,016', not a number
line 14: date '13/03/2025' is not YYYY-MM-DD
line 17: rain_mm is -1 (a sensor code, not rain)
line 20: weekday 'wed' is not in the same form as the others
line 23: exact duplicate of an earlier row
line 27: date '2025-03-32' is not a real day
line 29: avg_minutes is 1240 (more than 3 hours)

Clean it with a log, and see what it cost to trust the file New tab

This is src/data/clean.js with its exports removed and a demo underneath. The 2025-03-32 row is dropped, not read as 25 March: the log says why.

Preview

Press Run to see the page.

Console

Nothing logged yet.

Expected console output

Rows in the file: 32 | rows kept: 30
FLAGGED 2025-03-04 rides       "" -> null  (no usable number, kept as missing)
FLAGGED 2025-03-08 temp_c      "n/a" -> null  (no usable number, kept as missing)
FIXED   2025-03-11 rides       "2,016" -> 2016  (comma was a thousands separator)
FIXED   13/03/2025 date        "13/03/2025" -> "2025-03-13"  (day-first date: 13 cannot be a month)
FLAGGED 2025-03-16 rain_mm     "-1" -> null  (-1 is a sensor code for no reading, not rain)
FIXED   2025-03-19 weekday     "wed" -> "Wed"  (taken from the date)
DROPPED 2025-03-21 row         "2025-03-21" -> null  (exact duplicate of an earlier row)
DROPPED 2025-03-32 date        "2025-03-32" -> null  (not a real calendar day, so it cannot be placed on the chart)
FLAGGED 2025-03-27 avg_minutes "1240" -> null  (an average ride cannot last more than 3 hours)
Total rides if you just use +rides: 59415
Total rides after cleaning:         56930
March days with no row: [ '2025-03-25' ]
Rows whose rides is null (kept as gaps): [ '2025-03-04' ]

Break it on purpose

Errors are the best teachers. Make each change, read the error, guess what went wrong, then reveal the answer.

Break #1

Replace the missing value with 0

In the second example's toNumber, change return null; to return 0; for the blank case.

terminal

$ Console

── what you'll see ──

Rows whose rides is null (kept as gaps): []

Myth vs fact

Myth

Cleaning means making the data look nice.

Fact

Cleaning means making it trustworthy, and showing what you changed. A tidy table that hides its repairs is less honest than a messy one.

Myth

Replace missing values with the average so charts look complete.

Fact

An invented value is indistinguishable from a real one. Mark gaps, or add a separate estimated field.

Myth

An outlier is always an error.

Fact

14 August (439 rides) in the real daily.csv is real: the app was down. Check the cause before removing a point; flag it instead.

Pro corner

Extra depth for experienced readers. New to this? Skip it for now and come back later.

▸
A cleaner should be a pure function: raw rows in, { rows, log } out, no fetching and no page access. That makes it testable with the March file as a fixture (Phase 16) and reusable in a worker for big files (Phase 12).
▸
The thousands-comma rule matches only ^\d{1,3}(,\d{3})+$. A European "2,5" (decimal comma) is not matched and stays NaN, then is flagged: a wrong guess in either direction would be silent, so unknown shapes are flagged, not fixed.

Remember this

1
The problem this solves: Priya's March export (daily-dirty.csv, 32 rows) breaks charts in different ways: a blank becomes 0 or NaN, "2,016" is skipped by a sum, an impossible date becomes an Invalid Date, a duplicate counts a day twice. Without a clear policy, every chart makes its own quiet choice and no two totals agree.
2
Look before you fix. Recall from Topic 4.2: d3.csv without autoType keeps every cell as text, so nothing is guessed for you. The first example reads every cell as text and lists each problem with its line number in the file (line 1 is the header). It finds nine: a blank rides (line 5), n/a for temperature (9), "2,016" (12), the date 13/03/2025 (14), rain -1 (17), weekday wed (20), a duplicate row (23), the date 2025-03-32 (27) and an average ride of 1,240 minutes (29).
3
Three honest actions. Fix when the right value is certain: "2,016" is 2016 because a comma between digit groups of three is a thousands separator, wed is Wed, and 13/03/2025 is 13 March because 13 cannot be a month. Flag when a value is unusable but the row is real: a blank, n/a, a rain of -1 (a sensor code for "no reading", not minus one millimetre) and a ride of 1,240 minutes become null, which means "missing", never 0. Drop when the row cannot be placed: 2025-03-32 is not a day, and an exact duplicate is the same fact twice.
4
Never invent data. It is tempting to fill the blank with the day's neighbours' average, or to read 2025-03-32 as the missing 25 March. The code does not: that row is dropped, and 25 March is reported as a day with no row. A guess that looks like data is worse than a visible gap, because nobody can tell it from real measurements later. If you do estimate, mark the value as estimated in its own field.
5
Ambiguity is not certainty. 05/03/2025 could be 5 March or 3 May. The cleaner fixes a day-first date only when the first number is above 12 and refuses the rest (null, dropped). That rule is a decision: write it where the next person can read it.
6
Keep a log. Every change is recorded as { date, field, was, now, action, why }. The log answers "what happened to my number?" for Priya, and its length is a health check: 0 notes for the clean daily.csv, 9 for the March file. Show it in the dashboard (the build topic shows its count) and keep it out of the way of the charts.
7
Missing is not zero. Charts treat null differently from 0: a line chart should break (or mark a gap) at a missing day, and a bar chart should show no bar, not a bar of height 0. Topic 7.1 handles gaps in lines. Here you only make sure the data says "missing" honestly.

Explain it without notes

Why is null better than 0 for a missing day?

Name three actions a cleaner can take on a bad row, with an example of each.

Why does the cleaner refuse to read 2025-03-32 as 25 March?

Practice

Add a rule to cleanDaily that flags temp_c below −30 or above 45 as null with a reason. Run it on daily.csv and report how many notes it writes.

Write missingDays(rows, from, to) returning the YYYY-MM-DD texts of days between two dates that have no row.

Trade-offs

↔
Dropping a bad row loses its good columns; flagging only the bad cell keeps them but needs every chart to handle null. This course flags cells and drops only rows that cannot be placed on a date.

Done when you can

I list the problems in a new file before changing anything.
I can choose fix, flag or drop for each kind of bad value, and say why.
I never replace missing with 0 or an invented value.
My cleaner returns rows and a log, and I read the log.
I compare the dates I have with the dates I expect.

Command Palette

Words you'll meet

Step by step

01List the problems with line numbers

02Decide an action per problem

03Check what is still missing

04Run the cleaner and read its log

Try it yourself

Make the ambiguity rule stricter or looser

Lower the outlier limit

Run it on the clean file

Code & diagrams

Break it on purpose

Replace the missing value with 0

Myth vs fact

Pro corner

Remember this

Explain it without notes

Practice

Trade-offs

Done when you can