[R] join tables in R

[Steve Lianoglou]

Hi,

On Wed, May 4, 2011 at 10:32 AM, Roccato Alfredo (UniCredit)
<Alfredo.Roccato at unicredit.eu> wrote:
> I'd to match-merge 2 tables in such a manner that I keep all the rows in table 1, but not the rows that are in both table 1 and 2.
> Thank you for your help,
> Alfredo
>
>> master <- data.frame(ID=2001:2011)
>> train   <- data.frame(ID=2004:2006)
>> valid <- ???
>
> in this example table valid should have the following
>
>> str(valid)
>  Year: int  2001 2002 2003 2007 2008 2009 2010 2011

Are you working with only one column at a time? If so:

R> keep <- !(master$ID %in% train$ID)
R> valid <- master[keep,]

If you are working with combinations of columns as the keys for each
row, there are other ways ...

> in SAS I'd do the following:
> data master; do id=2001 to 2011; output; end; run;
> data train; do id=2004 to 2006; output; end; run;
> data valid; merge master(in=a) train(in=b); by id; if a and not b; run;
>
> and in SQL:
> create table valid as
>  select a.* from master where ID not in (select ID from train)

My solution does pretty much what this select statement would do.

-steve

-- 
Steve Lianoglou
Graduate Student: Computational Systems Biology
 | Memorial Sloan-Kettering Cancer Center
 | Weill Medical College of Cornell University
Contact Info: http://cbio.mskcc.org/~lianos/contact