Crossmaps: A principled approach to ex-post data harmonisation and dataset integration

Researchers and practitioners spend considerable time and effort designing and producing merged datasets. Unfortunately, dataset designs and merged datasets can be very difficult to audit, modify or reuse. Even with knowledge of the programming language, decoding long custom data wrangling scripts can be prohibitively complex and time consuming. Ex-post harmonisation scripts are especially difficult because careful recoding, aggregation or redistribution of values is required to merge related datasets such as labour statistics collected under evolving occupation nomenclature.

Moreover, coordination and verification issues arise when data are produced and used by different parties such as in interdisciplinary teams. The design of a harmonised dataset might require domain or statistical expertise from team members without sufficient time or programming skills to implement the data transformations. Data wrangling may be delegated to other team members or research assistants, and may even require different software to the data analysis. Downstream data users often rely on ad hoc validation to check a merged dataset matches the intended design. Without adequate checks, errors can go undetected unless they produce obviously suspect results downstream.

In this talk, I present a new approach to describing, implementing and validating ex-post harmonisation and data transformations. I will also demonstrate how to implement this approach, and verify existing data integration workflows, using the xmap package in the R language. The proposed approach separates categorical recoding and nomenclature transformation operations from procedural code by encoding the transformations as machine-readable ‘crossmap’ objects. The crossmap structure extends standard crosswalk or lookup table approaches using graph and matrix representations of data transformations. It encodes relations between nomenclatures as directed, bipartite weighted graphs. Graph properties of a crossmap can be checked to ensure consistency with the harmonisation design, circumventing the need for ad hoc validation of the transformed data.