how to merge 2 data frame if you want to exclude mutual obs

Previous Topic Next Topic
 
classic Classic list List threaded Threaded
6 messages Options
Reply | Threaded
Open this post in threaded view
|

how to merge 2 data frame if you want to exclude mutual obs

ramoss
In the example below, I am merging 2 data frames & I want everything in the first one(all)
all2 <- merge(all,spets, by.x=c("tdate","symbol"), by.y=c("tdate","symbol"),all.x=TRUE)

What if I want to exclude everything in y? I tried below but doesn't seem to work.
all2 <- merge(all,spets, by.x=c("tdate","symbol"), by.y=c("tdate","symbol"),all.y=FALSE)

In SAS I  would do:

All2;
  merge all(in=a)
             spetz(in=b)
            ;
 by tdate symbol;
 if a and not b;
run;

How can I do this in R?
Thanks ahead for your help.
Reply | Threaded
Open this post in threaded view
|

Re: how to merge 2 data frame if you want to exclude mutual obs

ramoss
To clarify:

So if in data frame A you have

Tdate            symbol        TA
12/12/12      AX               123
12/11/12      ZZ                A4R
12/12/12      WQ              B8R

Data frame B
Tdate            symbol        TA
12/12/12      AX               123
12/11/12      ZZ                A4R

I want to end up w/
Tdate            symbol        TA
12/12/12      WQ              B8R

All obs not in B
Reply | Threaded
Open this post in threaded view
|

Re: how to merge 2 data frame if you want to exclude mutual obs

Sarah Goslee
A[!(A$symbol %in% B$symbol), ]

maybe?

# converted your email to a reproducible example, as you should
# have done - use dput() to provide data, not just copy & paste.

A <- structure(list(Tdate = c("12/12/12", "12/11/12", "12/12/12"),
    symbol = c("AX", "ZZ", "WQ"), TA = c("123", "A4R", "B8R")), .Names
= c("Tdate",
"symbol", "TA"), class = "data.frame", row.names = c(NA, -3L))
B <- structure(list(Tdate = c("12/12/12", "12/11/12"), symbol = c("AX",
"ZZ"), TA = c("123", "A4R")), .Names = c("Tdate", "symbol", "TA"
), class = "data.frame", row.names = c(NA, -2L))

> A[!(A$symbol %in% B$symbol), ]
     Tdate symbol  TA
3 12/12/12     WQ B8R

This isn't a merge problem at all, as you've explained it.

On Mon, May 13, 2013 at 4:22 PM, ramoss <[hidden email]> wrote:

> To clarify:
>
> So if in data frame A you have
>
> Tdate            symbol        TA
> 12/12/12      AX               123
> 12/11/12      ZZ                A4R
> 12/12/12      WQ              B8R
>
> Data frame B
> Tdate            symbol        TA
> 12/12/12      AX               123
> 12/11/12      ZZ                A4R
>
> I want to end up w/
> Tdate            symbol        TA
> 12/12/12      WQ              B8R
>
> All obs not in B
>
>
>
--
Sarah Goslee
http://www.functionaldiversity.org

______________________________________________
[hidden email] 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.
Reply | Threaded
Open this post in threaded view
|

Re: how to merge 2 data frame if you want to exclude mutual obs

Adams, Jean
In reply to this post by ramoss
If you create a dummy variable for spets prior to merging, you can keep
only those rows where this value is missing after merging.  For example:

# fake data
all <- data.frame(tdate=c(1, 3, 5, 7), symbol=c(2, 4, 6, 8))
spets <- data.frame(tdate=c(1, 3, 5, 2), symbol=c(2, 8, 6, 6))

# create a dummy variable for spets
spets$dummy <- 1

# merge
all2 <- merge(all, spets, by=c("tdate", "symbol"), all.x=TRUE)
all3 <- all2[is.na(all2$dummy), ]
all3

Jean

        [[alternative HTML version deleted]]

______________________________________________
[hidden email] 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.
Reply | Threaded
Open this post in threaded view
|

