Shivam
2012-May-12 22:18 UTC
[R] Query regarding date as argument in functions - and about sqldf
Hi, I have a query about sqldf, and dates in general. I couldnt find much on the net or on the forums, hence I am here. Here is the issue: I want to write a function that accepts 3 arguments: date1, date2 and a dataframe, say 'df'. Within the function, I want to populate a temp dataframe which essentially contains the output of the query "select * from df where DATE between date1 and date2". DATE is a column (of class Date) which will be present in the input dataframe. This is how the function will look like: testfun = function("date1", "date2", df){ temp = sqldf("select * from df where DATE between 'date1' and 'date2'") return(temp) #not sure if I'm doing this correctly } There are multiple issues here: One is, how do I accept date as a parameter? Do I need to change the class of date1 and date2 at input level or within the sql query? In general how to format the input and how to return the temp dataframe once the function ends, kindly help. It may be basic but I've spent hours on it with no success. The above was the bigger picture. Another issue that I faced is when running a simple select (using sqldf) on a sample dataframe, I could not get a result. But when I use 'subset', I get an output. Below is the sample dataset named 'dlfhead': structure(list(NAME = c("DLF", "DLF", "DLF", "DLF", "DLF", "DLF" ), EXPDATE = structure(c(15001, 15001, 15001, 15001, 15001, 15001 ), class = "Date"), STRIKE = c(280, 280, 280, 280, 280, 280), TMSTMP = c("14:54:17", "14:42:38", "14:09:04", "14:20:25", "12:27:26", "11:59:36"), PRICE = c(22, 23, 23, 23, 23, 23.2 ), DATE = structure(c(14977, 14977, 14977, 14977, 14977, 14977), class = "Date"), TTE = c(24, 24, 24, 24, 24, 24), DTTM = structure(c(1294046657, 1294045958, 1294043944, 1294044625, 1294037846, 1294036176), class = c("POSIXct", "POSIXt")), SPOT = c(295, 294.5, 294.8, 293.85, 294.7, 294.55), MONEYNESS c(1.05357142857143, 1.05178571428571, 1.05285714285714, 1.04946428571429, 1.0525, 1.05196428571429), TTEBY7 = c(4, 4, 4, 4, 4, 4), COMB c("2011-01-034", "2011-01-034", "2011-01-034", "2011-01-034", "2011-01-034", "2011-01-034"), RATE = c(7.55219988366433, 7.55260732560891, 7.55302228192484, 7.55344470721609, 7.55387455629645, 7.82914555610361 )), .Names = c("NAME", "EXPDATE", "STRIKE", "TMSTMP", "PRICE", "DATE", "TTE", "DTTM", "SPOT", "MONEYNESS", "TTEBY7", "COMB", "RATE"), row.names = c(NA, 6L), class = "data.frame") Below are the commands and their outputs:> dlftest = sqldf("select * from dlfhead where DATE = '2011-01-03'") > nrow(dlftest)[1] 0 Kindly note that in the above, the query does complete successfully with 'dlftest' created with 0 rows. When I run the select on a column with class other than date I get the output.> dlftest = sqldf("select * from dlfhead where TTE=24") > nrow(dlftest)[1] 6 But when I use the 'subset' command, I do get an answer:> dlftest = subset(dlfhead, DATE == '2011-01-03') > nrow(dlftest)[1] 6 So the issue has to be the date-class columns in the dataframe, but what is the issue I have no idea. Can anyone help please? Thanks a lot for reading though this long email. Kindly reply if you have an idea about either issue. Regards, Shivam [[alternative HTML version deleted]]
Gabor Grothendieck
2012-May-12 23:14 UTC
[R] Query regarding date as argument in functions - and about sqldf
On Sat, May 12, 2012 at 6:18 PM, Shivam <shivamsingh at gmail.com> wrote:> Hi, > > I have a query about sqldf, and dates in general. I couldnt find much on > the net or on the forums, hence I am here. Here is the issue: > > I want to write a function that accepts 3 arguments: date1, date2 and a > dataframe, say 'df'. Within the function, I want to populate a temp > dataframe which essentially contains the output of the query "select * from > df where DATE between date1 and date2". DATE is a column (of class Date) > which will be present in the input dataframe. This is how the function will > look like: > > testfun = function("date1", "date2", df){ > temp = sqldf("select * from df where DATE between 'date1' and 'date2'") > return(temp) #not sure if I'm doing this correctly > }See FAQ 4 on the sqldf home page. http://sqldf.googlecode.com -- Statistics & Software Consulting GKX Group, GKX Associates Inc. tel: 1-877-GKX-GROUP email: ggrothendieck at gmail.com