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
3
votes
1 answer

R package sqldf not loading tcltk and returning a warning. How to solve it?

I'm using RStudio to run some data analysis and would like to use the package sqldf. Once I require the package, I get the output bellow: Loading required package: sqldf Loading required package: gsubfn Loading required package: proto Could…
Synue Cunioci
  • 31
  • 1
  • 2
3
votes
0 answers

R sqldf fails with "rsqlite_query_fetch: failed first step: database or disk is full"

I'm trying to select some subsets from the dataframe in R using sqldf. So my code looks like: library("sqldf") ... usecase <- as.data.frame(use_case_list[[i]]) user_day_view <- sqldf("select distinct targetuser, day, count(*) from usecase group by…
Andrey Sapegin
  • 454
  • 8
  • 33
3
votes
2 answers

R, join within a range vectorised

I'm trying to join two datasets where a variable (or position along a genome) in one dataset fits within a range in the second (gene start/stop position). However, positions are not unique, but nested within an additional column (chromosome). The…
smm
  • 75
  • 4
3
votes
4 answers

Join two datasets based on an inequality condition

I have used the call below to "join" my datasets based on an inequality condition: library(sqldf) sqldf("select * from dataset1 a, dataset2 b a.col1 <= b.col2") However, is there a way I can do this without sqldf? So far, I can only see merge…
anirana
  • 39
  • 1
  • 3
3
votes
1 answer

sqldf changes a numeric column into character one when ordered by it

Today I've found an issue I cannot explain. Is this a well-known behavior? Dataset: structure(list(Original.Unit = c("some unit", "some unit", "some unit", "some unit", "some unit", "some unit"), Result = c(24, 28, NA, 4.1, 4.5, 2.6),…
Bastian
  • 313
  • 1
  • 13
3
votes
3 answers

Sample Rows with SQLDF

sqldf has a limit option to get 'X' rows. Can we also do a 'x%' sample using sqldf? e.g. > sqldf("select * from iris limit 3") Loading required package: tcltk Sepal.Length Sepal.Width Petal.Length Petal.Width Species 1 5.1 3.5 …
myloginid
  • 1,463
  • 2
  • 22
  • 37
3
votes
1 answer

how to use right/left to split a variable in sqldf, as in left(x,n)

Is there a different function in R to use to get the same result as in SQL for left or right function? For instance, the following query in SQL would give the first 6 characters of a column: select left(x, 6) from table However, when I try this in…
asiehh
  • 553
  • 12
  • 22
3
votes
2 answers

converting numeric to character in sqldf

I am trying to concatenate 2 columns of a data-frame in R using sqldf command. However, it treats them as numeric and sums them up. I looked at the solution to a similar question on stackoverflow but it still doesn't work for me. Here's what my DF…
asiehh
  • 553
  • 12
  • 22
3
votes
0 answers

Error: could not find function "sqldf" using R to run SQL

I faced a question,there is a error message to run sqldf package. I just load install.packages("sqldf") install.packages("RSQLite") install.packages("tcltk2") library("sqldf") library("RSQLite") library("tcltk2") final <-sqldf("select a.age, …
user3849475
  • 267
  • 3
  • 4
  • 12
3
votes
1 answer

Read and query SQLite database using sqldf

I have a SQLite database pisa06.db created from a data frame. I would like to read variables (columns) from this database file selectively without actually loading into memory. I know I can do it with a data frame already in the workspace. I…
Murat Tasdemir
  • 75
  • 5
3
votes
0 answers

Error in R when running an sqldf function: ‘package ‘RSQLite.extfuns’ could not be loaded’

I'm getting an error when I'm running the sqldf command in R: ‘package ‘RSQLite.extfuns’ could not be loaded’ Here's my code: install.packages("RSQLite") library("RSQLite") install.packages("sqldf") library("sqldf") Ent_forecast<-sqldf("select day, …
Ryan Chase
  • 2,384
  • 4
  • 24
  • 33
3
votes
1 answer

How can I concatenate strings in SQLDF in R

I am looking for a function to concatenate two strings inside SQLDF in R, that works like paste(), but could not find any. The reason for doing that is I want to concatenate two columns while joining two data frames. Instead of using merge() to do…
smz
  • 263
  • 4
  • 11
3
votes
1 answer

Identify Inf in sqldf

How does one identify Inf, -Inf when using SQL with sqldf? Sample data: x <- data.frame(val = c(1, 2, 3, Inf)) Now, I am using: sqldf('select * from x where val < 999999999999999999999') But this does not seem very safe.
Tomas Greif
  • 21,685
  • 23
  • 106
  • 155
3
votes
3 answers

sqldf doesn't install on Ubuntu 14.04

I'm trying to install sqldf package in an Ubuntu 14.04 machine, but I'm getting the following error: Warning in install.packages : package ‘sqldf’ is not available (for R version 3.0.2) I tried to install the library libpq-dev as suggested from…
Filipe Ferminiano
  • 8,373
  • 25
  • 104
  • 174
3
votes
2 answers

How to store R objects (lists) in an SQLite database?

I was just wondering whether it is possible to store R lists directly in a SQLite database (and of course retrieve them) via sqldf or RSQLite.
jack
  • 73
  • 1
  • 7
1 2 3
…
45 46