Decide whether rows belong together before combining worksheets
Decide whether records describe the same observation before stacking them in one CSV, using branch and branch-month examples.
Start with the meaning of one row. Rows describing branches and rows describing branch-month visits should not be stacked into one ordinary observation table merely because they came from the same workbook. Conversely, branch-month records from several worksheets can belong together when their fields, units and definitions agree.
This is a decision about row meaning, often called the data’s grain. File size is a separate question.
Name the thing each row represents
Finish the sentence “Each row represents…” for every source sheet. In a hypothetical workbook, one sheet lists branch facilities; another records monthly visits.
| Source | One row represents | Example identifying fields |
|---|---|---|
| Branch directory | One branch | branch_id |
| Monthly visits | One branch in one month | branch_id, period |
| Another month’s visits | One branch in one month | branch_id, period |
The first two sheets have different row meanings. The second and third share a row meaning, although their periods differ. Wickham’s Tidy Data distinguishes variables, observations and types of observational unit; that distinction provides the basis for deciding which records belong in one table. Original paper.
Follow the decision path before appending
- Does each row describe the same kind of observation? If no, retain separate tables with a documented relationship. If yes, continue.
- Do shared fields have the same definitions and units? If no, resolve the difference or preserve it explicitly. A matching column heading alone is insufficient.
- Can the identifying fields distinguish the observations? Include the branch and period for branch-month rows. Resolve repeated keys before combining the sheets.
- Are some rows summaries of the others? Keep totals out of the ordinary observation rows. Otherwise an unqualified sum can count the same visits twice.
These are publication checks, not a claim that every file containing multiple record types is invalid. Some specified exchange formats deliberately use record-type markers. If you use such a format, publish its schema rather than asking readers to infer it from blank cells.
Apply the decision to the branch example
Combine compatible monthly-visits sheets into a table with branch_id, period and visits. Keep the branch directory separately, with one row per branch. Document how branch_id connects the tables; the foreign-key guide covers that next step.
A reader-facing download may add the branch name to each visit record. Label that as a derived branch-month table. Its row meaning remains branch-month even though the branch name repeats. If you instead want one row per branch, choose and document a summary operation before preparing that different output.
Check the result against its stated grain
Write the row definition beside the download. Confirm that required key fields are present, that the intended combinations identify rows and that each source observation appears as intended. The primary-key guide explains how to document those identifying fields.
For the hypothetical project, a branch row with no period does not belong among ordinary branch-month visit records. Resolve its role—directory entry, subtotal or missing data—before publishing. The finished table should let a reader understand what one row means without inspecting every other worksheet.