R Notebook

Practical Data Merging Exercise in R

Objective

To ensure and teach that participant pause and inspect their data before hitting “run”.

Practical Example

_____________________________________________________________________________________________________________

Libraries

###############################################################################
# Load Libraries
###############################################################################

library(tidyverse)
library(readxl)
library(haven)
library(janitor)
library(skimr)
library(naniar)
library(lubridate)
library(stringr)
library(forcats)
library(here)
library(writexl)
library(psych)
library(glue)

1. Importing data

# Dataset1

df1 <- read_xlsx(here("Data", "Data1_2026.xlsx"))
cat("df1: ", dim(df1), "\n")

df1:  729 4 

# Dataset2
df2 <- read_xlsx(here("Data", "Data2_2026.xlsx"))
cat("df2: ", dim(df2))

df2:  749 4

Dataset1 (df1) has 729 rows and 34 columns

Dataset2 (df2) has 749 rows and 34 columns

2. Inspecting df1 and df2

Before you think about merging;

You need to inspect their structures. Because both datasets have exactly 4 columns, you are likely going to do one of two things:

• A Vertical Join (Stacking rows): Combining them because they share the same columns but contain different rows of data (e.g., combining 2024 data with 2025 data).

• A Horizontal Join (Merging by ID): Combining them because they have matching rows but different information across variables (columns), utilizing a unique identifier key.

3. Cleaning Variable names

We check the variable names to ensure that they are all well written

# Checking variable names
print(names(df1))

[1] "household_ID"   "region"         "water source"   "household size"

print(names(df2))

[1] "HH_ID"          "malaria_test"   "bednet_used"    "avg_hemoglobin"

Renaming the “hh_id” column name in df2 to match “household_id

df2 <- df2 %>%
  rename(household_id = HH_ID)

Changing column names from “water source” to “water_source”

# df1
df1 <- df1 %>%
  clean_names()

print(names(df1))

[1] "household_id"   "region"         "water_source"   "household_size"

# df2
df2 <- df2 %>%
  clean_names()

print(names(df2))

[1] "household_id"   "malaria_test"   "bednet_used"    "avg_hemoglobin"

4. Checking if the column names are a match

# 1. Checking if column names are identical in names and order
all_columns_match <- identical(names(df1), names(df2))

print(paste("Are all columns matching;", all_columns_match))

[1] "Are all columns matching; FALSE"

# Finding mismatching columns
if(!all_columns_match){
  print("Columns in df1 are not in df2:")
  print(setdiff(names(df1), names(df2)))
  
  
  print("Columns in df2 are not in df1:")
  print(setdiff(names(df2), names(df1)))
}

[1] "Columns in df1 are not in df2:"
[1] "region"         "water_source"   "household_size"
[1] "Columns in df2 are not in df1:"
[1] "malaria_test"   "bednet_used"    "avg_hemoglobin"

5. Checking for data type mismatch

Even if columns share the same name, a vertical merge will fail if one dataset treats a column as a character and the other treats it as a numeric.

mismatched_types <- compare_df_cols(df1, df2) %>%
  filter(df1 != df2)

print(mismatched_types)

[1] column_name df1         df2        
<0 rows> (or 0-length row.names)

There are no data type mismatches

6. Identifying Conflicting columns

Before running the merge, you need to check if there are other matching columns besides Household_ID. If two columns share the exact same name but contain different information, R will force-rename them to “column.x” and “column.y”.

# Find all intersecting columns in both datasets
overlapping_cols <- intersect(names(df1), names(df2))
print(overlapping_cols)

[1] "household_id"

7. Keeping only household_id’s that are present in both datasets

Since our datasets have a shared identifier (household_ID) but contain different unique columns, we shall perform a Horizontal Join.

You want to align your observations by “household” and expand your columns so that the final dataset contains all variables from both df1 and df2.

Because df1 has 729 rows and df2 has 749 rows, you must first choose a merging strategy based on how you want to handle households that might only exist in one of the datasets.

Strategy	Function	What it does
Keep Everything	full_join()	Keeps all households from both datasets. Places NA where data is missing.
Keep Matches Only	inner_join()	Keeps only households present in both datasets. Drops the rest.
Prioritize df1	left_join()	Keeps all households in df1. Drops unmatched rows from df2.
Prioritize df2	right_join()	Keeps all households in df2. Drops unmatched rows from df1.

8. Merging:

household_id’s that dont match perfectly across both datasets (df1 and df2) will be dropped.

# Merging the df1 and df2
df_merged <- inner_join(df1, df2, by="household_id")

9. Final dataset

# Final clean dataset
df_final <- df_merged

# Checking final dimension
dim(df_final)

[1] 700   7

The final dataset (df_final) has 700 rows and 67 columns

10. Converting to csv

write_csv(df_final, here("Output", "Data_final.csv"))