Skip to content
Idaho AI Classroom
All lessons
AI Technical AptitudeData and Analysis9-12.AITA.DA.2

When a Number Is a Word

9–12 CS / engineering50 minutes90 minutes (lab extension)

Standard quoted exactly

Evaluate different approaches to verifying consistency and compliance with expected data types, values, and ranges.

Example from the standards. While preparing a data source for use, a column of numerical data has a row with a word/string.

Student-friendly learning targets

  • I can name the type, allowed values, and range a column is supposed to have before I trust it.
  • I can catch a word sitting in a number column and compare at least two ways to handle it.
  • I can argue why deleting the row, recoding it, or flagging it are different scientific choices.

Essential questions

  1. How do you know a cell that looks like 12.4 is actually a number a model can use?
  2. When is ice, NA, or trace a missing value, a real measurement, or a lie?
  3. Which check should run first: type, range, or allowed-value list?

Objectives

  1. Write a one-line schema for each column: type, units, legal min/max, and missing-value codes.
  2. Apply at least three verification approaches (type check, range check, allowed-list, duplicate key, visual scan) to a messy public table.
  3. Evaluate tradeoffs of drop, impute, recode, or quarantine for a string in a numeric column.
  4. Document a validation rule a teammate could rerun.

Key vocabulary

Data type
What kind of value a column should hold: number, text, date, true/false. A word in a number column is a type violation.
Range check
A test that a number sits between a real-world min and max (Snake River discharge cannot be −50 cfs).
Schema
The contract for a table: column names, types, units, allowed values, and missing codes, written before cleaning.
Sentinel value
A fake number used to mean missing (9999, −999, 0). It will wreck averages if you treat it as real.
Quarantine
Move suspect rows to a review sheet instead of deleting them so the original evidence survives.

Teacher background

The standard is evaluation, not a single 'fix.' When a numeric discharge column contains ice or NA, students must compare approaches: spreadsheet ISNUMBER / VALUE, a printed type-tally, a range gate (cfs > 0), an allowed-list for quality flags, and a human visual scan of the first 30 rows. Deleting the row is one approach and often the worst: ice on a USGS gauge is information. Recoding ice to missing, adding a quality-flag column, or quarantining for a hydrologist are competing designs. Use a public USGS Snake River or Boise River gauge extract plus a USDA potato-yield slice with n/a and a typo like 'tweleve'. Spreadsheet-first: data validation rules, COUNTIF of non-numeric, MIN/MAX vs. stated range. Python type checks are an extension.

Materials and prep

Materials

  • Messy CSV (and printed table): USGS-style daily discharge with numbers, ice, ---, NA, and one 'offline' row; a yield column with n/a and a word.
  • Blank schema card per pair: column, type, units, min, max, missing codes.
  • Approach cards to sort: type check, range check, allowed list, duplicate key, visual scan, checksum, plot.
  • Spreadsheet with ISNUMBER, VALUE, COUNTIF, MIN, MAX; printed formula crib.
  • Red/yellow/green flags (paper) for drop / recode / quarantine.
  • Exit ticket: one violated row and two evaluated approaches.

Before class

  • Build a 25-row public-looking table: site_id, date, discharge_cfs, water_temp_c, potato_cwt_per_acre. Plant at least six violations (string in number, 9999 sentinel, negative yield, future date, duplicate site-date, temp 140 F labeled as C).
  • Print the table for offline; highlight nothing — students find the faults.
  • Write the 'true' schema on a teacher key: discharge 0–200000 cfs, temp −2 to 30 C, yield 0–800 cwt/acre.
  • Practice the ice conversation: ice is not a number and not garbage; it is a condition code.

Instructional sequence

Spot the fake number

5 min
  1. Project one column: 4200, 4188, ice, 4301, NA, 12, 9999. Students mark which cells a SUM would silently destroy.
  2. Collect two nominations. Do not fix yet. Name the hour: verify before you average.

Five ways to catch a liar cell

10 min
  1. Write the schema first: type, units, min, max, missing codes. A check without a schema is a vibe.
  2. Approach 1 — type: ISNUMBER / VALUE / 'does it parse as a float?' Approach 2 — range: compare to physics and Idaho climate. Approach 3 — allowed list: ice, NA, --- are codes, not yields.
  3. Approach 4 — visual scan of head/tail/random rows. Approach 5 — plot or MIN/MAX to catch 9999.
  4. List four responses: drop the row, recode to missing, split codes into a flag column, quarantine. None is always right.

Schema, then hunt

12 min
  1. Pairs fill the schema card for discharge_cfs and potato_cwt_per_acre before looking at the messy rows.
  2. Run a type check together on discharge (spreadsheet or printed circle-the-words). Count string cells.
  3. Run a range check on yield. Debate 9999: sentinel or world-record field?
  4. As a class, pick a response for ice and write the reason in one sentence.

