Trade gains and data · Lesson 3

Cleaning comparisons and classifications

6 min read

Lesson objectives

  • Build a comparable dataset without mixing totals, details, classifications or periods.
Reading tools

A clean table is more than a table without blank cells. Its rows must represent consistent analytical units. Before summing trade values, define the row key: reporting economy, partner, period, flow, classification version, product code and level, plus any extra dimension such as customs procedure. Two rows matching on these fields may be duplicates, or the extract may have omitted a distinguishing field. Do not delete one merely because its value is identical. Inspect the source and its dimensions first. An explicit row key helps prevent totals from multiplying when files are joined.

Product codes form a hierarchy. The initial digits describe a broader group, and detailed-code values enter the parent total. Do not add a four-digit parent to all of its six-digit children. If a group totals 200 and its children are 120 and 80, the total is 200, not 400. Use either the aggregate or a mutually exclusive set of details covering it. Aggregate rows are useful reconciliation checks rather than additional observations in an indicator numerator. The same reasoning applies to a World partner total and its individual partners, or an annual total and the months included within it.

Store codes as text. A leading zero can be part of their identity; the code is not a number to round or add. Equal code length does not make classification versions equivalent. A code may split into two new codes, or a new category may combine parts of several old ones. Obtain an official correspondence table and inspect the relationship. One-to-one mappings are relatively simple. A one-to-many relationship does not, by itself, allocate the old value across the new branches. Equal allocation would invent observations rather than recover them.

Consider a fictional old code A worth 100 that splits into B and C. In the new year B is 60 and C is 50, totaling 110. Comparing old A with new B+C is defensible if the correspondence confirms identical coverage with no extra components. Group growth is ten percent. We cannot say B grew from 50 to 60: the old 50 would come from an unsupported equal split. If a decision needs B’s separate historical trend, request recoded historical data or report that the comparison is unavailable. A broader common grouping can preserve meaning at the cost of some detail.

Time coverage causes another frequent error. Nine months compared with a full year can suggest a decline even when monthly performance improved. Choose a shared period or clearly identify partial coverage. Do not automatically multiply a three-month observation by four when demand is seasonal. A month may arrive late and earlier values may be revised. Record the extraction date and release status, and distinguish a missing month from a reported zero. A blank cell does not become an economic zero because spreadsheet addition ignores it.

After selection, perform three reconciliations. First, compare the sum of details with the published total on the same basis. Second, count rows before and after joins; unexpected multiplication can expose a many-to-many join. Third, review period, product and partner coverage. A mathematically correct total can still omit important observations. If details do not reconcile, investigate the difference rather than distributing the residual across products without evidence. Confidentiality, rounding, unclassified records or update differences may matter, but source documentation determines which explanation can be established.

A reproducible transformation note states the original file, selected rows, anti-duplication rule, correspondence used and unresolved comparability limits. Keep the raw file and create a separate analytical copy. The output is a comparable table with a clear limitation note. This work is substantive analysis: it determines whether a subsequent growth rate or market share answers the intended question. The most polished chart cannot repair a denominator built from overlapping categories or mismatched periods.

Consider a more complex correspondence. Old categories K and Q have values of eighty and sixty. In the new classification, parts of both move into L and M, now worth ninety and seventy. The correspondence confirms that the union of K and Q matches the union of L and M but gives no allocation shares. Comparing the old group total of 140 with the new total of 160 gives growth of 20/140, or about 14.286 percent. It does not permit a direct K-to-L comparison or an invented equal allocation of the old eighty.

If a manager needs L alone, request a supported historical recoding or use the common group and disclose the loss of detail. If new L also contains material outside the old union, even the 140-to-160 comparison fails until that material is isolated or the grouping expanded. Correspondence is a coverage test rather than a match between labels.

Add relationship and comparison-decision columns to the working table. Distinguish one-to-one, one-to-many, many-to-one and many-to-many mappings. This does not automatically resolve the economic correspondence, but prevents a technically successful join from being mistaken for comparable categories. Check row counts and values before and after joining. If 140 becomes 280 merely through the join, inspect duplicated values across mappings before calculating growth.

Lesson summary

Build a comparable dataset without mixing totals, details, classifications or periods.