[R] Help with merge function

[arun]

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 <catferreira at gmail.com>
To: arun <smartpink111 at 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 <smartpink111 at 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 <catferreira at gmail.com>
>To: r-help at 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 at 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