0

I have created an R function to perform subsetting, summaries, densities, and plotting. I was initially assigning out the subsets to my workspace in RStudio but I started running into memory constraints. The latest revision is attempting to store the summarized observation counts in a SQLite database vs. exporting the subsets as their own dataframes. The theory was that this would utilize less memory. In order to perform this process I created a new database in my function as in:

sqldf("attach 'mydb' as new")
sqldf("create table TotalsTbl as select 'resimrr' as name, count(*) as count from resimrr", dbname="mydb")
sqldf("insert into TotalsTbl select 'resimrrquan' as name, count(*) as count from resimrrquan", dbname="mydb")

I then create a few tables, and populate them as the function processes. Finally I export the query results of SQLite Tables to dataframes that I then assign out to my workspace.

This provides the expected result:

sqldf("select * from TotalsTbl, dbname="mydb")
name count
1 resimrr 95517
2 resimrrquan 93928

The challenge now is that the database is persistent. It is created the first time the function is envoked, it lives in my Rsessions and my workspace and if I run the function again the CREATE TABLE commands fail because the tables already exist. So the question is how do I delete/clean up 'mydb' after I am through with it in my function?

lmo
  • 37,904
  • 9
  • 56
  • 69
Scott
  • 1
  • 1
  • What's wrong with just using `dbname = tempfile()` so that it uses an external database rather than memeory? Is it too slow? – G. Grothendieck May 16 '13 at 17:11
  • 1
    How about file.remove("mydb") as an sqlite DB is just a file. –  May 17 '13 at 15:14
  • @g-grothendieck, yes the reason I'm not using the tempfile() option is because I kept running out of memory while processing this dataset. – Scott May 22 '13 at 20:22
  • @jwijffels, got it thanks! This should work for dropping the database (i.e. deleting the file). It turns out not deleting the db will actually work to my advantage I just needed to modify my approach. My challenge was that I had no idea where R and the SQLDF package were creating and storing this file on the file system. Found my answer here - R's working directory, [link](http://tinyurl.com/q8jtg5p) – Scott May 22 '13 at 20:28

0 Answers0