Re: how to merge 2 data frame if you want to exclude mutual obs

ramoss
In reply to this post by ramoss
Thanks Adam your solution worked perfectly.  Thank you all for your responses.
Reply | Threaded
Open this post in threaded view
|

Re: how to merge 2 data frame if you want to exclude mutual obs

arun kirshna
In reply to this post by ramoss
Hi,
datA<-read.table(text="
Tdate            symbol        TA
12/12/12      AX              123
12/11/12      ZZ                A4R
12/12/12      WQ              B8R
",sep="",header=TRUE,stringsAsFactors=FALSE)
datB<- read.table(text="
Tdate            symbol        TA
12/12/12      AX              123
12/11/12      ZZ                A4R
",sep="",header=TRUE,stringsAsFactors=FALSE)
library(sqldf)
sqldf('SELECT * FROM datA  EXCEPT SELECT * FROM datB')
#     Tdate symbol  TA
#1 12/12/12     WQ B8R
#or
datA[!as.character(with(datA,interaction(Tdate,symbol,TA)))%in% as.character(with(datB,interaction(Tdate,symbol,TA))),]
#     Tdate symbol  TA
#3 12/12/12     WQ B8R
A.K.



----- Original Message -----
From: "Mossadegh, Ramine N." <[hidden email]>
To: arun <[hidden email]>
Cc:
Sent: Monday, May 13, 2013 4:17 PM
Subject: RE: [R] how to merge 2 data frame if you want to exclude mutual obs

Hi Arun,

I am trying to replicate sas code in R. 

So if in data frame A you have

Tdate            symbol        TA
12/12/12      AX               123
12/11/12      ZZ                A4R
12/12/12      WQ              B8R

Data frame B
Tdate            symbol        TA
12/12/12      AX               123
12/11/12      ZZ                A4R

I want to end up w/
Tdate            symbol        TA
12/12/12      WQ              B8R

All obs not in B

Thanks


-----Original Message-----
From: arun [mailto:[hidden email]]
Sent: Monday, May 13, 2013 4:13 PM
To: Mossadegh, Ramine N.
Subject: Re: [R] how to merge 2 data frame if you want to exclude mutual obs

HI,
Please provide a reproducible example and the output you expected.





----- Original Message -----
From: ramoss <[hidden email]>
To: [hidden email]
Cc:
Sent: Monday, May 13, 2013 3:22 PM
Subject: [R] how to merge 2 data frame if you want to exclude mutual obs

In the example below, I am merging 2 data frames & I want everything in the first one(all)
all2 <- merge(all,spets, by.x=c("tdate","symbol"),
by.y=c("tdate","symbol"),all.x=TRUE)

What if I want to exclude everything in y? I tried below but doesn't seem to work.
all2 <- merge(all,spets, by.x=c("tdate","symbol"),
by.y=c("tdate","symbol"),all.y=FALSE)

In SAS I  would do:

All2;
  merge all(in=a)
             spetz(in=b)
            ;
by tdate symbol;
if a and not b;
run;

How can I do this in R?
Thanks ahead for your help.



--
View this message in context: http://r.789695.n4.nabble.com/how-to-merge-2-data-frame-if-you-want-to-exclude-mutual-obs-tp4666975.html
Sent from the R help mailing list archive at Nabble.com.

______________________________________________
[hidden email] 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.


Confidentiality Notice:  This email, including attachments, may include non-public, proprietary, confidential or legally privileged information.  If you are not an intended recipient or an authorized agent of an intended recipient, you are hereby notified that any dissemination, distribution or copying of the information contained in or transmitted with this e-mail is unauthorized and strictly prohibited.  If you have received this email in error, please notify the sender by replying to this message and permanently delete this e-mail, its attachments, and any copies of it immediately.  You should not retain, copy or use this e-mail or any attachment for any purpose, nor disclose all or any part of the contents to any other person. Thank you

______________________________________________
[hidden email] 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.