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

Parse Dataframe string column into Street, City, State & Zip code

I am trying to break up the following fixed string into several columns as street ,city, state & zip code. Is it possible to do this in SQLDF via the INSTR & Subtr method? Sample Address String. The difficult part is the NV and zip code parsing.…
user2100493
  • 1,258
  • 4
  • 15
  • 26
2
votes
1 answer

sqldf re-use same connection

Is it possible for sqldf re-use same connection for all the queries? I've an application where I need to set session time zone. I tried sqldf("SET SESSION TIME ZONE 'US/Hawaii'"). Looks like each query is closing and opening new connection to…
kesavkolla
  • 459
  • 5
  • 14
2
votes
2 answers

Convert aggregate function into user defined function

I have created a dataset in R as follows: m <- mtcars m$dep<- ifelse(m$mpg <=16,1,0) Now if I try to sum the variable dep as per the group done on the basis of cyl a <-aggregate(dep_var~ cyl, FUN=sum, data=m) a I get the desired result. However,…
shejomamu
  • 141
  • 2
  • 13
2
votes
2 answers

select column according to value of another column in R

I am working on a dataset (namely w3) in R that looks like this: Q1 Q2 Q3 Q4 WorksheetID UserID 395 2178 2699 1494 3 65 395 2178 2694 1394 3 78 395 1178 2699 1394 3 79 395 278 2699 1394 3 …
tiffkyn
  • 79
  • 1
  • 7
2
votes
1 answer

R- sqldf error raw vs double

I have a vector lims, with the limits of a score: [1] 0.000000 7.025894 9.871630 12.411131 15.155998 18.099176 21.431354 25.391163 30.616550 40.356630 I create a table to classify other clients with: lims[1]<- -0.00001…
GabyLP
  • 3,649
  • 7
  • 45
  • 66
2
votes
1 answer

sqldf deleting observations glitch

I am using sqldf to delete three observations like this below. del_query <- sprintf("DELETE from data_table where ID_NUMBER IN (%s)", paste(( '8254700', …
King Frazier
  • 243
  • 3
  • 14
2
votes
3 answers

Improve run-time of loop

I am trying to increase the computational efficiency of below process. I have created toy example using data for review. The first method runs in half the time of the second method. How can I improve upon the run-time in the…
quant actuary
  • 75
  • 6
2
votes
0 answers

Problems with read.csv.sql when data has commas

I'm trying to read a .csv file like the following: "Name", "Address" "Me", "My address, City, State" When I try read.csv.sql(file = "myFile.csv", sql = "select * from file") I get an error: Error in .local(conn, name, value, ...) : …
random_forest_fanatic
  • 1,232
  • 1
  • 12
  • 30
2
votes
1 answer

sqldf: How to query based on a date condition

I have spent a couple of hours researching this but I am getting nowhere unfortunately. I am trying to get a subset of data by using sqldf to query a data frame, result. This is the structure of result: > str(result) 'data.frame': 316125 obs. of …
billelev
  • 369
  • 2
  • 13
2
votes
2 answers

r column values in sql where statement

I have a dataset and I am trying to pass the contents of a specific column into the SQL where statement. For example, assuming iris is my dataset data(iris) head(iris) Sepal.Length Sepal.Width Petal.Length Petal.Width Species …
Ernest Presley
  • 171
  • 10
2
votes
1 answer

How to multiply in sqldf statements using an external float variable

I have problems multiplying numbers of an external variable in sqldf In the projection of the SQL statement I would like to multiply the value of a column with an external float variable input$euro (e.g. 0.23). data <- read.csv("consumption1.csv",…
user1529973
  • 51
  • 1
2
votes
2 answers

Encoding of Umlauts while importing into R tables

I need to join two tables on the colum name. They come from different excel spreadsheets which I didn't create. I imported both sheets into R tables using the function read.xls from the gdata package. Sometimes the names contains umlauts and other…
Geoff
  • 925
  • 4
  • 14
  • 36
2
votes
2 answers

Create table, by selecting from data frames when a match occurs

I have three data frames dbase, data and dcode. I want to copy the rows from dbase to data when dbase$idbase = data$iddata and add based on dcode the names of dcode when dbase$nr matches the number in dcode. data <- structure(list(iddata =…
Adam
  • 434
  • 1
  • 4
  • 18
2
votes
2 answers

Using read.csv.sql to select multiple values from a single column

I am using read.csv.sql from the package sqldf to try and read in a subset of rows, where the subset selects from multiple values - these values are stored in another vector. I have hacked a way to a form that works but I would like to see the…
user2957945
  • 2,353
  • 2
  • 21
  • 40
2
votes
2 answers

Use values in df column to create a sql query

I would like to take the values from a data frame and paste them into a text string that can be used as a sql query. In SAS I would do it proc sql noprint; Select Names into :names separated by ", " from df; quit; this would create a variable…
muraenok
  • 95
  • 1
  • 9
1 2 3
…
45 46