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

Filtering Data by multiple value SQL

i like to query/filter some data with multiple value using sql query. but i got nothing from it. here is my code. gd = cal.get_date() dfd = datetime.strptime(gd, '%m/%d/%y').strftime('%Y-%m-%d') slspath = ('C:\\shgcgr\\RMWIN b4 patch 12.22.21\\SLS'…
Arvin Tuzki
  • 19
  • 6
1
vote
2 answers

Input all row values of dataframe into a variable

is there a way to get all output values of rows in dataframe into a variable? my code is like this.. q1 = ps.sqldf("SELECT sdetframe.bill_no AS 'bill_number', sdetframe.ref_no AS 'ref_number', sdetframe.price_paid AS 'price_paid',…
Arvin Tuzki
  • 19
  • 6
1
vote
0 answers

sqldf won't read values above 10

I use sqldf to select my data. Here's my code: sqldf(''' SELECT * FROM df ORDER BY PTS DESC LIMIT 5 ''') Results: |PTS| |9.9| |9.9| |9.9| |9.9| |9.9| In the data frame, there are values more than 10 such as 30.5, 25.2, etc, but it seems like sqldf…
bballcoder
  • 19
  • 4
1
vote
1 answer

R SQLDF extract only a value

I have read in a CSV file and want to run a query on it The query is in the form count_a = SELECT sum(A) as COLS FROM file where year = "2012" I use this: sum_of_a = all_data = read.csv.sql(data_all, count_a) I get back COLS 1 85221 When I…
Joseph
  • 541
  • 1
  • 4
  • 31
1
vote
1 answer

Why does read.csv2 work just fine, yet read.csv2.sql shows an error/warning?

I am trying to read a csv file in R using read.csv2.sql, since I would like to use a SELECT query from SQL to help me filter my data, but before I can even get to my SELECT query, I discovered that simply reading my csv file using read.csv2.sql…
Ethan Mark
  • 293
  • 1
  • 9
1
vote
1 answer

Issue in running sqldf command for comparing integers

I am working on simple data like below: teacher student 12 409 43 403 12 415 12 409 67 311 19 201 I am trying to retrieve the entries where teacher = 12 and student = 409. I am using the following…
Sandy
  • 1,100
  • 10
  • 18
1
vote
1 answer

sqldf only returning one row, same query used in SQL

For some reason I'm only returning one row when it comes to R while at SQL Server, I'm returning the correct number of rows. SQLDF: CustomerCodingChangesT <- sqldf(" SELECT c.CustID as ID , c.ReverseSupplier as Supplier …
Chardiezard Gaming
  • 23
  • 3
1
vote
1 answer

Merging two data frame based on maximum numbers of words in commonin R

I have two data.frame one containing partial name and the other one containing full name as follow partial <- data.frame( "partial.name" = c("Apple", "Apple", "WWF", "wizz air", "WeMove.eu", "ILU") full <- data.frame("full.name" = c("Apple Inc",…
JMCrocs
  • 77
  • 7
1
vote
3 answers

How to get same grouping results using dplyr to get result consistent with sqldf result?

I try to implement SQL query using sqldf and dplyr. I need to do this separately using these 2 different libraries. Unfortunately, I cannot produce the same result using dplyr. library(sqldf) library(dplyr) Id <- c(1,2,3,4) HasPet <-…
lol lol
  • 319
  • 3
  • 18
1
vote
1 answer

Filtering with sqldf in R when fields have quotation marks

I have a large sql db (7gbs), where the fields appear to have quotation marks in them. For example: res <- dbSendQuery(con, " SELECT * FROM master") dbf2 <- fetch(res, n = 3) dbClearResult(res) Yields NPI …
Aaron
  • 109
  • 5
1
vote
1 answer

Selecting values from one column and passing corresponding values from another column in MySQL queries in Shiny

I have a Shiny app querying large data from a MySQL database but for purposes of this question I will use SQLDF which is similar in syntax on Shiny environment. A mock up of my app is as…
R noob
  • 495
  • 3
  • 20
1
vote
2 answers

Convert non-missing date to missing in R

I have a data frame and I want to replace a date column of only the first row of each ID to missing based on a condition. library(data.table) table1 <- read.table(" id date1 var 1 01/02/1992 2 1 02/03/1992 1 1 01/03/1992 2 2 06/05/1992 …
Pam
  • 111
  • 6
1
vote
4 answers

Group by percentage in R is not same with Excel

I am trying to calculate the percentage of amount each account from the total amount in R. But i am not getting the right value when i compared with excel.i believe there is some missing in my logic. > dput(test) structure(list(Account_new =…
Numita
  • 141
  • 8
1
vote
1 answer

update join pandas - python

How to achieve update join on pandas native statement (.apply())? I want to update PSMark from Dataframe2 on PhySci in Dataframe1 Dataframe1 (tbl_ex): | | Sname | Tamil | English | Maths | Science | Sscience | PhySci…
goldminerz
  • 13
  • 3
1
vote
1 answer

How to use LIKE in SQLDF in R for searching text in multiple dataframes

I have 2 dataframes with text and want to compare partial string. What I am looking for is Id, t1.mo and monthid from table 2. t1 <- data.frame("id"=1:4,"mo"=c("Jan","Feb","Apr","Mar")) t2 <- data.frame("id"=1:4, "nt"= c("January","Jan…
Vithal Rd
  • 69
  • 1
  • 7
1 2 3
…
45 46