Re: [R] sqldf returns Error: database or disk is full

empty cells that consumes a great deal more memory. -Original Message- From: R-help On Behalf Of Ian Worthington via R-help Sent: Monday, June 27, 2022 5:06 PM Cc: R-help Subject: Re: [R] sqldf returns Error: database or disk is full [External Email] Well, iiuc the df is copied

Re: [R] sqldf and number of records affected

t;))) sqldf("select * from main.con") sqldf() -Original Message- From: Gabor Grothendieck [mailto:ggrothendi...@gmail.com] Sent: Thursday, June 11, 2020 10:30 AM To: Ravi Jeyaraman Cc: r-help@r-project.org Subject: Re: [R] sqldf and number of records affected There is no r

Re: [R] sqldf and number of records affected

seem to work ... > > con <- data.frame(V1 = c(1, 2, 3, 4, 5, 6, 7, 8, 9, 10)) > sqldf() > sqldf(c("pragma count_changes = 1", "update con set V1 = 0 where V1 > 5 ")) > ans <- sqldf("select * from main.con") > sqldf() > > -Original

Re: [R] sqldf and number of records affected

f(c("pragma count_changes = 1", "update con set V1 = 0 where V1 > 5 ")) ans <- sqldf("select * from main.con") sqldf() -Original Message- From: Gabor Grothendieck [mailto:ggrothendi...@gmail.com] Sent: Thursday, June 11, 2020 9:12 AM To: Ravi Jeyaraman C

Re: [R] sqldf and number of records affected

Here is an example. Ignore the warning or use the workaround discussed here https://github.com/ggrothendieck/sqldf/issues/40 to avoid the warning. library(sqldf) sqldf() # use same connection until next sqldf() sqldf(c("pragma count_changes = 1", "update BOD set demand = 99 where Time >

[R] sqldf and number of records affected

Hello all, When I execute a SQL using SQLDF, how do I get the number of records affected? I mean, if I run an UPDATE on a data frame, it doesn't tell me if and how many records got updated. I've read through the documentation and there don't seem to be a way to get this info unless it's done on

Re: [R] sqldf --Warning message:

sqldf does not use Tk so you can ignore this. On Fri, Feb 19, 2016 at 12:32 PM, Divakar Reddy wrote: > Dear R users, > > I'm getting Waring message while trying to load "sqldf" package in R3.2.3 > and assuming that we can ignore this as it's WARNING Message and not an >

[R] sqldf --Warning message:

Dear R users, I'm getting Waring message while trying to load "sqldf" package in R3.2.3 and assuming that we can ignore this as it's WARNING Message and not an error message. Can you guide me if my assumption is wrong? > library(sqldf); Loading required package: gsubfn Loading required package:

Re: [R] sqldf() difference between R 3.1.2 and 3.0.1

-project.org Subject: Re: [R] sqldf() difference between R 3.1.2 and 3.0.1 On Wed, Feb 11, 2015 at 9:45 AM, Doran, Harold hdo...@air.org wrote: I have a function written and tested using R 3.0.1 and sqldf_0.4-7.1 that works perfectly. However, using this same code with R 3.1.2 and sqldf_0.4-10 yields

Re: [R] sqldf() difference between R 3.1.2 and 3.0.1

On Wed, Feb 11, 2015 at 9:45 AM, Doran, Harold hdo...@air.org wrote: I have a function written and tested using R 3.0.1 and sqldf_0.4-7.1 that works perfectly. However, using this same code with R 3.1.2 and sqldf_0.4-10 yields the error below that I am having a difficult time deciphering.

[R] sqldf() difference between R 3.1.2 and 3.0.1

I have a function written and tested using R 3.0.1 and sqldf_0.4-7.1 that works perfectly. However, using this same code with R 3.1.2 and sqldf_0.4-10 yields the error below that I am having a difficult time deciphering. Hence, same code behaves differently on different versions of R and

[R] sqldf problems

Hello: I'm trying to run this code data2 - sqldf ( SELECT plot, age, avg(N) as N FROM data1 GROUP BY plot, t) The problem is that when calling sqldf with require(sqldf) appears this message Warning message: In sqliteCloseConnection(conn, ...) : RS-DBI driver warning: (closing pending result

