CODEWITHSUNDEEP
r
Emma
Emma

Reputation: 31

How to merge two data frames with only some matching column names and some matching values within that column?

I have two data frames that I am trying to combine. There are two columns within each data frame that have matching titles, lets say "State" and "City".

df1 has only a select amount of State and City combos, whereas df2 has all of them.

I want to take Pov2009 and Pov2010 from df2 and add those two columns to df1, with their corresponding State/City matching up with the State/City in df1. I also want all the States and Cities listed in df2 that don't occur in df1 to go away.

The result would be df3. 

df1
State     City     Votes2007    
CA        SF       17000        
CA        Fresno   16500
NY        Ithaca   12100
ID        Boise    17200

df2
State    City      Pov2009  Pov2010
CA       SF        .1       .15
OR       Bend      .05      .05
NY       Ithaca    .02      .04
NY       Montauk   .03      .02
CA       Fresno    .15      .12
ID       Boise     .04      .04

df3
State     City     Votes2007    Pov2009  Pov2010
CA        SF       17000        .1       .15  
CA        Fresno   16500        .15      .12
NY        Ithaca   12100        .02      .04
ID        Boise    17200        .04      .04

I've tried merge(), dplyrs inner_join(), and a bunch of other things I've found through this site, but none of the questions seem to be exactly what I am looking for.

Upvotes: 1

Views: 2975

Answers (1)

Jason Mathews
Jason Mathews

Reputation: 815

innerjoin using both the columns

df3 <- merge(df1, df2, by= c("State", "City"))

   State   City Votes2007 Pov2009 Pov2010
1:    CA     SF     17000    0.10    0.15
2:    CA Fresno     16500    0.15    0.12
3:    NY Ithaca     12100    0.02    0.04
4:    ID  Boise     17200    0.04    0.04

Upvotes: 3

Related Questions