How to merge two dataframes in R based on a matching part of a string?

user_15453560 0
Viewed 52

I have two data frames, one with economic information for various countries and the other with the proper names of the countries. The two data frames look like this:

country <- c("Afghanistan", "Afghanistan", "United States", "United States", "Congo, Dem. Rep.", "Congo, Dem. Rep.", "Middle East and North Africa", "Middle East and North Africa")
years <- c(2011, 2012, 2011, 2012, 2011, 2012, 2011, 2012)
gdp <- c(123, 442, 9451, 9999, 351, 664, 7531, 6634)
economic_data <- cbind.data.frame(country, years, gdp)

country_proper <- c("Afghanistan", "United States of America", "Congo DR")

I want to change the names of the countries in economic_data to their proper names in the country_proper data, and then drop the countries in economic_data which do not appear in country_proper (like "Middle East and North Africa").

dplyr gsub tidyverse r
2 Answers

You need to use fuzzy matching. Try this -

country <- c("Afghanistan", "Afghanistan", "United States", "United States", "Congo, Dem. Rep.", "Congo, Dem. Rep.", "Middle East and North Africa", "Middle East and North Africa")
years <- c(2011, 2012, 2011, 2012, 2011, 2012, 2011, 2012)
gdp <- c(123, 442, 9451, 9999, 351, 664, 7531, 6634)
economic_data <- data.frame(country, years, gdp, stringsAsFactors = F)

country_proper <- c("Afghanistan", "United States of America", "Congo DR")
country_proper <- data.frame(country = country_proper, stringsAsFactors = F)

library(fuzzyjoin)
stringdist_join(economic_data, 
                country_proper,
                method = c("soundex"),
                mode = "inner",
                by = "country")
user_49303570

Here is an alternative way: We could use str_replace_all from stringr package:

library(dplyr)
library(stringr)
economic_data %>% 
    mutate(country = str_replace_all(country, c(
        "^United States$" = "United States of America",
        "^Congo, Dem. Rep.$" = "Congo DR")))

data:

                       country years  gdp
1                  Afghanistan  2011  123
2                  Afghanistan  2012  442
3     United States of America  2011 9451
4     United States of America  2012 9999
5                     Congo DR  2011  351
6                     Congo DR  2012  664
7 Middle East and North Africa  2011 7531
8 Middle East and North Africa  2012 6634
user_133216470
Related