Merging Data Frames

October 2, 2024

What is a Join

Horizontal Join (Merge)

• Often we have data from two different sources
• Results in two data frames
• How to make them one so we can analyze?

Illustration

Source: R for HR

Types of Joins in dplyr

• Mutating versus filtering joins
• Four types of mutating joins
  • inner_join()
  • full_join()
  • left_join()
  • right_join()
• For the most part we will use left_join()

inner_join()

Source: R for HR

full_join()

Source: R for HR

left_join()

Source: R for HR

right_join()

Source: R for HR

Worked Example

Scenario

• We want to merge two data frames
• One is from the World Bank
• The other is from V-Dem
• How do we do it?

Grab Some WB Data

# Load packages
library(wbstats) 
library(dplyr) 
library(janitor) 

# Store the list of indicators in an object
indicators <- c("flfp" = "SL.TLF.CACT.FE.ZS", "women_rep" = "SG.GEN.PARL.ZS") 

# Download the data  
wb_dta <- wb_data(indicators, mrv = 25) |> # most recent 25 years
  select(!iso2c) |> 
  rename(year = date) |> 
  mutate(
    flfp = round_to_fraction(flfp, denominator = 100), # round to nearest 100th
    women_rep = round_to_fraction(women_rep, denominator = 100) 
  )

# View the data
glimpse(women_emp) 

Grab Some V-Dem Data

library(vdemlite)

vdem_dta <- fetchdem(indicators = c("v2x_gender", "v2x_gencl", "e_regionpol_6C") |>
                         start_year = 2000, end_year = 2020) |> # 20 year span
                 rename(
                   women_polemp = v2x_gender,
                   women_civlib = v2x_gencl,
                   region = e_regionpol_6C
                 ) |>
            mutate(
              region = case_match(region, 
                                  1 ~ "Eastern Europe", 
                                  2 ~ "Latin America",  
                                  3 ~ "Middle East",   
                                  4 ~ "Africa", 
                                  5 ~ "The West", 
                                  6 ~ "Asia")
              )

glimpse(vdem_dta)

Key Questions

• What is the unit of analysis?
• What is/are the corresponding identifier variables?
• Are the identifier variables in common?
• Or do they have to be added/transformed to match?

Merging WB and V-Dem Data

• These are both time-series, country-level data
• Need to merge by country-year
• Year is easy
• But there are many different country codes
• Can use countrycode package to assign country codes

Use countrycode

# Load countrycode
library(countrycode)

# Create new iso3c variable
vdem_data <- vdem_data |>    
  mutate(iso3c = countrycode(sourcevar = country_id, # what we are converting
        origin = "vdem",         # we are converting from vdem
        destination = "wb"))  |> # and converting to the WB iso3c code 
  relocate(iso3c, .after = country_id) # move iso3c 

# View the data
glimpse(dem_data)

Try it Yourself

• Using your democracy data frame from the last lesson
• Use mutate() and countrycode() to add iso3c country codes
• Use relocate to move your iso3c code to the “front” of your data frame (optional)

10:00

Use left_join() to Merge

# Perform left join using common iso3c variable and year
dem_wb <- left_join(vdem_dta, wb_dta, by = c("iso3c", "year")) |> #join
  select(-c(country_text_id, country_id, country)) # drop extra country variables
  
# View the data
glimpse(dem_wb)  

Try it Yourself

• Take your V-Dem data frame and your World Bank data frame
• Using left_join() to merge on country code and year
• Along the way, use rename() and select() to insure you have just one country name
• Try inner_join(), full_join(), and right_join() as time allows

10:00

Summarize the Data

• Do a group, summarize, arrange sequence on your merged data frame
• Group and summarize by country (mean or median)
• Try using across() to summarize multiple columns at once

10:00

Create a Scatter Plot

• Now you have one data point per country
• Use ggplot2 to create a scatter plot

10:00