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.
On Thu, Jul 26, 2012 at 3:59 AM, guruappa <guruappa at gmail.com> wrote:> 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.Many, but I'll give one: with(DataFrameName, tapply(Score, Name, mean)) also aggregate(), ave(), and many others. Off topic: you might look at the sqldf package to do sql-form queries on R objects. Best, Michael> > 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.
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!