[R] Help with merge function

[arun]

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.