add column to DF when two variables are matched with second DF

Question

I am looking to add another column to DF1 below which returns the population of the relevant country for the year of the observation. I.e when country and year match with DF2, population is added to a column in DF2. I have used merge for matching only one variable before, is there a method to complete for two variables?

DF1:

eventid    |iyear | imonth| iday | CountryTxt
1.97000e+1 |1970  |      7|    2 |  Albania
1.97000e+11|  1970|      0|    0 |  United Kingdom
1.97001e+11|  1984|      1|    0 |  Somalia
1.97001e+11|  1990|      1|    0 |  France
1.97001e+11|  1991|      1|    0 |  New Zealand

DF2:

Country.Name|Code|Year|Population
Aruba       |ABW |1960| 123
Afganistan  |AFG |1970| 456
Albania     |ALB |1970| 1000
France      |FRA |1990| 5000

what is the relationship of this question to your previous question https://stackoverflow.com/questions/65603623/r-add-new-column-in-df-when-two-variables-match-with-second-df .... ?? — Ben Bolker, Jan 06 '21 at 23:50

Ben Bolker · Accepted Answer · 2021-01-06T23:56:10.563

This is well within the capabilities of merge(): note all of the emphasized words in the quote from ?merge, which are plural, i.e. the function can work on multiple matching columns ...

by, by.x, by.y: specifications of the columns used for merging. See ‘Details’.
...
By default the data frames are merged on the columns with names they both have, but separate specifications of the columns can be given by ‘by.x’ and ‘by.y’. The rows in the two data frames that match on the specified columns are extracted

merge(df1,df2,
      by.x=c("iyear","CountryTxt"),
      by.y=c("Year","Country.Name"))

  iyear CountryTxt     eventid imonth iday Code Population
1  1970    Albania 1.97000e+01      7    2  ALB       1000
2  1990     France 1.97001e+11      1    0  FRA       5000

data setup

df1 <- read.table(header=TRUE,sep="|",  strip.white=TRUE, text="
eventid    |iyear | imonth| iday | CountryTxt
1.97000e+1 |1970  |      7|    2 |  Albania
1.97000e+11|  1970|      0|    0 |  United Kingdom
1.97001e+11|  1984|      1|    0 |  Somalia
1.97001e+11|  1990|      1|    0 |  France
1.97001e+11|  1991|      1|    0 |  New Zealand
")

df2 <- read.table(header=TRUE,sep="|",  strip.white=TRUE, text="
Country.Name|Code|Year|Population
Aruba       |ABW |1960| 123
Afganistan  |AFG |1970| 456
Albania     |ALB |1970| 1000
France      |FRA |1990| 5000
")

add column to DF when two variables are matched with second DF

1 Answers1

data setup