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

sqldf error in r when joining tables

I have two tables of customer and order,I want to filter out the customer_id which meets the demand of step1 and step2,while when I do the step2.5,the console shows Error: Cannot pass NA to dbQuoteIdentifier() In addition: Warning message: In…
Jeff Hoffman
  • 71
  • 1
  • 2
  • 9
-1
votes
1 answer

data aggregation based on timestamp lookback

I am looking for a data aggregation approach based on timestamps. Here is my sample data: new <- data.table( date = as.POSIXct( c( "2016-03-06 12:23:00", "2016-03-07 12:21:00", "2016-03-08 12:26:00" ,"2016-03-09 12:30:00","2016-03-10 12:50:00", …
PSraj
  • 229
  • 4
  • 10
-1
votes
1 answer

How to find standard deviation in R using sqldf package?

All i can do with sqldf package is calculate " avg" "count " and "sum". Can i define my own function for standard deviation? It is really necessary for me to calculate Standard deviation using sqldf only.
JAS
  • 25
  • 3
  • 6
-1
votes
1 answer

No results using read.csv.sql

I have a 6 gb csv file that I am trying to read into R using read.csv.sql from the sqldf package. For some reason, the result returns 0 results. What is wrong with my code? I get a warning message of "closing unused connection" which may not be…
user225205
  • 1
  • 2
-1
votes
2 answers

R name dataframe from i in loop

I have the following code, and I would like to name the dataframe created from sqldf, as t2_value_of_i. I then want to append those files into one single file, what is the recommended way of going about this? The iteration below overwrites as t2_i…
tcc
  • 11
  • 1
  • 4
-1
votes
1 answer

Overcome the coercion of dataframe to a list when using sqldf

I need to classify the rows into two groups(Present or Not present) based on the values in the two columns Var1, Var2 in the dataframe pum. Later add this as a new column to another dataframe as data.cov.cat$PU. When Var1 greater than 0.053 or Var2…
Prradep
  • 5,506
  • 5
  • 43
  • 84
-1
votes
1 answer

Sqldf: convert and select

I'm trying to read a SQL database in R (sqldf, postgresql) but on one column there has to be some restrictions (convert and select). The column I want to adjust contains three possible elements (all as strings), namely the empty string, four numbers…
ABC
  • 125
  • 1
  • 10
-2
votes
1 answer

How do I fix my "duplicate column" error?

I have included the original problem for reference. I'm not sure how to fix the "duplicate column" error. Also, I have to combine the three datasets into one (hence the duplicate issue). I've seen lots of how-to for 2 datasets combining, but am…
Katherine Roeser
  • 1
-2
votes
1 answer

R grouping and counting affinity lexicon

i have a list of words, freq, and affinity lexicon and am trying to count and group, but not sure how to include the frequency in the code. each of these 2 lines works except not counting the freq column in the addition and i'm not sure how to do…
Peter Ruben
  • 1
  • 1
-2
votes
1 answer

How to use "sqldf" in a function

I am trying with the below code but getting the error: I tried with both "sprintf", "fn$sqldf" to select the output. my function will run two times one with the column "a" and another with column "e". Please suggest. I got this suggestion from some…
Vish
  • 37
  • 4
-2
votes
2 answers

count character until ',' in sqldf in R

I have a text with comma and I need count of number characters until comma, because i want select the interval before comma. Example: text: "12345, Supply" I want select just "12345". Is it possible? tks!
Fernando M
  • 1
  • 1
-2
votes
1 answer

Remove Common columns while Join in sqldf

On doing different joins using "a.*" the columns with same name are also getting adjoined to the main dataset. I need a way to either remove the duplicate columns or not join them at the first place. It would be not be possible to indicate all the…
Ankit Haldar
  • 115
  • 1
  • 2
  • 8
-2
votes
1 answer

R - sqldf - update query

I am currently working on a project where I need to update variables in my dataframe conditionally. I found the simplest way to do this would be to use sqldf/RSQLite. I have tried several code combinations so far and this is the one that I've got…
johubb
  • 152
  • 11
-2
votes
1 answer

Increase the efficiency of the Sqldf operator in R by alternative ways

I'm dealing with a dataset with has data at a day level (one row for one day) - with data for a product (that is being advertised) on whether it is being seen by the user or if an action is being taken. Before running my analysis on this data set in…
Pratyush Tallapragada
  • 49
  • 7
-2
votes
1 answer

Subset dataframe with sqldf

I try to subset dataframe using sqlfd but it doesn't work. Can someone explain why? I have processed like this. library(sqldf) dataf <- read.csv("zert.csv") agep & pwgt1 are columns of dataf dd <- sqldf("select * from dataf where AGEP < 50 and…
rodrigue djouontu
  • 1
1 2 3
…
45
46