Merging EAS Files in R
This page is under construction as the Tech and Data Core Works to harmonize the data release and documentation. If you encounter issues with a merge, reach out to Dr. Nelson Roque and Sarah Logan.
This page is a tutorial that demonstrates how to merge data files from the EAS. It will provide examples of the general merge case, various special cases, and how to pare down to a smaller, working dataset post merge. We strongly encourage you to use the Merge Case Table to identify what kind of merge code applies to your data, and to consult the Date Range Table to make sure the files you want to merge overlap collection periods. You can also learn about Key Variables that are important to EAS merges and get prewritten File Intake Code for R to speed up your process.
Jump in to merging code by case here:
Note
This guide assumes a basic familiarity with joins. For further support, Hadley Wickam's R for Data Science has an informative chapter on joins in R.
Key Variables
Common merge variables are deliniated in the Airtable and descriptions can be found here: Airtable Merge Variables. Further information can be accessed in the Codebook.
There are 2 key merge variables that come in every data file that almost all merges will include.
participant_id- EAS ID for all linkages; Subject's ID Numberwave- Major longitudinal assessment period in which the observation or data collection occurred.
If your file missing a merge variable that you need for analysis or to combine files, the tracking_form file contains all necessary variables to get any other variable and should be the first stop for any merge you do if something is missing. (If nothing is missing, continue on to the general case or special cases)
Date Range
Over time, the EAS has collected different types of data for various durations. Due to this fact, it is possible to attempt a merge where there is no overlapping data collection, resulting in vast quantities of NA's. The reference table below contains the date of the earliest and latest observations found in the file to provide clarity on what files overlap (and are, therefore, useful to merge).
View Date Range Table
| File Name | Earliest Observation | Latest Observation |
|---|---|---|
| biomarkers_inflammation_20260417_091459.csv | 2017-05-01 | 2023-09-01 |
| biomarkers_oxylipins_20260417_091459.csv | 2017-05-01 | 2020-01-01 |
| clinical_core_summary_diagnoses_20260417_091459.csv | 2017-05-01 | 2025-12-01 |
| demographics_20260417_091459.csv | 2017-05-01 | 2026-02-01 |
| dep_cesd_20260701_140414.csv | 2023-02-01 | 2026-01-01 |
| dep_gds_20260701_140414.csv | 2017-05-01 | 2026-02-01 |
| ema_cog_2023_20260417_091459.csv | 2023-02-01 | 2025-11-01 |
| ema_survey_2023_all_sessions_20260507_115833.csv | 2023-02-01 | 2025-11-01 |
| ema_survey_2023_complete_sessions_20260507_115833.csv | 2023-02-01 | 2025-11-01 |
| physiology_20260701_140414.csv | 2017-05-01 | 2026-02-01 |
| sensor_atmotube_agg_participant_20260417_091459.csv | 2023-02-01 | 2026-01-01 |
| sensor_atmotube_agg_participant_date_20260417_091459.csv | 2023-02-01 | 2026-01-01 |
| sensor_atmotube_agg_participant_date_hour_20260417_091459.csv | 2023-02-01 | 2026-01-01 |
| sensor_cgm_agg_participant_20260417_091459.csv | 2023-12-01 | 2025-01-01 |
| sensor_cgm_agg_participant_date_20260417_091459.csv | 2023-12-01 | 2025-01-01 |
| sensor_cgm_agg_participant_date_hour_20260417_091459.csv | 2023-12-01 | 2025-01-01 |
| sensor_nonin_20260417_091459.csv | 2017-05-01 | 2025-12-01 |
| smoking_history_20260507_115833.csv | 2017-05-01 | 2026-02-01 |
| take_home_packets_20260417_091459.csv | 2017-05-01 | 2026-01-01 |
| tracking_form_20260417_091459.csv | 2017-05-01 | 2026-02-01 |
Merge Cases
Due to the nature of how data is structured and collected, there are a few different cases of merges. The tab below notes which file pertains to which case and can be used as a reference for navigating the remainder of the tutorial.
View Merge Case Table
| File Name | Merge Case |
|---|---|
| biomarkers_genetics_20260417_091459.csv | Case 1 |
| biomarkers_inflammation_20260417_091459.csv | Case 1 |
| biomarkers_oxylipins_20260417_091459.csv | Case 1 |
| clinical_core_summary_diagnoses_20260417_091459.csv | Case 1 |
| clinical_strain_20260417_091459.csv | Case 1 |
| demographics_20260417_091459.csv | Case 1 |
| dep_cesd_20260701_140414.csv | Case 1 |
| dep_gds_20260701_140414.csv | Case 1 |
| ema_cog_2017_20260417_091459.csv | Case 3 |
| ema_survey_2017_all_sessions_20260507_115833.csv | Case 3 |
| ema_survey_2017_complete_sessions_20260507_115833.csv | Case 3 |
| ema_cog_2023_20260417_091459.csv | Case 3 |
| ema_survey_2023_all_sessions_20260507_115833.csv | Case 3 |
| ema_survey_2023_complete_sessions_20260507_115833.csv | Case 3 |
| physiology_20260701_140414.csv | Case 1 |
| sensor_actigraph_2017_20260417_091459.csv | Case 2 |
| sensor_actigraph_light_cal_2017_20260417_091459.csv | Case 2 |
| sensor_actigraph_light_sleepday_2017_20260417_091459.csv | Case 2 |
| sensor_actigraph_2023_20260417_091459.csv | Case 2 |
| sensor_actigraph_light_cal_2023_20260417_091459.csv | Case 2 |
| sensor_actigraph_light_sleepday_2023_20260417_091459.csv | Case 2 |
| sensor_activpal_20260417_091459.csv | Case 1 |
| sensor_atmotube_agg_participant_20260417_091459.csv | Case 4 |
| sensor_atmotube_agg_participant_date_20260417_091459.csv | Case 4 |
| sensor_atmotube_agg_participant_date_hour_20260417_091459.csv | Case 4 |
| sensor_cgm_agg_participant_20260417_091459.csv | Case 4 |
| sensor_cgm_agg_participant_date_20260417_091459.csv | Case 4 |
| sensor_cgm_agg_participant_date_hour_20260417_091459.csv | Case 4 |
| sensor_nonin_20260417_091459.csv | Case 1 |
| smoking_history_20260507_115833.csv | Case 1 |
| take_home_packets_20260417_091459.csv | Case 1 |
| tracking_form_20260417_091459.csv | Case 1 |
File Intake by Case
Code to import data from all EAS files is provided below, organized by merge case.
Case 1 - General
biomarkers_genetics <- read_csv("data/biomarkers_genetics_20260417_091459.csv")
biomarkers_inflammation <- read_csv("data/biomarkers_inflammation_20260417_091459.csv")
biomarkers_oxylipins <- read_csv("data/biomarkers_oxylipins_20260417_091459.csv")
clinical_core <- read_csv("data/clinical_core_summary_diagnoses_20260417_091459.csv")
clinical_strain <- read_csv("data/clinical_strain_20260417_091459.csv")
demographics <- read_csv("data/demographics_20260417_091459.csv")
dep_cesd <- read_csv("data/dep_cesd_20260701_140414.csv")
dep_gds <- read_csv("data/dep_gds_20260701_140414.csv")
physiology <- read_csv("data/physiology_20260701_140414.csv")
sensor_activpal <- read_csv("data/sensor_activpal_20260417_091459.csv")
sensor_nonin <- read_csv("data/sensor_nonin_20260417_091459.csv")
smoking_history <- read_csv("data/smoking_history_20260507_115833.csv")
take_home_packets <- read_csv("data/take_home_packets_20260417_091459.csv")
tracking_form <- read_csv("data/tracking_form_20260417_091459.csv")
Case 2 - Vintage
sensor_actigraph_2017 <- read_csv("data/sensor_actigraph_2017_20260417_091459.csv")
sensor_actigraph_light_cal_2017 <- read_csv("data/sensor_actigraph_light_cal_2017_20260417_091459.csv")
sensor_actigraph_light_sleepday_2017 <- read_csv("data/sensor_actigraph_light_sleepday_2017_20260417_091459.csv")
sensor_actigraph_2023 <- read_csv("data/sensor_actigraph_2023_20260417_091459.csv")
sensor_actigraph_light_cal_2023 <- read_csv("data/sensor_actigraph_light_cal_2023_20260417_091459.csv")
sensor_actigraph_light_sleepday_2023 <- read_csv("data/sensor_actigraph_light_sleepday_2023_20260417_091459.csv")
Case 3 - EMA
ema_cog_2017 <- read_csv("data/ema_cog_2017_20260417_091459.csv")
ema_cog_2017_all_sessions <- read_csv("data/ema_survey_2017_all_sessions_20260507_115833.csv")
ema_cog_2017_complete_sessions <- read_csv("data/ema_survey_2017_complete_sessions_20260507_115833.csv")
ema_cog_2023 <- read_csv("data/ema_cog_2023_20260417_091459.csv")
ema_cog_2023_all_sessions <- read_csv("data/ema_survey_2023_all_sessions_20260507_115833.csv")
ema_cog_2023_complete_sessions <- read_csv("data/ema_survey_2023_complete_sessions_20260507_115833.csv")
Case 4 - Time Aggregation
sensor_atmotube_agg_participant <- read_csv("data/sensor_atmotube_agg_participant_20260417_091459.csv")
sensor_atmotube_agg_participant_date <- read_csv("data/sensor_atmotube_agg_participant_date_20260417_091459.csv")
sensor_atmotube_agg_participant_date_hour <- read_csv("data/sensor_atmotube_agg_participant_date_hour_20260417_091459.csv")
sensor_cgm_agg_participant <- read_csv("data/sensor_cgm_agg_participant_20260417_091459.csv")
sensor_cgm_agg_participant_date <- read_csv("data/sensor_cgm_agg_participant_date_20260417_091459.csv")
sensor_cgm_agg_participant_date_hour <- read_csv("data/sensor_cgm_agg_participant_date_hour_20260417_091459.csv")
Code Examples
Case 1 - General
The general case of merging EAS data uses participant_id and wave to merge any two of the data files that are marked as General Case in the Merge Case Table.
Tracking Form Merge
We start with the tracking form merge. If both your files have all the needed merge variables, move on to other cases. If not, this is the first step.
oxylipin_tracking_form_merge <- left_join(
biomarkers_oxylipins,
tracking_form,
by = c("participant_id", "wave")
)
Note
If the same column exists in the files that you're trying to merge,
.xand.ywill be appended to the column name to mark which came from the diagnosis file and which the tracking form. This can be avoided by trimming down the file to add.
Merging 2 Files
general_case_merge_2_files <- left_join(
biomarkers_oxylipins,
clinical_core_summary_diagnoses,
by = c("participant_id", "wave")
)
Merging 3 or More Files
general_case_merge_3_or_more_files <- left_join(
biomarkers_oxylipins,
clinical_core_summary_diagnoses,
demographics,
by = c("participant_id", "wave")
)
Filtering for Minimum Wave
EAS wave periods climb chronolocially with each passing period of the study - therefore, most participants minimum wave will not be wave 1. To create a dataset of the first time data was collected on each participant, use the following code:
minimum_wave <- biomarkers_oxylipins %>%
group_by(participant_id) %>%
filter(wave == min(wave)) %>%
ungroup()
Selecting a Narrow Data Set
After merging, it is frequently helpful to pare down a dataset into the most important columns needed via the select command.
narrow_dataset <- left_join(
biomarkers_oxylipins,
tracking_form,
by = c("participant_id", "wave")) %>%
select( participant_id, wave, j_mci, p_mci, '22_HDoHE', '17_HDoHE', day_1_completed_date)
Case 2 - Vintage
Some of the data is provided in vintages due to noticeable differences in collection mechanism between 2017 and 2023. While provided in distinct files, researchers may need to stack datasets together to create a combined dataset.
Bind Files
actigraph_2017_2023 <- bind_rows(
sensor_actigraph_2017,
sensor_actigraph_2023,
)
If the actigraph files do not already contain vintage as a column, after binding together, we'll merge with the tracking form to be able to differentiate on a column level.
Tracking Form Merge for Vintage Column
actigraph_2017_2023_vintage <- left_join(
actigraph_2017_2023,
tracking_form %>%
select(participant_id, wave, vintage),
by = c("participant_id", "wave")
)
Case 3 - EMA
EMA (Ecological Momentary Assessment) data is unique insofar as there are various vintages and granularities of data to handle, and some context is required to check for accurate merge due to the nature of how the data is collected. We'll start with a few important details before jumping into merging.
What is EMA Data
In the context of the EAS, EMA is survey and cognitive testing data collected via smartphone input multiple times a day over the course of several days. The surveys are always provided first, followed by cognitive brain games and are administered in the morning, afternoon, and night. Participants may exit from these surveys at any time, or may experience a loss of internet connectivity, which impacts the way data is collected.
EMA File Descriptions
ema_cog_20xx- File contains the results of cognitive testing; survey data is NOT includedema cog_20xx_complete_sessions- File contains only data from completed surveys.ema_cog_20xx_all_sessions- File contains data from every instance a survey was initiated (but not necessarily completed)
Most researchers use the ema cog_20xx_complete_sessions files unless there is a specific need to see all instances a survey was initiated.
Binding EMA Vintages
# Cognitive Test Binding
ema_cog_test_2017_2023 <- bind_rows(
ema_cog_2017,
ema_cog_2023
)
# Complete Session Binding
ema_complete_surveys_2017_2023 <- bind_rows(
ema_cog_2017_complete_sessions,
ema_cog_2023_complete_sessions
)
Merging Completed Surveys with Cognitive Testing Data
ema_survey_cogtest_merged <- left_join(
ema_complete_surveys_2017_2023,
ema_cog_test_2017_2023,
by = c("participant_id", "wave", "session_uuid")
)
Note
When joining survey and cognition data, survey should be the first file in the left join. Cognitive testing always comes after the surveys. This means that not every survey will have associated cognition data as participants may exit before completing the cog tasks.
Merging EMA Files with Other Cases
oxylipin_ema_merge <- left_join(
biomarkers_oxylipins,
ema_survey_cogtest_merged,
by = c("participant_id", "wave")
)
Selecting a Narrow EMA Dataset
narrow_ema_dataset <- left_join(
biomarkers_oxylipins,
ema_complete_surveys_2017_2023,
by = c("participant_id", "wave")) %>%
select(participant_id, wave, `22_HDoHE`, `22_HDoHE`, stress_now, anxious_now)
Case 4 - Time Aggregation
Atmotube and CGM data are aggregated at various granularity levels. I will use Atmotube as the example here, but everything written applies to CGM as well.
Time Aggregation File Descriptions
sensor_atmotube_agg_participant- File contains a single average for each participant at each wavesensor_atmotube_agg_participant_date- File contains daily participant averagessensor_atmotube_agg_participant_date_hour- File contains hourly participant averages
Merging Time Aggregation Files with Other Cases
atmotube_clinical_core_merge <- left_join(
sensor_atmotube_agg_participant_date_hour,
clinical_core_summary_diagnoses,
by = c("participant_id", "wave")
)
Troubleshooting
A few common cases/questions are as follows:
Unexpected Missing Data Following a Merge
Not every participant_id has data across every data file. All participants will have, at minimum: blank blank. Some participants chose not to participate in biomarker or cognitive data collection, so simply because a participant has NAs in a particular area does not mean the merge failed.
EMA Survey Data Present but Cognitive Data Missing
EMA data consists of a survey followed by cognitive tests. Participants may exit at any time, so certain ID sessions may have survey data but no cognitive data. This is not indicative of a failed merge. Researchers who are interested in only complete sessions can use ema_survey_2017_complete_sessions_20260507_115833.csv.
Many-to-Many Join Warnings
In some EMA cases, participants may refresh the screen or suddenly lose internet connectivity, resulting in a duplicate session being established. Edge cases such as these may be flagged during a merge with a warning about many-to-many joins. These cases may be handled as the researcher sees fit.
General Troubleshooting Checklist
If something feels off about a merge (you merged some files and now there are too many participants, too few, duplicated data, lots of NA's, missing columns, etc.,), refer to this checklist for problem solving steps:
- Look at your data. Open all the files involved and look at what you started with and what you ended up with. Take before and after counts of unique participants, sessions, rows, etc.
- Ensure all key columns are present in both files, and were not unintentionally renamed.
- Check that the join type is correct (for example, that
left_join()is indeed the appropriate merge), and that the files are ordered correctly in the join code. - Check that column types match across the relevant files.
- Refer to the Merge Case Table and ensure you're using the appropriate code.
- Refer to the Date Range Table and ensure the files overlap temporally.
After following these steps, you can reach out to the Data Core for assistance by emailing Dr. Nelson Roque and Sarah Logan. When reaching out via email, please provide the names of the files involved, a brief description of the problem, and the troubleshooting steps you've taken so far so that we can most effectively resolve the issue.