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

Make the Text Behave

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

Standard quoted exactly

Use a digital tool to clean and organize text-based data.

Example from the standards. Students can split text into words, sort by frequency or collate, normalize spelling, remove punctuation, and make everything lowercase.

Student-friendly learning targets

  • I can turn messy text into a clean, lowercase word list by splitting, stripping punctuation, and normalizing spelling.
  • I can build a frequency table that collates the same word written different ways.
  • I can name which cleaning step I used and why it changes what a model would count.

Essential questions

  1. Why does a computer treat Boise, BOISE, and boise, as three different words?
  2. Which cleaning steps help a model, and which steps can hide meaning we actually need?
  3. What does a frequency list reveal that a raw paragraph hides?

Objectives

  1. Split a public Idaho text dump into tokens using a spreadsheet formula or a printed tally sheet.
  2. Normalize case, punctuation, and a short spelling-variant list (Boise/BOISE/boise,).
  3. Collate tokens into a sorted frequency table and report the top ten words.
  4. Explain one cleaning choice that would change a downstream word-count feature.

Key vocabulary

Token
A single unit of text after splitting, usually a word. Cleaning decides what counts as one token.
Normalization
Making the same idea look the same to a computer: lowercase, stripped punctuation, agreed spellings.
Collate
Group matching tokens together so Boise, BOISE, and boise count as one word, not three.
Frequency table
A two-column list of each unique token and how many times it appears after cleaning.
Stop word
A very common word (the, and, of) that often adds little meaning. Removing them is a choice, not a law.

Teacher background

Models that read text do not see paragraphs; they see tokens. If students leave punctuation glued to words, BOISE and Boise stay different features, and a frequency model learns noise. This lesson is spreadsheet-first: LOWER, SUBSTITUTE or TRIM, SPLIT or Text to Columns, UNIQUE, COUNTIF, and SORT produce a clean bag-of-words table without writing Python. The standard example is the whole lab: split, sort or collate, normalize spelling, remove punctuation, lowercase. Warn students that aggressive cleaning can erase signal (Idaho vs. idaho is fine to merge; 208-area phone fragments or named rivers may not be). Keep the source public: NWS fire-weather discussions, USDA crop notes, or Idaho Fish and Game weekly fishing blurbs. Never paste student essays, names, or school emails into a tool.

Materials and prep

Materials

  • One public messy text file (printed and CSV): mixed-case Idaho wildfire incident names plus a few crop-report sentences, with punctuation and spelling variants.
  • Spreadsheet (Google Sheets, Excel, or LibreOffice Calc) with a starter tab of formulas; printed formula crib sheet.
  • Offline pack: the same text cut into word cards, punctuation stickers, and a tally sheet.
  • Short spelling-variant key: Boise/BOISE/boise,; wildfire/wild fire/Wildfire; russet/Russet/RUSSET.
  • Projector for a 6-row before/after demo.
  • Exit ticket half-sheet: top-ten table plus one sentence on a cleaning tradeoff.

Before class

  • Build a 40–60 token messy corpus from public NIFC incident names and a USDA Idaho crop brief. Include at least five punctuation glues (Boise,) and three spelling variants.
  • Pre-load a spreadsheet with a RAW column and empty CLEAN / TOKEN / COUNT columns. Hide the KEY tab until guided practice.
  • Print the offline card-sort pack (one per pair) for no-device and substitute days.
  • Confirm the district allows the spreadsheet tool offline or via cached files; no login-walled AI required.
  • Decide the stop-word rule for today: keep them in period 1, optional removal in the 90-minute block.

Instructional sequence

Three labels, one city

5 min
  1. Show three cells: Boise | BOISE | boise, and ask which the spreadsheet treats as the same word.
  2. Students vote on paper: same, different, or it depends. Reveal that without cleaning they are three different strings.
  3. Name the job of the hour: make the text behave so counts mean something.

The five cleaning moves

8 min
  1. Write the pipeline on the board: lowercase, strip punctuation, split on spaces, normalize spelling, collate and sort by frequency.
  2. Demo LOWER() and a nested SUBSTITUTE that peels commas and periods off one cell. Show Text to Columns or SPLIT as the split step.
  3. Collate with UNIQUE plus COUNTIF, then SORT the count column descending. Circle the top token.
  4. State the rule: we clean on a copy; the RAW column stays untouched so we can audit.

We-do on eight wildfire names

12 min
  1. Project eight messy incident strings (e.g., 'Pioneer Fire', 'PIONEER fire.', 'pioneer-fire'). Students predict the token count before cleaning.
  2. Walk LOWER, punctuation strip, and a find/replace of hyphen to space. Recount as a class.
  3. Build the frequency table together. Ask why 'fire' now dominates and whether that is useful for an incident-name model.
  4. If devices fail, do the same eight strings as a card sort: lowercase stickers, drop comma cards, stack duplicates, tally.

Clean the crop-note paragraph