Evaluate, don't just delete

10 min
  1. Each student chooses two remaining violations and, for each, names two approaches that would catch it and one that would miss it.
  2. They recommend drop, recode, flag, or quarantine and state who would be harmed if they chose wrong (a hydrologist, a grower, a model).
  3. Offline: annotate the printed table with approach initials (T/R/A/V/P).

Real-world examples

  • USGS Snake River at Hells Canyon: winter rows really do say ice instead of cfs. Averaging ice as zero invents a dry river.
  • Idaho Power load tables sometimes store offline or DST as text in a MW column; a peak-load model will skip or crash.
  • County potato yield spreadsheets use n/a, NA, and blank for unharvested acres; those are different facts.
  • Boise River flood stage in feet mixed with a row in meters will pass a type check and fail a range check — two approaches, two different bugs.

Hands-on activity

Validation rule on a sticky

8 min
  1. Students write one reusable rule: 'discharge_cfs must be numeric and 0–200000; ice/NA go to flag column, not to cfs.'
  2. They apply the rule to the 25-row table and report how many rows pass.
  3. Group share: which approach is cheapest, which is most trustworthy, which still needs a human.

Discussion questions

  1. Why is deleting every non-numeric row a dangerous default for river gauges?
  2. A range check says 140 C water is impossible. Could the unit be Fahrenheit mislabeled? How do you find out?
  3. Which approach would still miss a yield of 400 that is a typo for 40?
  4. If two counties use different missing codes, whose schema wins in a statewide model?

Differentiation

Support

  • Pre-filled schema with blanks only for min/max; fewer rows (12) with violations already highlighted in a support copy.
  • Sentence stems: 'This cell fails a ___ check because ___ . I would ___ because ___.'

Challenge

  • Design a two-pass checker: type then range then cross-field (temp vs. month). Find the 140 C / F mixup.
  • Argue whether 0 cfs is a legal min for an irrigated canal vs. the Snake.

Multilingual learners

  • Missing-code list in English/Spanish (n/a, s/d, sin datos) so students see allowed-lists are language-aware.
  • Keep units spoken and written (cfs, pies cubicos por segundo) on the schema card.

IEP / 504

  • Printed table with extra row spacing; students may check only type and range, not all five approaches.
  • Allow oral evaluation of one violation if writing is the barrier; the thinking is the standard.

Assessment

Formative

  • Schema card completeness before the hunt.
  • Color flags on ice / 9999 / n/a during guided practice.

Summative

  • Exit ticket: one string-in-number row, two approaches that catch it, recommended response with a harm statement.
  • Do not score 'deleted the row' as complete unless the student evaluated another approach and rejected it.

Success criteria

  • Student writes type, range, and missing codes for at least one column.
  • Student compares two verification approaches, not just one fix.
  • Student treats ice/NA as codes to evaluate, not as automatic trash.

Responsible use, ethics, and privacy

Responsible use

Validate on public tables. Do not import class gradebooks, health surveys, or anything with student IDs to 'practice' type checks.

Ethics

Dropping every messy row can drop every winter day, every small farm, or every Spanish-labeled sheet. Verification choices move who is represented.

Privacy

Public USGS/USDA extracts only. If a classroom export ever appears, stop: FERPA forbids using student records as dummy data.

Reflection

  1. Which approach would you run first on a new CSV, and why?
  2. When is a human visual scan still better than ISNUMBER?
  3. What will you refuse to auto-delete after today?

Homework

Printed 10-row energy-load table (teacher handout). Write a schema for MW and timestamp. Circle every type or range violation. Propose drop, recode, flag, or quarantine for two of them. No personal utility bills.

Closing

Point at ice in the discharge column: that is a word doing a number's job, and it is also a fact. Verification is a set of approaches, not a delete key. Next we walk a whole potato-yield file from raw to train/dev/test.

Extensions and cross-curricular links

Go further

  • 90-minute block: add a second table (Idaho energy load) and write spreadsheet data-validation rules that reject new bad rows on entry.
  • Python extension: pandas to_numeric(errors='coerce') vs. a regex type check; compare how each treats ice and 1,200 with a comma.
  • Card-sort the seven approach cards onto 'catches ice', 'catches 9999', 'catches unit mixup', 'catches nothing here'.
  • Invite a guest (county GIS, irrigation district) to say which missing codes they actually use.
Mathematics
Domain and range of a function are the same idea as a column schema; 9999 is an outlier only after you define the domain.
Earth science
Gauge ice and flood stage are physical constraints that make range checks more than computer hygiene.
Statistics
Mean and standard deviation lie if sentinels stay in the column; verification is a prerequisite to any summary.