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

R, issue with sqldf: cannot make condition on date

I have a R dataframe with a field date (type date), i want to query this dataframe using sqldf library, but the where condition doesn't seem to work on the date field. The query I'm using is: sqldf("select * from elog where date >=…
woshitom
  • 4,811
  • 8
  • 38
  • 62
0
votes
1 answer

R- SQLDF - SELECT ... CASE... END

Hello, I am trying to run a query that use a CASE statement from R. I am using the SQLDF package. The same query works fine in Mysql. The query gives the values of 0 or 1 to "express" according to the following situations: Select "express"…
shean10000
  • 25
  • 1
  • 1
  • 7
0
votes
1 answer

How to use fn$ within a for loop

I am unable to use fn$ within a for loop. When I test it with one variable, (x = "a_ind") I am able to successfully run a loop. However, when I add multiple variables via (c("a_ind",...)) it no longer works. I have been unable to find a solution…
Chris L
  • 338
  • 1
  • 4
  • 15
0
votes
2 answers

Trouble Finding ID's with Duplicate Fields

My data looks like this: ID Email 1 someone@hotmail.com 2 someone1@hotmail.com 3 someone2@hotmail.com 4 someone3@hotmail.com 5 someone4@hotmail.com 6 someone5@hotmail.com There should be exactly 1 email per ID, but there's not. >…
Hack-R
  • 22,422
  • 14
  • 75
  • 131
0
votes
2 answers

SQLite wildcards in Join

In a previous question of mine it appears as though my problem is SQLite dependent. I have simplified the problem as follows: Say I have two tables: table1 as follows Name | col1 ------------------------ Bob | B1 Mary Jane| M1 and table2…
Geoff
  • 925
  • 4
  • 14
  • 36
0
votes
1 answer

R! posIXCT in sqldf

first time question, so if I missed something I apologize: I imported an excel file into R! using XLconnect, the str() function is as follow: data.frame': 931 obs. of 5 variables: $ Media : chr "EEM" "EEM" "EEM" "EEM" ... $ Month …
user4313679
  • 1
  • 1
0
votes
1 answer

Shortest time and trip between two stops across many trip options (GTFS)

I am using a GTFS relational database to find the fastest trip time offered between two given points. The fields I have are trip_id, arrival_time and departure_time (in minutes from midnight), and stop_id A tranche of the data might be: trip_id |…
gren
  • 17
  • 3
0
votes
1 answer

Joining columns from a data frame to another on conditions/WHERE

I am trying to figure out how to join/append a column of values from one column to another in R based upon criteria. I'm sure this is obvious but I'm having trouble trying to teach myself how to do this. If someone could show me both how to do…
runningbirds
  • 6,235
  • 13
  • 55
  • 94
0
votes
0 answers

Passing na.strings to dbWriteTable

I'm trying to run the following command in R in order to read a local tab-delimited file as a SQLite database: library(RSQLite) banco <- dbConnect(drv = "SQLite", dbname = "data.sqlite") dbWriteTable(conn = banco, …
Waldir Leoncio
  • 10,853
  • 19
  • 77
  • 107
0
votes
1 answer

How to Use R to Manipulate Data

I'm trying to do a predictive analysis where metrics from year x will predict year x+1. I want to do the equivalent of an UPDATE query in SQL using R. If I have data like this: x <- c("Randy Watson", "Cleo McDowell", "Darryl Jenks", "Jaffe…
Frank B.
  • 1,813
  • 5
  • 24
  • 44
0
votes
4 answers

SQL query: convert

I'm trying to read a column from a database using a SQL query. The column consists of empty string or numbers as strings, such as "7500" "4460" "" "2900" "2640" "1850" "" "2570" "9050" "8000" "9600" I'm trying to find the right sql query to…
ABC
  • 125
  • 1
  • 10
0
votes
0 answers

Converting an SQL Statement into R Code Without SQLDF

I'm a new-ish programmer in R and I'm having a bit of an issue with some SQL code. What I want to do is to convert this operation to base R code. I know it's quite complicated and I tried using merge but I didn't really manage to get anywhere. …
user3801193
0
votes
1 answer

sqldf select statements in R not working?

I have a dataframe myframe with 8 variables in R. I then load the sqldf package using library(sqldf). Then I run the command: proxyframe = sqldf("select a, b, c, d, e from myframe where c > 1000") However, when I look at proxyframe using…
mt88
  • 2,855
  • 8
  • 24
  • 42
0
votes
1 answer

pass a column name correctly as a function argument for SQL query in R

I have a datafile sales_history. I want to query it in the following way. my_df<-sqldf("SELECT * FROM sales_history WHERE Business_Unit=='RETAIL'"") Now I want to write a function with argument datafile and…
user3698033
  • 39
  • 5
0
votes
1 answer

Compare input value from screen in SQLDF where clause

I am new to Shiny and R both. I am trying to capture input value from Shiny UI part and based on the value, I am filtering the records from dataframe. I am using this code: dat <- sqldf("select * from dat where CMPNT_NM = 'input$Compound'") It's…
Piyush
  • 1,571
  • 4
  • 14
  • 21
1 2 3
…
45 46