How to merge two survey datasets
Both ways of merging happen in your browser: the files never leave your computer.
Choose between stacking and joining
Stacking works when both datasets have the same questions and different cases, like two waves of a survey: the rows of one go below the rows of the other. Joining by identifier works when they are the same cases with different data: each person gets the columns of the other dataset.
Check that both datasets measure the same thing
No program can verify this for you: that both count the same unit (people, not records), that they cover the same period, that each variable means the same thing and that the codes are written the same way. When joining, also that the identifier belongs to the same case in both files.
Stacking: pair the columns and don’t count anyone twice
Pair each column with its counterpart in the other dataset even if it has a different name, and leave out the ones without a pair. If someone answered in both waves, count them only once, and add a column that says which file each row came from.
Joining: choose the identifier and decide what to do with loose cases
Choose a column that identifies each case in both datasets: national ID number, student or employee number, tax ID or any other value unique to each case. If you’re going to share the joined dataset afterwards, anonymize it first with HILO’s tool.
Review the result and document it
Check that the number of rows is what you expect and compare the dates with the original files: when two files are combined, dates can change format or end up with day and month swapped. Save the merged dataset under a new name and write down what you did.
Other ways to do it
You can do it by hand in a spreadsheet (copy and paste to stack, VLOOKUP to join), with Excel’s Power Query or with a script in R or Python.
HILO is handy for merging two datasets quickly and seeing what happens to each case: it works in the browser, there’s nothing to install, it’s free, it doesn’t ask you to sign up and the files never leave your computer. Another tool is better if you need to merge many files, join on several columns at once or keep the merge in a script you can run again.
Merge your own datasets
Load the two files in HILO and choose how to merge them.
Updated
More guides
How to measure intercoder reliability
Cohen’s kappa in 5 steps: code separately, build the table, count the agreement, remove chance and report.
How to share a dataset without exposing personal data
Column inventory, what to do with each one, free text, re-identification risk and the code table, in 5 steps.
How to clean a survey dataset before analyzing it
Read the file correctly, find repeats, check columns, spot non-response codes and fix at the source, in 5 steps.
How to code the open-ended responses of a survey
Read a sample, build the categories, assign each response, review and bring them into your dataset, in 5 steps.
How to operationalize a project’s objectives with your variables
Write the objectives, take stock of the variables, match them, define indicators and recognize what’s missing, in 5 steps.