10 min
  1. Each student (or pair) gets the remaining public crop-note text, about 80–120 words.
  2. They produce a cleaned token column and a frequency table of at least the top ten words.
  3. They mark one spelling merge they made and one token they refused to merge, with a reason.
  4. Offline path: highlight on the printed paragraph, list unique words, tally in the margin.

Real-world examples

  • Idaho Fish and Game weekly fishing reports: the same reach of the South Fork is written South Fork, S. Fork, and southfork; a count of mentions is wrong until those collapse.
  • NWS fire-weather discussions for the Boise foothills mix ALL CAPS headlines with sentence case; a keyword alert for red flag needs lowercase plus punctuation strip.
  • USDA NASS Idaho potato notes spell russet, Russet Burbank, and R. Burbank; a variety-frequency feature is garbage until a spelling key exists.
  • High school sports recaps from public maxpreps-style pages glue team names to scores (Vandals,); splitting and stripping is the first stats step.

Hands-on activity

Top-ten table and a cleaning log

10 min
  1. Students finish the frequency table and copy the top ten onto the exit ticket.
  2. They complete a three-line cleaning log: what they lowercased, what punctuation they removed, which spelling variants they collated.
  3. Spot-check one row against RAW so they practice audit, not just formula trust.
  4. Quick pair swap: partner tries to break the table by finding a token that still has punctuation.

Discussion questions

  1. If we delete the word fire from every wildfire name, what do we gain and what do we lose?
  2. Should 208 or I-84 stay in a token list of Idaho texts? When is a 'number' actually a place?
  3. A model trained on unclean text looks confident. How would you prove the confidence is counting junk?
  4. Who should write the spelling key for russet vs. Burbank — a data intern or a Magic Valley grower?

Differentiation

Support

  • Give a formula strip with LOWER, SUBSTITUTE, and COUNTIF already filled; students only paste and sort.
  • Start with a 20-token card sort before the spreadsheet so the idea is physical.

Challenge

  • Add a stop-word list and rebuild the top ten; write when stop-word removal would hide a signal (e.g., not in a phrase like 'not irrigated').
  • Write one extra column that flags tokens containing digits (I-84, 2024) and argue keep vs. drop.

Multilingual learners

  • Allow a two-language spelling key (Spanish/English crop terms such as papa/potato) and treat them as variants only if the task says they mean the same.
  • Provide the pipeline verbs on a bilingual strip: lowercase, strip, split, collate, sort.

IEP / 504

  • Large-print messy text and a one-page pipeline diagram; extra time on the frequency table, not extra tokens.
  • Speech-to-text is fine for the cleaning log; the data itself stays the public handout, never a personal recording.

Assessment

Formative

  • Warm-up vote plus the eight-name we-do table.
  • Cleaning log: three moves named in student language.

Summative

  • Exit ticket: top-ten frequency table from the crop notes, plus one sentence on a merge they refused.
  • Success if the table is sorted, tokens are lowercase without trailing punctuation, and the refused merge is justified.

Success criteria

  • Tokens are lowercase and free of attached commas/periods.
  • Spelling variants from the key are collated, not triple-counted.
  • Student can point to the RAW column and the CLEAN column and explain the difference.

Responsible use, ethics, and privacy

Responsible use

Use only the public handout or a teacher-provided public scrape. Do not paste classmate writing, social posts, or anything with a student name into a cloud spreadsheet or an AI cleaner. District-approved tools only; no login-walled scrapers.

Ethics

Cleaning is not neutral. Dropping dialect spellings, tribal place names, or Spanish crop terms can make a model look neater while erasing who is in the data. Students must log the merges they make.

Privacy

FERPA: this lab uses public wildfire names and crop notes, never student PII, emails, or school IDs. If a student spots a personal name in a public scrape, flag it and redact before sharing.

Reflection

  1. Which cleaning step changed the top ten the most, and why?
  2. What would a Magic Valley agronomist say we over-cleaned?
  3. How would you teach a ninth-grader the difference between RAW and CLEAN in one sentence?

Homework

On paper, take a public 6–8 sentence Idaho weather or sports blurb (printed in class). List ten tokens before cleaning and ten after. Circle one punctuation strip and one spelling merge. Do not use a personal message thread.

Closing

Hold up the before/after of Boise / BOISE / boise,. Say: models count strings, not cities, until we make the text behave. Tomorrow a number will pretend to be a word.

Extensions and cross-curricular links

Go further

  • 90-minute block: add stop-word removal, a bigram column (two-word phrases such as red flag), and a second public source (river-gauge comments) to compare top tens.
  • Python notebook extension (not required): split with a regex, lower, and Counter; compare the notebook top ten to the spreadsheet top ten and explain mismatches.
  • Build a class spelling dictionary for Idaho place names and reuse it in DA.4.
  • Trace a printed paragraph with colored pencils (case / punctuation / variants) for a no-device sub plan.
English Language Arts
Tokenizing is close to word study: students see how punctuation and capitalization change what counts as a word.
Statistics
A frequency table is a distribution; students can talk mode and long tail (the vs. Pioneer).
Agriculture / CTE
Crop notes and variety names are the messy text growers actually produce; cleaning is a farm-office skill.