THREE DATABASES, ONE STUDY
How I turned three databases from a clinical study, each with a different questionnaire, into a single dataset, and set up a standard so future data collection would arrive organized.
- Type
- Product case study
- Company
- Cignamed, internship
- Period
- May – Oct 2026
- Tools
- Python (pandas), Excel, REDCap
Project carried out during my internship at Cignamed, in a clinical research study. No participant data appears here: the case covers only the problem and the process.
OUTCOME
- 2 MONTHS TO 2 MIN
Merging the databases, which took about two months of work, now takes around two minutes.
- 950K ANSWERS
About 1,900 respondents and 500 questions brought together in a single dataset, without losing any question that was collected.
- 200+ DUPLICATES
Duplicates and hundreds of inconsistencies (out-of-range dates and ages, badly formatted values) found and fixed, with every correction recorded in a log.
- NEW STANDARD
Later interviews started being collected in REDCap, already in the format of the merged dataset, so the problem would not come back.
THE PROBLEM
- CONTEXT
I was brought into the internship precisely to solve this problem. The lead researcher had three databases from the same study, each with a different questionnaire, and needed everything combined: the questions and the respondents.
- WHY CODE ALONE WASN’T ENOUGH
At first it looked like a job for a script, but the questions were written differently in each database, with typos and variations. Code had no way of knowing that two columns were the same question.
- WHO FELT IT
The research team, who needed the complete dataset to analyze the study and depended on slow manual work to get there.
DISCOVERY AND DECISIONS
- STUDY BEFORE CODING
Before writing any code, I studied the research material to understand each question and find out which ones matched across the databases, even when they didn’t follow the pattern.
- FIX IT AT THE SOURCE
I standardized the questions in all three databases. Only once they were written the same way did the automatic merge start working.
- NOTHING GETS LOST
I merged the databases with pandas. Questions that existed in only one database were not dropped: they went to the end of the spreadsheet, so the final dataset keeps everything that was collected.
- KEEP IT FROM COMING BACK
So that future interviews would follow the same standard, I built the forms in REDCap, a tool widely used by researchers, with field types and validation rules that block errors at collection time.
LESSONS LEARNED
- CONTENT COMES FIRST
The hardest part wasn’t technical: question quality varied a lot from one database to another. Understanding the content is what unlocked the solution.
- WHAT I’D DO DIFFERENTLY
I would test other data collection tools before adopting REDCap, comparing each one against what the team needed day to day.