Combining Data

Author

Amanda Ng, Abigail Frix

Combining Multiple Datasets

Sometimes in working with data, one dataset does not have all of the variables or information you need to investigate the topics you are trying to explore. This is particularly pertinent with epidemiological research, as a lot of times epidemiologists have to investigate relationships between multiple variables that different organizations are responsible for measuring. For example, if you were looking at how air quality and pollution levels is related to acute respiratory infection incidence in a city, you’d likely find air quality measurements from an environmental research organization while finding respiratory infection incidence data from a public health organization.

PFAS exposure is an environmental health risk, so for this next part we will be incorporating another dataset to look at an outcome of interest (reproductive outcomes). The dataset was taken from the CDC archives, looking fertility, birth rates, and birth weights across the US for 2023.

To combine datasets correctly, it is important that the observations are matched correctly between datasets. Thus, there needs to be some shared characteristic or variable between datasets that can be used to join them together.

QUESTION

Which of the following variables should be used to join the pfas and birth datasets?

So now we know which two columns to use. However, notice between the pfas and births datasets, even though the rows have the same shared values, the name of the columns are different. We must fix this first, and can do this using the rename() function.

Importantly, in performing a join correctly, it is imperative that the information is correctly representing both datasets.

For example, birth_data is grouped by state, with 51 rows, while the original pfas_data dataset has 35,323 rows based on multiple observations per state. It is not correct to apply the average birth rate of a state to every unique location within a state. This would be a classic example of the ecological fallacy, or making inferences about smaller units or individuals based on aggregate data.

When working with any geographical or location-based data, we have to use the same geographical unit of analysis between datasets (ie. State/Province, Municipality, Neighborhood, Postal Code, etc.) The modifiable areal unit problem (MAUP) is a statistical bias that can occur when there are discrepancies in how spatial boundaries are defined between datasets. The scale effect is one component of the MAUP, and occurs when altering the size of the scale of measurement yields different results (for example, looking at average income at the block level versus the census tract level will yield different results). Relating this back to the pfas and births datasets, it is likely that there is significant variation in the measurements at the regional/sample site and state levels respectively.

Conclusively, the ecological fallacy and MAUP are important considerations when wrangling your data for analysis and drawing inferences from your results.

So now that we have renamed the State column, we can use the join() function to join the datasets. In particular, there are three ways to join datasets (say A and B):

  • A left join B: Retrieves all observations from A and matches observations from B based on the by argument, filling in NA for any columns where there’s no match in B.
  • A right join B: Retrieves all observations from B and matches observations from A based on the by argument, filling in NA for any columns where there’s no match in A.
  • A full join B: Returns all observations from both A and B, including both matching and non-matching observations.

Let’s use the total pfas mean values by state:

Notice the importance of the order of how we left join datasets

QUESTION

Why do we observe NA in the row about AS State?

Confounding Variables

In the last subsection, we mentioned how epidemiologists are often looking at multivariate relationships and some of the challenges that can come with that from a data wrangling perspective. A statistical challenge of analyzing multivariate relationships is the existence of confounding variables, which are factors that can influence the exposure and outcome variables simultaneously. If we think about how PFAS exposure’s impact on health outcomes, socioeconomic status of a region could influence the environmental exposure and the health outcomes of a region. In other words, worse health outcomes in regions with high PFAS exposure could be due to the other challenges stemming from income disparity, such as less access to healthcare. In this case, observed associations between PFAS exposure and poor health outcomes could be partially explained by socioeconomic disparities rather than PFAS exposure alone. This example highlights the importance of considering confounders when deriving causality between variables.

Conclusions

By this point in the module, you have learned a little bit about R and gained some experience running and modifying code to wrangle data.

This is not intended to be a comprehensive introduction to R and the dplyr package (a grammar of data manipulation package which is part of the tidyverse package), but rather to highlight the code and functions that will be useful in HMB342, and provide you with an opportunity to experience coding with them. If you would like to learn more on data wrangling in R, please see Wickham, H, Cetinkaya-Rundel, M, & Grolemund, G (2023). R for Data Science (2e). Also, the Data Wrangling Cheatsheet is a nice summary of the data wrangling functions that are available in the tidyverse package.

Return to Learning Hub Homepage

Learning Hub Homepage