Map changing category codes across CSV releases

Keep codes as exact text

A category code identifies a value in a particular release and column. Treat 01 as the string 01 until a documented decision says how it should be interpreted. Keep an unchanged copy of each source file and record its release, filename and definitions.

If you inspect CSVs in Excel, automatic conversion can remove leading zeros. Microsoft documents importing through Data → From Text/CSV and setting columns to Text in Power Query before loading. Follow the steps for your Excel version in Keeping leading zeros and large numbers. This exercise was checked with a CSV parser and Chrome; Excel execution was not tested.

CohortCrosswalk preserves original strings, including formula-like text. Import every output column as text when using a spreadsheet. The export does not sanitize spreadsheet formulas or silently rewrite source cells.

Compare two small annual tables

1. Extract the free archive and open examples/README.md for the fictional definitions.
2. Open parks-2023.csv and parks-2024.csv as text. Each contains a header and three data rows.
3. Use access as the shared concept. The source column is access_code in 2023 and entry_status in 2024.
4. Write one decision for each release and exact code using the exercise definitions below.

2023 access_code    2024 entry_status    Reviewed result
01                  FREE                 free
02                  PAID                 paid
99                  UNK                  missing: not recorded

For this invented exercise, free means no entry charge and paid means an entry charge. The definitions establish the correspondence; the spelling of the codes does not. Record separate decisions for 2023/01 and 2024/FREE even though their chosen target is the same.

Expected across both tables: six original rows, four mapped rows and two explicitly missing rows. The missing codes remain 99 and UNK in their source columns.

Resolve the deliberate unknown in the app

The paid app’s built-in version of this exercise deliberately leaves one decision unfinished. It shows how an unresolved value keeps a complete handoff from being exported.

1. Extract the paid ZIP, keep the app files together and open app/index.html in Chrome on Linux. Choose Try the parks example.
2. Expect 5/6 codes covered. Filter Needs review and find release 2024, concept access, exact code UNK.
3. Choose Explicit missing, enter not recorded as the reason, then choose Save decision.
4. Expect 6/6 codes covered and zero data rows needing review. Choose Download complete ZIP.

For your own inputs, select and preview each file, choose the correct comma or tab separator and connect each intended column to a shared concept. Coverage records your saved decisions. It does not establish that the underlying categories mean the same thing.

Compare the result with the expected CSVs

The free pack contains parks-2023-expected.csv and parks-2024-expected.csv. Compare them with your own result or with the corresponding CSVs in the exported ZIP. Read the values as text and check the original columns first.

park_id,access_code,access__value,access__status,access__reason
A,01,free,mapped,Practice definition: no entry charge
B,02,paid,mapped,Practice definition: entry charge
C,99,,missing,not recorded

• The 2023 table retains A/01, B/02 and C/99 in their original order.
• The 2024 table retains D/FREE, E/PAID and F/UNK in their original order.
• Only appended access__value, access__status and access__reason fields express the reviewed result.
• Mapped rows have the expected free or paid value. The two missing rows have an empty result value, missing status and not recorded reason.
• The report identifies the inputs and records the decisions. A complete review has six covered codes; it is not a certificate of scientific correctness.

Leave a new category unresolved until it is defined

The extra parks-2025.csv introduces SEASONAL. The practice pack gives it no settled correspondence. Do not assign free or paid simply to make the coverage counter complete. Find or define an adequate source explanation for your scenario, then record that decision.

Reusing another release as a template gives suggestions and resets destination approvals. Review each new release on its own terms. If multiple source codes map to one target within the same release, record why the merge is appropriate and acknowledge the information loss for every contributor.

Save a project before closing; the app has no autosave. Keep the exact original filenames and bytes separately because the project does not embed the complete tables. Restoration checks those originals. Changed input requires a fresh review.

Use a scope that fits the data

CohortCrosswalk 1.0.0 is for nonclinical open aggregate tables in UTF-8 CSV or TSV. It does not perform record joins, statistical analysis, fuzzy matching or automatic equivalence. XLSX, DTA and SAV are outside its supported input formats.

Verified environment: Chrome 152 on Linux. Other browsers and operating systems are unverified. Combined limits include 10 releases, 10 concepts, 5 MiB source data, 20,000 data rows, 200,000 cells including headers and 5,000 observed decisions. All limits apply together; see the complete setup guide for the remaining bounds.

Start with the six-row exercise, inspect the outputs and decide whether this review workflow suits your own tables. You remain responsible for source definitions, the meaning of each mapping and the analysis that uses it.