First/Last/Lead/Lag Errors with dplyr

[John Hall]

I'm having difficulty with the first/last/lead/lag functions in dplyr. I was hoping to do something similar to
http://stackoverflow.com/questions/27117429/scale-relative-to-a-value-in-each-group-via-dplyr/27117622

I'm using an sqlite database. I ran the following command to get data for 1 firm on two dates

test <- db_firm_data %>%
        select(date_id, firm_id, Total_Return) %>%
        filter(firm_id == 1 && (date_id == date_id_i || date_id == date_id_i_1))

Running test gives

From: firm_data [2 x 3]
Filter: firm_id == 1 && (date_id == "2014-05-31 00:00:00.000000" || date_id == "2014-04-30 00:00:00.000000") 

                     date_id firm_id Total_Return
1 2014-04-30 00:00:00.000000       1     1321.604
2 2014-05-31 00:00:00.000000       1     1315.310

I run into difficulty trying to create an index from these values. I've tried a few like
test2 <- test %>%
         mutate(index = Total_Return / first(Total_Return))
test2 <- test %>%
         mutate(index = Total_Return / order_by(date_id, first(Total_Return)))
test2 <- test %>%
         mutate(index = Total_Return / order_by(date_id, lag(Total_Return)))
test2 <- test %>%
         mutate(index = Total_Return / lag(Total_Return, order_by(date_id)))

but I get errors like

Error in sqliteSendQuery(con, statement, bind.data) : 
  error in statement: no such function: FIRST

Is this just a problem with sqlite (resolved by switching to another db?) or am I making a mistake?

[Hadley Wickham]

SQLite doesn't support window functions (i.e. first, last, lead, lag)
Hadley

> --
> You received this message because you are subscribed to the Google Groups
> "manipulatr" group.
> To unsubscribe from this group and stop receiving emails from it, send an
> email to manipulatr+...@googlegroups.com.
> To post to this group, send email to manip...@googlegroups.com.
> Visit this group at http://groups.google.com/group/manipulatr.
> For more options, visit https://groups.google.com/d/optout.



--
http://had.co.nz/

[John Hall]

So I guess I have to convert the SQLite database to something else. I was thinking PostgreSQL. I was hoping to do everything within RPostgreSQL, but it seems like I need to create the database outside of R. Next would be inserting the tables from the SQLite database to the one I create. Is this on the right track?

[Hadley Wickham]

Yeah, once you create the database you should be able to use
DBI::dbReadTable and DBI::dbWriteTable to copy the data across
(assuming it's not too large/complex)