Remapping Datasets
Background
I've been involved in COVID-19 related data gathering. Data was capured in CASTOR, but needed to be remapped to WHO standard. This meant mapping variables, remapping values (e.g. NO: 2 in the one NO: 0 in the other), adding missing units, substituting keys based on a key table, and some other stuff. While the script created for CASTOR -> REDCAP is quite complicated, I've made a simpler version for Remapping Datasets in Python that is available on Github.
The challenge was to involve people who could do and check the mapping, but have not so much of a background in scripting (and no desire to learn it). Hence a solution based on Python and Excel. Python to do the stuff, Excel for mapping and configuration in a way that a normal user would find this not too difficult.
Requirements
- no coding required
- all mapping and configurations via the included Excel
- the only requirement is to run a python script or a Jupyter Notebook (not too difficult for it basically knowing which button to push)
- so either python or Jupyter must be installed with pandas and openpyxl
- download and unzip: https://github.com/flow4u/Remapping-Datasets/archive/refs/heads/main.zip
- or visit https://github.com/flow4u/Remapping-Datasets
- easy to set up in a myDRE Workspace
- Python, Miniconda, Jupyterlab & Python Packages (mydre.org)
- Or create a VM with the template Windows-OSDS/1.0.0 Open Source Data Science (mydre.org)
- download, upload, and unzip https://github.com/flow4u/Remapping-Datasets/archive/refs/heads/main.zip
How to use
For how to use it, see the instructions in the table Explanation in conversion.xlsx (part of the zip / available on GitHub)
- Jupyter Notebook: it will ask which Excel to use for mapping & configuration
- From the command line: python conversion.py conversion.xlsx (other Excel can be used)
What the script can do
You can have multiple conversion.xlsx; each with its unique name, own mapping & configuration
- Substitute IDs based on the key list
- Remap variables with or without value conversion
- Swap two variables; A->B & B->A
- Convert a date format (e.g. %m/%d/%Y) into an ISO8601 date (%Y-%m-%d)
- Add variable with duration in days between two valid ISO8601 dates
- Remap Option group Variables to Check Box group Variables (granted only one option can be remapped per entry)
- Remap Check Box group Variables to Option group Variables (when multi check box, then the 'option' group gets multi options comma separated)
- Add unit variables (e.g. add variable Temperature_Unit = 1 (could be Celcius) when source variable has a value, empty when empty
- Removes all 'redundant variables (non mapped variables in the source)
- Orders the variables based on the Excel order list of target variables
The output
Remapping creates a time-stamped:
- CSV, and
- Excel
Topic Participants
Stefan van Aalst