Re: [R] sqldf problems

2014-07-14 7:52 GMT-04:00 Juan Daniel García juandaniel...@gmail.com: Hello: I'm trying to run this code data2 - sqldf ( SELECT plot, age, avg(N) as N FROM data1 GROUP BY plot, t) The problem is that when calling sqldf with require(sqldf) appears this message Warning message: In

Re: [R] sqldf problems

One doesn't call a package... so your description is unclear. One doesn't load (using the require or library functions) the relevant package AFTER calling functions in the package. Since it appears you have left out some steps or described them out of order, please supply a reproducible example

[R] SQLDF column errors

I am trying to exclude integer values from a small data frame 1, d1 that have matching hits in data frame 2, d2 (Very big) which involves matching those hits first. I am trying to use sqldf on the df's in the following fashion: df1: V1 12675 14753 16222 18765 df2: head(df2) V1 V2

Re: [R] SQLDF column errors

i think this is what you want.. :) http://stackoverflow.com/questions/4765936/using-joined-tables-to-exclude-certain-records library(sqldf) # use the mtcars example table mtcars # keep the first eight records in a second, separate data set x - mtcars[ 1:8 , ] # keep all the records from

Re: [R] sqldf package: using variables in where condition

Sent from my iPhone On Jan 13, 2013, at 9:47 PM, ravsre ravishre...@hotmail.com wrote: I am trying to use the sqldf package to create independent data frames from a master dataframe. I want to use sqldf package and perform a simple select statement. However, what I want to do is to

Re: [R] sqldf package: using variables in where condition

On Mon, Jan 14, 2013 at 8:36 AM, Ravi Sreedhar ravishre...@hotmail.com wrote: Sent from my iPhone On Jan 13, 2013, at 9:47 PM, ravsre ravishre...@hotmail.com wrote: I am trying to use the sqldf package to create independent data frames from a master dataframe. I want to use sqldf package

[R] sqldf merging with subset in specific range

Hi all: I have two data sets. Set A includes a long list of hits in a single column, say: m$V1 10 15 36 37 38 44 45 57 61 62 69 ...and so on Set B includes just a few key ranges set up by way of a minimum in column X and a maximum in column Y. Say, n$X n$Y 30 38 # range from 30 to 38 52

Re: [R] sqldf merging with subset in specific range

Is this what you want: m - read.table(text = 10 + 15 + 36 + 37 + 38 + 44 + 45 + 57 + 61 + 62 + 69 ) n - read.table(text = 30 38 + 52 62 ) require(sqldf) sqldf(select m.V1 + from m, n + where m.V1 between n.V1 and n.V2 + ) V1 1 36 2 37 3 38 4 57 5 61 6 62 On Wed,

Re: [R] sqldf Date problem

Zitat von jim holtman jholt...@gmail.com: Most likely your Date is either a character or a factor (you need to provide an 'str' of the dataframe). You are therefore most likely doing a character compare and that is the reason for your problem. You need to convert to a character string of the

[R] sqldf Date problem

Dear R-help readers, i've created a database for quotes data (for 4 years; 2007 -- 2010) with the sqldf package. This database contains a column Date in the format mm/dd/. The table in the database is called main.data and the database itself Honda. I tried to get the Data just for

[R] sqldf Date problem

Dear R-help readers, i've created a database for quotes data (for 4 years; 2007 -- 2010) with the sqldf package. This database contains a column Date in the format mm/dd/. The table in the database is called main.data and the database itself Honda. I tried to get the Data just for

Re: [R] sqldf Date problem

Most likely your Date is either a character or a factor (you need to provide an 'str' of the dataframe). You are therefore most likely doing a character compare and that is the reason for your problem. You need to convert to a character string of the format -MM-DD to do the correct character

Re: [R] sqldf for Very Large Tab Delimited Files

Hi, On Fri, Feb 3, 2012 at 1:12 PM, HC hca...@yahoo.co.in wrote: Thank you. The readLines command is working fine and I am able to read 10^6 lines in one go and write them using the write.table command. Does this readLines command using a block concept to optimize or goes line by line?

Re: [R] sqldf for Very Large Tab Delimited Files

