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

Error in dbPreExists && !overwrite : invalid 'x' type in 'x && y'

I'm executing SQL using RPostgreSQL and sqldf packages. Connection: drv <- dbDriver("PostgreSQL") con <- dbConnect(drv, dbname="dbname",host="host",port=5432,user="usr",password="pwd") Then I build a pretty complex SQL statement and at the end I…
Tomas Greif
  • 21,685
  • 23
  • 106
  • 155
0
votes
1 answer

sqldf--select nested in another select doesn't work

I'm working with the sqldf package in R. I want to create a dataset whose students' ID are excluded from another dataset. My code looks like: delete <- sqldf("select distinct ID from A where ...") B<-sqldf("select * from A where ID not in ('select…
digdigbrain
  • 13
  • 3
0
votes
0 answers

How do you explicitly delete a SQLite database created with the sqldf library in an R script

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…
Scott
  • 1
  • 1
0
votes
1 answer

read.csv.sql with empty commas

I have a csv file with fixed headers. But some of the column values can be missing with empty commas. This is creating problems for read.csv.sql. Am I missing any parameter for this function? I expect to read null/NA - is there any…
Nishanth
  • 6,932
  • 5
  • 26
  • 38
0
votes
1 answer

Repeat sqldf over different values of a variable

Just a little background: I got into programming through statistics, and I don't have much formal programming experience, I just know how to make things work. I'm open to any suggestions to come at this from a differet direction, but I'm currently…
user1507458
  • 3
  • 3
0
votes
1 answer

Same output for two entirely different queries

The following two queries gives the same output when run in R studio v_0.96 1) ab<-sqldf('select a.Family_tree_id, a.parent_name from test as a, test as b where a.child_id <> b.parent_id group by…
Bilal arif
  • 1
  • 1
-1
votes
1 answer

find timestamp difference between 2 columns with sqldf

According to this answer: https://stackoverflow.com/a/25863597/12304000 We can use something like this in mysql to calculate the time diff between two cols: SELECT TIMESTAMPDIFF(,,); How can I achieve the same…
x89
  • 2,798
  • 5
  • 46
  • 110
-1
votes
2 answers

SQL to R transition on a query

So I need to transition a query from SQL to R and it just so happens that there are limited functions that can be available in R i think. SQL: if object_id ('tempdb..#ProductCodingChanges') is not null drop table #ProductCodingChanges SELECT…
Chardiezard Gaming
  • 23
  • 3
-1
votes
1 answer

Filter data.frame using sqldf and/or dplyr in R

I need to find null values inside a data.frame using sqldf or dplyr libraries. I know that I can use na.omit() to do that, but I cant find the way to do the same using sqldf or dplyr libraries. Does anyone know how to do that? Thank you
Aragorn64
  • 149
  • 7
-1
votes
1 answer

Sqldf: Error in result_create(conn@ptr, statement) : near "over": syntax error in R

Example of my data mydata=structure(list(generated_id = c(1003477323030100, 1003477323030100, 1003477323030100, 1003477323030100, 1003477323030100, 1003477323030100, 1003477323030100, 1003477323030100, 1003477323030100, 1003477323030100,…
Julia
  • 141
  • 11
-1
votes
1 answer

pandasql: count occurrences of pairs

I was trying to count the number of matches that A and B have ever played, the dataset looks like this: so the number of matches team1 and team 29 have played is 2 as they each once were HomeTeam and AwayTeam, however, using my query I could only…
Kristy.Z
  • 27
  • 3
-1
votes
1 answer

How do I query sqldf in R using Function parameter/arguments

I am trying the following code : df_rhs<- sqldf("select rhs from df_basket12 where lhs like '%", med, "%'") here, med the function argument of the function med_aff1<- function(med) error coming: Error in result_create(conn@ptr, statement) :…
Koushik Roy
  • 1
  • 2
-1
votes
1 answer

How to find customers with the same pattern over time?

I have a table with columns ID, DATES, RATES, one ID (customer) has multiple rates but one can only have one rate on one specific date. ID DATES RATES 5 2014-07-01 0.02 5 2014-07-02 0.03 5 2014-07-03 …
NightDog
  • 91
  • 7
-1
votes
1 answer

Automatic transfer data from the sql to R

I get the data from the sql server to perform regression analysis, and then the regression results i return back to another sql table. library("RODBC") library(sqldf) dbHandle <- odbcDriverConnect("driver={SQL…
psysky
  • 3,037
  • 5
  • 28
  • 64
-1
votes
1 answer

Loop to match records in one data frame and replace with records in another data frame until there's no replacement in R or Python

I have two data frames. i want to write a while loop to check for match values and Replace. Eg. if item = item2 and Rep !=Rep2 then create a new column in df2 with Rep. else if item=item2 and Rep = Rep2 then check if Rep2 has another replacement…
BenT
  • 37
  • 5
1 2 3
…
45 46