CODEWITHSUNDEEP
rmerge
user6727485
user6727485

Reputation:

Merging datasets based on more than 1 column in both datasets

I'm trying to merge two datasets, by year and country. The first data set (df = GNIPC) represent Gross national income per capite for every country from 1980-2008.

         Country  Year GNIpc
       (chr) (dbl) (dbl)
1    Afghanistan  1990    NA
2    Afghanistan  1991    NA
3    Afghanistan  1992  2010
4    Afghanistan  1993    NA
5    Afghanistan  1994 12550
6    Afghanistan  1995    NA

The second dataset (df = sanctions) represents the imposition of economic sanctions from 1946 to present day.

      country imposition sanctiontype sanctions_period
      (chr)      (dbl)        (chr)            (chr)
1 Afghanistan       1     1 6 8           1997-2001
2 Afghanistan       1     7               1979-1979
3 Afghanistan       1     4 7             1995-2002
4 Albania           1     2 8             2005-2005
5 Albania           1     7               2005-2006
6 Albania           1     8               2004-2005

I would like to merge the two datasets so that for every GNI year i either have sanctions present in the country or not. For the GNI years that are not in the sanctions_period the value would be 0 and for those that are it would be 1. This is what i want it to look like:

         Country  Year GNIpc Imposition sanctiontype
           (chr) (dbl) (dbl)   (dbl)        (chr)
1    Afghanistan  1990    NA    0            NA
2    Afghanistan  1991    NA    0            NA
3    Afghanistan  1992  2010    0            NA
4    Afghanistan  1993    NA    0            NA
5    Afghanistan  1994 12550    0            NA
6    Afghanistan  1995    NA    1           4 7

Upvotes: 0

Views: 99

Answers (2)

jdobres
jdobres

Reputation: 11957

Some example data:

df1 <- data.frame(country = c('Afghanistan', 'Turkey'), 
                  imposition = c(1, 0), 
                  sanctiontype = c('1 6 8', '4'), 
                  sanctions_period = c('1997-2001', '2003-ongoing')
)

      country imposition sanctiontype sanctions_period
1 Afghanistan          1        1 6 8        1997-2001
2      Turkey          0            4     2012-ongoing

The "sanctions_period" column can be transformed with dplyr and tidyr:

library(tidyr)
library(dplyr)

df.new <- separate(df1, sanctions_period, c('start', 'end'), remove = F) %>% 
  mutate(end = ifelse(end == 'ongoing', '2016', end)) %>% 
  mutate(start = as.numeric(start), end = as.numeric(end)) %>% 
  group_by(country, sanctions_period) %>% 
  do(data.frame(country = .$country, imposition = .$imposition, sanctiontype = .$sanctiontype, year = .$start:.$end))

   sanctions_period     country imposition sanctiontype  year
             <fctr>      <fctr>      <dbl>       <fctr> <int>
1         1997-2001 Afghanistan          1        1 6 8  1997
2         1997-2001 Afghanistan          1        1 6 8  1998
3         1997-2001 Afghanistan          1        1 6 8  1999
4         1997-2001 Afghanistan          1        1 6 8  2000
5         1997-2001 Afghanistan          1        1 6 8  2001
6      2012-ongoing      Turkey          0            4  2012
7      2012-ongoing      Turkey          0            4  2013
8      2012-ongoing      Turkey          0            4  2014
9      2012-ongoing      Turkey          0            4  2015
10     2012-ongoing      Turkey          0            4  2016

From there, it should easy to merge with your first data frame. Note that your first data frame capitalizes Country and Year, while the second doesn't.

df.merged <- merge(df.first, df.new, by.x = c('Country', 'Year'), by.y = c('country', 'year'))

Upvotes: 1

Felipe Gerard
Felipe Gerard

Reputation: 1622

Using dplyr:

left_join(GNIPC, sanctions, by=c("Country"="country", "Year"="Year")) %>%
  select(Country,Year, GNIpc, Imposition, sanctiontype)

Upvotes: 0

Related Questions