Thank you Jim for your reply. I could figure out that readLines works fine until 35,841,335 lines (records). When the next line is read to be read, a window with R for Windows GUI front-end has stopped working message comes, with an option to close program or checking online for a solution.

Re: [R] sqldf for Very Large Tab Delimited Files

Thank you for indicating that SQLite may not handle a file as big as 160 GB. Would you know of any utility for *physically splitting *the 160 GB text file into pieces. And if one can control the splitting at the end of a record. Thank you again. HC -- View this message in context:

Re: [R] sqldf for Very Large Tab Delimited Files

On Fri, Feb 3, 2012 at 6:03 AM, HC hca...@yahoo.co.in wrote: Thank you for indicating that SQLite may not handle a file as big as 160 GB. Would you know of any utility for *physically splitting *the 160 GB text file into pieces. And if one can control the splitting at the  end of a record.

Re: [R] sqldf for Very Large Tab Delimited Files

On Fri, Feb 3, 2012 at 7:37 AM, Gabor Grothendieck ggrothendi...@gmail.com wrote: On Fri, Feb 3, 2012 at 6:03 AM, HC hca...@yahoo.co.in wrote: Thank you for indicating that SQLite may not handle a file as big as 160 GB. Would you know of any utility for *physically splitting *the 160 GB text

Re: [R] sqldf for Very Large Tab Delimited Files

This is a 160 GB tab-separated .txt file. It has 9 columns and 3.25x10^9 rows. Can R handle it? Thank you. HC -- View this message in context: http://r.789695.n4.nabble.com/sqldf-for-Very-Large-Tab-Delimited-Files-tp4350555p4354556.html Sent from the R help mailing list archive at

Re: [R] sqldf for Very Large Tab Delimited Files

