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
2
votes
0 answers

CSV to SQLite to R: too many quotes for character columns

I'm trying to create a workflow where I Create a SQLite database using R Load some (potentially large -- larger than can fit in memory) CSV files into the database as tables Do analysis with dplyr It seems like something below is very close to…
kevinykuo
  • 4,600
  • 5
  • 23
  • 31
2
votes
1 answer

How to add a SQLite temp table from an R dataframe?

I have an SQLite database connection to a database file. I want to extract some data from one of the tables, do some processing in R and then create a temporary table on the same connection from the processed data. It needs to be a temp table…
dspringate
  • 1,805
  • 2
  • 13
  • 20
2
votes
2 answers

Wrapping a function around multiple SQL queries in R?

I have some SQL queries that basically parse a dataset by time (POSIXct date format): library(sqldf) data_2013 <- sqldf("SELECT * FROM data WHERE strftime('%Y-%m-%d', time, 'unixepoch', 'localtime') >= '2013-01-01' AND strftime('%Y-%m-%d',…
histelheim
  • 4,938
  • 6
  • 33
  • 63
2
votes
2 answers

Preserving a column as a factor when performing a left join using sqldf in R

How do you preserve factored variables in left joins using sqldf? I am trying to perform a left join using the sqldf function in R; however, the process seems to convert a factored column in my "right" dataframe to a character class in the merged…
penguinv22
  • 349
  • 5
  • 12
2
votes
0 answers

How can I read a .dump file with R ?

Suppose I have a .dump file that I would like to convert to a data.frame that I can then analyse in R. I know that sqldf allows us to use SQL commands on data frames but can we also use this or another package to read in .DUMP files? This post…
kpeyton
  • 149
  • 7
2
votes
0 answers

R sqldf force my numeric attribute to character

I have the following sqldf: library(sqldf) B <- sqldf( " select * , CASE WHEN Length = '1 day' THEN 1 WHEN Length = '2 days' THEN 2 WHEN Length = '4- 8 weeks' THEN 42 WHEN Length = '26+ weeks' THEN 180 END as lengthI …
AdamNYC
  • 19,887
  • 29
  • 98
  • 154
2
votes
1 answer

Inner Join when Duplicates Exist for Joining Variable

Possible Duplicate: How to join data frames in R (inner, outer, left, right)? This question nicely goes over joins in R. And the Wikipedia article on inner join is useful. I would like to reproduce this result in base R. I don't think the…
julieth
  • 430
  • 4
  • 9
1
vote
1 answer

R tcl/tk package running Mac OS X not working

I have R 2.14 installed on a Mac OS X 10.7.2 (Lion). I noticed that when I try to use the sqldf package, R hangs. I tried the fix as suggested by the sqldf help, which is to execute: options(gsubfn.engine = "R") prior to loading the sqldf…
user1086586
  • 11
  • 3
1
vote
1 answer

Why did sqldf in R give this syntax error?

I tried package sqldf in R. I tried a very easy join. However, I got this syntax error. library(sqldf) radius1 <- sqldf("select radius.*, all.height, all.bmi from radius left join all on radius.uID =…
Jin Long
  • 13
  • 2
1
vote
3 answers

While performing SQL query in python using pandas i am facing the error : TypeError: __init__() got multiple values for argument 'schema'

enter image description here Trying to compile sql query in python. Which i used to do very frequently in python. But never faced this error in my past. Help me in fixing the same. Query: from pandasql import sqldf import pandas as pd from sklearn…
Sai Nandhan Rayapureddy
  • 41
  • 2
  • 4
1
vote
0 answers

Is it possible to use PostgreSQL driver in r programs on local dataframes via sqldf?

Of course, it is possible to connect from r to a PostgreSQL server and use sqldf to run queries on that server. But is it possible to use PostgreSQL's SQL syntax and functions on local dataframes without even establishing a connection to a remote…
user17911
  • 1,073
  • 1
  • 8
  • 18
1
vote
2 answers

Fill a data.frame column with values from other data.frame column with a condition R

I have this two data.frames: df1 = data.frame( "scientific_name"=c("Anchietea ballardii","Anemia patens","Cololobus argenteus"), "threat_status"=c("VU","EN","EN") ) > df1 scientific_name threat_status 1 Anchietea ballardii …
Fabiula Arantes
  • 25
  • 2
1
vote
1 answer

How to troubleshoot no such column error sqldf in r

I'm getting an error (no such column: cs7.Start) while executing a Where clause using sqldf in r. I have looked at the other historical post and tested few things (single quote, underscore) but of no luck. Any help appreciated. dat <-…
Aravindan Kalai
  • 125
  • 6
1
vote
1 answer

R sqldf Count, Group By, Having > 1

I have a table with 3 columns, and a few thousand records, sample is below: df1 <- data.frame( ID = c('V1', 'V1', 'V1', 'V3', 'V3', 'V3', 'V4', 'V5','V5','V5'), Category = c('a', 'a', 'a', 'a', 'b', 'b', 'a', 'b', 'c', 'c'), Amount = c(1, 1,…
Jorge
  • 336
  • 1
  • 4
  • 15
1
vote
0 answers

Error with python using pandasql: "no such column:..." when the column actually exists

I'm Lucas and I'm a master student in management science in Belgium. I have a very poor background in coding and everything related to it, so my problem is probably very basic but still very confusing for me. This semester, I follow a course named…
Lucas Leblond
  • 11
  • 1
1 2 3
…
45 46