[R] Passing arguments to SQL Query in R

[John Kane]

Let me count the ways...

R supplies a number of different ways.  Here is sample using basic R and some other packages.  Youprobably will  need to install the packages ( ?install.packages) to run any but aggregate(). 

mydata <- structure(list(Name = structure(c(4L, 4L, 4L, 4L, 2L, 2L, 2L, 
3L, 3L, 3L, 3L, 3L, 3L, 1L, 1L, 1L), .Label = c("Jacques K", 
"Rahul D  ", "Ricky P  ", "Sachin T "), class = "factor"), Score = c(25L, 
53L, 57L, 34L, 38L, 31L, 53L, 7L, 45L, 27L, 17L, 86L, 48L, 23L, 
86L, 32L)), .Names = c("Name", "Score"), class = "data.frame", row.names = c(NA, 
-16L))

# R basic
aggregate(Score ~ Name, mean, data = mydata)

library(reshape2) 
dcast(mydata, Name ~ . , mean)

library(plyr)
ddply(mydata, .(Name), summarize, Mean = mean(Score))

library(data.table)
DT  <-  data.table(mydata)
DT[, mean(Score),by=Name]

John Kane
Kingston ON Canada


> -----Original Message-----
> From: guruappa at gmail.com
> Sent: Thu, 26 Jul 2012 01:59:37 -0700 (PDT)
> To: r-help at r-project.org
> Subject: [R] Passing arguments to SQL Query in R
> 
> Hello all,
> 
> I am a newbie at R, with some experience in PERL.
> 
> I have a database table that contains the following data:
> Name      | Score
> ======= | =====
> Sachin T  | 25
> Sachin T  | 53
> Sachin T  | 57
> Sachin T  | 34
> Rahul D   | 38
> Rahul D   | 31
> Rahul D   | 53
> Ricky P   |  7
> Ricky P   | 45
> Ricky P   | 27
> Ricky P   | 17
> Ricky P   | 86
> Ricky P   | 48
> Jacques K | 23
> Jacques K | 86
> Jacques K | 32
> 
> I want the summary data as below:
> 
> Name | Avg Score
> Jacques K|47.00
> Rahul D|40.67
> Ricky P|38.33
> Sachin T|42.25
> 
> Currently, I am trying to pass the name of the person as an argument in a
> sql query by breaking the query into parts, and then concatenating the
> query
> string.  Of course, the scheme is not working.
> 
> I want a query as below:
> SELECT NAME, SCORE FROM PLAYER_SCORE_TBL where NAME = ?
> 
> Then I can pass the parameters like we do in PERL.
> 
> If there is an alternative to the whole scheme, that would be wonderful.
> a
> way that would just take the whole table, and provide pivot table like
> solution.
> 
> Thanks in advance,
> Guruppa Padsali.
> 
> 
> 
> 
> --
> View this message in context:
> http://r.789695.n4.nabble.com/Passing-arguments-to-SQL-Query-in-R-tp4637899.html
> Sent from the R help mailing list archive at Nabble.com.
> 
> ______________________________________________
> 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.

____________________________________________________________
FREE 3D EARTH SCREENSAVER - Watch the Earth right on your desktop!