Hi, >From the output you wanted, it looks like: library(plyr) join(x1,x2,type="right") #Joining by: State_prov, Shape_name, bob2009, bob2010 # State_prov Shape_name bob2009 bob2010 bob2011 FID coy2009 #1 Nova Scotia Annapolis 0 0 1 0 10 #2 Nova Scotia Antigonish 0 0 0 1 1 #3 Nova Scotia Gly 0 0 NA 2 1 merge(x1,x2,all.y=TRUE) # State_prov Shape_name bob2009 bob2010 bob2011 FID coy2009 #1 Nova Scotia Annapolis 0 0 1 0 10 #2 Nova Scotia Antigonish 0 0 0 1 1 #3 Nova Scotia Gly 0 0 NA 2 1
A.K. ________________________________ From: Catarina Ferreira <catferre...@gmail.com> To: arun <smartpink...@yahoo.com> Sent: Friday, April 26, 2013 2:23 PM Subject: Re: [R] Help with merge function Hello, I didn't realize that the format had been changed after I sent the email. I'm sending you the original mail in attach in a word with the correct format, since I don't think your answer is the one I'm looking for, likely due to the erroneous format. Thank you again for your help. On Fri, Apr 26, 2013 at 2:11 PM, arun <smartpink...@yahoo.com> wrote: Hi, > >The format is bit messed up. >So, not sure this is what you wanted. > >x1<- read.table(text="State_prov,Shape_name,bob2009,bob2010,bob2011 >Nova Scotia,Annapolis,0,0,1 >Nova Scotia,Antigonish,0,0,0 >Nova Scotia,Gly,NA,NA,NA >",sep=",",header=TRUE,stringsAsFactors=FALSE) > >x2<- read.table(text=" >FID,State_prov,Shape_name,bob2009,bob2010,coy2009 >0,Nova Scotia,Annapolis,0,0,10 >1,Nova Scotia,Antigonish,0,0,1 >2,Nova Scotia,Gly,0,0,1 >",sep=",",header=TRUE,stringsAsFactors=FALSE) > merge(x1,x2,all=TRUE) ># State_prov Shape_name bob2009 bob2010 bob2011 FID coy2009 >#1 Nova Scotia Annapolis 0 0 1 0 10 >#2 Nova Scotia Antigonish 0 0 0 1 1 >#3 Nova Scotia Gly 0 0 NA 2 1 >#4 Nova Scotia Gly NA NA NA NA NA > > > > > > >----- Original Message ----- >From: Catarina Ferreira <catferre...@gmail.com> >To: r-help@r-project.org >Cc: >Sent: Friday, April 26, 2013 1:10 PM >Subject: [R] Help with merge function > >Dear all, > >I'm trying to merge 2 dataframes, but I'm not being entirely successful and >I can't understand why. > >Dataframe x1 > >State_prov Shape_name bob2009 bob 2010 bob2011 >Nova Scotia Annapolis 0 0 1 >Nova Scotia Antigonish 0 0 0 >Nova Scotia Gly NA NA NA > >Dataframe x2 - has 20000 rows and 193 variables, contains one important >field which is "FID" that is a link to a shapefile (this is not in x1) and >shares common columns with x1, like this: > >FID State_prov Shape_name bob2009 bob 2010 coy 2009 >0 Nova Scotia Annapolis 0 >0 10 >1 Nova Scotia Antigonish 0 >0 1 >2 Nova Scotia Gly 0 >0 1 > >So when I do > >x3 <- merge(x1, x2, by=intersect(names(x1), names(x2)), all=TRUE) > >it should do the trick. The thing is that it works for the columns (it adds >all the new columns not common to both dataframes), but it also adds the >rows. This is what I get (x3): > >FID State_prov Shape_name bob2009 bob 2010 coy 2009 bob2011 >0 Nova Scotia Annapolis 0 >0 10 NA >NA Nova Scotia Annapolis NA NA NA > 1 >1 Nova Scotia Antigonish 0 >0 1 NA >NA Nova Scotia Antigonish NA NA NA > 0 >2 Nova Scotia Gly 0 >0 1 NA >NA Nova Scotia Gly NA NA >NA NA > >What I want to get is a true merge, like this: > >FID State_prov Shape_name bob2009 bob 2010 coy 2009 bob2011 >0 Nova Scotia Annapolis 0 >0 10 1 >1 Nova Scotia Antigonish 0 >0 1 0 >2 Nova Scotia Gly 0 >0 1 NA > >Can anybody please help me to understand what I'm doing wrong. >Any help will be much appreciated!! > > >-- >Catarina C. Ferreira, PhD > > [[alternative HTML version deleted]] > >______________________________________________ >R-help@r-project.org mailing list >https://stat.ethz.ch/mailman/listinfo/r-help >PLEASE do read the posting guide http://www.R-project.org/posting-guide.html >and provide commented, minimal, self-contained, reproducible code. > > -- Catarina C. Ferreira, PhD Post-doctoral Research Fellow Department of Biology Trent University Peterborough, ON Canada URL: http://www.researcherid.com/rid/A-3898-2011 ______________________________________________ R-help@r-project.org mailing list https://stat.ethz.ch/mailman/listinfo/r-help PLEASE do read the posting guide http://www.R-project.org/posting-guide.html and provide commented, minimal, self-contained, reproducible code.