On Fri, Feb 3, 2012 at 8:08 AM, HC hca...@yahoo.co.in wrote: This is a 160 GB tab-separated .txt file. It has 9 columns and 3.25x10^9 rows. Can R handle it? You can process a file N lines at time like this: con - file(myfile.dat, r) while(length(Lines - readLines(con, n = N)) 0) { ...

Re: [R] sqldf for Very Large Tab Delimited Files

Thank you. The readLines command is working fine and I am able to read 10^6 lines in one go and write them using the write.table command. Does this readLines command using a block concept to optimize or goes line by line? Steve has mentioned about *nix and split commands. Would there be any

Re: [R] sqldf for Very Large Tab Delimited Files

Bad news! The readLines command works fine upto a certain limit. Once a few files have been written the R program crashes. I used the following code: * iFile-Test.txt con - file(iFile, r) N-125; iLoop-1 while(length(Lines - readLines(con, n = N)) 0 iLoop41) {

Re: [R] sqldf for Very Large Tab Delimited Files

Exactly what does crashed mean? What was the error message? How you tried to put: rm(Lines) gc() at the end of the loop to free up and compact memory? If you watch the performance, does the R process seem to be growing in terms of the amount of memory that is being used? You can add:

Re: [R] sqldf for Very Large Tab Delimited Files

On Wed, Feb 1, 2012 at 11:57 PM, HC hca...@yahoo.co.in wrote: Hi All, I have a very (very) large tab-delimited text file without headers. There are only 8 columns and millions of rows. I want to make numerous pieces of this file by sub-setting it for individual stations. Station is given as

Re: [R] sqldf for Very Large Tab Delimited Files

On Thu, Feb 2, 2012 at 3:11 AM, Gabor Grothendieck ggrothendi...@gmail.com wrote: On Wed, Feb 1, 2012 at 11:57 PM, HC hca...@yahoo.co.in wrote: Hi All, I have a very (very) large tab-delimited text file without headers. There are only 8 columns and millions of rows. I want to make numerous

Re: [R] sqldf for Very Large Tab Delimited Files

Hi Gabor, Thank you very much for your guidance and help. I could run the following code successfully on a 500 mb test data file. A snapshot of the data file is attached herewith. code start*** library(sqldf) library(RSQLite) iFile-Test100.txt con -

Re: [R] sqldf for Very Large Tab Delimited Files

On Thu, Feb 2, 2012 at 8:07 PM, HC hca...@yahoo.co.in wrote: Hi Gabor, Thank you very much for your guidance and help. I could run the following code successfully on a 500 mb test data file. A snapshot of the data file is attached herewith. code start*** library(sqldf)

[R] sqldf for Very Large Tab Delimited Files

Hi All, I have a very (very) large tab-delimited text file without headers. There are only 8 columns and millions of rows. I want to make numerous pieces of this file by sub-setting it for individual stations. Station is given as in the first column. I am trying to learn and use sqldf package for

[R] sqldf + Date class. Ordering and summary statistics appear to be incorrect.

I've been using sqldf heavily lately but have encountered problems with ordering of observations or calculating statistics such as max() and min() when the variable used is of class Date. For example, if I run the following code: === begin code = library(sqldf)

Re: [R] sqldf + Date class. Ordering and summary statistics appear to be incorrect.

On Sun, Jan 22, 2012 at 10:59 PM, Grant Farnsworth gvfa...@gmail.com wrote: I've been using sqldf heavily lately but have encountered problems with ordering of observations or calculating statistics such as max() and min() when the variable used is of class Date. For example, if I run the

Re: [R] sqldf + Date class. Ordering and summary statistics appear to be incorrect.

On Mon, Jan 23, 2012 at 12:46 AM, Gabor Grothendieck ggrothendi...@gmail.com wrote: On Sun, Jan 22, 2012 at 10:59 PM, Grant Farnsworth gvfa...@gmail.com wrote: I've been using sqldf heavily lately but have encountered problems with ordering of observations or calculating statistics such as

[R] sqldf and not converting integers to floating point in SQLite

Hi, I have following 2 tables: Table 1: POSTAL | VALUE 1000|49 1010|100 1020|50 Table 2: INSEE | POSTAL A|1000 B|1000 C|1010 D|1020 I would like to convert this to the following: INSEE | VALUE_SPREAD A|24.5 B|24.5 C|100 D|50 I can achieve this with a nested SQL query (through counting the

Re: [R] sqldf and not converting integers to floating point in SQLite

try this: library(sqldf) table1 - read.csv(text = POSTAL | VALUE + 1000|49 + 1010|100 + 1020|50, sep=|) table2 - read.csv(text = INSEE | POSTAL + A|1000 + B|1000 + C|1010 + D|1020, sep=|) table3 - sqldf( + select table2.INSEE + , 1.0 * table1.VALUE / counts.nPostals as

[R] sqldf if iif

Dear all, I have problems with iif function using sqldf library. I counted abundance (Num) of different SPECIES in two moments (esf) saving the information in two Tables (esf50, esf100): esf50 SAMPLE SPECIES Num esf 1289diso1 44

Re: [R] sqldf if iif

sqldf uses the SQLite database by default for backend processing. The iif function is specific to the Jet database engine syntax (which underlies MS Access). You could read up on SQLite syntax, or you could avoid using nonstandard SQL syntax, retrieve the data into a data frame, and use R code

[R] sqldf syntax, selecting rows, and skipping

I am using the example in this post: https://stat.ethz.ch/pipermail/r-help/2010-October/257204.html # create a file write.table(iris,iris.csv,row.names=FALSE,sep=,,quote=FALSE) # this does not work # has the syntax changed or is there a mistake in my usage? # the line from the post above is:

Re: [R] sqldf syntax, selecting rows, and skipping

On Sep 29, 2011, at 10:06 AM, Juliet Hannah wrote: I am using the example in this post: https://stat.ethz.ch/pipermail/r-help/2010-October/257204.html # create a file write.table(iris,iris.csv,row.names=FALSE,sep=,,quote=FALSE) # this does not work # has the syntax changed or is there a

[R] SQldf with sqlite and H2

SQldf with sqlite and H2 I have a large csv file (about 2GB) and wanted to import the file into R and do some filtering and analysis. Came across sqldf ( a great idea and product) and was trying to play around to see what would be the best method of doing this. csv file is comma delimited with

Re: [R] SQldf with sqlite and H2

On Thu, Jul 14, 2011 at 10:33 AM, Mandans mandan...@yahoo.com wrote: SQldf with sqlite and H2 I have a large csv file (about 2GB) and wanted to import the file into R and do some filtering and analysis. Came across sqldf ( a great idea and product) and was trying to play around to see what

Re: [R] SQldf with sqlite and H2

Thanks a lot Gabor. It helped a lot. Appreciate your time and effort. Thanks --- On Thu, 7/14/11, Gabor Grothendieck ggrothendi...@gmail.com wrote: From: Gabor Grothendieck ggrothendi...@gmail.com Subject: Re: [R] SQldf with sqlite and H2 To: Mandans mandan...@yahoo.com Cc: r-help@r

[R] Sqldf INSERT INTO

Hi, I am new to R and trying to migrate from SAS. I am trying to copy data from one table to another table which have same columns using sqldf. but not working and showing NULL I wrote statement as sqldf(INSERT INTO new select * from data) but showing NULL Please help me in this regard. Thank

Re: [R] Sqldf INSERT INTO

On Wed, Apr 20, 2011 at 12:39 PM, new2R bv_agr...@yahoo.co.in wrote:  Hi, I am new to R and trying to migrate from SAS. I am trying to copy data from one table to another table which have same columns using sqldf. but not working and showing NULL I wrote statement as sqldf(INSERT INTO new

Re: [R] SQLDF syntax

On Mon, Apr 18, 2011 at 6:34 PM, new2R bv_agr...@yahoo.co.in wrote: Hi, I am new to R and trying to migrate from SAS. I am trying to use sqldf to create a new table from existed table and change some of the columns. I have table called DataOld with columns commodity, rate and total and I am

Re: [R] SQLDF syntax

Thank you very much. Its working. -- View this message in context: http://r.789695.n4.nabble.com/SQLDF-syntax-tp3458919p3460448.html Sent from the R help mailing list archive at Nabble.com. __ R-help@r-project.org mailing list

[R] SQLDF syntax

Hi, I am new to R and trying to migrate from SAS. I am trying to use sqldf to create a new table from existed table and change some of the columns. I have table called DataOld with columns commodity, rate and total and I am trying to create new table called DataNew with columns commodity,

[R] SQLDF - Submitting Queries with R Objects as Columns

Fellow R programmers, I'd like to submit SQLDF statements with R objects as column names. For example, I want to assign X to var1 (var1-X) and then refer to var1 in the SQLDF statement. SQLDF needs to understand that when I reference var1, it should look for X in the dataframe. This is

Re: [R] SQLDF - Submitting Queries with R Objects as Columns

On Wed, Mar 9, 2011 at 11:41 AM, Mike Schumacher mike.schumac...@gmail.com wrote: Fellow R programmers, I'd like to submit SQLDF statements with R objects as column names. For example, I want to assign X to var1  (var1-X) and then refer to var1 in the SQLDF statement.  SQLDF needs to

Re: [R] SQLDF - Submitting Queries with R Objects as Columns

You're submitting queries for SQLDF to execute as strings. So, if you want to use a variable column name, sprintf() or paste() your statement together, like: sqldf(sprintf('select sum(%s) as XSUM, Y as Y from testdf group by Y', var1)) -- Robert Tirrell | r...@stanford.edu | (607) 437-6532

Re: [R] sqldf hanging on macintosh - works on windows

Marc: Installing Simon's package worked perfectly. Thanks so much! -- View this message in context: http://r.789695.n4.nabble.com/sqldf-hanging-on-macintosh-works-on-windows-tp3022193p3023736.html Sent from the R help mailing list archive at Nabble.com.

[R] sqldf error only on Unix not Windows

Hello Group, I am having trouble with the sqldf package on unix. The same code works fine on windows. Silly Example script: # Load the package library(sqldf) # Use the titanic data set data(women) colnames(women) head(women) sqldf('select height, count(*) from women where

Re: [R] sqldf error only on Unix not Windows

On Mon, Nov 1, 2010 at 9:28 AM, Alex Bryant abry...@i-review.com wrote: Hello Group,                I am having trouble with the sqldf package on unix.  The same code works fine on windows. Silly Example script: # Load the package library(sqldf) # Use the titanic data set data(women)

[R] sqldf hanging on macintosh - works on windows

Have a long script that runs fine on windows (32 bit). When I try to run in on two different macs (64 bit), however, it hangs with identical behavior. I start with: library(sqldf) This results in messages: Loading required package: DBI Loading required package: RSQLite Loading required package:

Re: [R] sqldf hanging on macintosh - works on windows

On Mon, Nov 1, 2010 at 9:59 AM, GL pfl...@shands.ufl.edu wrote: Have a long script that runs fine on windows (32 bit). When I try to run in on two different macs (64 bit), however, it hangs with identical behavior. I start with: library(sqldf) This results in messages: Loading required

Re: [R] sqldf hanging on macintosh - works on windows

added library(RH2) Still get message: Loading required package: tcltk Loading Tcl/Tk interface + directly after sqldf statement df.final - sqldf('select Date, Hour, x as RoomsInUse from df.possible.combos + left join df.aggregate using (Hour, Date)') There is no progress

Re: [R] sqldf hanging on macintosh - works on windows

On Mon, Nov 1, 2010 at 10:32 AM, GL pfl...@shands.ufl.edu wrote: added library(RH2) Still get message: Loading required package: tcltk Loading Tcl/Tk interface + directly after sqldf statement   df.final - sqldf('select Date, Hour, x as RoomsInUse from df.possible.combos +    

Re: [R] sqldf hanging on macintosh - works on windows

library(sqldf) Loading required package: DBI Loading required package: RSQLite Loading required package: RSQLite.extfuns Loading required package: gsubfn Loading required package: proto Loading required package: chron debug(sqldf) df.final - sqldf('select Date, Hour, x as RoomsInUse

Re: [R] sqldf hanging on macintosh - works on windows

On Mon, Nov 1, 2010 at 10:55 AM, GL pfl...@shands.ufl.edu wrote: library(sqldf) Loading required package: DBI Loading required package: RSQLite Loading required package: RSQLite.extfuns Loading required package: gsubfn Loading required package: proto Loading required package: chron

Re: [R] sqldf hanging on macintosh - works on windows

On Nov 1, 2010, at 10:55 AM, Gabor Grothendieck wrote: On Mon, Nov 1, 2010 at 10:55 AM, GL pfl...@shands.ufl.edu wrote: library(sqldf) Loading required package: DBI Loading required package: RSQLite Loading required package: RSQLite.extfuns Loading required package: gsubfn Loading

Re: [R] sqldf hanging on macintosh - works on windows

On Mon, Nov 1, 2010 at 12:10 PM, Marc Schwartz marc_schwa...@me.com wrote: On Nov 1, 2010, at 10:55 AM, Gabor Grothendieck wrote: On Mon, Nov 1, 2010 at 10:55 AM, GL pfl...@shands.ufl.edu wrote: library(sqldf) Loading required package: DBI Loading required package: RSQLite Loading

Re: [R] sqldf syntax

suggestion about data.table and report. Cheers everybody. Stephen B -Original Message- From: Gabor Grothendieck [mailto:ggrothendi...@gmail.com] Sent: Thursday, August 26, 2010 4:26 PM To: Bond, Stephen Cc: r-help@r-project.org Subject: Re: [R] sqldf syntax On Thu, Aug 26, 2010 at 2:31 PM

[R] sqldf syntax

Please correct the following sqldf(update esc left join forwagg on esc.ym=forwagg.Date set esc.ri2=forwagg.N1 where esc.age=12,select * from main.esc) Error in sqliteExecStatement(con, statement, bind.data) : RS-DBI driver: (error in statement: near left: syntax error) Thanks. Stephen

Re: [R] sqldf syntax

On Thu, Aug 26, 2010 at 2:31 PM, Bond, Stephen stephen.b...@cibc.com wrote: Please correct the following sqldf(update esc left join forwagg  on esc.ym=forwagg.Date set esc.ri2=forwagg.N1 where esc.age=12,select * from main.esc) Error in sqliteExecStatement(con, statement, bind.data) :  

Re: [R] SQLDF from Variable Matrix

On Wed, Aug 4, 2010 at 12:29 AM, Suphajak Ngamlak supha...@phatrasecurities.com wrote: Dear all, I would like to do sample statistics, e.g. mean, median from very large dataset. This is part of commands I use routinely with several dataset so I would like to make it into function. The

[R] SQLDF from Variable Matrix

Dear all, I would like to do sample statistics, e.g. mean, median from very large dataset. This is part of commands I use routinely with several dataset so I would like to make it into function. The simplified examples are Test-data.frame(A=c('a','b','c','a','b','c'),B=c(1,2,3,4,5,6)) #Create

Re: [R] sqldf 0.3-5 package or tcltk problem

runs with no problem on my Windows machine. Here is what happens: r=sqldf(select ... ) Loading required package: tcltk Loading Tcl/Tk interface ... Then it never loads. I have X11 open. I have all the latest versions of all the necessary packages for sqldf 0.3-5: DBI 0.2-5 RSQLite 0.9

[R] sqldf 0.3-5 package or tcltk problem

happens: r=sqldf(select ... ) Loading required package: tcltk Loading Tcl/Tk interface ... Then it never loads. I have X11 open. I have all the latest versions of all the necessary packages for sqldf 0.3-5: DBI 0.2-5 RSQLite 0.9-1 RSQLite.extfuns 0.0.1 gsubfn 0.5-3 proto 0.3-8 chron 2.3-35 Although

[R] sqldf modify table

Hi - I am something of a newbie and am a little perplexed. When (trying to) modify a table I issue the following commands with subsequent errors sqldf(alter table Korea drop column code, dbname = mydb) error in statement: near drop: syntax error or sqldf(alter table Korea rename column hyr to

Re: [R] sqldf modify table

On Fri, Jul 16, 2010 at 2:46 PM, PeterTucker pthet...@gmail.com wrote: Hi - I am something of a newbie and am a little perplexed.  When (trying to) modify a table I issue the following commands with subsequent errors sqldf(alter table Korea drop column code, dbname = mydb) error in

[R] sqldf: issues with natural joins

Hello, I'm having trouble discovering what's going wrong with my use of natural joins via sqldf. Following the instructions under 4i at http://code.google.com/p/sqldf/, which discusses creating indices to speed joins, I have been only unreliably able to get natural joins to work. For example,

Re: [R] sqldf: issues with natural joins

There are two problems: 1. A natural join will join all columns with the same names in the two tables and that includes not only Tid but also dfName and since there are no rows that have the same Tid and dfName the result has zero rows. 2. the heuristic it uses fails when you retrieve the same

Re: [R] sqldf: issues with natural joins

Although that works I had meant to write: names(B)[2] - dfNameB # ... other commands sqldf('select * from main.A natural join main.B') so that now only Tid is in common so the natural join just picks it up and also the heuristic works again since we no longer retrieve duplicate column names.

Re: [R] sqldf: issues with natural joins

Thank you very much for these clarifying responses, Gabor. I had mistakenly assumed that creating the index on Tid restricted the natural join to joining on Tid. Can you describe when and how indices speed up joins, or can you point me to resources that address this? Is it only for natural joins

Re: [R] sqldf: issues with natural joins

They work on any join that is able to make use of them. If you preface the select statement with explain query plan then it will give you some info, e.g. sqldf('explain query plan select * from main.A natural join main.B') order from detail 1 00 TABLE

[R] sqldf not joining all the fields

Dear R users, I have two data frames that were read from text files as follows: x_data - read.table(x.txt, header = TRUE, sep = |, quote = \', dec = .,as.is = TRUE,na.strings = NA,colClasses = NA, nrows = 3864284, skip = 0, check.names = TRUE,fill=TRUE,

Re: [R] sqldf not joining all the fields

Can you show the output of dput(x_data) and dput(y_data). On Fri, Mar 12, 2010 at 11:56 AM, Newbie19_02 nvanzuy...@gmail.com wrote: Dear R users, I have two data frames that were read from text files as follows: x_data - read.table(x.txt, header = TRUE, sep = |, quote = \',                

Re: [R] sqldf not joining all the fields

http://n4.nabble.com/file/n1590804/feb09_267_presc_items_tsf.txt feb09_267_presc_items_tsf.txt is the total file for y so if I use the command line with the total data for y then I get the output specified in z Thanks, Natalie -- View this message in context:

Re: [R] sqldf not joining all the fields

dput(x_data) structure(list(prochi = c(CAO713, CAO713, CAO713, CAO713, CAO713, CAO713, CAO713, CAO713, CAO713, CAO713), prescribed_date = c(22/06/2001, 28/04/2000, 10/04/2000, 03/07/2000, 09/01/2001, 16/10/2001, 16/08/2001, 17/09/1993, 01/05/2001,

Re: [R] sqldf not joining all the fields

y_data - read.table(feb_267_presc_items_tsf.txt, header = TRUE, sep = |, quote = \', dec = .,as.is = TRUE,na.strings = NA,colClasses = NA, nrows = 3864284, skip = 0, check.names = TRUE,fill=TRUE, strip.white = TRUE, blank.lines.skip = TRUE,

Re: [R] sqldf not joining all the fields

Please provide code that I can just copy from your post and paste into my session. Either provide dput output as requested or provide the files on the internet together with code that reads them off the internet. On Fri, Mar 12, 2010 at 12:06 PM, Newbie19_02 nvanzuy...@gmail.com wrote:

Re: [R] sqldf not joining all the fields

What about y_data? On Fri, Mar 12, 2010 at 12:14 PM, Newbie19_02 nvanzuy...@gmail.com wrote: dput(x_data) structure(list(prochi = c(CAO713, CAO713, CAO713, CAO713, CAO713, CAO713, CAO713, CAO713, CAO713, CAO713), prescribed_date = c(22/06/2001,

Re: [R] sqldf not joining all the fields

The y_data file has over 9000 rows in it so I thought it would be more practical to give you the file to download -- View this message in context: http://n4.nabble.com/sqldf-not-joining-all-the-fields-tp1590786p1590833.html Sent from the R help mailing list archive at Nabble.com.

Re: [R] sqldf not joining all the fields

You have now given two different assignments to x_data and none to y_data: The str( from the file access offering: str(x_data) 'data.frame': 2848 obs. of 5 variables: $ item_code : int 100 110 150 160 161 164 200 210 212 220 ... $ name: chr NEONACLEX K NEONACLEX

Re: [R] sqldf not joining all the fields

Sorry! It is the end of the day for me. So dput(x) structure(list(prochi = c(CAO713, CAO713, CAO713, CAO713, CAO713, CAO713, CAO713, CAO713, CAO713, CAO713), prescribed_date = c(22/06/2001, 28/04/2000, 10/04/2000, 03/07/2000, 09/01/2001, 16/10/2001,

Re: [R] sqldf not joining all the fields

Yes, its not clear what data was used. Please provide a single email that contains code that can be copied from R and pasted into another session that will reproduce the problem. x_data - ...dput output goes here... y_data - dput output goes here ... library(sqldf) ... rest of code ... or else:

Re: [R] sqldf not joining all the fields

Sorry! It is the end of the day for me. So dput(x) structure(list(prochi = c(CAO713, CAO713, CAO713, CAO713, CAO713, CAO713, CAO713, CAO713, CAO713, CAO713), prescribed_date = c(22/06/2001, 28/04/2000, 10/04/2000, 03/07/2000, 09/01/2001, 16/10/2001,

Re: [R] sqldf not joining all the fields

If I assign the file input to y_data and change you sqldf to z - sqldf(select * from x_data left join y_data using (item_code)); z I can replicate your result. Even after changing the types of the two item_code fields to match I still get the same result and when I see to what degree

[R] sqldf

Hello, I would like some help with sqldf syntax. Suppose I have table 1 and table 2. What do I have to do to generate a table with columns 2,5,6 from table 1 (for example), and columns 3,4,5,9 from table 2, but only when values in column 2 from table 1 are equal to values in column

Re: [R] sqldf

Google for sql join and see the examples in Example 4 on the sqldf home page: http://code.google.com/p/sqldf/#Example_4._Join On Thu, Nov 19, 2009 at 2:30 PM, JoK LoQ jok...@gmail.com wrote: Hello,   I would like some help with sqldf syntax.   Suppose I have table 1 and table 2.   What do

[R] sqldf with date (class date) variables ?

Hello, I am having trouble with retrieving some data in queries involve with variables with data type date. See the enclosed example: ll-c(21DEC2006,15DEC2006) ss-data.frame(ll) ss-transform(ss,ll=as.date(as.character(ll))) (ss) ll 1 17156 2 17150 tt-sqldf(select ll from ss) (tt) ll 1

  1   2   >