[R] Question regarding sqldf

[Rizzo.Michael at epamail.epa.gov]

Here is a test data frame:

> testframe<-data.frame(sdate=rep(paste
("day",1:5,sep=""),each=5),bext=runif(25,1,100),otherstuff=runif
(25,45,60),stuff=runif(25,3,25))
> testframe
   sdate      bext             otherstuff     stuff
1   day1 37.863859   49.19816 10.036211
2   day1 58.557049   59.23145 21.793954
3   day1 70.345462   48.82313 24.630742
4   day1  1.245913   57.70718 14.131270
5   day1 40.170634   50.38963 21.420328
6   day2 36.126846   51.97043 22.815169
7   day2 95.801896   53.30061 13.540535
8   day2 19.959486   50.46775  9.403084
9   day2  5.772996   55.14082 17.137113
10  day2 77.956966   51.44969 13.448527
11  day3  6.743810   54.49836 17.650795
12  day3 60.758896   55.35407  5.005359
13  day3 86.924873   57.74122  8.691572
14  day3 91.933544   48.17538  8.559737
15  day3 23.627126   59.28906  4.766172
16  day4 76.200158   55.78072 19.493428
17  day4 67.809049   51.06784  7.116428
18  day4 70.309643   54.38067 10.736461
19  day4 91.152166   58.39768 23.199943
20  day4 30.358417   54.16645 24.153823
21  day5 64.064953   58.31813 19.856882
22  day5 49.220106   48.49721 16.615940
23  day5 21.554696   47.03421  3.585458
24  day5 30.227874   55.51724 14.435731
25  day5 51.518716   54.31445 20.837326

If I run the following sqldf statement:

 maxdays<-sqldf("select distinct * from testframe group by sdate having
bext=max(bext)")

I get:

> maxdays
data frame with 0 columns and 0 rows

I want to get the entire observation for each day where "bext" is the
maximum for the day.



|------------>
| From:      |
|------------>
  >--------------------------------------------------------------------------------------------------------------------------------------------|
  |milton ruser <milton.ruser at gmail.com>                                                                                                       |
  >--------------------------------------------------------------------------------------------------------------------------------------------|
|------------>
| To:        |
|------------>
  >--------------------------------------------------------------------------------------------------------------------------------------------|
  |Michael Rizzo/RTP/USEPA/US at EPA                                                                                                              |
  >--------------------------------------------------------------------------------------------------------------------------------------------|
|------------>
| Cc:        |
|------------>
  >--------------------------------------------------------------------------------------------------------------------------------------------|
  |r-help at r-project.org                                                                                                                        |
  >--------------------------------------------------------------------------------------------------------------------------------------------|
|------------>
| Date:      |
|------------>
  >--------------------------------------------------------------------------------------------------------------------------------------------|
  |07/30/2009 03:03 PM                                                                                                                         |
  >--------------------------------------------------------------------------------------------------------------------------------------------|
|------------>
| Subject:   |
|------------>
  >--------------------------------------------------------------------------------------------------------------------------------------------|
  |Re: [R] Question regarding sqldf                                                                                                            |
  >--------------------------------------------------------------------------------------------------------------------------------------------|





Hi Rizzi,

how about a reproducible example/data.frame?

:-)
milton

On Thu, Jul 30, 2009 at 1:46 PM, <Rizzo.Michael at epamail.epa.gov> wrote:

  Hello,

  I am having a problem using sqldf.  I'm trying to choose a subset of
  observations from a data set based on the date and maximum value of a
  variable by date.

  Here is the code I am using:

  test<-sqldf("select distinct * from bextuse group by sdate having
  bext=max(bext)",method="raw");

  The result I get back is a data frame with 0 rows and 0 columns.  I
  have
  tried the code in another program that utilizes SQL, and I retrieve
  the
  14 rows I was expecting to get.
  I looked at the SQLite information on the web, and it mentions that
  the
  "having" clause can be used to select observations from grouped data
  using aggregating functions.

  Any help or advice is greatly appreciated.

  ______________________________________________
  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.