0

I currently have a dataframe in R called DDS_LS. Currently each line is by transaction but I need the transactions aggregated by the Customer_ID. 

sqldf("SELECT Customer_ID, count(distinct Lifestyle), count(distinct Price_Point),
         cumsum(Delivered_Sales), cumsum(QTY_sold)
        FROM DDS_LS GROUP BY Customer_ID")

Then I get the following error:

Error in rsqlite_send_query(conn@ptr, statement) : 
  external pointer is not valid

What is causing this?

Scarabee
  • 5,437
  • 5
  • 29
  • 55
shockman
  • 1
  • 1
  • 2
    Can you make a reproducible example? – Roman Luštrik Aug 17 '17 at 19:30
  • 1
    I'm able to reproduce the behavior (if not the root cause) by encouraging `sqldf` to persist the SQLite connection and manually closing it. That is, `library(sqldf); sqldf(); getOption("sqldf.connection");` shows a connection as active. If I close it with `dbDisconnect(getOption("sqldf.connection"))` and try a call to `sqldf`, it fails. This is the only way I can reproduce that error using `sqldf`, but it also seems unlikely that you would unwittingly do that. A reproducible example (from a fresh R instance) is very important here. – r2evans Aug 17 '17 at 19:46

1 Answers1

0

You're trying to use cumsum, which doesn't exist in sqldf. You can see the list of supported aggregating functions here: https://sqlite.org/lang_aggfunc.html

If you want to get the cumulative sum, I recommend moving your code to dplyr. 

library(tidyverse)
DDS_LS %>%
  group_by(Customer_ID) %>%
  summarise(count(distinct(Lifestyle)),
            count(distinct(Price_Point)),
            cumsum(Delivered_Sales),
            cumsum(QTY_sold))
Andrew Brēza
  • 7,705
  • 3
  • 34
  • 40
  • 1
    The error provided doesn't match this answer. If it were just this, the error would read `no such function: cumsum` (I just checked). Instead it says `external pointer is not valid`, indicating something else entirely. – r2evans Aug 17 '17 at 19:34
  • I also had trouble reproducing the exact error with some demo datasets, but I decided that the invalid call to `cumsum` meant the code was doomed no matter what so I suggested an alternative. – Andrew Brēza Aug 17 '17 at 19:35