Stack Overflow
Stack Overflow

Questions tagged [sqldf]

SQLDF is an R package for running SQL statements on R data frames.

SQLDF is an R package for running SQL statements on R data frames.

The user simply specifies an SQL statement in R using data frame names in place of table names and a database with appropriate table layouts/schema is automatically created, the data frames are automatically loaded into the database, the specified SQL statement is executed, the result is read back into R and the database is deleted all automatically behind the scenes.

sqldf supports:

  • the SQLite backend database (by default),
  • the H2 java database,
  • the PostgreSQL database and
  • sqldf 0.4-0 onwards also supports MySQL.

Resources:

684 questions
4
votes
2 answers

Merge two tables on LIKE but for whole string not parts of strings

This is my first post/question so be kind. I have a dataframe like this: id product 1 00109290 Wax Salt; Pepper 2 23243242 Wood Stuff 3 23242433 Magic Unicorn Powder…
DieselBlue
  • 137
  • 6
4
votes
1 answer

Inner join on LIKE sqldf

How can I use the LIKE clause with an inner join using sqldf in R? The code: Name <- c("Jack","Jill","Romeo") Name <- as.data.frame(Name) FullName <- c("School Jack H", "School Juliet G", "College Jill M", "College Romeo F") Marks <-…
CuriousBeing
  • 1,592
  • 14
  • 34
4
votes
2 answers

Select specified rows when importing CSV

I have a large CSV file and I only want to import select certain rows if it. First I create the indices of the rows that will be imported then I wish to pass the names of these rows to sqldf and return the full records for specified rows. #create…
SharkSandwich
  • 197
  • 1
  • 8
4
votes
1 answer

R sqldf - match.fun(asfn) 'c("as.labelled", "as.integer")' is not a function, character or symbol

Total newbie to R, have just spent a couple of hours playing and thought I'd have a play with some of the NHANES datasets e.g. ftp://ftp.cdc.gov/pub/Health_Statistics/NCHS/nhanes/2003-2004/ So grabbed a couple and after a play with merge(bmx_c,…
arober11
  • 1,969
  • 18
  • 31
4
votes
0 answers

sqldf LIKE operator for cyrillic words

I have a table - data.frame in UTF-8: id|city 1|Moscow 2|London 3|Москва Row 3 is a Russian name for a city I'm using sqldf library: sqldf("select * from table_name where city like '%Moscow%'") It's OK (returns 1st row) sqldf("select * from…
Ivan
  • 107
  • 1
  • 7
4
votes
3 answers

Convert an integer value to datetime in sqldf

I am using sqldf library to return a data frame with distinct values and also only the max of the date column. The data frame looks like this +------+----------+--------+-----------------+ | NAME | val1 | val2 | DATE …
Sharath
  • 2,225
  • 3
  • 24
  • 37
4
votes
2 answers

How to aggregate strings in R SQLDF?

I have a dataset like this: DEPTNO ENAME 10 CLARK 10 KING 10 MILLER 20 ADAMS 20 FORD 20 JONES And I am trying to create a results like this: DEPTNO AGGREGATED_ENAMES 10 CLARK,KING,MILLER 20 ADAMS,FORD,JONES In Oracle…
Parth Tiwari
  • 855
  • 2
  • 9
  • 23
4
votes
4 answers

R call variable inside sqldf

I need to do a loop over sqldf statements and for that I need to call the loop variable inside the sqldf code: My table "data", might be: data <- read.table(text =" loaddate DaysRange DaysRangeNext 1 2014-03-16 30 30 2…
GabyLP
  • 3,649
  • 7
  • 45
  • 66
4
votes
3 answers

sqldf not working after update

When I run the first example from sqldf function documentation: library(sqldf) a1s <- sqldf("select * from warpbreaks limit 6") I get an error: Error in if (.allows_extensions(db)) { :missing value where TRUE/FALSE needed This error appeared…
tomaz
  • 493
  • 4
  • 13
4
votes
2 answers

r read in multiple .dat-files

Hi I am new here and a beginner in R, My problem: in the case i have more than one file (test1.dat, test2.dat,...) to work with in R i use this code to read them in filelist <- list.files(pattern = "*.dat") df_list <- lapply(filelist, function(x)…
MOMO
  • 57
  • 5
4
votes
1 answer

call SQL function within R function

I am wondering if it is possible to call an SQL function within an R function? Say for example that I have this dummy data and SQL function written in Postgres 9.3 CREATE TABLE tbl ( id VARCHAR(2) PRIMARY KEY ,name TEXT ,year_born NUMERIC …
jO.
  • 3,384
  • 7
  • 28
  • 38
4
votes
1 answer

Update function sqldf R Language

I have a problem with SQLdf. Although I am trying to update a table, it always gives NULL as an output. I red things about this problem but I cannot figure out how to solve it. My code is: fn$sqldf("update cons set V1='%$numbernew%' where…
Tony
  • 469
  • 1
  • 6
  • 18
4
votes
2 answers

SQL - Selection in one table based on matching values given in another table

After having endlessly tried to handle large (3-35gb) csv files in R, I have moved over to SQL for handling these datasets. So I am using this code within an R environment (using the using the SQlite based RSQLite package) but it should not detract…
Hugstime
  • 131
  • 1
  • 9
4
votes
1 answer

SQLDF Left Join in R

My goal is to take 'matr', sort it by column c1, and keep unique(c1) where c2 = 1. For example, from this code... c1 = c("a",'a','a','b','b','b','b','c','c') c2 = c(1,1,0,1,1,0,1,0,0) matr = as.data.frame(cbind(c1,c2)) one = sqldf('select…
RAFisherman
  • 889
  • 1
  • 8
  • 9
4
votes
0 answers

ideas to avoid hitting memory limit when using dbWriteTable to save an R data table inside a SQLite database

a data frame that's small enough to be loaded into R still occasionally hits the memory-limit ceiling during a dbWriteTable call if it was near the maximum amount of RAM available. i'm wondering if there's any better solution than reading the table…
Anthony Damico
  • 5,779
  • 7
  • 46
  • 77
1 2 3
…
45 46