Does SQL group by have a heavy duty equivalent in R

I have hundreds of humans who have undergone SNP genotyping at hundreds of
loci. Some have even undergone the procedure twice or thrice (kind of an
internal control).

So obviously I need to find those replications, and confirm that the results
are the same. If there is discordance then I need to address it.

my.dat <- data.frame(ID=rep(1:100, sample(1:3,100,repl=T)))
snp.dat <- lapply(1:200,function(x) 0:1 )
snp.frame <- as.data.frame(do.call(cbind,snp.dat))
my.dat <- cbind( my.dat,snp.frame[sample(nrow(my.dat))%%2+1,])
system.time( table(duplicated(my.dat)) )

system.time( which( (!duplicated(my.dat)) & (my.dat$ID %in% names(which(table(my.dat$ID)>1)) ) ))

I tried to use the aggregate function

nr.attempts
<-aggregate(RawSeq$GENOTYPE_ID,list(sample=RawSeq$SAMPLE_ID,assay=RawSeq$ASSAY_ID),length)
This was simply to figure out how many times the same piece of information
had been obtained. I ran out of patience. It took beyond forever and tapply
did not perform much better. The reshape package did not help - it implied
one was out of luck if the data was not numeric. All of my data is character
or factor.

Instead I used RODBC

sqlSave(channel,RawSeq)
to push the table into a Microsoft Access database
Then a sql query, courtesy of the Microsoft Access Query Wizard a la design
mode.

SELECT RawSeq.SAMPLE_ID, RawSeq.ASSAY_ID, Min(RawSeq.GENOTYPE_ID) AS
MinOfGENOTYPE_ID, Max(RawSeq.GENOTYPE_ID) AS MaxOfGENOTYPE_ID, Count(
RawSeq.rownames) AS CountOfrownames
FROM RawSeq
WHERE (((RawSeq.GENOTYPE_ID)<>""))
GROUP BY RawSeq.SAMPLE_ID, RawSeq.ASSAY_ID
ORDER BY Count(RawSeq.rownames) DESC;

This way I could easily use the minimum and maximum values to see if they
were discordant.
Microsoft Access handled it with aplomb. I plan to use RODBC to bring the
result of the SQL query back into R.

This is the first time I have seen Microsoft Access outpace R.
Is my observation correct or am I missing something. I would much rather
perform all data manipulation and analyses in R.



-- 
Farrel Buchinsky

	[[alternative HTML version deleted]]