Fixing Duplicate Entries When Combining Word Lists
TL;DR: Merging lists duplicates STAIN in three capitalizations, with a trailing space, and as a six-letter STAINER you forgot to filter. Deduplicate on a normalized key: uppercase, trim, length=5, slot 3=I. Then unique(). If you unique() first, STAIN and stain and “stain ” survive as three. Order of operations is the whole article. Pretty Excel is not a method.
Normalize before unique
Trim spaces. Casefold. Strip punctuation. Then length. Then slot 3. Then unique. If you unique on raw rows, you keep junk twins. Junk twins inflate counts and crash “random pickers” that land on the same word twice in a party. Parties argue. Arguments are duplicate STAIN with a space. Spaces are invisible. Invisible is why you trim first, not after someone yells.
Excel: =TRIM(UPPER(A2)) in a helper, then length, then MID(helper,3,1)=”I”, then a unique on the helper. Helpers are ugly and correct. Correct is the only pretty that matters.
Length is a filter, not a hope
Paste from a webpage includes headings, “five letter,” and DAISY with a footnote star. Stars make length 6. Stars fail length=5 and should. If you strip stars after unique, you might merge DAISY and DAISY*. Good. If you never strip, you keep a fake word that sorts to the top. Top is where eyes go. Eyes then type a star into Wordle. Wordle does not like stars. Filters like length.
Use one canonical page as source A, your club file as source B. Two sources, one normalize pipeline. Three sources without a pipeline is a hymn to chaos. Chaos looks like a 900-row sheet with 400 unique real fives. The 500 extra rows are ghosts. Ghosts slow search-in-page. Slow search misses BRICK. Missing BRICK is a duplicate-tax on your attention, not a vocabulary gift.
Slot 3 after length
If you test slot 3 on a six-letter ghost, you might keep a word with I in 3 that is still illegal by length. Order: length then slot. Reverse order keeps IRATES if you naively look at character 3 in a longer string. Character 3 in IRATES is A. Anyway: length first. Always length first. This is the homophone of every other article’s length-first. It is still true in spreadsheets. Spreadsheets do not read the other articles. You must.
Proper nouns: decide a rule, then filter. If you filter after unique, you still unique’d PARIS and paris as two then dropped both or one. If PARIS is banned, drop it in the rule stage, then unique. Rules before unique, or unique will treat bans as a surprise.
Spot-check like a scientist
Sort the unique list. Scan for STAIN twice. If twice, your key is dirty. Scan for PIZZA. If present, slot filter failed. Scan for a six-letter. If present, length failed. Three spot-checks beat a vibe that “the unique button is green.” Green buttons are not tests. Tests are PIZZA. PIZZA is a unit test. Keep PIZZA as a poison pill in source data on purpose once, to see it vanish. If it does not vanish, stop the party. Fix the pipeline. Then party.
Random sample 10 words, count slots on paper. Paper finds MID bugs (off-by-one). Off-by-one is the demon of 1-index vs 0-index. Excel MID is 1-index. Python is 0-index. Mixing in one notebook is how slot 3 becomes slot 4. Pick a language per file. Do not mix in one cell. Cells are not bilingual without tears.
Version the output
Save unique_v3.txt, not unique.txt overwritten. Overwrites are how you cannot explain why yesterday’s list had 380 and today’s has 402. Diff the versions. Diff is academic. If you cannot diff, you are collecting again. Collecting was the disease. Versioning is the medicine. Medicine is a dated filename. Dated filenames are uncool and adult. Be adult. Parties can still use unique_v3. They do not need to know it is v3. You do. You are the person who will be asked “why is STAIN twice.” Twice is a version story. Tell it with files, not with shrugs.
When the website updates, re-run the pipeline; do not hand-merge. Hand-merges reintroduce spaces. Spaces were the villain in paragraph one. They always come back if you pet them. Do not pet them. Run the pipeline. Go outside.
CSV versus Excel pretty-merge
Pretty-merge in a GUI will silently keep the first STAIN and drop a comment in column B that said “ban this.” Comments are data. Data dies in pretty-merge. CSV plus a script that you can read is uglier and keeps comments until you decide. Decide is a verb. Verbs should be in the pipeline, not in a click you cannot replay. Replay is version control. Version control is unique_v4 with a log line: “dropped PARIS.” Log lines are academic. Academic is this article’s cousin. Cousins should be circled. Circle the log. If you cannot replay, you cannot answer “why is STAIN twice.” Twice is a question that will arrive at 11 p.m. 11 p.m. is a bad time to click Undo in a GUI that forgot. GUIs forget. Files remember. Remember with CSV. CSV is five letters. It is not middle-I. It is still a word you need. Need is the whole pipeline. Pipeline is trim, case, length, slot, rules, unique, log. Log is the suffix. Suffix it. Then go outside. Outside is not a cell. Cells were enough. Enough is unique_v3 dated. Dated is a letter in the filename. Filenames are a lexicon of your own. Own it. Do not pretty-merge it into a fog.
